Thread: XAResource implementation

XAResource implementation

From
joël Winteregg
Date:
Dear jdbc users,

I'm using Hibernate with a standalone JTA implementation (Atomikos) and
it seems I'm having troubles with pgsql-jdbc XA implementation. As I'm
not a JTA expert at all, I got help from Atomikos R&D (Thanks to Guy)
and it seems that my problem could come from jdbc implementation. Guy
from Atomikos told me to look at this post:
http://archives.postgresql.org/pgsql-jdbc/2006-10/msg00011.php

I'm wondering if my problem is the same kind of troubles ? The full
description of my problem is available here:
http://www.atomikos.org/forums/viewtopic.php?t=1280

Here are advices I had from Atomikos:
It seems pgsql-jdbc don't allow what is perfectly valid XA:
1. XAResource.start ( TMNOFLAGS)
2. XAResource.end ( TMSUCCESS )
3. XAResource.start (TMJOIN )


Any suggestion, tip, etc. would be appreciated !


Many thanks in advance and best regards,


Joël


Re: XAResource implementation

From
Heikki Linnakangas
Date:
joël Winteregg wrote:
> I'm using Hibernate with a standalone JTA implementation (Atomikos) and
> it seems I'm having troubles with pgsql-jdbc XA implementation. As I'm
> not a JTA expert at all, I got help from Atomikos R&D (Thanks to Guy)
> and it seems that my problem could come from jdbc implementation. Guy
> from Atomikos told me to look at this post:
> http://archives.postgresql.org/pgsql-jdbc/2006-10/msg00011.php
>
> I'm wondering if my problem is the same kind of troubles ? The full
> description of my problem is available here:
> http://www.atomikos.org/forums/viewtopic.php?t=1280
>
> Here are advices I had from Atomikos:
> It seems pgsql-jdbc don't allow what is perfectly valid XA:
> 1. XAResource.start ( TMNOFLAGS)
> 2. XAResource.end ( TMSUCCESS )
> 3. XAResource.start (TMJOIN )
>
>
> Any suggestion, tip, etc. would be appreciated !

It does seem like Atomikos is doing the same thing as WebLogic. However,
that patch was committed back then in December 2006. Are you using the
most recent version of the JDBC driver?

--
   Heikki Linnakangas
   EnterpriseDB   http://www.enterprisedb.com

Re: XAResource implementation

From
joël Winteregg
Date:
Hello Heikki,

Thanks for your answer. Yes, i'm using the following jdbc driver
version: 8.2-506.jdbc4.jar

Do you have any other idea ? Should I tune something for the jdbc
driver ?

Thanks in advance.

Joël



On Tue, 2007-10-30 at 14:39 +0000, Heikki Linnakangas wrote:
> joël Winteregg wrote:
> > I'm using Hibernate with a standalone JTA implementation (Atomikos) and
> > it seems I'm having troubles with pgsql-jdbc XA implementation. As I'm
> > not a JTA expert at all, I got help from Atomikos R&D (Thanks to Guy)
> > and it seems that my problem could come from jdbc implementation. Guy
> > from Atomikos told me to look at this post:
> > http://archives.postgresql.org/pgsql-jdbc/2006-10/msg00011.php
> >
> > I'm wondering if my problem is the same kind of troubles ? The full
> > description of my problem is available here:
> > http://www.atomikos.org/forums/viewtopic.php?t=1280
> >
> > Here are advices I had from Atomikos:
> > It seems pgsql-jdbc don't allow what is perfectly valid XA:
> > 1. XAResource.start ( TMNOFLAGS)
> > 2. XAResource.end ( TMSUCCESS )
> > 3. XAResource.start (TMJOIN )
> >
> >
> > Any suggestion, tip, etc. would be appreciated !
>
> It does seem like Atomikos is doing the same thing as WebLogic. However,
> that patch was committed back then in December 2006. Are you using the
> most recent version of the JDBC driver?
>


Re: XAResource implementation

From
Heikki Linnakangas
Date:
joël Winteregg wrote:
> Hello Heikki,
>
> Thanks for your answer. Yes, i'm using the following jdbc driver
> version: 8.2-506.jdbc4.jar
>
> Do you have any other idea ? Should I tune something for the jdbc
> driver ?

Hmm. Looking at the logs on that forum, apparently Atomikos calls
XAResource.start(TMJOIN) using a different connection:

