Introduction
In this series, I will provide all of the information you need to understand in order to deal with deadlocks.
In part 1 (this article) I will explain:
- Deadlock definition
- the different types of deadlocks
- how SQL Server handles deadlocks
Deadlock Definition
A deadlock occurs when 2 processes are competing for exclusive access to a resource but is unable to obtain exclusive access to it because the other process is preventing it. This results in a standoff where neither process can proceed. The only way out of a deadlock is for one of the processes to be terminated. SQL Server automatically detects when deadlocks have occurred and takes action by killing one of the processes known as the victim.
Deadlocks do not only occur on locks, from SQL Server 2012 onward, deadlocks can also happen with memory, MARS (Multiple Active Result Sets) resources, worker threads and resources related to parallel query execution.
How do I know if I have a deadlock?
The first sign you will have of a deadlock is the following error message which will be displayed to the user who own the process that was selected as the deadlock victim.
Msg 1205, Level 13, State 51, Line 6
Transaction (Process ID 62) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
The other user whose process was not selected as the victim will be most likely be completely unaware that their process participated in a deadlock.
Deadlocks definitions types
There are 2 different types of deadlocks.
Cycle locks deadlock definition
A cycle deadlock is what happens when a process A which is holding a lock on resource X is waiting to obtain an exclusive lock on resource Y, while at the same time process B is holding a lock on resource Y and is waiting to obtain an exclusive lock on resource X.
Conversion locks deadlock definition
A conversion deadlock occurs when a thread tries to convert a lock from one type to another exclusive type but is unable to do so because another thread is already also holding a shared lock on the same resource.
There are 3 types of conversions locks in SQL Server.
| Type | Name | Description |
| SIU | Share with Intent Update | The thread holds some shared locks but also has update locks on some components (page or row). |
| SIX | Share with Intent Exclusive | The thread has both a shared lock and an exclusive lock on some components (page or row). |
| UIX | Update with Intent Exclusive | Both a U lock and an IX lock are taken separately but held at the same time. |
How SQL Server handles deadlocks
The lock manager in SQL Server automatically searches for deadlocks, this thread which is called the LOCK_MONITOR looks for deadlocks every 5 seconds. It looks at all waiting locks to determine if there are any cycles. When it detects a deadlock it chooses one of the transactions to be the victim and sends a 1205 error to the client which owns the connection. This transaction is then terminated and rolled back which releases all the resources on which it held a lock, allowing the other transaction involved in the deadlock to continue.
If there are a lot of deadlocks SQL Server automatically adjusts the frequency of the deadlock search, and back up to 5 seconds if deadlocks are no longer as frequent.
How does SQL Server choose the victim?
There are a couple of factors that come into play here. The first is the deadlock priority. The deadlock priority of a transaction can be set using the following command:
|
1 2 3 |
SET DEADLOCK_PRIORITY LOW; |
The typical values for the deadlock priority are:
| Priority | Value | Result |
| LOW | -5 | If other transactions have a priority of NORMAL or HIGH or numerically higher than -5 , this transaction will be chosen as the deadlock victim |
| NORMAL | 0 | This is the default priority. The transaction could be chosen as the victim if other transactions have a priority higher than 0. |
| HIGH | 5 | This process will not be selected as the victim unless there is a process with a numeric priority higher than 5. |
| <numeric> | -10 to 10 | This can be used to manage deadlock priority at a more granular level. |
If the transactions involved in a deadlock have the same deadlock priority, the one with the lowest cost is rolled back. In an example the one where the least amount of transaction log has been used, indicating that there is less data to roll back.
Keeping track of deadlocks
There are various tools that can be used to obtain the details of deadlocks. These include trace flags 1204 and 1222. You can also capture the deadlock graph event using SQL Profiler.
Personally I find that when I suspect that deadlocking is occurring in my server, that setting up and extended event session to log the deadlock graph each time it happens is the easiest.
From SQL Server 2012 onwards this can be done in SQL Server Management Studio under Management \ Extended Events:
Using extended events you will be able to see quite easily how frequently deadlocks occur in your database, and immediately have the deadlock graph available for each deadlock which occurred in order to help you resolve it.
How to minimize deadlocks
Here are a couple of tips to minimize deadlocks
Always try to hold locks for as short a period as possible.
Always access resources in the same order
Ensure that you don’t have to wait on user input in the middle of a transaction. First, get all the information you need and then submit the transaction
Try to limit lock escalation, by using hints such as ROWLOCK etc
Use READ COMMITTED SNAPSHOT ISOLATION or SNAPSHOT ISOLATION
Resolving deadlocks
Resolving deadlocks can be a tricky business, and is beyond the scope of this article. Look out for my next articles which explain how to read the deadlock graph which is the most useful in understanding the cause of your deadlock and will give you the insight on how to tackle a deadlock.
Other articles in this series:
- Understanding the graphical representation of the SQL Server Deadlock Graph
- Understanding the XML description of the Deadlock Graph in SQL Server
Minette currently works as a Data Platform Solution Architect at Microsoft South Africa.
View all posts by Minette Steynberg
- The end is nigh! (For SQL Server 2008 and SQL Server 2008 R2) - April 4, 2018
- 8 things to know about Azure Cosmos DB (formerly DocumentDB) - September 4, 2017
- Introduction to Azure SQL Data Warehouse - August 29, 2017