db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Bergquist, Brett" <BBergqu...@canoga.com>
Subject RE: Seeing something like DERBY-2220 (marked closed/fixed) happening
Date Wed, 07 Dec 2011 01:47:33 GMT
More testing and more information. 

If using a connection with no timeout starts an XA transaction with 

    xaRes.start(xid, XAResource.TMNOFLAGS);

and some work is done but the application fails without calling

   xaRes.end(xid, XAResource.TMSUCCESS)

then another application cannot join that transaction using

   xaRes.start(xid, XAResource.TMJOIN)

and then commit or rollback the transaction.  Basically the transaction is ACTIVE but cannot
be rolled back or committed and will exist until the database server is restarted.

So in my case, it looks like a Glassfish provided connection failed after work was performed
without calling the XAResource.end().  These transactions are existing, holding on to locks,
causing transaction logs to be created, etc. and they will exist until I restart Derby network
server.  With about 1500 of these, it looks like this will take between 2 and 5 hours for
the database to boot up as clean.  Ouch :(

I am just starting to become familiar with the XA but it seems as if a XA connection does
not have a timeout set is active (that is XAResource.start has been called but XAResource.end
has not) and the connection gets closed, the XA transaction should be rolled back implicitly.
 Any thoughts?
________________________________________
From: Bergquist, Brett [BBergquist@canoga.com]
Sent: Tuesday, December 06, 2011 3:29 PM
To: derby-dev@db.apache.org
Subject: Seeing something like DERBY-2220 (marked closed/fixed) happening

In a previous email I discussed that I have a database with 44 XA transactions in the ACTIVE
state associated with no connections.  In trying to find out the cause and a possible solution,
I tried writing a test case that would put the database into the same state.  Below is the
code to get the database into the same state.  Basically it creates a XA transaction, does
some work, and then exits without explicitly ending the transaction.   This leaves the database
with an active XA transaction with no connections and also leaves locks associated with this
transaction in existence.

In doing some looking around I came across DERBY-2220 which is marked as closed/fixed which
indicates that locks are released if the application terminates or crashes.

https://issues.apache.org/jira/browse/DERBY-2220?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12474776

But I just proved that this is not the case with my test case.  So I set about to find out
the difference.  In my test case,  I do not have:

 xaResource.setTransactionTimeout(10);

The reason that I did not have this is that Glassfish V2.1.1 in its default installation does
not have transaction timeouts configured.  So in my test case I did not either.  Without this,
the XA transaction stays around and locks are still held.  If I add setting of the timeout,
then this is not the case.  Note that even if I set the timeout to something large like 600
seconds (10 minutes), immediately after the client code exists, the XA transaction no longer
exits and the locks are released.

So is this the expected behavior if there is no timeout set?

A bigger question is how can I get the existing XA transactions that are ACTIVE to be either
committed or rolled back without restarting the database engine (using NetworkControlServer).

Here is the test code:

/*
* To change this template, choose Tools | Templates
* and open the template in the editor.
*/
package createtest;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.Statement;
import javax.sql.XAConnection;
import javax.transaction.xa.XAResource;
import javax.transaction.xa.Xid;
import org.apache.derby.jdbc.ClientXADataSource;

/**
*
* @author brett
*/
public class Main {

    /**
     * @param args the command line arguments
     */
    public static void main(String[] args) {
        // Create a variable for the connection string.
        String connectionUrl = "jdbc:derby://localhost:1527/csemdb" + ";user=CSEM;password=CSEM";

        try {
            // Establish the connection.
            Class.forName("org.apache.derby.jdbc.ClientDriver");
            Connection con = DriverManager.getConnection(connectionUrl);

            // Create a test table.
            Statement stmt = con.createStatement();
            try {
                stmt.executeUpdate("DROP TABLE XAMin");
            } catch (Exception e) {
            }
            stmt.executeUpdate("CREATE TABLE XAMin (f1 int, f2 varchar(1024))");
            stmt.close();
            con.close();

            // Create the XA data source and XA ready connection.
            ClientXADataSource ds = new ClientXADataSource();
            ds.setUser("CSEM");
            ds.setPassword("CSEM");
            ds.setServerName("localhost");
            ds.setPortNumber(1527);
            ds.setDatabaseName("csemdb");
            XAConnection xaCon = ds.getXAConnection();
            con = xaCon.getConnection();

            // Get a unique Xid object for testing.
            XAResource xaRes = null;
            Xid xid = null;
            xid = XidImpl.getUniqueXid(1);

            // Get the XAResource object and set the timeout value.
            xaRes = xaCon.getXAResource();
/            xaRes.setTransactionTimeout(600);

            // Perform the XA transaction.
            System.out.println("Write -> xid = " + xid.toString());
            xaRes.start(xid, XAResource.TMNOFLAGS);
            PreparedStatement pstmt = con.prepareStatement("INSERT INTO XAMin (f1,f2) VALUES
(?, ?)");
            pstmt.setInt(1, 1);
           pstmt.setString(2, xid.toString());
            pstmt.executeUpdate();

            // Commit the transaction.
//            xaRes.end(xid, XAResource.TMSUCCESS);
//            xaRes.prepare(xid);
//            try {
//                //xaRes.commit(xid, true);
//            } catch (Exception e) {
//                e.printStackTrace();
//            }
//
//            // Cleanup.
//            pstmt.close();
//            con.close();
//            xaCon.close();
//
//            // Open a new connection and read back the record to verify that it worked.
//            con = DriverManager.getConnection(connectionUrl);
//            ResultSet rs = con.createStatement().executeQuery("SELECT * FROM XAMin");
//            rs.next();
//            System.out.println("Read -> xid = " + rs.getString(2));
//            rs.close();
//            con.close();
        } // Handle any errors that may have occurred.
        catch (Exception e) {
            e.printStackTrace();
        }
        System.exit(1);
    }
}


Mime
View raw message