Article ID: 235220 - View products that this article applies to.
This article was previously published under Q235220
This article has been archived. It is offered "as is" and will no longer be updated.
BUG #: 18764 (SQLBUG_65)
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.
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.
To work around this problem, limit the number of connections (SPIDs) bound to a single DTC transaction to a single connection.
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:
197177If 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:
(http://support.microsoft.com/kb/197177/EN-US/ )INF: How to Obtain SQL Server 6.5 Service Pack 5a
274036For more information, contact your primary support provider.
(http://support.microsoft.com/kb/274036/EN-US/ )INF: How to Obtain SQL Server 6.5 Post Service Pack 5a Update
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.
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.
Article ID: 235220 - Last Review: October 21, 2013 - Revision: 4.0
Contact us for more help
Connect with Answer Desk for expert help.