It’s time to add WCF services to the mix and see how their presence adds to the complexity of the otherwise non-trivial transactional deadlocks. Consider the following transaction:
- Client: Begin transaction
- Client: Select all orders by customer A
- Client: Call a WCF service and pass to it the list of orders
- Service: Update the shipping date of all selected orders
- Client: Commit
If both parties share the same distributed transaction, it completes successfully. However, if the transaction flow between the client and the service were not properly configured (see earlier post), then a deadlock would occur. There would be two transactions—the service-side transaction would attempt to acquire an exclusive lock on rows that are under a shared lock by the client’s transaction, while the client’s transaction won’t commit until the (synchronous) service call returns.
In this case, by the way, the database access attempted by the service will likely yield the following exception:
SqlException: Timeout expired. The timeout period elapsed prior to the completion of the operation of the server is not responding.
In other words, the database does not detect the deadlock in this case—and it’s not very surprising, considering that only one part of the deadlock is actively accessing the database while it’s happening. [SQL Server Profiler can even plot for you the database deadlock as a wait graph. This is very similar to the operating system’s Wait Chain Traversal.]
One tool for diagnosing transactional deadlocks involving an SQL Server database is the SQL Server Activity Monitor that you can access from the SQL Management Studio. It displays all the transactions that are currently in flight, and can tell you which locks (shared or exclusive) they acquired as well as whether they are currently blocked waiting for another transaction.
Here’s a screenshot from the Activity Monitor that I captured while two transactions entered a mutual deadlock:
Right-click any process and choose Details, and you can see the last query that the process is trying to perform:
Sometimes, you would see –2 as the blocking transaction identifier. It means that the transaction is blocked waiting for a distributed transaction to commit, but that other distributed transaction is no longer connected to the database (so it doesn’t have a database process ID).
This can happen if you’re performing multiple database accesses within a single transaction, or if the transaction performs a database access and then blocks for another reason, as in the following example:
|Time||Transaction 1||Transaction 2|
|0||Open session to CRM DB||Open session to CRM DB|
|1||Select all orders from customer A from the CRM DB|
|2||Update the shipping date of all selected orders|
|3||Close session to CRM DB|
|4||Open session to Inventory DB||Select all orders from customer A from the CRM DB|
|5||Select inventory data from the Inventory DB|
|6||. . .|
In this case, Transaction 2 would block at T=4, even though Transaction 1 closed its session to the CRM DB. The session might be closed, but until the distributed transaction commits, the locks are still held.
Question: Suggest an operation that Transaction 1 would perform at T=6 so that it would become deadlocked with Transaction 2. (Hint: This does not have to be a database operation.)
Answer: One possibility is to enter a wait for an event that the thread executing Transaction 2 is supposed to signal when it completes.
If the Activity Monitor reports –2 as the blocking transaction identifier, you can use the Microsoft Distributed Transaction Coordinator (MSDTC) MMC snap-in to view the currently active distributed transactions. [It’s under Administrative Tools –> Component Services –> Computers –> My Computer –> Distributed Transaction Coordinator.]
Inspecting the transaction’s distributed transaction identifier (shown here as the Unit of Work ID) might be sufficient to go back to the logs and see what the transaction is doing.
One thing that might help you with diagnostics of that sort is having a trace statement output the transaction identifier, stack trace, and SQL statement whenever you create a new transaction and whenever you use a transaction to access the database. There are many places where you might want to add such tracing information to your code, so I’ll leave it up to you to decide what’s best. [For example, if you’re using LINQ to SQL, you can implement a TextWriter that outputs the transaction information and provide it to the DataContext.Log property.]
In the next (and final) installment, we’ll see some additional examples of problems that arise from incorrect use of distributed transactions.