3 Types of SQL Server Deadlocks
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.
#1: Order of Operations Deadlock
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
How Can I Prevent an Order of Operations Deadlock?
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.
#2: Lookup Deadlock
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
#3: Parallelism 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.
SQL Server Deadlock Analysis with SQL Sentry
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.
The graph is still difficult to navigate visually. But once we zoom in, we can see helpful details.
The view above is zoomed in close enough to view individual nodes, and I changed the Layout Type to Force Directed. The Layout Type provide different ways to lay the graph out. It is very helpful with complicated deadlocks like this one. You can see how, even in this small cutout of the graph, everything seems to be pointing back to the parallelism exchange event.
Another benefit of the layout change is that some nodes that weren't directly involved in the SQL Server deadlock have been set off to the side. Those are now out of the way as you try to determine what impact this monster deadlock might have had.
How Can I Prevent a Parallelism Deadlock?
This is a tough question because there isn't a great answer. You can reference this StackExchange thread for some advice, and you should consider the following:
- Resist the impulse to set MAXDOP to 1. It's easy to consider that removing parallelism will remove the chance to deadlock. However, you could introduce real ongoing SQL Server performance problems by doing so. The deadlock appears frightening, but in reality it is far less frightening than long-term performance issues. These deadlocks often have no real impact as well.
- This type of SQL Server deadlock is most often seen with ORM frameworks. Some data professionals suggest that you avoid using ORM and others recommend that you take the time to truly master your ORM. If you know the framework well enough, you can find ways to have it generate simpler queries. They will be less likely to create this deadlock and additional unrelated SQL Server performance problems.
- Don't leave modeling and design on the table. A well-designed database that accounts for transactions and queries will be a developer's friend later on. These queries often get so complicated because the underlying model isn't designed to support what it is you're trying to do. Although updating the schema isn't always viable, if you have the option, remember to use it.
Detecting SQL Server Deadlocks
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.
Pairing SQL Sentry and Plan Explorer
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.
Plan Explorer
The free Plan Explorer helps you quickly get to the root of SQL Server query problems.