XAResourceTransaction 127.0.0.1.tm0000100044127.0.0.1.tm1: about to
switch to XAResource org.postgresql.xa.PGXAConnection@364641 [thread:
main] on: 07-10-28 12:37:53,333
XAResourceTransaction 127.0.0.1.tm0000100044127.0.0.1.tm1: switched to
XAResource org.postgresql.xa.PGXAConnection@364641 [thread: main] on:
07-10-28 12:37:53,333
...
XAResource.start ( 127.0.0.1.tm0000100044127.0.0.1.tm1 ,
XAResource.TMNOFLAGS ) called on resource testDS1 represented by
XAResource instance org.postgresql.xa.PGXAConnection@364641 [thread:
main] on: 07-10-28 12:37:53,335
...
XAResource.end ( 127.0.0.1.tm0000100044127.0.0.1.tm1 ,
XAResource.TMSUCCESS ) called on resource testDS1 represented by
XAResource instance org.postgresql.xa.PGXAConnection@364641 [thread:
main] on: 07-10-28 12:37:53,434
...
XAResourceTransaction 127.0.0.1.tm0000100044127.0.0.1.tm1: about to
switch to XAResource org.postgresql.xa.PGXAConnection@b6548 [thread:
main] on: 07-10-28 12:37:53,441
XAResourceTransaction 127.0.0.1.tm0000100044127.0.0.1.tm1: switched to
XAResource org.postgresql.xa.PGXAConnection@b6548 [thread: main] on:
07-10-28 12:37:53,442
...

Note how the PGXAConnection instance used above changes from
PGXAConnection@364641 to PGXAConnection@b6548. Unfortunately that's the
kind of transaction interleaving that we don't support.

You said you tried setExclusiveConnectionMode(true), but I think there's
something wrong with that. If I'm reading the Atomikos source code
right, in exclusive connection mode it should use
ExclusiveExternalXAPooledConnectionImp, but in your case it's using
ExternalXAPooledConnectionImp, which according to the source code is
used when not in exclusive connection mode. Are you sure you set that
parameter in the right place?

--
   Heikki Linnakangas
   EnterpriseDB   http://www.enterprisedb.com

Re: XAResource implementation

From
Heikki Linnakangas
Date:
Heikki Linnakangas wrote:
> You said you tried setExclusiveConnectionMode(true), but I think there's
> something wrong with that. If I'm reading the Atomikos source code
> right, in exclusive connection mode it should use
> ExclusiveExternalXAPooledConnectionImp, but in your case it's using
> ExternalXAPooledConnectionImp, which according to the source code is
> used when not in exclusive connection mode. Are you sure you set that
> parameter in the right place?

Actually ExclusiveExternalXAPooledConnectionImp is mention in the log as
well, so it looks like the Atomikos exclusive connection mode isn't
doing what it's supposed to / isn't doing what we need it to.

--
   Heikki Linnakangas
   EnterpriseDB   http://www.enterprisedb.com

Re: XAResource implementation

From
joël Winteregg
Date:
Hello Heikki,


As said in my last email, I'm coming back to you because I have new
informations about my problem using Atomikos JTA and the postgresql XA
driver.
At the end, I tryed an other TransactionManager
http://docs.codehaus.org/display/BTM/Home and the postgresql XA driver
was mostly working with it... So it seems it may comes from Atomikos
implementation but I was not able to find out why and where was the
problem...
I said that BTM TransactionManager was "mostly" working because when I
was using the XA implementation, SQL BEGIN and COMMIT where generated
arround each SQL query (even if several queries were located inside the
same transaction). So at the end, I used the TransactionManager with the
"Last Resource Commit Optimization" which allow the use of a pure jdbc
driver during transaction (I only have a single DB (1 Phase Commit) so
it is 100% safe to use this specificity). In this case, my BEGIN and
COMMIT statement where just perfect:
BEGIN
  SELECT ..
  SELECT ..
  SELECT ..
COMMIT

So I was just wondering if my XA problem (BEGIN and COMMIT location when
using XA) could come from the postgresql XA driver ?

Many thanks for your help and best regards.

Joël

On Tue, 2007-10-30 at 16:28 +0000, Heikki Linnakangas wrote:
> Heikki Linnakangas wrote:
> > You said you tried setExclusiveConnectionMode(true), but I think there's
> > something wrong with that. If I'm reading the Atomikos source code
> > right, in exclusive connection mode it should use
> > ExclusiveExternalXAPooledConnectionImp, but in your case it's using
> > ExternalXAPooledConnectionImp, which according to the source code is
> > used when not in exclusive connection mode. Are you sure you set that
> > parameter in the right place?
>
> Actually ExclusiveExternalXAPooledConnectionImp is mention in the log as
> well, so it looks like the Atomikos exclusive connection mode isn't
> doing what it's supposed to / isn't doing what we need it to.
>


Re: XAResource implementation

