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

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
breakpointsdo not work for the driver classes (though it works for any other class), and the logger.logDebug() of the
PGXAConnectionalways returns false (Though the debug mode is activated in log4j.xml and in the driver conf), so I can't
seeanything logged in those classes.
 

Spring starts and closes the transaction itself. I'm not doing anything but setting a "@transactional" annotation on
themethod I want to encapsulate with a transaction. I use a single transaction in which there are select calls on
severalXA 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
orused by, any other party. If you are not an intended recipient then please promptly delete this e-mail and any
attachmentand all copies and inform the sender. Thank you.