It's been quite a while, and I don't even have Derby setup but I'll ask some questions.
Do you see the same behavior with embedded? With Network Server, in your test environment, does the transaction persist if you bounce network server? It seems to me there was a command to list the network server active connections. Can you run that and see if the session is still there? Kathey > On Mar 25, 2016, at 7:48 AM, Bergquist, Brett <[email protected]> wrote: > > 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).
