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.
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.
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.
