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.
- Using Bound Connections
- Distributed Transactions
- How to use Microsoft Distributed Transaction Coordinator (ODBC)
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.
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.
ID d'article : 239753 - Dernière mise à jour : 4 sept. 2002 - Révision : 1