FIX: Enlisting Multiple Connections in the Same DTC Transaction Context Can Cause SPID Stop Responding (235220)

SYMPTOMS

Enlisting multiple connections in the same Distributed Transaction Coordinator (DTC) transaction context can lead to SQL Server connections (SPIDs) not responding.

The following is a stack trace of an ODBC connection that has stopped responding, using the TCP/IP network library to connect to SQL Server. The client is waiting for a response packet from the SQL Server when performing the enlistment operation.

1 id: 1d4.151 Suspend: 0 Teb 7ffdd000 Unfrozen
ChildEBP RetAddr Args to Child
00cef098 776689ce 000000ac 00000001 00cef0c8 ntdll!ZwWaitForSingleObject+0xb
00cef0f0 776642b6 000000ac 00000050 00000002 msafd!SockWaitForSingleObject+0x204
00cef160 776b84ee 00000050 00cef1c0 00000001 msafd!WSPRecv+0x14b
00cef19c 776d1173 00000050 00cef1c0 00000001 WS2_32!WSARecv+0xae
00cef1d0 73331198 00000050 01141e68 00001000 WSOCK32!recv_v11+0x33
00cef214 73331423 0114a030 01141e68 00ce0008 DBMSSOCN!ConnectionRead+0xff
00cef238 41231a20 0114a030 01141e68 01142e80 DBMSSOCN!ConnectionTransact+0x6f - Waiting on packet from PROPOGATE operation to return
0114a030 00000050 00000000 00000000 00000000 sqlsrv32!SQLSetConnectAttrW+0x7f3 - Handling SQLEnlistTransaction
.
.
.

CAUSE

When enlisting multiple connections (SPIDs) in the same DTC transaction context, the SQL Server must consider the SPIDs as bound. The following is from the SQL Server 7.0 Books Online, in the "Using Bound Connections" topic:

Bound connections allow two or more connections (SPIDs) to share the same transaction and locks. Bound connections can work on the same data without lock conflicts.

SQL Server experiences a synchronization problem keeping the bound connections synchronized with the DTC transaction. During enlistment, the SPIDs can infinitely block each other as they attempt to maintain synchronization and propagate with the designated Distributed Transaction Coordinator. The client attempting to enlist in the DTC transaction will encounter a wait in SQLSetConnectOption.

STATUS

Microsoft has confirmed this to be a problem in SQL Server version 6.5. This problem has been corrected in the Post Service Pack 5a Update for Microsoft SQL Server version 6.5. To install the Post Service Pack 5a Update, you must have either SQL Server 6.5 SP5 or SP5a installed.

For information about how to download and install the SQL Server 6.5 Service Pack 5a, refer to the following article in the Microsoft Knowledge Base:

197177 INF: How to Obtain SQL Server 6.5 Service Pack 5a

If you already have SQL Server 6.5 SP5 or SP5a installed, you can download the Post SP5a Update from the following article in the Microsoft Knowledge Base:

274036 INF: How to Obtain SQL Server 6.5 Post Service Pack 5a Update

For more information, contact your primary support provider.

MORE INFORMATION

When establishing a connection to the SQL Server, it is possible to enlist (join) the connection in an active DTC transaction. This is commonly done from the Microsoft Transaction Server (MTS) environment to enforce the transactional property settings of the package.

The ODBC API exposes this with SQLSetConnectOption.

SQLSetConnectOption (ptr->hdbc, SQL_COPT_SS_ENLIST_IN_DTC, (UDWORD)pTransaction);

NOTE: Similar behavior can be obtained with the DB-Library API or an OLE DB provider.

For more information on the DTC transaction code, see DtcGetTransactionManager and the ITransactionDispenser BeginTransaction method.

For more information on the MTS transaction scopes, refer to the MTS documentation and the GetContextObject method. The opposite attempt to create two DTC transactions and attempt to enlist the same ODBC connection in both is not standard protocol. The client should abort the first transaction before attempting the second enlistment. The attempt to enlist the same connection in the second DTC scope will lead to a connection block in the SQLSetConnectOption. The DTC monitor will show two active transactions but will never change from the active state.