Thread: XAResource implementation
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
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
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? >
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
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
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. >
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(); } }
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
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; }
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
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. >
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
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
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
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
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
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
>
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