In [Part 1] of this series, we explored the locking and blocking of SQL Server — what they are, why they occur, and their detection and prevention mechanism. In Part 2, we will focus on how DBAs can detect, analyze, and resolve deadlock scenarios in real-world workloads. You will learn how to leverage SQL Server’s built-in tools, interpret key system views, capture deadlock graphs, and apply strategies to minimize performance impact. Along the way, we will also discuss best practices and preventive measures to help ensure smooth concurrency handling in your databases.
Figure-2: A deadlock
Deadlocking
As discussed in part-1, deadlock is a special type of locking. It happens when two or more queries block each other in a circular loop. Each process holds a resource the other needs, and neither can proceed, resulting in a standoff. SQL Server detects this and kills one process (called the victim) to break the deadlock.
Deadlock Victim
Deadlock is a nasty type of blocking which can not resolve itself even after a longtime. SQL Server runs lock monitor routine periodically to detect deadlock condition. Once it detects any, one participating session is selected for termination to break the deadlock situation. This session is called deadlock victim. Victim is selected based on lowest transaction rollback cost.
Deadlock Priority
SQL Server selects a deadlock victim based on the lowest transaction rollback cost. In case of same roll back cost, one of the transaction is chosen as a victim and rolled back. However, you can intervene the process by deadlock hints.
- SET DEADLOCK_PRIORITY LOW - SQL Server will choose the transaction as deadlock victim as its priority is low.
- SET DEADLOCK_PRIORITY NORMAL - This resets the low priority to normal.
- SET DEADLOCK_PRIORITY HIGH - This will reduce the likelihood of being a deadlock victim but not guarantee in case both transactions have the same HIGH priority.
Producing Deadlock
Let's open two windows in SSMS and follow below steps to produce deadlock. You will get output like figure-2.
| Steps |
Window - 1 |
Window - 2 |
Step-1 |
DROP TABLE IF EXISTS dbo.Lefty;
GO
CREATE TABLE dbo.Lefty (C1 INT PRIMARY KEY CLUSTERED);
GO
DROP TABLE IF EXISTS dbo.Righty;
GO
CREATE TABLE dbo.Righty (C1 INT PRIMARY KEY CLUSTERED);
GO
INSERT INTO dbo.Lefty VALUES (1),(2),(3)
INSERT INTO dbo.Righty VALUES (1),(2),(3)
|
|
| Step-2 |
Begin Tran -- Window-1
Update dbo.Lefty SET
C1 = C1 + 1
|
|
| Step-3 |
|
Begin Tran -- Window-2
Update dbo.Righty SET
C1 = C1 + 1
|
| Step-4 |
Update dbo.Righty SET
C1 = C1 + 1
|
|
| Step-5 |
|
Update dbo.Lefty SET
C1 = C1 + 1
|
| Step-6 |
Deadlock message will appear in any of the windows |
| Step-7 |
Rollback
|
Rollback
|
Table-1: Producing a deadlock
Figure-2: Producing a deadlock
Deadlock Detection
There are couple of ways to detect the deadlock.
Extended Events
The popular deadlock monitoring tool is system_health Extended events. Start the session and watch live data. It will display a window like Figure-3 with the deadlock graph.
Figure-3: Extended event deadlock graph
You can also extract it from system_health session ring buffer using below query which will produce XML output like Figure-4. Click on the XML link open and save it in your desktop.
DECLARE @path NVARCHAR(260)
--to retrieve the local path of system_health files
SELECT @path = dosdlc.path
FROM sys.dm_os_server_diagnostics_log_configurations AS dosdlc;
SELECT @path = @path + N'system_health_*';
WITH fxd
AS (SELECT CAST(fx.event_data AS XML) AS Event_Data
FROM sys.fn_xe_file_target_read_file(@path,NULL,
NULL,
NULL) AS fx )
SELECT dl.deadlockgraph
FROM
( SELECT dl.query('.') AS deadlockgraph
FROM fxd
CROSS APPLY event_data.nodes('(/event/data/value/deadlock)') AS
d(dl) ) AS dl;
Figure-4: XML output of deadlock info
Now download and install the Plan Explorer. This is an amazing FREE tool for deadlock analysis. Open the Plan Explorer tool and open the just saved xml file from your desktop. It will be like Figure-5. You can check details info of deadlock queries and deadlock graph. Even you can re-play the deadlock by clicking bottom play button. You can adjust layout of deadlock graph also from bottom settings.
Figure-5: Deadlock xml in Plan Explorer tool
Trace Flag
When a deadlock occurs, SQL Server can log details using Trace Flags 1204 and 1222.
- Trace Flag 1204: Reports deadlock information grouped by each node involved.
- Trace Flag 1222: Reports deadlock information grouped first by processes, then by resources.
Both can be enabled together to get two views of the same deadlock. Try to avoid the trace flag as it has performance impacts.
First Responder Kit - sp_BlitzLock
sp_BlitzLock gives you a slice-and-dice analysis of which tables, indexes, and queries are involved in most of your deadlocks so you can quickly get to the root cause. For details check my article on sp_BlitzLock.
Deadlock Resources
Below resources can cause blocking and steer to deadlocks.
- Locks - Waiting to acquire locks on objects like pages, rows, metadata, and applications can cause a deadlock.
- Worker threads - Assume a queued task is waiting for worker thread. It holds resources that are blocking all worker threads can produce deadlock scenario.
- Memory - When concurrent requests need more memory than available, there can be deadlock. For example, two queries holding 10 MB and 20 MB memory respectively. Their requirements are 30 MB but available memory is 20 MB. Forcing them to wait on each other.
- Parallel query threads (coordinator, producer, consumer) can block each other if at least one thread is outside of the parallel query and result-in deadlock.
Minimizing Deadlocks
You can not fully escape from deadlocks. However, you can reduce it by following some techniques.
- Ensure every transaction accesses the resources in the same physical order. For example, if in transaction T1 tables are accessed in A, B and C then in transaction T2, access the tables in A, B and C order.
- Deadlocks occur due to long running queries which hold locks and block other queries. Keep the transaction short and run in a batch.
- Using lower isolation level helps to reduce deadlocks. For example read committed holds shared locks for a shorter duration than a higher isolation level, such as serializable. This reduces locking contention.
- Try to use clustered index over nonclustered. Nonclustered index acquires two locks to protect the data and index (as data and index resides in separate B-tree leaf). Where as clustered index stores both data and index in the same B-tree leaf. So, it holds a single lock.
- Use a covering index for the SELECT statement as it stops SELECT statement to access the base table. So, other transaction can lock the base table.
- Using READ_COMMITTED_SNAPSHOT can reduce deadlock. It creates a version of rows in tempdb. There will be no lock contention due to reads since the reads are on a different version of the data.
Final Words
In this second part of DBA’s Guide to SQL Server Lock, Block, and Deadlock, we explored how deadlocks arise when processes are stuck waiting on each other. Understanding the distinctions and behaviors of locks, blocks, and deadlocks is essential for diagnosing performance bottlenecks and ensuring system stability. By leveraging tools such as deadlock graphs, trace flags, and Extended Events, you can effectively capture and analyze contention scenarios. Ultimately, proactive monitoring, well-designed indexing strategies, optimized queries, and appropriate isolation levels are the keys to preventing these issues and maintaining smooth database operations.
References
Going Further
If SQL Server is your thing and you enjoy learning real-world tips, tricks, and performance hacks—you are going to love my training sessions too!
Need results fast? I am also available for 1-on-1 consultancy to help you troubleshoot and fix your database performance issues.
Let’s make your SQL Server to take your business Challenge!
For any queries, mail to mamehedi.hasan[at]gmail.com.