From
Heikki Linnakangas
Date:
joël Winteregg wrote:
> I said that BTM TransactionManager was "mostly" working because when I
> was using the XA implementation, SQL BEGIN and COMMIT where generated
> arround each SQL query (even if several queries were located inside the
> same transaction). So at the end, I used the TransactionManager with the
> "Last Resource Commit Optimization" which allow the use of a pure jdbc
> driver during transaction (I only have a single DB (1 Phase Commit) so
> it is 100% safe to use this specificity). In this case, my BEGIN and
> COMMIT statement where just perfect:
> BEGIN
>   SELECT ..
>   SELECT ..
>   SELECT ..
> COMMIT
>
> So I was just wondering if my XA problem (BEGIN and COMMIT location when
> using XA) could come from the postgresql XA driver ?

Hmm. I downloaded the BTM newUserDemo.zip and modified it to run with
Postgres, and to run two queries in same transaction. Works for me.

Attached is the modified Test.java I used. To run:
0. Download newUserDemo.zip from
http://docs.codehaus.org/display/BTM/NewUserGuide
1. Put postgresql.jar in newUserDemo/lib
2. Copy the attached Test.java to newUserDemo/src/jtatest
3. Modify database/username/password in Test.java if necessary
4. Run the CREATE TABLE from derby-create.sql

Here's what I get in the Postgres log, with log_statements='all':

LOG:  execute <unnamed>: SELECT gid FROM pg_prepared_xacts
LOG:  execute S_1: BEGIN
LOG:  execute <unnamed>: insert into messages(content) values ($1)
DETAIL:  parameters: $1 = 'hello, world!'
LOG:  execute S_2: COMMIT
LOG:  execute S_1: BEGIN
LOG:  execute <unnamed>: select content from messages
LOG:  execute <unnamed>: select content from messages
LOG:  execute S_2: COMMIT

--
   Heikki Linnakangas
   EnterpriseDB   http://www.enterprisedb.com
package jtatest;

import bitronix.tm.TransactionManagerServices;
import bitronix.tm.resource.jdbc.PoolingDataSource;

import javax.transaction.UserTransaction;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.ResultSet;

public class Test {

    public static void main(String[] args) throws Exception {
        String who = "world";
        if (args.length > 0)
            who = args[0];

        PoolingDataSource derby1Ds = new PoolingDataSource();
        derby1Ds.setClassName("org.postgresql.xa.PGXADataSource");
        derby1Ds.setUniqueName("derby1");
        derby1Ds.setPoolSize(3);
        derby1Ds.getDriverProperties().setProperty("user", "postgres");
        derby1Ds.getDriverProperties().setProperty("databaseName", "postgres");
        derby1Ds.init();

        UserTransaction ut = TransactionManagerServices.getTransactionManager();
        ut.begin();
        try {
            Connection c = derby1Ds.getConnection();

            PreparedStatement stmt = c.prepareStatement("insert into messages(content) values (?)");
            stmt.setString(1, "hello, " + who + "!");
            stmt.executeUpdate();
            stmt.close();

            c.close();
            ut.commit();
        } catch (SQLException ex) {
            ex.printStackTrace();
            ut.rollback();
        }

        ut.begin();
        try {
            Connection c = derby1Ds.getConnection();

            PreparedStatement stmt = c.prepareStatement("select content from messages");
            ResultSet rs = stmt.executeQuery();
            while(rs.next())
                System.out.println(rs.getString(1));
            rs.close();
            stmt.close();

        /* Run another query */
             stmt = c.prepareStatement("select content from messages");
             rs = stmt.executeQuery();
            while(rs.next())
                System.out.println(rs.getString(1));
            rs.close();
            stmt.close();

            c.close();
            ut.commit();
        } catch (SQLException ex) {
            ex.printStackTrace();
            ut.rollback();
        }

        derby1Ds.close();
    }
}

Re: XAResource implementation

From
joël Winteregg
Date:
Hello Heikki,

Many thanks for your answer and for the time you took to do some
testing ! As you can see I put Ludovic Orban as Cc because he is the BTM
developer and he seems to be interested to this issue too...

> Hmm. I downloaded the BTM newUserDemo.zip and modified it to run with
> Postgres, and to run two queries in same transaction. Works for me.
>

Ah Yes, you're right, your transaction just looks perfect !

> Attached is the modified Test.java I used. To run:
> 0. Download newUserDemo.zip from
> http://docs.codehaus.org/display/BTM/NewUserGuide
> 1. Put postgresql.jar in newUserDemo/lib
> 2. Copy the attached Test.java to newUserDemo/src/jtatest
> 3. Modify database/username/password in Test.java if necessary
> 4. Run the CREATE TABLE from derby-create.sql
>
> Here's what I get in the Postgres log, with log_statements='all':
>
> LOG:  execute <unnamed>: SELECT gid FROM pg_prepared_xacts
> LOG:  execute S_1: BEGIN
> LOG:  execute <unnamed>: insert into messages(content) values ($1)
> DETAIL:  parameters: $1 = 'hello, world!'
> LOG:  execute S_2: COMMIT
> LOG:  execute S_1: BEGIN
> LOG:  execute <unnamed>: select content from messages
> LOG:  execute <unnamed>: select content from messages
> LOG:  execute S_2: COMMIT

