Understand SQL Server deadlocks, pull the deadlock graph fast, and fix the code patterns behind error 1205 before they come back. Learn what to change first.
SQL Server Deadlocks Explained for Developers (With Real Fixes)
Key Takeaways
● SQL Server deadlocks are circular waits, not generic slowness and not ordinary blocking.
● A good first step is to pull the deadlock graph from the system_health session and inspect the victim, processes, and resources involved. On busy systems, the ring buffer may have already rolled over — for recurring or business-critical deadlocks, a dedicated Extended Events session gives you more durable evidence.
● The most common developer-side causes are inconsistent access order, long transaction scope, missing indexes, and ORM behavior that locks more than expected.
● Retry logic helps your app survive error 1205, but it does not remove the concurrency issue that caused it.
● NOLOCK is not a deadlock fix, and RCSI reduces some deadlocks without eliminating write-write conflicts.
You check your logs and find this:
Transaction (Process ID 89) was deadlocked on lock resources with another process and has been chosen as the deadlock victim.
That message looks random the first time you hit it. It is not.
Many developers treat deadlocks like timeouts, slow queries, or an overloaded server. A deadlock is different. It is a locking cycle, and SQL Server breaks it by rolling one transaction back and returning error 1205.
This guide shows you how to separate deadlocks from normal blocking, pull the deadlock graph with zero setup, spot the code patterns that trigger most incidents, and fix them in the right order.
Deadlock or just blocking? Read error 1205 the right way
Start with the distinction that saves the most time: blocking can clear on its own. A deadlock cannot.
Blocking is one session waiting for another to release a lock. Once that lock is released, work continues. That means a blocked request can still succeed later. You may still have a performance problem, but you do not have a circular wait.
A deadlock is a closed loop. Session A holds something Session B needs. Session B holds something Session A needs. Neither session can finish, so neither can release its lock. SQL Server detects the cycle, picks a victim, and rolls it back.
By default, the victim is the transaction that is cheaper to undo, unless you change that behavior with DEADLOCK_PRIORITY. That is why error 1205 does not mean the server was too busy. It means your code created a circular wait on locked resources.
Find the deadlock graph fast with system_health
SQL Server is often already collecting the evidence. The default system_health Extended Events session runs by default and captures deadlock reports.
Run this first:
SELECT xdr.value('@timestamp', 'datetime2') AS deadlock_time,
xdr.query('.') AS deadlock_graph
FROM (
SELECT CAST(target_data AS XML) AS target_data
FROM sys.dm_xe_session_targets t
JOIN sys.dm_xe_sessions s
ON s.address = t.event_session_address
WHERE s.name = 'system_health'
AND t.target_name = 'ring_buffer'
) AS data
CROSS APPLY target_data.nodes('//RingBufferTarget/event[@name="xml_deadlock_report"]') AS xdt(xdr)
ORDER BY deadlock_time DESC;
That query reads from the ring buffer and gives you the deadlock XML. It is a better starting point than Profiler, which is deprecated. The ring buffer is convenient for a quick look, but it is held in memory and can roll over on busy systems. For recurring or business-critical deadlocks, set up a dedicated Extended Events session that writes to an event file — that target is more durable and is what most DBAs use for serious investigations.
Read the output in this order:
1. victim-list
This shows which session SQL Server killed.
2. process-list
This shows the SQL text, isolation level, and the locks each session held or needed.
3. resource-list
This shows the contested objects and lock modes. That tells you whether the conflict was on a key, page, or table.
In practice, ask four questions: who died, what query was running, what resource was locked, and which other statement completed the loop? If this is unfamiliar territory, a dedicated DBA team can read the graph and trace it to the right query in minutes.
Four patterns that create deadlocks in real code
Most deadlocks come from a few repeatable patterns.
Inconsistent access order
One code path updates Orders and then OrderLines. Another updates OrderLines and then Orders. Under concurrency, each path can grab one lock and then wait on the other.
The principle is clear: make every code path touch shared tables in the same order. In practice this is harder than it looks. With ORMs, triggers, cascades, foreign key checks, and mixed read/write code paths, the actual lock acquisition order may not match what the source code suggests. Trace the real execution path, including generated SQL, rather than assuming table order in application code tells the full story.
ORM transaction scope
ORM code can obscure transaction scope in ways that widen lock contention. SaveChanges() already runs in an implicit transaction, and adding stricter isolation like SERIALIZABLE on top makes deadlocks more likely. The real issue is that developers often do not see implicit transaction boundaries, generated SQL shape, batching behavior, change tracking side effects, or isolation settings until they appear in a deadlock graph.
That is why some teams blame SQL Server when the real issue is transaction scope hidden inside application code.
Missing indexes and wide scans
Bad indexing can contribute to deadlocks by widening the lock footprint and extending transaction duration. If a query scans more rows than it needs, it holds locks longer and touches more resources. That said, deadlocks are fundamentally about access order, transaction design, and concurrency patterns. Indexing is often a factor, not the root cause.
Indexing can sometimes resolve a deadlock without touching business logic, but treat this as symptom reduction, not a root fix. Better indexes reduce lock footprint; they do not address underlying access order or transaction design problems. In one Erik Darling example, an UPDATE dropped from about 5 seconds to roughly 200 milliseconds after the right index was added.
Long transactions and lock escalation
Long transactions create more chances for collisions. Large write batches make that worse. Microsoft notes that SQL Server considers lock escalation after more than 5,000 locks on the current table or index. That 5,000 figure is a heuristic threshold, not a clean deterministic trigger. Actual escalation also depends on memory pressure, access pattern, partitioning, and whether escalation is permitted for the object.
That is one reason deadlocks often appear only in production. Single-user testing rarely recreates the concurrency pattern that exposes them.
Fixes that work, in the order you should try them
The first fix keeps the app alive. The later fixes remove the cause.
1. Add retry logic for error 1205
Catching error 1205 and retrying the unit of work is a standard resilience pattern for deadlock victims. It works when the operation is idempotent and has no irreversible side effects. Be cautious with non-idempotent operations, externally coordinated workflows, or financially sensitive transactions where retrying can cause duplicates or other harm. A small number of attempts with a brief delay is typical; the right count depends on your workload, backoff strategy, and whether the app is likely to contend on the same hot resource each time.
In .NET, that usually means catching SqlException and checking for Number == 1205. This is a resilience fix, not a root fix.
2. Enable RCSI where reader-writer blocking is the problem
If your deadlocks involve readers and writers stepping on each other, READ_COMMITTED_SNAPSHOT is often the highest-leverage database setting you can change.
ALTER DATABASE YourDatabase
SET READ_COMMITTED_SNAPSHOT ON;
RCSI lets readers use row versions instead of waiting on read locks. That can reduce reader-writer deadlocks sharply. It does not remove write-write deadlocks, and it does not replace good transaction design. Before enabling RCSI in production, account for increased tempdb usage, row version store overhead, and the change in read semantics. READ COMMITTED under RCSI no longer acquires shared locks, which can affect applications that relied on blocking reads for coordination. Test the behavior change under your actual workload before applying this database-wide.
3. Fix the query and transaction design
This is the real fix. Shorten transactions. Commit sooner. Touch shared tables in a consistent order. Add indexes so statements touch fewer rows. Break large writes into smaller batches when escalation is part of the pattern.
Also, skip the fake fix. NOLOCK is not a deadlock solution. Beyond dirty reads, it can also return duplicate rows, miss rows entirely, or produce inconsistent aggregates when data pages move during a scan. Most writer-writer deadlocks are completely unaffected by read hints — NOLOCK does not prevent the lock acquisitions that cause those conflicts.
When one deadlock means a bigger concurrency problem
One deadlock can be a code smell. Repeating deadlocks across different queries can point to a broader architecture problem: hot rows, weak indexing, transaction scope, or workload shape. But recurring deadlocks can also come from a single sharp local problem — one bad queue table, one fragile upsert pattern, one trigger, or one ORM-generated access path. Do not jump straight to architecture diagnosis when a focused investigation may find the root cause faster.
Start with the graph. Fix the obvious query or transaction issue. If the pattern keeps coming back in different places, treat it like an architecture problem, not a one-off incident. Keep in mind that the deadlock graph is essential but is often only the starting point. You still need to correlate it with execution plans, transaction scope, application call patterns, and row access frequency under real load to understand the full picture. That fuller investigation is what stops teams from patching symptoms forever.
FAQ: Quick answers to the deadlock questions developers actually ask
What does SQL Server error 1205 mean?
It means your transaction was chosen as the deadlock victim and rolled back so another transaction could continue.
Is a deadlock the same as blocking?
No. Blocking is waiting. A deadlock is a closed loop where both sessions wait on each other, so SQL Server has to kill one.
How do I find deadlocks in SQL Server?
Start with the default system_health Extended Events session, which captures xml_deadlock_report on most modern SQL Server builds. Query the ring buffer for a quick first look. If the ring buffer has already rolled over or deadlocks are recurring, set up a dedicated Extended Events session writing to an event file for more durable and reliable capture.
Does RCSI fix deadlocks?
It reduces many reader-writer deadlocks, but it does not eliminate write-write conflicts or every concurrency edge case.
Will NOLOCK prevent deadlocks?
No. It may reduce some read locking, but it carries serious risks: dirty reads, duplicate rows, missed rows, non-repeatable results, and inconsistent aggregates when pages move during a scan. It also has no effect on writer-writer deadlocks, which are the most common type. Avoid NOLOCK as a deadlock remedy.