SQL Server problems
Error 1205 in SQL Server: Read the Deadlock Graph and Fix the Cause
22 November 2026
A deadlock is two sessions that each hold a lock the other needs. SQL Server ends one of them with error 1205 and the other goes on. The server already keeps the full report of every deadlock, with both statements and both locks, so the cause is one query away and no trace flag is needed.
What SQL Server says
Msg 1205, Level 13: Transaction (Process ID 62) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
The lock monitor looks for cycles every 5 seconds, and more often while it finds them. It chooses as victim the session that is cheapest to roll back, rolls its whole transaction back and returns the error to it. The other session never notices.
The deadlocks the server already kept
The system_health Extended Events session runs on every instance and records each deadlock as an xml_deadlock_report:
SELECT x.value('@timestamp', 'datetime2(0)') AS utc_time,
x.query('data/value/deadlock') AS deadlock_graph
FROM (SELECT CAST(event_data AS xml) AS e
FROM sys.fn_xe_file_target_read_file(
'system_health*.xel', NULL, NULL, NULL)
WHERE object_name = 'xml_deadlock_report') f
CROSS APPLY f.e.nodes('/event') AS n(x)
ORDER BY utc_time DESC;
The files roll over, so a busy instance keeps hours or days. How many deadlocks happened since the instance started:
SELECT cntr_value AS deadlocks_since_start
FROM sys.dm_os_performance_counters
WHERE counter_name = 'Number of Deadlocks/sec'
AND instance_name = '_Total';
Despite its name the counter only adds up. Two readings a minute apart give the rate.
How to read a deadlock graph
| Part | What it tells |
|---|---|
victim-list | The process that got error 1205. |
process-list | One process for each session: its statement in inputbuf and in the frame rows, its isolationlevel, the lock it waits for in waitresource and lockMode, its program in clientapp. |
resource-list | The locks of the cycle: keylock, pagelock or objectlock, with objectname and indexname. Each has an owner-list, who holds it, and a waiter-list, who wants it. |
Two statements, two locks and the order they were taken in: that is the whole cause. Most graphs match one of four patterns.
- Opposite order. One transaction updates orders and then customers, another customers and then orders.
- Read, then update. Two sessions read the same row with a shared lock and both then want to update it. Neither can, because of the other's shared lock.
- A reader against a writer on two indexes. A SELECT finds the row in a nonclustered index and then looks it up in the clustered index. An UPDATE changes the clustered index first and the nonclustered one after it. They meet in the middle.
- A scan that locks too much. Without a useful index a statement locks rows it does not change, and collides with sessions it has nothing to do with.
The fix, in order
- Run the victim again. Error 1205 is made to be retried: the transaction was rolled back as a whole, and the second attempt usually passes. An application that catches 1205 and repeats the transaction turns a deadlock into a short delay.
- Take the tables in one order. When every transaction touches the same tables in the same sequence, a cycle cannot form.
- Lock for the update at the read.
SELECT ... WITH (UPDLOCK)on the row that will be updated, or oneUPDATEthat does both. Only one session can hold an update lock, so the second waits in line. - Cover the lookup. Add the columns the SELECT needs to the nonclustered index with
INCLUDE. The reader no longer goes to the clustered index and the two paths no longer cross. - Give the scan an index. A statement that finds its rows through an index locks those rows only.
- Take the readers out of locking.
ALTER DATABASE [Sales] SET READ_COMMITTED_SNAPSHOT ON WITH ROLLBACK IMMEDIATE;Readers take no shared locks, so every deadlock between a reader and a writer disappears. Test it first: it keeps row versions in tempdb. - Choose the victim.
SET DEADLOCK_PRIORITY LOW;in the session that is cheap to repeat, a report or a background job, so the order entry never loses.
See it coming
A few deadlocks a day are the routine of many applications. The news is a change: a release that reversed the order of two statements, an index that was dropped. Radar Engine, the detection layer of Scops, counts the deadlocks of every instance against its own past, with no agent on the server:
| Detector | When it fires |
|---|---|
| Deadlock burst | Watch: 3 or more deadlocks in 10 minutes, at 3 times the rate of the hour before. Warning: 5 or more in 10 minutes, more than in the whole hour before. |
| Blocking pile-up | Warning: at least 10 lock waiters behind one head, the longest waiting 3 minutes or more. Critical: at least 20 lock waiters that are at least half of the active requests. |
| Wait wave | The instance's waits move to one category, far from the usual level of that hour. |
Scops reads the deadlock reports and groups them by pattern. The finding says how many deadlocks of the last hour are the same pattern, on which objects, from which application and in which database, so the statements to fix are named before anybody opens a graph.
Questions
What is error 1205 in SQL Server?
The error a session gets when SQL Server chose it as the victim of a deadlock. Two sessions waited for locks the other held, and the server rolled one transaction back so the other could go on.
How do I find what caused a deadlock in SQL Server?
Read the xml_deadlock_report events of the system_health Extended Events session with sys.fn_xe_file_target_read_file. Each report holds both statements and the locks they held and wanted. No trace flag is needed.
How does SQL Server choose the deadlock victim?
The session with the lower DEADLOCK_PRIORITY loses. When the priorities are equal, the victim is the session whose transaction is cheapest to roll back.
Does READ_COMMITTED_SNAPSHOT prevent deadlocks?
It removes the deadlocks between readers and writers, because readers no longer take shared locks. Deadlocks between two writers remain and are fixed by order, by indexes or by an update lock at the read.
Related problems
- Blocking: find the head blocker
- tempdb is full: what uses it
- All SQL Server problems
- SQL Server monitoring without agents