On my side, I tryed the following example:
http://docs.codehaus.org/download/attachments/9240687/HibernateBTM.zip?version=2

(see attachement to see my modifications)

and my pg logs look like this for the following code:
userTransaction.begin();
loopCountQuery(sf); //many select count queries
userTransaction.commit();


2007-11-13 08:13:51 CET DETAIL:  parameters: $1 = 'username 6'
2007-11-13 08:13:51 CET LOG:  execute S_2: COMMIT
2007-11-13 08:13:51 CET LOG:  execute S_1: BEGIN
2007-11-13 08:13:51 CET LOG:  execute <unnamed>: select count(*) as y0_
from USERS this_ where this_.NAME=$1
2007-11-13 08:13:51 CET DETAIL:  parameters: $1 = 'username 7'
2007-11-13 08:13:51 CET LOG:  execute S_2: COMMIT
2007-11-13 08:13:51 CET LOG:  execute S_1: BEGIN
2007-11-13 08:13:51 CET LOG:  execute <unnamed>: select count(*) as y0_
from USERS this_ where this_.NAME=$1
2007-11-13 08:13:51 CET DETAIL:  parameters: $1 = 'username 8'
2007-11-13 08:13:51 CET LOG:  execute S_2: COMMIT
2007-11-13 08:13:51 CET LOG:  execute S_1: BEGIN
2007-11-13 08:13:51 CET LOG:  execute <unnamed>: select count(*) as y0_
from USERS this_ where this_.NAME=$1
2007-11-13 08:13:51 CET DETAIL:  parameters: $1 = 'username 9'
2007-11-13 08:13:51 CET LOG:  execute S_2: COMMIT
2007-11-13 08:13:51 CET LOG:  execute S_1: BEGIN
2007-11-13 08:13:51 CET LOG:  execute <unnamed>: select count(*) as y0_
from USERS this_ where this_.NAME=$1
2007-11-13 08:13:51 CET DETAIL:  parameters: $1 = 'username 10'
2007-11-13 08:13:51 CET LOG:  execute S_2: COMMIT

When I use the pure JDBC driver (not the XA implementation), I don't
have this behaviour (BEGIN and COMMIT are related to userTransaction
from application level) and not anymore around each query:

2007-11-13 08:19:55 CET LOG:  execute S_1: BEGIN
2007-11-13 08:19:55 CET LOG:  execute <unnamed>: select count(*) as y0_
from USERS this_ where this_.NAME=$1
2007-11-13 08:19:55 CET DETAIL:  parameters: $1 = 'username 0'
2007-11-13 08:19:55 CET LOG:  execute <unnamed>: select count(*) as y0_
from USERS this_ where this_.NAME=$1
2007-11-13 08:19:55 CET DETAIL:  parameters: $1 = 'username 1'
2007-11-13 08:19:55 CET LOG:  execute <unnamed>: select count(*) as y0_
from USERS this_ where this_.NAME=$1
2007-11-13 08:19:55 CET DETAIL:  parameters: $1 = 'username 2'
2007-11-13 08:19:55 CET LOG:  execute <unnamed>: select count(*) as y0_
from USERS this_ where this_.NAME=$1
2007-11-13 08:19:55 CET DETAIL:  parameters: $1 = 'username 3'
2007-11-13 08:19:55 CET LOG:  execute <unnamed>: select count(*) as y0_
from USERS this_ where this_.NAME=$1
2007-11-13 08:19:55 CET DETAIL:  parameters: $1 = 'username 4'
2007-11-13 08:19:55 CET LOG:  execute <unnamed>: select count(*) as y0_
from USERS this_ where this_.NAME=$1
2007-11-13 08:19:55 CET DETAIL:  parameters: $1 = 'username 5'
2007-11-13 08:19:55 CET LOG:  execute <unnamed>: select count(*) as y0_
from USERS this_ where this_.NAME=$1
2007-11-13 08:19:55 CET DETAIL:  parameters: $1 = 'username 6'
2007-11-13 08:19:55 CET LOG:  execute <unnamed>: select count(*) as y0_
from USERS this_ where this_.NAME=$1
2007-11-13 08:19:55 CET DETAIL:  parameters: $1 = 'username 7'
2007-11-13 08:19:55 CET LOG:  execute <unnamed>: select count(*) as y0_
from USERS this_ where this_.NAME=$1
2007-11-13 08:19:55 CET DETAIL:  parameters: $1 = 'username 8'
2007-11-13 08:19:55 CET LOG:  execute <unnamed>: select count(*) as y0_
from USERS this_ where this_.NAME=$1
2007-11-13 08:19:55 CET DETAIL:  parameters: $1 = 'username 9'
2007-11-13 08:19:55 CET LOG:  execute <unnamed>: select count(*) as y0_
from USERS this_ where this_.NAME=$1
2007-11-13 08:19:55 CET DETAIL:  parameters: $1 = 'username 10'
2007-11-13 08:19:55 CET LOG:  execute S_2: COMMIT


