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

ID: Q235220

The information in this article applies to:

Microsoft SQL Server version 6.5

BUG #: 18764 (SQLBUG_65)

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.

RESOLUTION

A supported fix that corrects this problem is now available from Microsoft, but it has not been fully regression tested and should be applied only to systems experiencing this specific problem. If you are not severely affected by this specific problem, Microsoft recommends that you wait for the next SQL Server service pack that contains this fix.

To resolve this problem immediately, contact Microsoft Product Support Services to obtain the fix. For a complete list of Microsoft Product Support Services phone numbers and information on support costs, please go to the following address on the World Wide Web:

http://www.microsoft.com/support/supportnet/overview/overview.asp

The English version of this fix should have the following file attributes or later:


   File name     Platform
   ----------------------
   SQL449i.exe   INTEL
   SQL449a.exe   ALPHA

NOTE: Due to file dependencies, the most recent hotfix or feature that contains the above files may also contain additional files.

WORKAROUND

To work around this problem, limit the number of connections (SPIDs) bound to a single DTC transaction to a single connection.

STATUS

Microsoft has confirmed this to be a problem in SQL Server version 6.5.

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.

Additional query words: hang hangs hung sync synch dtc transaction xact

Keywords :
Version : winnt:6.5
Platform : winnt
Issue type : kbbug