Microsoft KB Archive Search

Notice: This website is an unofficial Microsoft Knowledge Base (hereinafter KB) archive and is intended to provide a reliable access to deleted content from Microsoft KB. All KB articles are owned by Microsoft Corporation. Read full disclaimer for more details.

  1. Home
  2. Orphaned XA transactions when you connect to SQL Server by using JDBC Driver for SQL Server

Orphaned XA transactions when you connect to SQL Server by using JDBC Driver for SQL Server

View products that this article applies to.

Symptoms

When you use XA transactions, Microsoft SQL Server JDBC Driver, and Microsoft SQL Server as the back-end database, you may have orphaned transactions pending on the SQL Server if the transaction manager experiences a crash or a connectivity issue.

Additionally, you may experience one of the following common symptoms:
  • Transactions are pending for a long time on the instance of SQL Server. They may have either a null or -2 session ID in the database.
  • Transactions are blocked for a long time by a transaction that has a -2 session ID. This causes a lock time-out or threads to freeze.

↑ Back to the top

Cause

If the connection between the transaction manager and SQL Server is lost for an unprepared transaction, then the JDBC Driver for SQL Server does not clean up these transactions, and they may continue to use resources and block other transactions. Because of the XA implementation in the JDBC Driver for SQL Server, SQL Server cannot detect abnormal disconnections of transaction managers. Therefore, for SQL Server, these transactions stay in the system until the XA transaction times out or the database is restarted. The time-out uses infinity on the instance of SQL Server, and is not configurable on the database.

↑ Back to the top

Workaround

To resolve this issue, use one of the following methods:
  • Restart the database server when you experience any Java Virtual Machine (JVM) crashes or network connectivity issues that result in these orphaned transactions.
  • Individually stop the orphaned transactions on the database to clean up their resources. To verify that a transaction is orphaned, make sure that it is not an in-doubt (prepared) transaction or one that is currently completing. Any transactions that last through a restart of the related JVM and a successful recovery cycle should be orphaned.
  • Set a time-out value for the XA transactions that is longer than your longest transaction to force them to stop as soon as the time-out value is exceeded. You can do this by calling the XAResource.setTransactionTimeout() method.
  • For many third-party transaction managers, the XAResource object is not directly accessed by the calling code and the transaction manager does not set the transaction time-out value. In this situation, an external application can be used to set the transaction time-out to a new default value. This new default will be used by all other transactions that do not specify a transaction time-out.

↑ Back to the top

Keywords: kb, kbsurveynew, kbexpertiseadvanced, kbtshoot

↑ Back to the top

Article Info
Article ID : 3005016
Revision : 1
Created on : 1/7/2017
Published on : 10/20/2014
Exists online : False
Views : 314