Seeing something like DERBY-2220 (marked closed/fixed) happening

[Bergquist, Brett]

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);
    }
}