Introduction

A SQL Server deadlock occurs when two or more sessions wait for resources locked by each other, creating a circular dependency. SQL Server detects the deadlock and automatically terminates one transaction as the deadlock victim, returning error 1205.

For a DBA, the goal is not just to resolve the immediate error but to identify the root cause and prevent the deadlock from recurring.

The Four Necessary Conditions for Deadlock

For a deadlock to occur, all four of the following conditions must be met simultaneously. This is often referred to as the Coffman Conditions:

1. Mutual Exclusion: At least one resource must be held in a non-shareable mode. Only one process can use the resource at a time. This is a fundamental property of locking mechanisms in databases.
2. Hold and Wait: A process is holding at least one resource and is waiting to acquire additional resources that are currently held by other processes.
3. No Preemption: A resource cannot be forcibly taken from a process. It can only be released voluntarily by the process that is holding it after that process has completed its task.
4. Circular Wait: A set of processes {P0​,P1​,…,Pn​} exists such that P0​ is waiting for a resource held by P1​, P1​ is waiting for a resource held by P2​, and so on, with Pn​ waiting for a resource held by P0​. This forms a closed loop of dependencies.

Read more about SQL Deadlock – Causes and Prevention

1. Deadlock vs. Blocking

Feature	Blocking	Deadlock
What happens?	One session waits for another	Sessions wait on each other in a cycle
Is it always an error?	No	Yes, for the victim transaction
Does SQL Server resolve it automatically?	Usually when the blocking lock is released	Yes, by selecting a victim
Error number	No specific error by default	1205
First action	Identify the blocking session	Capture and analyze the deadlock graph

2. Common Causes of Deadlocks

Cause	Example	Recommended action
Inconsistent access order	Session A updates Table A then B; Session B updates B then A	Access resources in a consistent order
Long transactions	Transaction remains open while application performs other work	Keep transactions short
Missing or unsuitable indexes	Update or lookup touches more rows than necessary	Review execution plans and indexing
Range locking	Concurrent queries lock overlapping key ranges	Review isolation level, indexes, and query design
Reader-writer conflicts	Readers and writers compete for locks	Consider Read Committed Snapshot Isolation (RCSI) where appropriate
Complex transactions	Multiple tables are modified in one transaction	Simplify transaction scope and resource access

3. Find Deadlocks Using Extended Events

SQL Server’s built-in system_health Extended Events session commonly captures deadlock graphs through the xml_deadlock_report event.

In SQL Server Management Studio (SSMS):

1. Expand Management → Extended Events → Sessions.
2. Right-click system_health and select Watch Live Data, or inspect its event files.
3. Find xml_deadlock_report events around the time of the incident.
4. Open the deadlock graph and identify the victim, participating sessions, locked resources, and statements.

The built-in session has finite retention, so older events may no longer be available. For recurring incidents, consider a dedicated Extended Events session that writes deadlock reports to an event file.

4. Check Active Blocking

Use this query to inspect current requests that are waiting on another session.

SELECT
    r.session_id,
    r.blocking_session_id,
    r.status,
    r.wait_type,
    r.wait_time,
    r.wait_resource,
    DB_NAME(r.database_id) AS database_name,
    t.text AS sql_text
FROM sys.dm_exec_requests AS r
OUTER APPLY sys.dm_exec_sql_text(r.sql_handle) AS t
WHERE r.blocking_session_id <> 0
ORDER BY r.wait_time DESC;

Important: This query helps investigate current blocking; it does not reliably retrieve a deadlock that has already occurred. Use the deadlock graph for historical deadlock analysis.

Permissions depend on SQL Server version and configuration. On SQL Server 2022 and later, access to server-level performance DMVs generally requires VIEW SERVER PERFORMANCE STATE; earlier versions commonly require VIEW SERVER STATE.

5. How to Read a Deadlock Graph

Focus on these elements first:

• Victim: The transaction SQL Server selected to roll back.
• Processes: Sessions involved in the deadlock.
• Resources: The keys, pages, objects, or other resources involved.
• Wait relationships: Which session owns a resource and which session is waiting for it.
• Statements: The SQL statements active when the deadlock occurred.
• Execution stack and input buffer: Additional context to help identify the application code or procedure involved.

Do not assume the victim is the session that caused the problem. SQL Server chooses a victim to break the cycle; investigate all participating transactions.

6. Fix the Deadlock Based on the Root Cause

Finding	What to do
Tables accessed in different orders	Standardize the order of table and resource access
Transaction runs for too long	Move non-database work outside the transaction and commit promptly
Excessive rows scanned or locked	Review the execution plan, predicates, and appropriate indexes
Reader-writer deadlocks	Evaluate RCSI or another suitable isolation strategy after testing
Range-lock deadlocks	Review indexes, predicates, transaction logic, and isolation requirements
Deadlocks occur under load	Reproduce with concurrent sessions and validate the fix under realistic workload

An index can reduce the number of rows SQL Server needs to examine, but adding an index blindly is not a guaranteed solution. Validate the actual execution plan, existing indexes, write overhead, and workload before changing production.

7. Should You Use NOLOCK to Fix Deadlocks?

No. NOLOCK is not a general deadlock solution. It can return dirty, missing, or duplicate rows, and it does not eliminate every type of locking or deadlock.

If read-write blocking is a major contributor, evaluate READ_COMMITTED_SNAPSHOT (RCSI). It can reduce reader-writer blocking by using row versions, but it does not eliminate write-write deadlocks and requires careful testing before enabling it.

8. Handle Error 1205 in the Application

Deadlocks can still occur in a correctly designed system. Applications should handle error 1205 by retrying the entire transaction, using a limited number of attempts and a short delay or backoff.

Before implementing retries, verify that the operation is safe to repeat. External side effects such as sending a payment request or message may need separate idempotency controls.

SET DEADLOCK_PRIORITY LOW can influence which session SQL Server chooses as the victim, but it does not remove the underlying deadlock cycle.

9. Production Deadlock Troubleshooting Checklist

DBA incident checklist

• Confirm error 1205 and record the incident timestamp.
• Capture the deadlock graph from Extended Events.
• Identify all participating sessions and SQL statements.
• Determine the locked resources and wait relationships.
• Check transaction scope, access order, indexes, and isolation level.
• Choose a fix based on the graph rather than assumptions.
• Reproduce the scenario with concurrent sessions in a test environment.
• Validate correctness and performance after the change.
• Add bounded, safe retry handling where appropriate.
• Monitor whether the deadlock recurs under production load.

10. Key Takeaways

• Use the deadlock graph to find the actual cause.
• Keep transactions short and access shared resources in a consistent order.
• Review execution plans and indexes before making changes.
• Consider READ_COMMITTED_SNAPSHOT (RCSI) for reader-writer contention, not as a universal fix.
• Handle error 1205 safely in application code.
• Always test the solution under concurrent workload before deploying it.

Remember: A deadlock graph tells you what happened. Your job as a DBA is to understand why the transactions formed a cycle, fix the underlying design or concurrency issue, and verify that the problem does not return.