As I'm not a JTA and JDBC expert at all, I'm not able to provide you
more information, I'm sorry about that... I hope this behaviour is not
coming from my miss-understanding !


Thanks for your help !

Regards,

Joël

On Mon, 2007-11-12 at 09:59 +0000, Heikki Linnakangas wrote:
> joël Winteregg wrote:
> > I said that BTM TransactionManager was "mostly" working because when I
> > was using the XA implementation, SQL BEGIN and COMMIT where generated
> > arround each SQL query (even if several queries were located inside the
> > same transaction). So at the end, I used the TransactionManager with the
> > "Last Resource Commit Optimization" which allow the use of a pure jdbc
> > driver during transaction (I only have a single DB (1 Phase Commit) so
> > it is 100% safe to use this specificity). In this case, my BEGIN and
> > COMMIT statement where just perfect:
> > BEGIN
> >   SELECT ..
> >   SELECT ..
> >   SELECT ..
> > COMMIT
> >
> > So I was just wondering if my XA problem (BEGIN and COMMIT location when
> > using XA) could come from the postgresql XA driver ?
>
> Hmm. I downloaded the BTM newUserDemo.zip and modified it to run with
> Postgres, and to run two queries in same transaction. Works for me.
>
> Attached is the modified Test.java I used. To run:
> 0. Download newUserDemo.zip from
> http://docs.codehaus.org/display/BTM/NewUserGuide
> 1. Put postgresql.jar in newUserDemo/lib
> 2. Copy the attached Test.java to newUserDemo/src/jtatest
> 3. Modify database/username/password in Test.java if necessary
> 4. Run the CREATE TABLE from derby-create.sql
>
> Here's what I get in the Postgres log, with log_statements='all':
>
> LOG:  execute <unnamed>: SELECT gid FROM pg_prepared_xacts
> LOG:  execute S_1: BEGIN
> LOG:  execute <unnamed>: insert into messages(content) values ($1)
> DETAIL:  parameters: $1 = 'hello, world!'
> LOG:  execute S_2: COMMIT
> LOG:  execute S_1: BEGIN
> LOG:  execute <unnamed>: select content from messages
> LOG:  execute <unnamed>: select content from messages
> LOG:  execute S_2: COMMIT
>

Attachment

Re: XAResource implementation

From
Heikki Linnakangas
Date:
joël Winteregg wrote:
> Many thanks for your answer and for the time you took to do some
> testing ! As you can see I put Ludovic Orban as Cc because he is the BTM
> developer and he seems to be interested to this issue too...
>
>> Hmm. I downloaded the BTM newUserDemo.zip and modified it to run with
>> Postgres, and to run two queries in same transaction. Works for me.
>>
>
> Ah Yes, you're right, your transaction just looks perfect !
>
>> Attached is the modified Test.java I used. To run:
>> 0. Download newUserDemo.zip from
>> http://docs.codehaus.org/display/BTM/NewUserGuide
>> 1. Put postgresql.jar in newUserDemo/lib
>> 2. Copy the attached Test.java to newUserDemo/src/jtatest
>> 3. Modify database/username/password in Test.java if necessary
>> 4. Run the CREATE TABLE from derby-create.sql
>>
>> Here's what I get in the Postgres log, with log_statements='all':
>>
>> LOG:  execute <unnamed>: SELECT gid FROM pg_prepared_xacts
>> LOG:  execute S_1: BEGIN
>> LOG:  execute <unnamed>: insert into messages(content) values ($1)
>> DETAIL:  parameters: $1 = 'hello, world!'
>> LOG:  execute S_2: COMMIT
>> LOG:  execute S_1: BEGIN
>> LOG:  execute <unnamed>: select content from messages
>> LOG:  execute <unnamed>: select content from messages
>> LOG:  execute S_2: COMMIT
>
> On my side, I tryed the following example:
> http://docs.codehaus.org/download/attachments/9240687/HibernateBTM.zip?version=2

Ok, I downloaded and installed that as well, and saw similar results.

It looks like there is indeed a bug in the Postgres driver. I believe it
was introduced by the recent changes to keep the connection in
autocommit mode when no XA-transaction is in progress.

It's this call sequence that borks it up:

xares.start()
conn = xares.getConnection()
// do stuff
conn.close();
conn = xares.getConnection()
// do more stuff
xares.end()
xares.commit()

The 2nd getConnection call inadvertently commits the transaction, and
opens another one.

Patch attached. I also added a test for this in the test suite.

Can you check that this fixes the issue for you, please? I can send you
a patched jar if you don't have build environment, let me know if you
need it.

