Обсуждение: XA rollback problem
Hi, All inserts or updates I do using PGXADatasource get rolled back silently (no exceptions). I reproduced the problem using a simple test table with a single int2-column. As you can see in the driver logging it uses one-phase commit. I think the problem is in the part that states *CommandStatus(ROLLBACK)*, the driver just proceeds to ending and committing the transaction. Does anyone have a clue how I would debug this further? I could not find much XA-documentation on the site. Thanks, Niels Query: INSERT INTO test (test_id) VALUES (0) Configuration: Driver: both 404 and 405 Server: both 8.0 and 8.1 Tomcat: 5.5 Transactionmanager: SimpleJTA 1.04 Relevant logging: -- DriverManager.setLogWriter(new PrintWriter(System.out)); -- Driver.setLogLevel(Driver.DEBUG); XAResource 13d21d6: starting transaction xid = SimpleXidImpl[format=1c131d0a,gtrid={txmgrId=TM1,txmgrBirthTime=11456292 11806,myBirthTime=1145629233910,id=13}, bqual={1}] simple execute, handler=org.postgresql.jdbc2.AbstractJdbc2Statement$StatementResultHandl er@1d26552, maxRows=0, fetchSize=0, flags=5 FE=> Bind(stmt=S_1,portal=null) FE=> Execute(portal=null,limit=0) FE=> Parse(stmt=null,query="INSERT INTO test (test_id) VALUES (0)",oids={}) FE=> Bind(stmt=null,portal=null) FE=> Describe(portal=null) FE=> Execute(portal=null,limit=1) FE=> Sync <=BE BindComplete [null] <=BE CommandStatus(BEGIN) <=BE ParseComplete [null] <=BE BindComplete [null] <=BE NoData <=BE CommandStatus(INSERT 0 1) <=BE ReadyForQuery(T) simple execute, handler=org.postgresql.jdbc2.AbstractJdbc2Connection$TransactionCommandH andler@1fa157c, maxRows=0, fetchSize=0, flags=22 FE=> Bind(stmt=S_2,portal=null) FE=> Execute(portal=null,limit=1) FE=> Sync <=BE BindComplete [null] <=BE CommandStatus(ROLLBACK) <=BE ReadyForQuery(I) XAResource 13d21d6: ending transaction xid = SimpleXidImpl[format=1c131d0a,gtrid={txmgrId=TM1,txmgrBirthTime=11456292 11806,myBirthTime=1145629233910,id=13}, bqual={1}] XAResource 13d21d6: committing xid = SimpleXidImpl[format=1c131d0a,gtrid={txmgrId=TM1,txmgrBirthTime=11456292 11806,myBirthTime=1145629233910,id=13}, bqual={1}] (one phase)
On Fri, 21 Apr 2006, Niels Beekman wrote: > All inserts or updates I do using PGXADatasource get rolled back > silently (no exceptions). I reproduced the problem using a simple test > table with a single int2-column. Could you show us the java test code you are using as well? > As you can see in the driver logging it uses one-phase commit. I think > the problem is in the part that states *CommandStatus(ROLLBACK)*, the > driver just proceeds to ending and committing the transaction. The log alone isn't really enough to see what's going on here, because it doesn't show the Parse for the S_2 statement. It is unclear whether the driver is issuing a commit or a rollback here. Kris Jurka
Hi, Thanks for the quick response. I did not leave anything out of the driver logging, that's why I think there is something wrong in the driver, there is no S_2 parse. I do not know what the driver is issuing but it results in no record being inserted. Now the code: try { UserTransaction userTransaction = (UserTransaction)new InitialContext().lookup("java:comp/UserTransaction"); DataSource datasrc = (DataSource)new InitialContext().lookup("java:comp/env/jdbc/default"); userTransaction.begin(); try { Connection conn = datasrc.getConnection(); Statement stmt = conn.createStatement(); stmt.executeUpdate("INSERT INTO test (test_id) VALUES (0)"); stmt.close(); conn.close(); userTransaction.commit(); } catch (Exception e) { userTransaction.rollback(); throw e; } } catch (Exception e) { e.printStackTrace(); } The problem occurs when calling conn.close(), if you move the close() after the commit() it works just fine. I think the connection gets invalidated while it should stay active because it still has to commit or rollback (whatever the transactionmanager requests). See below for the driver loggings. I hope you can help me solve this. Niels Results of the code with conn.close() *before* userTransaction.commit(): XAResource 13d21d6: starting transaction xid = SimpleXidImpl[format=1c131d0a,gtrid={txmgrId=TM1,txmgrBirthTime=11456916 08045,myBirthTime=1145691634374,id=13}, bqual={1}] simple execute, handler=org.postgresql.jdbc2.AbstractJdbc2Statement$StatementResultHandl er@1fa157c, maxRows=0, fetchSize=0, flags=5 FE=> Bind(stmt=S_1,portal=null) FE=> Execute(portal=null,limit=0) FE=> Parse(stmt=null,query="INSERT INTO test (test_id) VALUES (0)",oids={}) FE=> Bind(stmt=null,portal=null) FE=> Describe(portal=null) FE=> Execute(portal=null,limit=1) FE=> Sync <=BE BindComplete [null] <=BE CommandStatus(BEGIN) <=BE ParseComplete [null] <=BE BindComplete [null] <=BE NoData <=BE CommandStatus(INSERT 0 1) <=BE ReadyForQuery(T) simple execute, handler=org.postgresql.jdbc2.AbstractJdbc2Connection$TransactionCommandH andler@1988d36, maxRows=0, fetchSize=0, flags=22 FE=> Bind(stmt=S_2,portal=null) FE=> Execute(portal=null,limit=1) FE=> Sync <=BE BindComplete [null] <=BE CommandStatus(ROLLBACK) <=BE ReadyForQuery(I) XAResource 13d21d6: ending transaction xid = SimpleXidImpl[format=1c131d0a,gtrid={txmgrId=TM1,txmgrBirthTime=11456916 08045,myBirthTime=1145691634374,id=13}, bqual={1}] XAResource 13d21d6: committing xid = SimpleXidImpl[format=1c131d0a,gtrid={txmgrId=TM1,txmgrBirthTime=11456916 08045,myBirthTime=1145691634374,id=13}, bqual={1}] (one phase) Results of the code with conn.close() *after* userTransaction.commit(): XAResource 13d21d6: starting transaction xid = SimpleXidImpl[format=1c131d0a,gtrid={txmgrId=TM1,txmgrBirthTime=11456924 08570,myBirthTime=1145692431844,id=13}, bqual={1}] simple execute, handler=org.postgresql.jdbc2.AbstractJdbc2Statement$StatementResultHandl er@1fa157c, maxRows=0, fetchSize=0, flags=5 FE=> Bind(stmt=S_1,portal=null) FE=> Execute(portal=null,limit=0) FE=> Parse(stmt=null,query="INSERT INTO test (test_id) VALUES (0)",oids={}) FE=> Bind(stmt=null,portal=null) FE=> Describe(portal=null) FE=> Execute(portal=null,limit=1) FE=> Sync <=BE BindComplete [null] <=BE CommandStatus(BEGIN) <=BE ParseComplete [null] <=BE BindComplete [null] <=BE NoData <=BE CommandStatus(INSERT 0 1) <=BE ReadyForQuery(T) XAResource 13d21d6: ending transaction xid = SimpleXidImpl[format=1c131d0a,gtrid={txmgrId=TM1,txmgrBirthTime=11456924 08570,myBirthTime=1145692431844,id=13}, bqual={1}] XAResource 13d21d6: committing xid = SimpleXidImpl[format=1c131d0a,gtrid={txmgrId=TM1,txmgrBirthTime=11456924 08570,myBirthTime=1145692431844,id=13}, bqual={1}] (one phase) simple execute, handler=org.postgresql.jdbc2.AbstractJdbc2Connection$TransactionCommandH andler@1988d36, maxRows=0, fetchSize=0, flags=22 FE=> Parse(stmt=S_3,query="COMMIT",oids={}) FE=> Bind(stmt=S_3,portal=null) FE=> Execute(portal=null,limit=1) FE=> Sync <=BE ParseComplete [S_3] <=BE BindComplete [null] <=BE CommandStatus(COMMIT) <=BE ReadyForQuery(I) -----Original Message----- From: Kris Jurka [mailto:books@ejurka.com] Sent: vrijdag 21 april 2006 22:11 To: Niels Beekman Cc: pgsql-jdbc@postgresql.org Subject: Re: [JDBC] XA rollback problem On Fri, 21 Apr 2006, Niels Beekman wrote: > All inserts or updates I do using PGXADatasource get rolled back > silently (no exceptions). I reproduced the problem using a simple test > table with a single int2-column. Could you show us the java test code you are using as well? > As you can see in the driver logging it uses one-phase commit. I think > the problem is in the part that states *CommandStatus(ROLLBACK)*, the > driver just proceeds to ending and committing the transaction. The log alone isn't really enough to see what's going on here, because it doesn't show the Parse for the S_2 statement. It is unclear whether the driver is issuing a commit or a rollback here. Kris Jurka
On Sat, 22 Apr 2006, Niels Beekman wrote: > The problem occurs when calling conn.close(), if you move the close() > after the commit() it works just fine. I think the connection gets > invalidated while it should stay active because it still has to commit > or rollback (whatever the transactionmanager requests). > Indeed the closing before committing is a problem. The PGXADatasource code is piggybacking on the PooledConnection code. When you close a PooledConnection a rollback occurs so that the connection can be reused. For the XAConnection it must put the connection into a pending-closed state and wait for the transaction manager to commit or rollback the connection. It'll be a little tricky to make this work for both the PooledConnection and XAConnection cases, but I'll take a look at that this week. Also to test this I added pg support to Simple JTA, but this required adjusting the PGXADatasource code to have a setUrl method. Did you do the same or do you have a simpler way of testing? Kris Jurka
I'm glad that you were able to reproduce. Would be very nice if you could fix it this week. About the SimpleJTA: I passed the properties as a string separated by a ;, then split the properties again and pass them to the datasource using the ClassUtils. invokeMethod(). Would be convenient when the datasource had a setUrl(), but it's really SimpleJTA's job. Niels -----Original Message----- From: Kris Jurka [mailto:books@ejurka.com] Sent: maandag 24 april 2006 7:59 To: Niels Beekman Cc: pgsql-jdbc@postgresql.org Subject: Re: [JDBC] XA rollback problem On Sat, 22 Apr 2006, Niels Beekman wrote: > The problem occurs when calling conn.close(), if you move the close() > after the commit() it works just fine. I think the connection gets > invalidated while it should stay active because it still has to commit > or rollback (whatever the transactionmanager requests). > Indeed the closing before committing is a problem. The PGXADatasource code is piggybacking on the PooledConnection code. When you close a PooledConnection a rollback occurs so that the connection can be reused. For the XAConnection it must put the connection into a pending-closed state and wait for the transaction manager to commit or rollback the connection. It'll be a little tricky to make this work for both the PooledConnection and XAConnection cases, but I'll take a look at that this week. Also to test this I added pg support to Simple JTA, but this required adjusting the PGXADatasource code to have a setUrl method. Did you do the same or do you have a simpler way of testing? Kris Jurka
On Mon, 24 Apr 2006, Kris Jurka wrote: > Also to test this I added pg support to Simple JTA, but this required > adjusting the PGXADatasource code to have a setUrl method. Did you do the > same or do you have a simpler way of testing? > If anyone else is interested in looking at this the attached patch adds a test for this situation to our existing regression tests. Kris Jurka
Вложения
On Mon, 24 Apr 2006, Kris Jurka wrote: > Indeed the closing before committing is a problem. The PGXADatasource code > is piggybacking on the PooledConnection code. When you close a > PooledConnection a rollback occurs so that the connection can be reused. For > the XAConnection it must put the connection into a pending-closed state and > wait for the transaction manager to commit or rollback the connection. It'll > be a little tricky to make this work for both the PooledConnection and > XAConnection cases, but I'll take a look at that this week. > I have applied the attached patch to the 8.1 and head cvs branches which fixes it for me. I've uploaded some jars for you to test here, and a new official release should hopefully be out within the week. http://www.ejurka.com/pgsql/jars/nb/ Kris Jurka
Вложения
Hi Kris, Thank you very much, it works! Niels -----Original Message----- From: Kris Jurka [mailto:books@ejurka.com] Sent: woensdag 26 april 2006 22:31 To: Niels Beekman Cc: pgsql-jdbc@postgresql.org Subject: Re: [JDBC] XA rollback problem On Mon, 24 Apr 2006, Kris Jurka wrote: > Indeed the closing before committing is a problem. The PGXADatasource code > is piggybacking on the PooledConnection code. When you close a > PooledConnection a rollback occurs so that the connection can be reused. For > the XAConnection it must put the connection into a pending-closed state and > wait for the transaction manager to commit or rollback the connection. It'll > be a little tricky to make this work for both the PooledConnection and > XAConnection cases, but I'll take a look at that this week. > I have applied the attached patch to the 8.1 and head cvs branches which fixes it for me. I've uploaded some jars for you to test here, and a new official release should hopefully be out within the week. http://www.ejurka.com/pgsql/jars/nb/ Kris Jurka