大网数据广告活动
数据库与应用安全

Fix orphaned XA transactions with the JDBC Driver for SQL Server

· 52CE 我爱测

Resolve orphaned XA transactions in SQL Server caused by JDBC Driver connectivity failures. Discover diagnostic queries, manual termination steps, and timeout configurations to fix the issue.

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

Source: Microsoft Learn / IT Pro troubleshooting by Microsoft and contributors. Documentation licensed CC BY 4.0. Code samples are covered by the MIT License. Converted from Markdown, with links normalized and embedded media omitted.

资料来源与适用范围

资料来源
Microsoft Learn / IT Pro troubleshooting
适用范围
See source applicability · Source date 04/14/2026
内容语言
中文;命令、路径和错误标识保留技术原文
本地收录日期
2026-11-15
授权说明
CC BY 4.0 (documentation), MIT (code samples)

正文与所需资源由本站本地存储。查看来源及更新记录 ↗

请按适用版本和实际日志确认原因;本站没有对每一种系统或设备逐项执行文中的操作。