Re: PGXAConnection and autocommit problem

"Maurin, Marion" <marion(dot)maurin(at)logica(dot)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(at)postgresql(dot)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(at)postgresql(dot)org
> [mailto:pgsql-jdbc-owner(at)postgresql(dot)org] De la part de Achilleas Mantzios
> Envoyé : jeudi 10 février 2011 10:45
> À : pgsql-jdbc(at)postgresql(dot)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(at)postgresql(dot)org [pgsql-jdbc-owner(at)postgresql(dot)org]
> > > de la part de rsmogura [rsmogura(at)softperience(dot)eu] Date d'envoi :
> > > mercredi 9 février 2011 21:13
> > > À : pgsql-jdbc(at)postgresql(dot)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