--
   Heikki Linnakangas
   EnterpriseDB   http://www.enterprisedb.com
? xa-getConnection-fix.patch
Index: org/postgresql/ds/jdbc23/AbstractJdbc23PooledConnection.java
===================================================================
RCS file: /cvsroot/jdbc/pgjdbc/org/postgresql/ds/jdbc23/AbstractJdbc23PooledConnection.java,v
retrieving revision 1.2
diff -c -r1.2 AbstractJdbc23PooledConnection.java
*** org/postgresql/ds/jdbc23/AbstractJdbc23PooledConnection.java    10 Sep 2007 08:38:15 -0000    1.2
--- org/postgresql/ds/jdbc23/AbstractJdbc23PooledConnection.java    13 Nov 2007 16:41:27 -0000
***************
*** 137,143 ****
                  }
                  con.clearWarnings();
              }
!             con.setAutoCommit(autoCommit);
          }
          catch (SQLException sqlException)
          {
--- 137,149 ----
                  }
                  con.clearWarnings();
              }
!             /*
!              * In XA-mode, autocommit is handled in PGXAConnection,
!              * because it depends on whether an XA-transaction is open
!              * or not
!              */
!             if (!isXA)
!                 con.setAutoCommit(autoCommit);
          }
          catch (SQLException sqlException)
          {
Index: org/postgresql/test/xa/XADataSourceTest.java
===================================================================
RCS file: /cvsroot/jdbc/pgjdbc/org/postgresql/test/xa/XADataSourceTest.java,v
retrieving revision 1.9
diff -c -r1.9 XADataSourceTest.java
*** org/postgresql/test/xa/XADataSourceTest.java    6 Jul 2007 20:32:43 -0000    1.9
--- org/postgresql/test/xa/XADataSourceTest.java    13 Nov 2007 16:41:27 -0000
***************
*** 258,272 ****
--- 258,302 ----

          xaRes.start(xid, XAResource.TMNOFLAGS);

+         conn.createStatement().executeQuery("SELECT * FROM testxa1");
+
+         java.sql.Timestamp ts1 = getTransactionTimestamp(conn);
+
+         conn.close();
          conn = xaconn.getConnection();
          assertFalse(conn.getAutoCommit());

+         java.sql.Timestamp ts2 = getTransactionTimestamp(conn);
+
+         /* Check that we're still in the same transaction.
+          * close+getConnection() should not rollback the XA-transaction
+          * implicitly.
+          */
+         assertEquals(ts1, ts2);
+
          xaRes.end(xid, XAResource.TMSUCCESS);
          xaRes.prepare(xid);
          xaRes.rollback(xid);
          assertTrue(conn.getAutoCommit());
      }

