Article ID: 239753
Article Last Modified on 9/4/2002
APPLIES TO
- Microsoft SQL Server 7.0 Standard Edition
This article was previously published under Q239753
BUG #: 56284 (SQLBUG_70)
SYMPTOMS
Bound sessions and DTC operations allow multiple SPIDs to bind or enlist in the same transaction scope. This can lead to a condition termed Distributed Deadlock. The SQL Server does not detect the deadlock situation when three or more SPIDs are engaged in two bound or enlisted transactions.
Here is a simple example of a distributed deadlock the SQL Server will not detect:
| Sequence order | DTC Tran #1 | DTC Tran #2 |
|---|---|---|
| 1 | SPID 10 enlists | |
| 2 | SPID 10 updates authors | |
| 3 | SPID 11 enlists | |
| 4 | SPID 11 updates titles | |
| 5 | SPID 11 updates authors Becoming blocked on SPID 10 | |
| 6 | SPID 12 enlists | |
| 7 | SPID 12 updates titles Becoming blocked on SPID 11 |
The example clearly shows a deadlock between the two transactions but the deadlock is distributed across multiple SPIDs enlisted in those transactions.
If the final update to titles was attempted on SPID 10 instead of SPID 12, the correct deadlock message will be raised.
For more details on bound or enlisted sessions refer to the following sections of SQL Server Books Online:
- Using Bound Connections
- dbenlisttrans
- Distributed Transactions
- How to use Microsoft Distributed Transaction Coordinator (ODBC)
WORKAROUND
Reduce the chances of encountering the deadlock by employing standard deadlock avoidance techniques. Refer to the Avoiding Deadlocks section of SQL Server Books Online for complete details.
STATUS
Microsoft has confirmed this to be a problem in SQL Server 7.0.
MORE INFORMATION
The design of Microsoft Transaction Server (MTS) is such that the behavior can be encountered easier. The MTS components can be registered to use a DTC transaction to complete the proper business logic. However, use of ODBC or OLE DB connections can be pooled by OLE DB Resource Pooling and/or ODBC connection pooling.
Many sections of the MTS documentation encourage a developer to open the connection, perform the work and close the connection. Actually, this is opening a connection and automatically enlisting the connection in a DTC transaction based on transaction properties established for the MTS-based object. The default transaction level is also SERIALIZABLE. This can cause shared locks at the SQL Server to be held increasing the chance of blocking and deadlock activity.
The COM object may contain multiple methods that are invoked to complete the given actions for the transaction. If multiple object methods are used to complete the business logic associated with the transaction it is very likely that the pooling can and will use multiple connections (SPIDs) to the SQL Server to complete the transactional work. However, when the undetected deadlock situation is encountered the default transaction timeout for DTC is 60 seconds. In many cases 60 seconds later a transaction will time out and the processing can continue.
In addition, the query timeout could play a significant roll. The default query timeout for many MDAC components is 30 seconds, thus the query timeout would be exceeded before the DTC transaction timeout expired.
The following article contains detailed information and techniques for handling and identifying blocking issues:
162361 INF: Understanding and Resolving SQL Server Blocking Problems
'A special consideration:' When looking at blocked SPIDs bound or enlisted in the same transaction watch the sysprocesses column open_tran. SPIDs bound or enlisted in the same transaction space contain identical transaction counts. This can help to pattern which SPIDs are part of the same transaction space.
Use of SQL Profiler, capturing the text column of the DTC events, records the DTC transaction IDs. (The binary data column can also provide valuable information about bound and enlisted transaction IDs.) When working with DTC transactions specifically refer to the PROPAGATE DTC event to cross reference the SPIDs and which transactions were propagated to which SPIDs.
Keywords: kbbug kbpending KB239753
