Updated: June 3, 2020
Table of Contents:
SQL Server transactional processing is designed to be highly concurrent, meaning lots of things can generally be happening at the same time. Locking mechanisms must be in place to protect the integrity of your data in concurrent processing. A SQL Server deadlock occurs when exclusive locks are held on resources required by multiple processes and those processes cannot continue to completion.
Yes and no. On one hand, a deadlock will result in one of the processes falling "victim" to failure. On the other hand, deadlocks maintain the integrity of your data in highly concurrent applications. Deadlocks, and blocking for that matter, are normal behavior in a lock-based concurrency system such as SQL Server. Whether they are bad depends on how you plan for and handle them when they happen.
The only way forward for SQL Server when a deadlock situation is detected is for it to designate one of the processes as the victim. Although it sounds a bit ominous, that terminology is likely purposeful. The victim process is killed and will not complete successfully. If this occurs, SQL Server will raise an error up the call stack for the victim:
"Msg 1205, Level 13, State 51, Line 6
Transaction (Process ID ) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction."
What happens next depends on how your application handles the error. In a worst case scenario, your app could crash or be left in a semi-functional state. In a best case scenario, the app can handle the error gracefully and potentially retry the operation with success.
There are a few classes or types of deadlocks, which we'll review in a moment. Some types of deadlocks are nigh impossible to predict or prevent before they happen. Preventable deadlocks tend to occur between processes that might not be directly related but use the same database objects and data.
Therefore, your application design process should include consideration of potential deadlocks. Not all applications need to retry every process after a deadlock. After consideration, you likely won't want a single approach to deadlocks for all application processes up front.
Let's look at 3 types of deadlocks with examples. The screenshots shown come from viewing deadlock graphs in SolarWinds Plan Explorer. Plan Explorer is a free query analysis and optimization tool, and its feature set is enhanced and included in SolarWinds SQL Sentry.
An order of operations deadlock is what most people think of when they consider deadlocks in SQL Server. It is also one of the types of deadlocks that is preventable. This deadlock happens when exclusive locks are used or locks escalate between different processes that need respective resources.
The following example comes from the Plan Explorer Demo Kit that Aaron Bertrand put together. You can download the kit to try it out for yourself.
In the screenshot to right, the replay controls are highlighted to illustrate that the screenshot was taken toward the end of the "playback" of the deadlock. As you'll see in some of the upcoming examples, SQL Server deadlocks can become far more complicated than this one. The ability to replay the requested order of lock operations can help immensely in finding a way to prevent the deadlock.
In this example, you can see that SPID 63 holds an exclusive lock on the primary key in InvoiceLines. SPID 63 also requires an exclusive lock on the primary key in Invoices. SPID 64 holds an exclusive lock on the primary key in Invoices and requires an exclusive lock on the primary key in InvoiceLines. Neither SPID 63 nor SPID 64 can continue, and SPID 63 was chosen as the victim.
If SPID 63 is selected on the graph, you'd quickly see the details in grid format. This helps you fully understand what failed for the victim and what it was trying to do. You can also view details about the other processes in the grid to get a full picture of the objects and SQL statements involved.
A view of deadlock victim details in the SQL Sentry deadlock details grid
There is no definite answer to this question. It could be prevented by timing operations differently, tuning queries to run faster, changing the transaction isolation level, or any number of other ways.
Here is one method: When deadlocks are happening with transactions that escalate locks, isolating only the rows of data that need to be changed can help. Place any "list" information you need to use outside the scope of the transaction in a temporary table. Doing so prevents locks on objects that don't need to be locked as part of the transaction. It also speeds up the entire operation by reducing the complexity of update queries joining to tables that aren't being updated. They can, instead, join to your temporary holding table.
An order of operations deadlock can be resolved or prevented, but it might take some time and heuristic troubleshooting to reach an ideal state.
Greg Gonzalez estimates that the majority of SQL Server deadlocks he has encountered are lookup deadlocks. However, as Greg mentions in "Resolving Key Lookup Deadlocks with Plan Explorer," this type of deadlock can usually be resolved easily.
Be sure to read Greg's blog post on lookup deadlocks, but let's discuss the basics. Lookup deadlocks happen when a query includes a Lookup operation in the plan, which means the engine has to retrieve one or more values from somewhere other than the index used to fulfill the query. The values must be "looked up" using a key or RID value. Thus, the "Key Lookup" or "RID Lookup" plan operations. Deadlocks can happen if the lookup is acting on a range of rows that also frequently receives UPDATE or DELETE operations.
Click to enlarge: A key lookup query plan operation in Plan Explorer from a plan involved in a lookup deadlock
Some deadlocks occur within the same process running on different threads. These deadlocks are referred to as parallelism deadlocks because they can happen when the query plan uses parallelism operations and the SQL Server deadlock details include parallel exchange events.
This type of deadlock is difficult to predict or prevent. In an update to SQL Server 2017, Microsoft did recognize an opportunity to help us understand these deadlocks better. Although this additional SQL Server deadlock information is quite helpful, the nature of these deadlocks still makes it very hard to proactively manage them.
The image to the right is a high-level graph of a parallel exchange deadlock from Plan Explorer. As you can see, it can be jarring when you run into one of these deadlocks. Zooming out to view the entire graph is reminiscent of string art.
If you look closely, you can still see tiny nodes in the graph representing processes, locks, and annotation. Viewing the complicated graph this way isn't very helpful. SQL Sentry and Plan Explorer both include features to help.
Let's take a look at one of the best features available in SQL Sentry for deadlocks. It's the Optimize Layout option (shown below) found to the far right of the replay controls at the bottom of the deadlock analysis view.
Optimizing the layout removes a lot of the repetitive nodes that won't reveal anything that would help you troubleshoot. It reduces the complicated graph you started with.
SQL Sentry helps you quickly detect and capture SQL Server deadlocks. The Deadlocks tab of SQL Sentry's Performance Analysis Dashboard presents the most complete view of SQL Server deadlocks available. It lets you see all of the processes and resources involved in a deadlock visually. Using the grid view, you can perform detailed analysis with the statements that were executed while the deadlock was being detected. There is no need to install agents or set trace flags. You also don’t need to perform any particular configuration of the monitored servers. It just works.
With SQL Sentry, there is no need to translate SPIDs or reference IDs. The host, application, and resource names are all labeled in the graph. You can easily see the SQL deadlock victim (shown in red), as well as lock types, all in the order they were applied. With this functionality, your deadlock analysis becomes a journey that can be concluded in victory.
Deadlocks captured in SQL Sentry can be exported as .xdl files. XDL is Microsoft's XML format for SQL Server deadlocks. The files can be shared and opened in Plan Explorer just like a plan file. You can view the deadlock file in the deadlock grid view. Here, each file is expandable so that you can see the details of the processes that participated in the deadlock. You can also use the SQL Server deadlock graph to view the victim and the process and resource nodes, as well as any relationships that occur between them.
“We installed SQL Sentry on a system where performance testing identified poor performance. On the first day, we identified three deadlocks that were the root cause of the performance issue. This was a few days before a very large and critical production release. The deadlocks were fixed and we doubled the number of concurrent users that the application could support.”
- Steve Strelzyk
Senior Application Architect, Aon
Use Plan Explorer (free!) and SQL Sentry together to distribute the best defense possible against SQL Server deadlocks. Distribute SQL Sentry to DBAs, and Plan Explorer and the SQL Sentry Portal to everyone. You'll soon find that troubleshooting deadlocks feels more like an invitation to a party than a chore that conjures dread.