Avoiding a Common Deadlock

A common cause of deadlocks is concurrent identical update/delete queries.  For example, a query such as

DELETE FROM MyTable WHERE MyCode=@code

When two queries start executing this query with different codes, they have a good chance of deadlocking.  This can be via lock escalation, where query A requests an escalation to a table lock, which will wait on the query B.  As soon as the query B requests a lock escalation, or any lock that query A has, there is a deadlock.  You can have a deadlock without table escalation, by the two concurrent queries requesting page locks.  Eventually query A might ask for a page that query B has and query B is waiting on query A.

A very simple solution to this kind of deadlock is to take a Table lock at the beginning of the query.  That way whoever gets in first will finish before the other query starts, removing the possibility of a deadlock on that one table.  This can be as simple as

DELETE FROM MyTable WITH (TABLOCKX) WHERE MyCode=@code

Taking a table lock will reduce parallelism.  No other queries (except NOLOCK) will be able to read the table while the TABLOCKX is held.   However, the reduction in parallelism is generally less painful that experiencing deadlocks.  Also, queries that DELETE/UPDATE many rows in a single query will necessarily lock large parts of the table (often the whole table if lock escalation is triggered), so taking an explicit table lock for the query generally is not too onerous.

Note, another trick that I sometimes use in stored procedures that are looping through many records with possible updates is to take an explicit lock on the table at the beginning of a transaction.  For example, the following code will take an explicit table lock on MyTable, which it holds until the transaction is committed.

BEGIN TRANSACTION

SELECT TOP 0 * FROM MyTable WITH (TABLOCKX)

-       - - put your code in here to perform updates/inserts/deletes on the table(s)

 COMMIT TRANSACTION

A couple of notes for deadlocks and locking

• The amount of locking and deadlocks exponentially increase as transaction time increase.  So, anything you can do to reduce transaction time will reduce contention.
• It is common practice for middle tier applications (eg .NET) to start transactions and call the database server multiple times within the transaction.  This is OK, but it is even safer (and reduced transaction time) if you encapsulate the logic in a stored procedure, and let the stored procedure begin/commit the transaction.
• SELECT queries can be called without locks (NOLOCK).  DELETE/UPDATE/INSERT queries cannot be called without locking.
• Read queries (DELETE/UPDATE/INSERT queries also read queries for this point) generally only lock the current record/page by using CURSOR STABILITY lock isolation.  It is possible for them to use more onerous strategies, such as, REPEATABLE READ, in which case they do not release read locks until the end of the transaction.  This can lead to contention and deadlocks with concurrent update queries. Sometimes these REPEATABLE READ read queries will benefit from an explicit table lock.
• Lock escalation is generally your enemy in avoiding deadlocks.  If you see that a query is regularly escalating to a table lock, it is probably better for you to explicitly take a table lock at the beginning of the transaction/query as I outlined above.
• Transactions can be started and committed while a stored procedure holds position on a cursor.  This way, you can commit and release locks every n records while processing rows in a cursor.
• Use TRY CATCH in your procedures to handle exceptions such as deadlocks.

Causing a Deadlock in SQL Server

SQL Server has a background process that is continually looking for deadlock chains.  If it finds a deadlock chain, it will rollback the transaction that has done the least amount of work, which should be the fastest to rollback.  That's unless one of the transactions has volunteered as a deadlock victim  by setting their deadlock_priority to low.
If you want to cause a deadlock, it is quite easy.  Most DBAs will be able to do it, and it can be useful in determining what profiler and logging information is available in the event of an unplanned deadlock.

Here are some instructions to cause a deadlock between 3 transactions.  Most deadlocks are between 2 transactions, but a deadlock chain can have any number of transactions.  By the way, there are many ways to set up a deadlock, the only common characteristic is that there are a chain of processes that are waiting on each other for locks.  So in a 2 process chain, Process A is waiting on a resource held by Process B and Process B is waiting on a resource held by Process A.  As you can imagine, the only solution is for one of the transactions to be rolled back.  They can't both go forward.

In my experience, a common class of deadlocks is the one caused by update (update, insert or delete) transactions that update so many rows that the lock must be escalated.  For example, escalate several page locks to a table lock.  When there are two transactions like this starting about the same time, they both start updating, and taking locks, until one of them escalates to a table lock.  At this moment that transaction waits on the other.  But if the other is also destined to escalate to a table lock on the same table, there will be a deadlock.

Now, to cause a deadlock between 3 tasks follow these instructions.
You might like to start SQL Profiler and enable a trace for Lock:Deadlock graph, Lock:Deadlock Chain and Lock:Deadlock, which will give you considerable information on the deadlock chain participants and resource locks.
Create 3 tables

• create table t1 (c1 int, c2 varchar(50))
• create table t2 (c1 int, c2 varchar(50))
• create table t3 (c1 int, c2 varchar(50))

Create 3 query windows in SQL Server Enterprise Manager.
In the first query window execute
begin tran
insert into t1 select 1, 'abc'

In the second query window execute
begin tran
insert into t2 select 2, 'xyz'
Select * from t1

The second query window will be waiting on the first query.
In the third query window execute
begin tran
insert into t3 select 3, 'mno'
Select * from t2

The third query window will be waiting on the second query.  At this point in time there is no deadlock.  We just have a locking chain, with query 1 at the head.  You can see the locking chain if you execute.
sp_who2

In the first query window execute
--begin tran
--insert into t1 select 1, 'abc'
Select * from t3
Now, query 1 will be waiting on query 3, which is waiting on query 2, which is waiting on query 1.  We have a deadlock.  You should notice that within a couple of seconds, one of the queries is cancelled and the transaction rolled back.

Msg 1205, Level 13, State 45, Line 4
Transaction (Process ID 52) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

Now, have a look at the Profiler output, and you should see your deadlock chart with the three processes and the locked resources.  The transaction that was selected as the victim, and rolled back, has a cross through it.  If you hover over the transactions, you will see the last batch to be submitted.  Note, the last batch is not necessarily the entire transaction.  There may have been an explicit BEGIN TRAN and many statements within the transaction that aren't included in the chart.  If you manage to capture a blocking transaction in action, you can see the locks it has (and ones its waiting on) with the sp_lock command. 

Remember to COMMIT or ROLLBACK the two queries that weren't rolled back.  There is nothing worse that a query holding locks while the user has gone off to do something else.