+     /**
+      * Get transaction_timeout() from server.
+      *
+      * This can be used to check that transaction doesn't get committed/
+      * rolled back inadvertently, by calling this once before and after the
+      * suspected piece of code, and check that they match. It's a bit iffy,
+      * conceivably you might get the same timestamp anyway if the
+      * suspected piece of code runs fast enough, and/or the server clock
+      * is very coarse grained. But it'll do for testing purposes.
+      */
+     private static java.sql.Timestamp getTransactionTimestamp(Connection conn) throws SQLException
+     {
+         ResultSet rs = conn.createStatement().executeQuery("SELECT transaction_timestamp()");
+         rs.next();
+         return rs.getTimestamp(1);
+     }
+
      public void testEndThenJoin() throws XAException {
          Xid xid = new CustomXid(5);

Index: org/postgresql/xa/PGXAConnection.java
===================================================================
RCS file: /cvsroot/jdbc/pgjdbc/org/postgresql/xa/PGXAConnection.java,v
retrieving revision 1.12
diff -c -r1.12 PGXAConnection.java
*** org/postgresql/xa/PGXAConnection.java    27 Jul 2007 10:15:39 -0000    1.12
--- org/postgresql/xa/PGXAConnection.java    13 Nov 2007 16:41:28 -0000
***************
*** 81,98 ****

      public Connection getConnection() throws SQLException
      {
          Connection conn = super.getConnection();

          // When we're outside an XA transaction, autocommit
          // is supposed to be true, per usual JDBC convention.
          // When an XA transaction is in progress, it should be
          // false.
!
!         // super.getConnection rolls back any previous transaction, and resets
!         // autocommit to true, so we have to set it to false before handing the
!         // connection to the caller, if an XA transaction is active.
!         if(state == STATE_ACTIVE)
!             conn.setAutoCommit(false);

          return conn;
      }
--- 81,97 ----

      public Connection getConnection() throws SQLException
      {
+         if (logger.logDebug())
+             debug("PGXAConnection.getConnection called");
+
          Connection conn = super.getConnection();

          // When we're outside an XA transaction, autocommit
          // is supposed to be true, per usual JDBC convention.
          // When an XA transaction is in progress, it should be
          // false.
!         if(state == STATE_IDLE)
!             conn.setAutoCommit(true);

          return conn;
      }

Re: XAResource implementation

From
Kris Jurka
Date:

On Tue, 13 Nov 2007, Heikki Linnakangas wrote:

> It looks like there is indeed a bug in the Postgres driver. I believe it was
> introduced by the recent changes to keep the connection in autocommit mode
> when no XA-transaction is in progress.
>
> It's this call sequence that borks it up:
>
> xares.start()
> conn = xares.getConnection()
> // do stuff
> conn.close();
> conn = xares.getConnection()
> // do more stuff
> xares.end()
> xares.commit()
>
> The 2nd getConnection call inadvertently commits the transaction, and opens
> another one.

When I raised this issue, you said it can't/shouldn't happen.  What's
different now?  It's still a bug in the driver, but is the calling code
doing something it shouldn't?

http://archives.postgresql.org/pgsql-jdbc/2007-06/msg00095.php

> Patch attached. I also added a test for this in the test suite.
>

transaction_timestamp() is an 8.2 feature, but since XA is an 8.1 feature
you should use now() instead.  (No need for a new patch, I've updated mine
here.)

Kris Jurka

Re: XAResource implementation

From
joël Winteregg
Date:
Hello Heikki,

Many thanks for these informations !

> Patch attached. I also added a test for this in the test suite.

Really cool !

> Can you check that this fixes the issue for you, please? I can send you
> a patched jar if you don't have build environment, let me know if you
> need it.

Yes, that would be great (easier) to have it as a patched jar. Then, I will provide you a feedback about its behavior
inmy application...  

Many many thanks for your support !

Joël


On Tue, 2007-11-13 at 16:51 +0000, Heikki Linnakangas wrote:
> joël Winteregg wrote:
> > Many thanks for your answer and for the time you took to do some
> > testing ! As you can see I put Ludovic Orban as Cc because he is the BTM
> > developer and he seems to be interested to this issue too...
> >
> >> Hmm. I downloaded the BTM newUserDemo.zip and modified it to run with
> >> Postgres, and to run two queries in same transaction. Works for me.
> >>
> >
> > Ah Yes, you're right, your transaction just looks perfect !
> >
> >> Attached is the modified Test.java I used. To run:
> >> 0. Download newUserDemo.zip from
> >> http://docs.codehaus.org/display/BTM/NewUserGuide
> >> 1. Put postgresql.jar in newUserDemo/lib
> >> 2. Copy the attached Test.java to newUserDemo/src/jtatest
> >> 3. Modify database/username/password in Test.java if necessary
> >> 4. Run the CREATE TABLE from derby-create.sql
> >>
> >> Here's what I get in the Postgres log, with log_statements='all':
> >>
> >> LOG:  execute <unnamed>: SELECT gid FROM pg_prepared_xacts
> >> LOG:  execute S_1: BEGIN
> >> LOG:  execute <unnamed>: insert into messages(content) values ($1)
> >> DETAIL:  parameters: $1 = 'hello, world!'
> >> LOG:  execute S_2: COMMIT
> >> LOG:  execute S_1: BEGIN
> >> LOG:  execute <unnamed>: select content from messages
> >> LOG:  execute <unnamed>: select content from messages
> >> LOG:  execute S_2: COMMIT
> >
> > On my side, I tryed the following example:
> > http://docs.codehaus.org/download/attachments/9240687/HibernateBTM.zip?version=2
>
> Ok, I downloaded and installed that as well, and saw similar results.
>
> It looks like there is indeed a bug in the Postgres driver. I believe it
> was introduced by the recent changes to keep the connection in
> autocommit mode when no XA-transaction is in progress.
>
> It's this call sequence that borks it up:
>
> xares.start()
> conn = xares.getConnection()
> // do stuff
> conn.close();
> conn = xares.getConnection()
> // do more stuff
> xares.end()
> xares.commit()
>
> The 2nd getConnection call inadvertently commits the transaction, and
> opens another one.
>
> Patch attached. I also added a test for this in the test suite.
>
> Can you check that this fixes the issue for you, please? I can send you
> a patched jar if you don't have build environment, let me know if you
> need it.
>


Re: XAResource implementation

From
Kris Jurka
Date:

On Tue, 13 Nov 2007, joël Winteregg wrote:

> Yes, that would be great (easier) to have it as a patched jar. Then, I
> will provide you a feedback about its behavior in my application...
>

Here's one:

http://ejurka.com/pgsql/jars/xafix/

Kris Jurka

Re: XAResource implementation

From
joël Winteregg
Date:
Hello Kris,

It's just cooking ;-) (I didn't did a deep testing, I only did it
regarding my transaction boundaries issue using BTM transaction
manager).

