Over a million developers have joined DZone.

Debugging Distributed Transactions: Transactional Deadlocks, Part 1

·

The most commonly encountered problem with distributed transactions is that of transactional deadlocks. Transactions guarantee isolation, which is usually effected through locks. This is the case with SQL Server (as well as other relational databases) transactions. Transaction isolation levels provide additional granularity as to when and whether the locks are released prior to the completion of the transaction.

Database Locks

Simply put, the database issues shared (read) and exclusive (write) locks on data touched by the transaction. For most practical purposes, a SELECT statement effects a shared lock on all the rows returned by the statement, while an UPDATE statement effects an exclusive lock on all the rows affected by the statement.

The following is a simple example of a deadlock caused by locks between distributed transactions:

Time Transaction 1 Transaction 2
0 Select all orders from customer A  
1   Select all orders from customer A
2 Update the shipping date of all selected orders  
3   Update the discount rate of all selected orders
4 Commit Commit

Question: At which time will the system encounter the deadlock? Which transaction will be the first to block?

Answer: At time T=2. Transaction 1 will block while attempting to update (i.e., lock for writing) records that are currently under a shared lock (for reading) by Transaction 2. Similarly, Transaction 2 will block while attempting to update records that are currently under a shared lock by Transaction 1. Therefore, the two transactions would wait for each other.

This form of deadlock is resolved by the database itself. SQL Server detects that the two transactions are deadlocked on the same resources, and chooses one of them as the transaction victim. The victim transaction is aborted—your application will receive an exception similar to the following:

SqlException: Transaction (Process ID ...) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

Lock Query Hints

You can explicitly specify the desired lock behavior for your SELECT statement by using the XLOCK and NOLOCK query hints. If you’re using an ORM (such as LINQ to SQL), you’ll have to find a way to add these hints to the query issued by the ORM classes, or issue the query yourself and have the ORM materialize the results into objects.

In the previous example, if the SELECT query used by the transactions were to effect an immediate exclusive lock, the deadlock would not be possible. One of the transactions would acquire the lock first, and proceed to completion while the other would wait. Indeed, the XLOCK query hint can instruct the database to acquire an exclusive lock:

SELECT * FROM Customers WITH (ROWLOCK, XLOCK)
WHERE Name = 'A'

As a result, the following execution history will be obtained:

Time Transaction 1 Transaction 2
0 Select all orders from customer A with XLOCK  
1 Update the shipping date of all selected orders  
2 Commit  
3   Select all orders from customer A with XLOCK
4   Update the discount rate of all selected orders
5   Commit

In this case, the first transaction to acquire the exclusive lock commits first.

Similarly, the NOLOCK query hint instructs the database to acquire no locks (accepting the possibility of reading dirty data that might be modified by another transaction). This query hint should be used with great caution, because you might rely on data that is about to be changed, or data that has been changed but the change will be rolled back should the transaction that caused the change abort.

Question: In the following execution history, is it necessary to introduce a NOLOCK query hint to the query executed by Transaction 2 at T=2 to ensure that there is no deadlock?

Time Transaction 1 Transaction 2
0 Select all orders from customer A  
1 Update all selected orders by marking them as FAILED TO DELIVER  
2   Select all orders marked as FAILED TO DELIVER
3 Commit Commit
4   Dispatch a customer service representative to all customers who have the orders selected earlier

Answer: There’s no need to use the NOLOCK query hint. Transaction 2 performs only reads, and therefore cannot in any way become deadlocked with Transaction 1. The operation at T=2 might wait until T=3 when Transaction 1 commits.

Indexes

Database indexes have a considerable effect on the locks acquired by transactions. In the previous example, if there is an index on customer names, the SELECT statement would lock only the orders by customer A. Otherwise, the following two transactions would deadlock, even though they are not actually working on the same rows:

Time Transaction 1 Transaction 2
0 Select all orders from customer A  
1   Select all orders from customer B
2 Update the shipping date of all selected orders  
3   Update the discount rate of all selected orders
4 Commit Commit

In the next installment, we’ll see how transactional deadlocks involving also WCF services can be detected with SQL Server Activity Monitor, the MSDTC MMC snap-in, and some diagnostic code.

Topics:

Published at DZone with permission of Sasha Goldshtein, DZone MVB. See the original article here.

Opinions expressed by DZone contributors are their own.

The best of DZone straight to your inbox.

SEE AN EXAMPLE
Please provide a valid email address.

Thanks for subscribing!

Awesome! Check your inbox to verify your email so you can start receiving the latest in tech news and resources.
Subscribe

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}