Upravit

Orphaned XA transactions when using Microsoft JDBC Driver for SQL Server

Original KB number:   3005016

Summary

When you use XA transactions that include the Microsoft JDBC Driver for SQL Server, you might see orphaned transactions that stay pending on the server running Microsoft SQL Server. These orphaned transactions typically occur when the transaction manager stops responding or loses connectivity to the SQL Server-based server before the XA transaction finishes. This article explains the symptoms, causes, diagnostic steps, and resolution strategies for orphaned XA transactions in SQL Server.

Symptoms

You might experience one or more of the following issues:

  • Transactions stay in a pending state for a long time in SQL Server.
  • Transactions appear by having a NULL or -2 session ID in the database.
  • Transactions block other transactions indefinitely, and cause lock timeouts and frozen threads.

Verify orphaned XA transactions

To verify orphaned XA transactions, follow these steps:

  1. Query active XA transactions by using the sys.dm_tran_active_transactions dynamic management view (DMV):

    SELECT transaction_id, name, transaction_state
FROM sys.dm_tran_active_transactions
WHERE name LIKE '%XA%';

  2. Check for transactions that have a session ID of -2:

    SELECT request_session_id, resource_type, resource_database_id
FROM sys.dm_tran_locks
WHERE request_session_id = -2;

    This ID indicates an orphaned distributed transaction.

  3. Verify that the transaction meets both the following conditions before you try to resolve the issue:

    • The transaction isn't in a prepared (in-doubt) state.
    • The transaction isn't completing recovery.

Cause

If connectivity between the transaction manager and SQL Server is lost before the XA transaction is prepared, the JDBC Driver doesn't clean up these transactions. Because of the XA implementation in the JDBC Driver for SQL Server, SQL Server can't detect abnormal disconnections of transaction managers.

The result is that orphaned transactions remain active until one of the following events occurs:

  • The XA transaction timeout occurs. By default, SQL Server sets the timeout to infinity.
  • SQL Server restarts.

Note

The XA transaction timeout isn't configurable at the SQL Server level. It requires external handling.

Solutions

Use one of the following methods to resolve orphaned XA transactions.

Restart SQL Server

Restart SQL Server after any JVM failure or network disruption that generates orphaned transactions. A restart clears in-memory transaction references.

Manually terminate orphaned transactions

  1. Run the following query to identify and stop transactions that survive application recovery:

    SELECT transaction_id, name
FROM sys.dm_tran_active_transactions
WHERE name LIKE '%XA%';

  2. After you find the IDs of the orphaned transactions, commit or roll back by using XAResource in Java. For example:

    • Commit

      XAResource xaRes = xaConnection.getXAResource();
xaRes.commit(xaTransactionId, bOnePhase);

    • Roll back

      XAResource xaRes = xaConnection.getXAResource();
xaRes.rollback(xaTransactionId);

Configure XA transaction timeout

Set an XA timeout to enforce automatic cleanup of orphaned transactions. The timeout value should be longer than your longest-running transaction. For example:

XAResource xaRes = xaConnection.getXAResource();
xaRes.setTransactionTimeout(600); // Timeout in seconds

Note

Many third-party transaction managers don't expose XAResource. In these cases, use an external management app to set a default timeout value globally.

Preventive best practices

Follow these guidelines to reduce the risk of orphaned XA transactions:

Related content

  • Last updated on