Thanks for the jar !
BTW, does new postresql jdbc version directly goes to a specific Maven
repository ?

Thanks and best regards,

Joël

On Tue, 2007-11-13 at 15:42 -0500, Kris Jurka wrote:
>
> On Tue, 13 Nov 2007, joël Winteregg wrote:
>
> > Yes, that would be great (easier) to have it as a patched jar. Then, I
> > will provide you a feedback about its behavior in my application...
> >
>
> Here's one:
>
> http://ejurka.com/pgsql/jars/xafix/
>
> Kris Jurka


Re: XAResource implementation

From
Kris Jurka
Date:

On Tue, 13 Nov 2007, joël Winteregg wrote:

> BTW, does new postresql jdbc version directly goes to a specific Maven
> repository ?
>

No.  Some older versions have found their way there, but I have no idea
who did that.

Kris Jurka

Re: XAResource implementation

From
Heikki Linnakangas
Date:
Kris Jurka wrote:
>
>
> On Tue, 13 Nov 2007, Heikki Linnakangas wrote:
>
>> It looks like there is indeed a bug in the Postgres driver. I believe
>> it was introduced by the recent changes to keep the connection in
>> autocommit mode when no XA-transaction is in progress.
>>
>> It's this call sequence that borks it up:
>>
>> xares.start()
>> conn = xares.getConnection()
>> // do stuff
>> conn.close();
>> conn = xares.getConnection()
>> // do more stuff
>> xares.end()
>> xares.commit()
>>
>> The 2nd getConnection call inadvertently commits the transaction, and
>> opens another one.
>
> When I raised this issue, you said it can't/shouldn't happen.  What's
> different now?  It's still a bug in the driver, but is the calling code
> doing something it shouldn't?
>
> http://archives.postgresql.org/pgsql-jdbc/2007-06/msg00095.php

Oh, I had forgotten about that. The difference is the close() between
the getConnection()s.

I started having second doubts about that case you posted in June, so I
tested what Derby does if you call getConnection() twice, without a
close in between. You get an error ("Cannot close a connection while a
global transaction is still active."), so at least they consider it
incorrect as well.

Throwing an error in that case would be a nice thing for us to do as
well. Currently, we just silently rollback.

>> Patch attached. I also added a test for this in the test suite.
>
> transaction_timestamp() is an 8.2 feature, but since XA is an 8.1
> feature you should use now() instead.  (No need for a new patch, I've
> updated mine here.)

Thanks!

--
   Heikki Linnakangas
   EnterpriseDB   http://www.enterprisedb.com

Re: XAResource implementation

From
Kris Jurka
Date:

On Tue, 13 Nov 2007, Heikki Linnakangas wrote:

> Oh, I had forgotten about that. The difference is the close() between the
> getConnection()s.
>
> I started having second doubts about that case you posted in June, so I
> tested what Derby does if you call getConnection() twice, without a close in
> between. You get an error ("Cannot close a connection while a global
> transaction is still active."), so at least they consider it incorrect as
> well.

I'm not super happy that explicit close vs implicit close has different
transactional behavior, but people really shouldn't be writing code like
this, so I've applied your patch to 8.1, 8.2 and HEAD.

Kris Jurka


Re: XAResource implementation

From
joël Winteregg
Date:
Hello Kris,


I saw your change on CVS (Rev 1.13 of PGXAConnection.java). Do you know
when it would be packaged with a new build number ?

Would it be postgresql-8.X-507.jdbcX.jar ?


Many thanks in advance !

Joël

On Wed, 2007-11-14 at 17:11 -0500, Kris Jurka wrote:
>
> On Tue, 13 Nov 2007, Heikki Linnakangas wrote:
>
> > Oh, I had forgotten about that. The difference is the close() between the
> > getConnection()s.
> >
> > I started having second doubts about that case you posted in June, so I
> > tested what Derby does if you call getConnection() twice, without a close in
> > between. You get an error ("Cannot close a connection while a global
> > transaction is still active."), so at least they consider it incorrect as
> > well.
>
> I'm not super happy that explicit close vs implicit close has different
> transactional behavior, but people really shouldn't be writing code like
> this, so I've applied your patch to 8.1, 8.2 and HEAD.
>
> Kris Jurka
>


Re: XAResource implementation

From
Kris Jurka
Date:

On Sat, 17 Nov 2007, joël Winteregg wrote:

> I saw your change on CVS (Rev 1.13 of PGXAConnection.java). Do you know
> when it would be packaged with a new build number ?

Probably a week or two when I'll try to put out some sort of 8.3
beta and release back branches.

> Would it be postgresql-8.X-507.jdbcX.jar ?

The build number depends on the driver version number.  So 8.2's next
build will be 507, but 8.1's will be 411, and 8.0's will be 321.

Kris Jurka