Blog

Deadlocks in SQL Server can be difficult to avoid completely, but there are some basic rules that can help in avoiding them.

This blog post will cover the basics of deadlocking in SQL Server.

Deadlock Basics

When a deadlock situation occurs in SQL Server, an error such as the following error message is raised:

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

There are always at least two different connections involved in a deadlock, and one of them will automatically be chosen as a deadlock victim. The victim transaction gets a rollback. The setting DEADLOCK_PRIORITY can be used to control which connection gets chosen as a deadlock victim.

For experimental purposes it is easy to create a deadlock situation. Create three new connection windows in SSMS. In connection window A, create a database and two tables with one row each:

CREATE DATABASE DEADLOCKTEST
GO
 
USE DEADLOCKTEST
GO
 
CREATE TABLE DBO.T1 (COL1 INT)
 
CREATE TABLE DBO.T2 (COL1 INT)
 
INSERT T1 VALUES (1)
 
INSERT T2 VALUES (1)

In connection window B run the following statement:

USE DEADLOCKTEST
GO

BEGIN TRAN
UPDATE T1 SET COL1 = 2 WHERE COL1 = 1

And in connection window C, run the following:

USE DEADLOCKTEST
GO

BEGIN TRAN
UPDATE T2 SET COL1 = 2 WHERE COL1 = 1

Now we have two active transactions running (connection B and C). There is no conflict yet, since they access different tables (T1 and T2). But what happens if they “cross paths” and try to access the others table?

Go back to connection window B and run:

UPDATE T2 SET COL1 = 2 WHERE COL1 = 1

Now, in connection window C, run this:

UPDATE T1 SET COL1 = 2 WHERE COL1 = 1

Now we have created a conflict, a solution that cannot be solved, and SQL Server chooses one connection as the deadlock victim. The other connection will go through without error. And this is the way deadlocks happen. A single resource is accessed from different access paths. If there is only one access path to the resource, a deadlock will not happen.

A common solution when this happens, is to catch deadlocks in the application layer and simple reissue the statement that failed. This way, the end users won’t be affected by the deadlock.

A Real World Example

There is a database design that I have seen many times that is prone to deadlocking. The design is to create one table containing a current value for several sequence numbers, for instance InvoiceNo, CaseNo and CustomerNo. To get the next value, the current value + 1 is used and the corresponding row is updated in the table. This is an example of how such a table might look like:

Problem is, these sequence numbers will for sure be accessed from transactions that use different paths to get to the sequence number. There will be different business logic leading up to the incrementing of CurrentValue. A table like this is a recipe for deadlocking. However, there need to be some amount of simultaneous transactions for deadlocking to occur. If you have few transactions in your system, deadlocks may never occur.

Avoiding Deadlocks

So what can be done to avoid deadlocks? Sometimes deadlocks can’t be avoided completely, so a combination of strategies may be the best solution. Here are some strategies to lessen the number of deadlocks in a system:

1. Catch the error 1205 in the application layer. If it is possible, rerun the transaction without the end user noticing. Actually, this is what the text in the error message suggests.

2. Use explicit blocking of the resource that is in the center of deadlocking scenarios. This will increase blocking, but decrease deadlocking. Of course, this depends on if your system will tolerate increased blocking.

3. Split the resource that is in the center of deadlocking cases. In the sequence table example above, use one table per type of sequence number. Or use the new (from SQL Server 2012) SEQUENCE object.

4. Try to access the centralized resource in the same “place” in the different access paths. For instance, always update the table last.

How to know if there is deadlocking in your system

The easiest way to know if you have deadlocking in your system is to use a monitoring tool, such as Red Gate SQL Monitor. By default, SQL Monitor catches deadlock event and creates an alert.

Another way is to enable traceflag 1222. When you do this, deadlock events will be written to the SQL Server event log. (Actually, SQL Monitor uses this technique.)

A third way is to catch the Deadlock Graph event in SQL Server Profiler. Deadlocks can be hard to predict and to recreate however, so the trace may need to run for a while.