Обсуждение: 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