In todays blog posting I want to talk about how to handle Deadlocks in SQL Server. A deadlock occurs when 2 queries are waiting for each other, and no query can continue its work anymore. In the first step I want to give you an overview how SQL Server handles deadlocks. And finally I will show you some specific deadlock types in SQL Server, and how you can avoid and resolve them.

Deadlock Handling

The nice thing about deadlocks is that SQL Server automatically detects and resolves them. To resolve a deadlock, SQL Server has to rollback the cheapest of the 2 transactions. In the context of SQL Server, the cheapest transaction is the transaction that has written the fewer bytes to the transaction log.

SQL Server implements the deadlock detection in a background process called the Deadlock Monitor. This background process runs every 5 seconds and checks the current locking situation for deadlocks. In the worst case, a deadlock should therefore not last longer than 5 seconds. The query which gets rolled back receives the error number 1205. The “nice” thing about deadlocks is that you can fully recover from that error situation without any user interaction. A smart developer must do the following steps to recover from a deadlock:

• Check for error number 1205, when an exception is thrown
• Pause the application briefly to give the other query time to complete its transaction and release its acquired locks
• Resubmit the query, which was rolled back by SQL Server

After the resubmission of the query, the query should continue without any problems, because the other blocking query will have already finished its transaction. Of course you should keep track of reoccuring deadlocks, so that you do not retry your transaction over and over again.

You can troubleshoot a deadlock in multiple ways. SQL Server Profiler provides you the Deadlock Graph event, which occurs as soon as a deadlock was detected. If you are on SQL Server 2008 and higher, you can also use Extended Events to troubleshoot deadlock situations. Extended Events provides you the system_health event session, which tracks historical deadlocks since the last restart of SQL Server. And with the enabled trace flag 1222, SQL Server will log deadlock information into the error log.

Deadlock Types

There are multiple types of deadlocks that can occur in SQL Server. In this section I want to talk a little bit more about the most common ones.

A typical deadlock that I see at almost every SQL Server installation is the famous Bookmark Lookup Deadlock, which occurs when you have concurrent read and write activity to Clustered- and Non-Clustered Indexes. It’s mainly a deadlock that occurs because of a bad indexing strategy. In my day-to-day life as a SQL Server troubleshooter I can say that at least 90% of all deadlocks can be avoided by applying a better indexing strategy to your workload. A Bookmark Lookup Deadlock can be eliminated very easily by providing a Covering Non-Clustered Index as we discussed in week 8 of the SQLpassion Performance Tuning Training Plan.

Another common deadlock is the so-called Cycle Deadlock, where your individual queries have accessed tables in different orders. To avoid that specific deadlock, you always have to make sure that queries access tables in the same order. And the “nicest” deadlock that can occur in SQL Server is the so-called Intra-Parallelism Deadlock, where a parallelism operator (Distribute Streams, Gather Streams, Repartition Streams) has deadlocked internally among its individual threads. The following picture shows a typical deadlock graph.

The graph itself is pure art, and it occurred because you hit a bug in SQL Server. Unfortunatelity such bugs are not going to be fixed by Microsoft, because of the possibility of introduced regressions. Therefore you have to make sure that the queries which caused this deadlock, are running single-threaded in SQL Server. You can achieve single-threaded execution plans through multiple options:

• Work on your indexing strategy, so that the query costs are below the current Cost Threshold for Parallelism (by default 5)
• Hint SQL Server to run your problematic query single-threaded with the query hint MAXDOP 1

Another miracle cure to deadlocks is to enable optimistic concurrency, especially Read Committed Snapshot Isolation (RCSI), because it’s completely transparent to your application. With optimistic concurrency, S locks are gone, which means that you can eliminate a huge amount of typical deadlock situations in SQL Server.

Summary

Deadlocks are automatically handled by SQL Server by rolling back the cheapest transaction. Nevertheless you have to make sure that you minimize deadlocks as much as possible, because every rolled back transaction influences your end-users in a negative way. Deadlocks can be avoided by a good indexing strategy, and using optimistic concurrency can be also a miracle cure for them.

Thanks for your time,

-Klaus