Orphaned XA transactions might exist when using Microsoft SQL Server with IBM Business Process Manager (BPM)

Symptom

You might experience the following symptoms of this issue:

• Prior to the issues, you might have had a Java™ virtual machine (JVM) crash or network connectivity issue causing a transaction to terminate while it is in an unprepared state. This scenario does not refer to indoubt (prepared) transactions, which are recovered when a JVM restarts.
• You see that a transaction persists in the database for a long time. These problematic transactions have either a null or -2 session ID in the database. Although there are other transaction states that can use this session ID, those transactions generally are cleaned up quickly or during recovery.
• You might see that current transactions are blocked for a long time frame by a transaction with a -2 session ID. This scenario can lead to lock timeouts or hung threads depending on the lock timeout setting.
• Depending on the orphaned transaction, you can have various behaviors that have a varying impact to the environment:
• Stuck business process definition (BPD) instances
• Hung threads that are waiting on query responses
• A lock that prevents the server from starting up.

• Unprepared transaction:
  A transaction is unprepared while the bulk of the processing is occurring between the xastart and xaend commands. The xaprepare command is called just before the transaction is ready to commit or rollback. These transactions are not persistent.
• Prepared transaction:
  For these transactions, the prepare command has completed and the process to commit or rollback has begun. These transactions are persisted to the WebSphere Application Server tranlogs. There is a small window for issues to occur. If they occur, they lead to in-doubt transactions, which are recovered after a server restart.
• Transaction Manager:
  The Transaction Manager is the code in WebSphere Application Server that manages the flow of the transaction and the recovery of it.
• Resource Manager:
  The Resource Manager is the part of the database that manages the transaction resources on the database side. An XA transaction might involve multiple resource managers.
• Connection Manager:
  The Connection Manager is the code in WebSphere Application Server that manages the communication between the transaction manager and the JDBC driver that communicates with the resource manager.