Re: PGXAConnection and autocommit problem - Mailing list pgsql-jdbc

 On Mon, 21 Feb 2011 10:05:04 +0000, Maurin, Marion wrote:
> I actually tried to see if I could see which calls were made and how,
> but I didn't succeed in seeing any logs. My breakpoints do not work
> for the driver classes (though it works for any other class), and the
> logger.logDebug() of the PGXAConnection always returns false (Though
> the debug mode is activated in log4j.xml and in the driver conf), so
> I
> can't see anything logged in those classes.
>
> Spring starts and closes the transaction itself. I'm not doing
> anything but setting a "@transactional" annotation on the method I
> want to encapsulate with a transaction. I use a single transaction in
> which there are select calls on several XA datasources.
>
> -----Message d'origine-----
> De : Radosław Smogura [mailto:rsmogura@softperience.eu]
> Envoyé : samedi 19 février 2011 11:47
> À : pgsql-jdbc@postgresql.org
> Cc : Maurin, Marion; Achilleas Mantzios
> Objet : Re: [JDBC] PGXAConnection and autocommit problem
>
> "Maurin, Marion" <marion.maurin@logica.com> Thursday 17 February 2011
> 14:02:28
>> Hi,
>>
>> I checked the two links you proposed.
>> Concerning this one:
>>
>> http://archives.postgresql.org/message-id/201003021629.07368.achill%40matr
>> ix.gatewaynet.com The problem of autocommit described in the
>> conclusion
>> seemed to be caused by the reuse of a connexion by different
>> transactions.
>> So I tried to run my tests while assuring that the connection used
>> was
>> really a new one (checked the ids in debug mode), and nothing
>> changed for
>> me. Thus I don't think it is the same problem I'm facing. I also
>> followed
>> the advice of activating the debug mode for postgresql logs, and I
>> got
>> that: -----
>> 18:15:22.623 (1) simple execute,
>>
>> handler=org.postgresql.jdbc2.AbstractJdbc2Statement$StatementResultHandler
>> @d458ab, maxRows=0, fetchSize=0, flags=17 18:15:22.623 (1)  FE=>
>> Parse(stmt=null,query="   UPDATE ACTUALITE SET TITRE = $1,
>> CONTENU =
>> $2,    IMPORTANTE = $3,    ETAT_ESPACE_DISCUSSION_ID = $4,
>> MODERATION_ID = $5,        AUTEUR_MAJ = $6,    ESPACE_DISCUSSION =
>> $7,
>>   DATE_PUBLICATION = $8,       DATE_EXPIRATION = $9,       DATE_MAJ
>> = $10,
>>       IMAGE_POSITION = $11,       IMAGE_DIMENSION = $12,
>> TYPE_ACTUALITE_ID = $13         WHERE ACTUALITE_ID =
>> $14",oids={1043,1043,16,0,0,20,16,0,0,0,20,20,20,20}) 18:15:22.623
>> (1)
>> FE=> Bind(stmt=null,portal=null,$1=<'transactions'>,$2=<'<p>test
>> transactions mmau prepare
>>
>> modif</p>'>,$3=<'1'>,$4=<NULL>,$5=<NULL>,$6=<'110000000000007027'>,$7=<'0'
>> >,$8=<'2011-01-17 00:00:00.000000 +01:00:00'>,$9=<'2011-07-05
>> 00:00:00.000000 +02:00:00'>,$10=<'2011-02-15 18:15:21.733000
>> +01:00:00'>,$11=<'1'>,$12=<'100'>,$13=<'0'>,$14=<'182'>)
>> 18:15:22.623 (1)
>> FE=> Describe(portal=null)
>> 18:15:22.623 (1)  FE=> Execute(portal=null,limit=0)
>> 18:15:22.623 (1)  FE=> Sync
>> 18:15:22.639 (1)  <=BE ParseComplete [null]
>> 18:15:22.639 (1)  <=BE BindComplete [null]
>> 18:15:22.639 (1)  <=BE NoData
>> 18:15:22.639 (1)  <=BE CommandStatus(UPDATE 1)
>> 18:15:22.639 (1)  <=BE ReadyForQuery(I)
>> -----
>> As we can see, there is no BEGIN order before the update.
>>
>> For the second link:
>> http://archives.postgresql.org/message-id/4D01DFA8.3060207@emaze.net
>> I
>> applied the patch you proposed for the prepare() method. Nothing
>> changed
>> either. The update was still commited and the postgresql logs did
>> not
>> change. I tried to check what it was actually doing in the prepare()
>> method, to see if everything was going correctly, but I couldn't
>> succeed
>> in seeing either the breakpoints or the debug logs I added. I don't
>> see
>> the default PGXAConnection class logs either. Could it be possible
>> that it
>> doesn't even go in the prepare() method?
>>
>> Thanks for any help.
>> Marion
>>
>> -----Message d'origine-----
>> De : Maurin, Marion
>> Envoyé : jeudi 10 février 2011 19:45
>> À : 'Achilleas Mantzios'; pgsql-jdbc@postgresql.org
>> Objet : RE: [JDBC] PGXAConnection and autocommit problem
>>
>> Thanks for those two inputs. I couldn't save time today to check but
>> it
>> seems interesting. I'll test to see if it's related.
>>
>> -----Message d'origine-----
>> De : pgsql-jdbc-owner@postgresql.org
>> [mailto:pgsql-jdbc-owner@postgresql.org] De la part de Achilleas
>> Mantzios
>> Envoyé : jeudi 10 février 2011 10:45
>> À : pgsql-jdbc@postgresql.org
>> Objet : Re: [JDBC] PGXAConnection and autocommit problem
>>
>> Στις Thursday 10 February 2011 10:42:44 ο/η Heikki Linnakangas
>> έγραψε:
>> > On 10.02.2011 09:50, Maurin, Marion wrote:
>> > > Yes, I think I saw that too, that the autocommit shouldn't be
>> set from
>> > > outside. But shouldn't the driver itself set it to false at the
>> > > beginning of the transaction?
>> ________________________________________
>> > > De : pgsql-jdbc-owner@postgresql.org
>> [pgsql-jdbc-owner@postgresql.org]
>> > > de la part de rsmogura [rsmogura@softperience.eu] Date d'envoi :
>> > > mercredi 9 février 2011 21:13
>> > > À : pgsql-jdbc@postgresql.org
>> > > Objet : Re: [JDBC] PGXAConnection and autocommit problem
>> > >
>> > >   Driver should prevent set auto commit, or change it value in
>> any
>> > >   case. I thought I saw exception in code preventing this.
>> > >>
>> > >   On Wed, 9 Feb 2011 14:28:44 +0000, Maurin, Marion wrote:
>> > >> Hi,
>> > >>
>> > >> I hope I'm in the right mailing list.
>> > >>
>> > >> I'm currently facing a problem with Postgresql XA connections.
>> I'm
>> > >> trying to have my transactions working but no rollback seems to
>> take
>> > >> effect. After searching, I think the problem is about the
>> autoCommit
>> > >> mode which is set to true even during the transaction.
>> >
>> > It's quite possible that there's a bug somewhere in the XA driver
>> -
>> > we've had several bugs related to autocommit and XA in the past.
>>
>> I remember my similar case one year ago :
>>
>> http://archives.postgresql.org/message-id/201003021629.07368.achill%40matri
>> x.gatewaynet.com
>>
>> Anyways Heikki helped a lot on this one i remember, and also wrote a
>> patch.
>> Thanx Heikki!
>>
>> > And I think this one is still pending:
>> >
>> http://archives.postgresql.org/message-id/4D01DFA8.3060207@emaze.net.
>> > Could it be related?
>> >
>> > Can you create a small self-contained test program to reproduce
>> the
>> > issue, and post it to the list?
>
> Looking at code there is no way to set auto commit to true (actually
> you can
> do this using some back door and plain JDBC), but point is that auto
> commit is
> set to false in start(), and changed to true on prepare().
>
> Things that comes to my head are:
> 1. Transaction Managers call prepare or rollback in meantime of your
> code,
> this can be due to transaction timout - it should close connection
> too.
> 2. Like above but from different reasons. In some way connection may
> be shared
> and tried to be used in two transactions.
> 3. Maybe connection is used without XA, i mean missing call to start,
> or after
> prepare from TM?
>
> I think you should put break point on setAutoCommit in
> AbstractJdbc2Connection, or prepare/rollback/commit and check all
> call stacks
> to see what actully calls this.
>
> I saw one time simillar error, but didn't noticed preconditions,
>
> You wrote You use Tomcat + Atomikos, I see Spring there too. Is your
> bug
> reproducible using simpler case only with tomcat / atomikos?
>
> I don't know Spring at all, but I see user transactions, do You call
> begin or
> Spring do it? Do you use multiple (or nested) transactions per call,
> or many
> XA resources?
>
> Regards,
> Radek
>
>
> Think green - keep it on the screen.
>
> This e-mail and any attachment is for authorised use by the intended
> recipient(s) only. It may contain proprietary material, confidential
> information and/or be subject to legal privilege. It should not be
> copied, disclosed to, retained or used by, any other party. If you
> are
> not an intended recipient then please promptly delete this e-mail and
> any attachment and all copies and inform the sender. Thank you.


 Will be hard to investigate this without test case.

 If you are using Netbeans download sources appropriate for your JDBC
 version. You may need to import sources to NetBeans (new free form
 project), You don't need to compile those. You may need to go to
 Window->Debugging->Sources right click attach sources (On 7 beta 2
 version). Then use socket attach to attach debugger to tomcat.

 Regards,
 Radek.