xa connection issues

[Jonathan Halliday]

Hello SQL Server gurus

I'm having an issue with the following scenario:

- Java 1.6 client using MS SQL JDBC drivers 2.0 obtains an XAConnection to
SQL Server 2005 using SQLServerXADataSource
- client obtains an XAResource, calls start on it.
- client obtains a Connection, does some SQL, resulting in locks on data
structures in the db.
- client crashes.

Note the crash occurs before an xa prepare

- client reconnects, attempts further SQL in a different transaction, blocks
because the original XA transaction is still holding the locks.

- client attempts a recovery (XAResource.recover) pass on the server, to
find and resolve the outstanding XA transaction, thus releasing the lock. The
server returns 0 in-doubt transactions.

- the MS DTC console shows the XA transaction, but does not consider it
in-doubt (which is correct, as it's not prepared yet) so won't allow a commit
or rollback to be forced. Nor will it allow a forget, as a resource maanger
(SQLServer) is still active.

- The XA transaction and associated locks can be removed by restarting DTC
or SQL Server, but that's not a viable option on a production system.

In my view the SQLServer should abort the XA transaction when the JDBC
client connection drops, in accordance with XA's presumed abort approach.
This does not appear to be happening.

Is there any way to force such behaviour, or have the client discover and
resolve the orphaned XA transaction?

Thanks

[christor]

Hi Jonathan, I'm having a very similar problem. See here:

http://forums.java.net/jive/thread.jspa?threadID=61749&tstart=0

Have you made any progress on correcting this, or found out any more?

Thanks,

--Chris

[Manfred Rosenboom]

You should start your new XA session with an XA Recovery:

Xid[] xids = xares.recover(XAResource.TMSTARTRSCAN);

If the Xid array isn't empty, you have open XA transactions
to handle and you have three options to react:

1. xares.forget(xid)
You don't know this global transaction and give the resource
manager the freedom to act how he like to act.

2. xares.rollback(xid)
You want a rollback of the global tranaction

3. xares.commit(xid, <false or true>)
You want a commit of the global tranaction.

After you have handled the list of Xids you can start
with the intended XA session.

Best,
Manfred

[Jonathan Halliday]

"Manfred Rosenboom" wrote:

> You should start your new XA session with an XA Recovery:

Thanks, but it looks like you did not read my original post?

"- client attempts a recovery (XAResource.recover) pass on the server, to
find and resolve the outstanding XA transaction, thus releasing the lock. The
server returns 0 in-doubt transactions."

or to put it another way... I tried that and it does not help. The tx is not
considered in-doubt because it's not prepared yet, so it's not returned in
the recover scan and thus there is no way to reattach to it.

[Manfred Rosenboom]

Sorry, my fault :-(

This seems to be a problem of the SQL Server 2005 or the related JDBC
Driver. I have performed my last XA test with SQL Server 2000 and the
old JDBC driver. The result was as follows: if I have closed my
conenction BEFORE the xa end call the transaction was automatically
rolled back. If I have closed my conenction AFTER the xa end call (using
XA_OK) the transaction was indoubt and also visible for the xa recover
in the next session.

This behavior differs from Oracle: as long as I haven't called xa
prepare a transaction will be rolled back by closing the connection.

Best,
Manfred

[Mugunthan Mugundan]

What version of the OS you are running on the SQL Machine.

[Evan T. Basalik (MSFT)]

Just an FYI for folks reading this thread. Jonathan opened up a support
case and it looks like he was missing an MSDTC hotfix
(http://support.microsoft.com/default.aspx?scid=kb;EN-US;899756).