db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Katherine Marsden <kmarsdende...@sbcglobal.net>
Subject Re: Need help with clearing a XA transaction ! Important
Date Fri, 25 Mar 2016 15:43:01 GMT
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 <BBergquist@canoga.com> 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).

Mime
View raw message