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 [bbergqu...@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);
}
}
