What is a SQL Server deadlock?What is a SQL Server deadlock?
A deadlock in SQL Server occurs when 2 processes/sessions are competing for exclusive access to data in the tables but is unable to obtain exclusive access to it because the other process/session is preventing it. This results in a situation where neither process can proceed and gets struck. The only way out of this deadlock when happens is that 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 are not confined to table locks now but from SQL Server 2012 onwards it can happen with memory, Multiple Active Result Sets, worker threads and any resources related to parallel query execution.
Normally when deadlock happens the first sign is the error message which will be displayed to the process/session that was selected as the deadlock victim as shown below.
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 process/session which was not selected as the victim will mostly unaware that this process participated in a deadlock.
Types of deadlocks: There are 2 types of deadlocks.
A cycle deadlock happens normally when a process X which is holding a lock on resource Z is waiting to obtain an exclusive lock on resource A, while at the same time process C is holding a lock on resource A and is waiting to obtain an exclusive lock on resource Z.
A conversion deadlock normally happens when in a session tries to convert a lock from one type(smaller lock) to another exclusive type but is unable to do so because another session is already 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 Shared locks but also has update locks on some resources like page or row.
SIX Share with Intent Exclusive Shared locks and an exclusive lock on some resources like page or row.
UIX Update with Intent Exclusive Both a U(Update) lock and an IX(Intent exclusive) lock are taken separately but held at the same time.
How does SQL Server handles deadlocks is given below:
The lock manager in SQL Server automatically searches for deadlocks using LOCK_MONITOR for deadlocks every 5 seconds. It looks at all waiting locks to determine if there are any cycles. When it detects a deadlock then it chooses one of the transactions to be the victim and sends a 1205 error to the client/session which owns the connection. The victim transaction is then terminated and rolled back which releases all the resources on which it held a lock on thus allowing the other transaction involved in the deadlock to continue.
Selection of SQL Server victim in deadlock is normally done by first deadlock priority for selecting the victim.
If the both transactions involved in a deadlock have the same deadlock priority then normally the one with the lowest cost is rolled back. That is in other words, the one where the least amount of transaction log has been used that is 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.
Best method to record deadlocks when we suspect is to setting up extended event session to log the deadlock graph for recording each occurance with details.
From SQL Server 2012 onwards, this can be done in SQL Server Management Studio under Management -> Extended Events to capture deadlocks. Using extended events you will be able to see quite easily how frequently deadlocks occur in your database.Also we can have the deadlock graph available for each deadlock which occurred in order to help you resolve it.
Best to avoid deadlocks is to try to hold locks for as short a period as possible. Ensure session won’t have to wait on user input in the middle of a transaction but to get all the information you need first only.
Try to limit lock escalation, by using hints such as ROWLOCK etc or to Use READ COMMITTED SNAPSHOT ISOLATION or SNAPSHOT ISOLATION
Resolving deadlocks can needs to analyse what’s causing and thinking for best solution. There is no way one strict way to resolve but to analyse, study the occurances, try fixes, deploy to Live.