I have a database with a stuck XA transaction. Derby 10.9.
I am able to reproduce this problem with two test programs. In the first
program I do:
try {
System.out.println("Connection to the database");
XAConnection xaConnection = null;
Connection conn = null;
String driver = "org.apache.derby.jdbc.ClientDataSource";
ClientXADataSource ds = new ClientXADataSource();
ds.setDatabaseName("csemdb");
ds.setServerName("localhost");
ds.setPortNumber(1527);
Class.forName(driver);
xaConnection = ds.getXAConnection("CSEM", "CSEM");
conn = xaConnection.getConnection();
System.out.println("creating a transaction");
XAResource xaResource = xaConnection.getXAResource();
Xid xid = new MyXid(100, new byte[]{0x01}, new byte[]{0x02});
xaResource.start(xid, XAResource.TMNOFLAGS);
createTransaction(conn);
xaResource.end(xid, XAResource.TMSUCCESS);
System.exit(1);
} catch (XAException ex) {
Logger.getLogger(RollbackTransactionsTest.class.getName()).log(Level.SEVERE,
null, ex);
} catch (SQLException ex) {
Logger.getLogger(RollbackTransactionsTest.class.getName()).log(Level.SEVERE,
null, ex);
} catch (ClassNotFoundException ex) {
Logger.getLogger(RollbackTransactionsTest.class.getName()).log(Level.SEVERE,
null, ex);
}
Basically I create a XA transaction, start it, execute an insert statement, end
it, but never prepare or commit it and the exit the application. The leaves a
transaction in the database:
XID GLOBAL_XID USERNAME TYPE STATUS
FIRST_INSTANT SQL_TEXT
132775 (100,01,02) CSEM UserTransaction ACTIVE
(108,782111) <null>
I try to rollback this transaction with another program but it does not even
see the transaction:
try {
System.out.println("Connection to the database");
XAConnection xaConnection = null;
Connection conn = null;
String driver = "org.apache.derby.jdbc.ClientDataSource";
ClientXADataSource ds = new ClientXADataSource();
ds.setDatabaseName("csemdb");
ds.setServerName("localhost");
ds.setPortNumber(1527);
Class.forName(driver);
xaConnection = ds.getXAConnection("CSEM", "CSEM");
conn = xaConnection.getConnection();
System.out.println("Transactions before");
dumpTransactionTable(conn);
XAResource xaResource = xaConnection.getXAResource();
System.out.println("Scanning for XA Transactions");
for (Xid xid :
xaResource.recover(XAResource.TMSTARTRSCAN|XAResource.TMENDRSCAN)) {
System.out.println("Rolling back " + xid.toString());
xaResource.rollback(xid);
System.out.println("Rolled back " + xid.toString());
}
System.out.println("Transactions after");
dumpTransactionTable(conn);
} catch (XAException ex) {
Logger.getLogger(RollbackTransactions.class.getName()).log(Level.SEVERE, null,
ex);
} catch (SQLException ex) {
Logger.getLogger(RollbackTransactions.class.getName()).log(Level.SEVERE, null,
ex);
} catch (ClassNotFoundException ex) {
Logger.getLogger(RollbackTransactions.class.getName()).log(Level.SEVERE, null,
ex);
}
This shows this when run:
Connection to the database
Transactions before
XID,GLOBAL_XID,USERNAME,TYPE,STATUS,FIRST_INSTANT,SQL_TEXT
132775,(100,01,02),CSEM,UserTransaction,ACTIVE,(108,782111),null
Scanning for XA Transactions
Transactions after
XID,GLOBAL_XID,USERNAME,TYPE,STATUS,FIRST_INSTANT,SQL_TEXT
132775,(100,01,02),CSEM,UserTransaction,ACTIVE,(108,782111),null
So the transaction is seen in the syscs_diag.transaction_table, but not seen by
the XAResource.recover.
Any help will be greatly appreciated as this has occurred in a production
environment and because of such, the derby transaction logs are multiplying.
Shutting down the system and restarting will take many hours for derby to
process the transaction logs.
________________________________
Canoga Perkins
20600 Prairie Street
Chatsworth, CA 91311
(818) 718-6300
This e-mail and any attached document(s) is confidential and is intended only
for the review of the party to whom it is addressed. If you have received this
transmission in error, please notify the sender immediately and discard the
original message and any attachment(s).
