I need a test program to get the database into this same state. I have tried
the following in attempt to get the database into the state. If I query
syscs_diag.transaction_table, I see
but if I restart the database service, the ACTIVE transaction is no longer
present. So it seems that this does not get the database into the same state
where there are ACTIVE XA transactions but no connections associated with these
transactions.
In trying to write a utility, I need first to get the database into the same
state. I cannot risk shutting down the customer's system if it is going to
take hours to come back up. I need some way of either committing or rolling
back these transactions while the system is up and running.
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(150);
// 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);
-----Original Message-----
From: Bergquist, Brett [mailto:bbergqu...@canoga.com]
Sent: Tuesday, December 06, 2011 11:45 AM
To: derby-dev@db.apache.org
Subject: RE: how can I force a rollback of an XA transaction
The database has the following transactions being show from querying
syscs_diag.transaction_table:
NPCAcv# sh /opt/csem/canogaview/app/scripts/solaris/gettrans.sh
ij version 10.8
ij> ij> XID |GLOBAL_XID
|USERNAME
|TYPE
|STATUS |FIRST_INSTANT |SQL_TEXT
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1089187896
|(4871251,588b2100c7e5bef84e50434163762c7365727665722c5033373030,4e50434163762c7365727665722c50333730302c00)
|CSEM
|UserTransaction |ACTIVE |NULL |NULL
1089188131
|(4871251,638b2100c7e5bef84e50434163762c7365727665722c5033373030,4e50434163762c7365727665722c50333730302c00)
|CSEM
|UserTransaction |ACTIVE |NULL |NULL
1089188159
|(4871251,648b2100c7e5bef84e50434163762c7365727665722c5033373030,4e50434163762c7365727665722c50333730302c00)
|CSEM
|UserTransaction |ACTIVE |NULL |NULL
1089187793
|(4871251,518b2100c7e5bef84e50434163762c7365727665722c5033373030,4e50434163762c7365727665722c50333730302c00)
|CSEM
|UserTransaction |ACTIVE |NULL |NULL
1089187804
|(4871251,508b2100c7e5bef84e50434163762c7365727665722c5033373030,4e50434163762c7365727665722c50333730302c00)
|CSEM
|UserTransaction |ACTIVE |NULL |NULL
1089188329
|(4871251,6e8b2100c7e5bef84e50434163762c7365727665722c5033373030,4e50434163762c7365727665722c50333730302c00)
|CSEM
|UserTransaction |ACTIVE |NULL |NULL
1089187874
|(4871251,558b2100c7e5bef84e50434163762c7365727665722c5033373030,4e50434163762c7365727665722c50333730302c00)
|CSEM
|UserTransaction |ACTIVE |NULL |NULL
1102388777
|(4871251,43e72b00c7e5bef84e50434163762c7365727665722c5033373030,4e50434163762c7365727665722c50333730302c00)
|CSEM
|UserTransaction |ACTIVE |NULL |NULL
1089188296
|(4871251,6b8b2100c7e5bef84e50434163762c7365727665722c5033373030,4e50434163762c7365727665722c50333730302c00)
|CSEM
|UserTransaction |ACTIVE |NULL |NULL
1089187948
|(4871251,598b2100c7e5bef84e50434163762c7365727665722c5033373030,4e50434163762c7365727665722c50333730302c00)
|CSEM
|UserTransaction |ACTIVE |NULL |NULL
1102388811
|(4871251,49e72b00c7e5bef84e50434163762c7365727665722c5033373030,4e50434163762c7365727665722c50333730302c00)
|CSEM
|UserTransaction |ACTIVE |NULL |NULL
1089188058
|(4871251,5e8b2100c7e5bef84e50434163762c7365727665722c5033373030,4e50434163762c7365727665722c50333730302c00)
|CSEM
|UserTransaction |ACTIVE |NULL |NULL
1089188423
|(4871251,6f8b2100c7e5bef84e50434163762c7365727665722c5033373030,4e50434163762c7365727665722c50333730302c00)
|CSEM
|UserTransaction |ACTIVE |NULL |NULL
1089188227
|(4871251,668b2100c7e5bef84e50434163762c7365727665722c5033373030,4e50434163762c7365727665722c50333730302c00)
|CSEM
|UserTransaction |ACTIVE |NULL |NULL
1102388774
|(4871251,42e72b00c7e5bef84e50434163762c7365727665722c5033373030,4e50434163762c7365727665722c50333730302c00)
|CSEM
|UserTransaction |ACTIVE |NULL |NULL
1089187872
|(4871251,568b2100c7e5bef84e50434163762c7365727665722c5033373030,4e50434163762c7365727665722c50333730302c00)
|CSEM
|UserTransaction |ACTIVE |NULL |NULL
1089188129
|(4871251,628b2100c7e5bef84e50434163762c7365727665722c5033373030,4e50434163762c7365727665722c50333730302c00)
|CSEM
|UserTransaction |ACTIVE |NULL |NULL
1089188425
|(4871251,718b2100c7e5bef84e50434163762c7365727665722c5033373030,4e50434163762c7365727665722c50333730302c00)
|CSEM
|UserTransaction |ACTIVE |NULL |NULL
1089187780
|(4871251,4c8b2100c7e5bef84e50434163762c7365727665722c5033373030,4e50434163762c7365727665722c50333730302c00)
|CSEM
|UserTransaction |ACTIVE |NULL |NULL
1089188258
|(4871251,6a8b2100c7e5bef84e50434163762c7365727665722c5033373030,4e50434163762c7365727665722c50333730302c00)
|CSEM
|UserTransaction |ACTIVE |NULL |NULL
1089188088
|(4871251,608b2100c7e5bef84e50434163762c7365727665722c5033373030,4e50434163762c7365727665722c50333730302c00)
|CSEM
|UserTransaction |ACTIVE |NULL |NULL
1089187779
|(4871251,4e8b2100c7e5bef84e50434163762c7365727665722c5033373030,4e50434163762c7365727665722c50333730302c00)
|CSEM
|UserTransaction |ACTIVE |NULL |NULL
1089187873
|(4871251,578b2100c7e5bef84e50434163762c7365727665722c5033373030,4e50434163762c7365727665722c50333730302c00)
|CSEM
|UserTransaction |ACTIVE |NULL |NULL
1089188008
|(4871251,5d8b2100c7e5bef84e50434163762c7365727665722c5033373030,4e50434163762c7365727665722c50333730302c00)
|CSEM
|UserTransaction |ACTIVE |NULL |NULL
1089188233
|(4871251,678b2100c7e5bef84e50434163762c7365727665722c5033373030,4e50434163762c7365727665722c50333730302c00)
|CSEM
|UserTransaction |ACTIVE |(462881,845067) |NULL
1089188322
|(4871251,6c8b2100c7e5bef84e50434163762c7365727665722c5033373030,4e50434163762c7365727665722c50333730302c00)
|CSEM
|UserTransaction |ACTIVE |NULL |NULL
1097595231
|(4871251,bbde2b00c7e5bef84e50434163762c7365727665722c5033373030,4e50434163762c7365727665722c50333730302c00)
|CSEM
|UserTransaction |ACTIVE |NULL |NULL
1089187814
|(4871251,528b2100c7e5bef84e50434163762c7365727665722c5033373030,4e50434163762c7365727665722c50333730302c00)
|CSEM
|UserTransaction |ACTIVE |NULL |NULL
1102388810
|(4871251,48e72b00c7e5bef84e50434163762c7365727665722c5033373030,4e50434163762c7365727665722c50333730302c00)
|CSEM
|UserTransaction |ACTIVE |NULL |NULL
1102383932
|(4871251,41e72b00c7e5bef84e50434163762c7365727665722c5033373030,4e50434163762c7365727665722c50333730302c00)
|CSEM
|UserTransaction |ACTIVE |NULL |NULL
1089187870
|(4871251,538b2100c7e5bef84e50434163762c7365727665722c5033373030,4e50434163762c7365727665722c50333730302c00)
|CSEM
|UserTransaction |ACTIVE |NULL |NULL
1102388800
|(4871251,47e72b00c7e5bef84e50434163762c7365727665722c5033373030,4e50434163762c7365727665722c50333730302c00)
|CSEM
|UserTransaction |ACTIVE |NULL |NULL
1089187871
|(4871251,548b2100c7e5bef84e50434163762c7365727665722c5033373030,4e50434163762c7365727665722c50333730302c00)
|CSEM
|UserTransaction |ACTIVE |NULL |NULL
1089187771
|(4871251,4b8b2100c7e5bef84e50434163762c7365727665722c5033373030,4e50434163762c7365727665722c50333730302c00)
|CSEM
|UserTransaction |ACTIVE |(462881,826529) |NULL
1089188007
|(4871251,5c8b2100c7e5bef84e50434163762c7365727665722c5033373030,4e50434163762c7365727665722c50333730302c00)
|CSEM
|UserTransaction |ACTIVE |NULL |NULL
1089188086
|(4871251,618b2100c7e5bef84e50434163762c7365727665722c5033373030,4e50434163762c7365727665722c50333730302c00)
|CSEM
|UserTransaction |ACTIVE |NULL |NULL
1102388796
|(4871251,46e72b00c7e5bef84e50434163762c7365727665722c5033373030,4e50434163762c7365727665722c50333730302c00)
|CSEM
|UserTransaction |ACTIVE |NULL |NULL
1089188323
|(4871251,6d8b2100c7e5bef84e50434163762c7365727665722c5033373030,4e50434163762c7365727665722c50333730302c00)
|CSEM
|UserTransaction |ACTIVE |NULL |NULL
1089187986
|(4871251,5a8b2100c7e5bef84e50434163762c7365727665722c5033373030,4e50434163762c7365727665722c50333730302c00)
|CSEM
|UserTransaction |ACTIVE |NULL |NULL
1089188426
|(4871251,708b2100c7e5bef84e50434163762c7365727665722c5033373030,4e50434163762c7365727665722c50333730302c00)
|CSEM
|UserTransaction |ACTIVE |NULL |NULL
1102376671
|(4871251,40e72b00c7e5bef84e50434163762c7365727665722c5033373030,4e50434163762c7365727665722c50333730302c00)
|CSEM
|UserTransaction |ACTIVE |NULL |NULL
1089188238
|(4871251,688b2100c7e5bef84e50434163762c7365727665722c5033373030,4e50434163762c7365727665722c50333730302c00)
|CSEM
|UserTransaction |ACTIVE |NULL |NULL
1089188226
|(4871251,658b2100c7e5bef84e50434163762c7365727665722c5033373030,4e50434163762c7365727665722c50333730302c00)
|CSEM
|UserTransaction |ACTIVE |NULL |NULL
1089188085
|(4871251,5f8b2100c7e5bef84e50434163762c7365727665722c5033373030,4e50434163762c7365727665722c50333730302c00)
|CSEM
|UserTransaction |ACTIVE |NULL |NULL
44 rows selected
I have stopped the application server so I know that it is not active and there
are no other connections open to the database beside the connection being used
by IJ. I tried the little utility to see if I could cause these transactions
to rollback, but it acts as if it does not see these transactions because they
are active.
The utility is basically what you suggested Knut.
Any ideas on where to go from here?
-----Original Message-----
From: Bergquist, Brett [mailto:bbergqu...@canoga.com]
Sent: Tuesday, December 06, 2011 9:50 AM
To: derby-dev@db.apache.org
Subject: RE: how can I force a rollback of an XA transaction
I will give this a try and that is exactly what I was just trying to write.
This is the second time in about 5 years that this has happened :( I need to
get away from using XA but the application server was bitching without it.
Thank you for your help on this. It would have taken me much longer to come up
with this myself.
Brett
-----Original Message-----
From: Knut Anders Hatlen [mailto:knut.hat...@oracle.com]
Sent: Tuesday, December 06, 2011 9:27 AM
To: derby-dev@db.apache.org
Subject: Re: how can I force a rollback of an XA transaction
"Bergquist, Brett" <bbergqu...@canoga.com> writes:
> The JVM that derby was running in crashed with a SEGV. There
> application server also went down. When I brought both up, the
> database shows two XA transactions in the PREPARE state (looking at
> syscs_diag.transaction_table) but the application server has no
> reference to these transactions to force a rollback. Files in the
> database “log” directory are being created, with 3862 being the
> current count.
>
> I need a way to get this to be cleaned up. It would be best if this
> could be done with the database online as I don’t have days to wait
> for the database to come up.
Hi Brett,
If your application server doesn't manage to recover the transactions,
you could try to run the XA recovery procedure manually. Something like
this (untested) code:
XAConnection xac = ...; // get connection from the XADataSource
XAResource xar = xac.getXAResource();
for (Xid xid : xar.recover(XAResource.TMSTARTRSCAN)) {
xar.rollback(xid);
// or commit:
// xar.commit(xid, false);
}
Hope this helps,
--
Knut Anders
