There are many reasons why applications might be slow to respond, but if users are complaining about performance, you may be dealing with a SQL Server deadlock. Fortunately, there are ways to identify and correct SQL Server deadlock, and even prevent it from negatively affecting application performance.
SQL Server deadlock is essentially a standoff between two processes that are competing for exclusive access to the same resource. Because only one process can use a resource at a time, performance slows until the deadlock is resolved.
There are two types of SQL Server deadlocks to watch out for: conversion locks and cycle locks.
Conversion lock deadlocks occur when a thread tries to convert a lock from one exclusive type to another exclusive type but can’t because a different thread already holds a shared lock on the resource the first thread is trying to convert.
In SQL Server, there are three types of conversion locks:
- Shared with intent exclusive (SIX): This lock occurs when a transaction that holds a shared lock also has an exclusive lock on some pages or rows.
- Shared with intent update (SIU): This lock occurs when a transaction that holds a shared lock also has some pages or rows locked with an update lock.
- Update with intent exclusive (UIX): This lock occurs when a transaction that holds an update lock also has an exclusive lock on some pages or rows.
Cycle locks are SQL Server deadlocks caused by two processes vying for an exclusive lock on a resource that is locked by the other process.
For example, Process 1 is holding a lock on Resource 1 while waiting for Process 2 to release its lock on Resource 2. If Process 2 is holding a lock on Resource 2 while waiting for Process 1 to release Resource 1, we’ve got ourselves a cycle lock deadlock.
How to Diagnose SQL Server Deadlock
SQL Server deadlock is just one of dozens of possible reasons your application may have performance issues. If queries that normally run quickly suddenly slow way down, it is possible you have a deadlock. But it’s also possible something else is going on.
So, aside from noticing reduced query speed, how do you determine for certain whether deadlock is to blame for your database performance problems?
The easiest and most definitive way to identify deadlock is the presence of a 1205 error message:
Transaction (Process ID %d) was deadlocked on %.*ls resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
The 1205 error message literally tells you there is a deadlock and how to fix it. However, as Jeremiah Peschka points out, if you haven’t fixed the cause of the deadlock, rerunning the transaction will probably be unsuccessful.
Another option for finding deadlocks is to pull a SQL Server deadlock graph out of Extended Events. Extracting the deadlock through Extended Events lets you look at the deadlock XML, which provides more information than the graphical representation of a deadlock graph.
The deadlock XML is organized by the victim list, the process list, and the resource list. Each section gives detailed descriptions of the victims, processes, and resources involved in the deadlock, which makes it easier to troubleshoot and resolve the issue.
How to Fix SQL Server Deadlock
The only way to resolve a SQL Server deadlock is to terminate one of the processes and free up the locked resource so the process can complete. This occurs automatically when SQL Server detects a deadlock and kills off one of the competing processes (i.e., the victim).
SQL Server normally chooses which connection to kill at random, but it is possible to set deadlock priorities to determine which connection is killed during a deadlock. When two connections have different priority settings, SQL Server will kill off the transaction with the lowest priority.
How to Prevent SQL Server Deadlock
SQL Server deadlock is a fact of life when you’re managing a busy database. However, DBAs can help reduce the occurrence of deadlocks and minimize their impact on database performance by taking a few preventative measures:
- Create better indexes
- Adjust transaction priorities
- Enact a Try/Retry model
- Change isolation modes
- Hold locks for as short a time as possible
- Access resources in the same order every time
- Don’t submit a transaction until you have all the information you need
- Limit lock escalation
Though it isn’t possible to prevent SQL Server deadlock completely, you can implement these best practices and proactively circumvent some of the most common sources of deadlock to keep transactions flowing smoothly and optimize database performance.