Re: XA rollback problem

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(at)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(at)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(at)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(at)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(at)ejurka(dot)com]
Sent: vrijdag 21 april 2006 22:11
To: Niels Beekman
Cc: pgsql-jdbc(at)postgresql(dot)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

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