SQL Server Error 1205: transaction was deadlocked and chosen as the deadlock victim

The error text returned to the client is explicit:

Transaction (Process ID %d) was deadlocked on %.*ls resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

%d is the SPID. %.*ls names the resource type, typically lock. The message tells the application to rerun the transaction but not why the deadlock happened, which resource was contended, or which other session was involved. To answer those questions you need the deadlock graph.

Error 1205 is unusual in one operational respect: by default it is not written to the SQL Server error log or the Windows Application Event Log. It is delivered only to the client. If your monitoring assumes error-log scanning, you will miss it. Many application frameworks catch 1205 and retry transparently, so end users never see it even though the database burns worker threads, log space, and CPU on transactions that get rolled back and re-executed.

A few deadlocks per day on a busy OLTP system may be acceptable. A sustained rate, a sudden jump from baseline, or a deadlock storm is a design or workload problem. This page covers what to check, how to pull the deadlock graph from the system_health Extended Events session, and which patterns to look for.

What this means

A deadlock is a cycle of lock dependency. Two or more sessions each hold a lock the other needs, and neither can proceed. SQL Server’s deadlock monitor wakes every ~5 seconds by default. When it detects a deadlock, it selects a victim and rolls the victim’s transaction back. After detection the scan interval drops to as low as 100ms and climbs back to 5 seconds when contention subsides.

Victim selection is deterministic, not random:

  1. Lowest DEADLOCK_PRIORITY wins (default is NORMAL = 0; values range -10 to 10).
  2. If priorities are equal, the transaction with the lowest rollback cost is chosen, measured by log bytes already written.
  3. If both are equal, the victim is chosen arbitrarily.

The victim receives error 1205. The other session proceeds. From the client’s perspective the transaction failed and must be resubmitted.

flowchart TD
    A["Session A: holds Lock X, requests Lock Y"]
    B["Session B: holds Lock Y, requests Lock X"]
    A -- waits on --> B
    B -- waits on --> A
    C["Lock Monitor scans every 5s,
down to 100ms under load"]
    C --> D{"Pick victim:
lowest DEADLOCK_PRIORITY,
then lowest rollback cost"}
    D --> E["Victim rolled back,
client receives error 1205"]

Two operational consequences follow. First, the 5-second cadence means deadlocks have already happened before you see them. You cannot intervene live, only analyze and prevent. Second, victim selection is silent. Without an alert on the Number of Deadlocks/sec counter or a captured xml_deadlock_report, you may not know deadlocks are happening at all.

The xml_deadlock_report event is enabled by default in the system_health Extended Events session since SQL Server 2012. The system_health session uses both a ring buffer and an event file. Both roll over, and on a busy instance the ring buffer can age out deadlock graphs in minutes to hours.

Common causes

CauseWhat it looks likeFirst thing to check
Inconsistent lock orderingTwo stored procs update the same tables in opposite order; same pair of sessions deadlocks repeatedlyCompare the input buffers of the two processes in the deadlock graph
Lock escalationOne transaction escalated to a table lock and collided with row-level workLook at the resource list for a table-level lock
Missing nonclustered indexesAn UPDATE or DELETE scans a table, takes many row or key locks, escalates, then collides with another writerPull the victim’s execution plan and check sys.dm_db_missing_index_details
Application retry stormsFramework catches 1205 and retries in a tight loop, immediately re-deadlockingCheck application logs for retry counts; correlate with Batch Requests/sec
Schema changes under loadDDL takes schema modification (Sch-M) locks and collides with concurrent readers or writersCheck for Sch-M locks in the resource list

Quick checks

-- Deadlock count since instance startup (cumulative counter):
SELECT cntr_value
FROM sys.dm_os_performance_counters
WHERE counter_name = 'Number of Deadlocks/sec'
  AND instance_name = '_Total';
-- Take two samples and compute (v2 - v1) / elapsed_seconds.

-- Confirm the system_health session is running and has both targets:
SELECT s.name, s.is_started, st.target_name
FROM sys.dm_xe_sessions s
JOIN sys.dm_xe_session_targets st ON s.address = st.event_session_address
WHERE s.name = 'system_health';

-- Recent deadlock graphs from system_health ring buffer:
SELECT
    xdr.value('(event/@timestamp)[1]', 'datetime2') AS deadlock_time,
    xdr.query('(event/data[@name="xml_report"]/value/deadlock)[1]') AS deadlock_graph
FROM (
    SELECT CAST(target_data AS XML) AS target_data
    FROM sys.dm_xe_session_targets st
    JOIN sys.dm_xe_sessions s ON s.address = st.event_session_address
    WHERE s.name = 'system_health'
      AND st.target_name = 'ring_buffer'
) AS tab
CROSS APPLY target_data.nodes('RingBufferTarget/event[@name="xml_deadlock_report"]') AS xed(xdr);

-- Top lock-related waits to confirm deadlocks are part of a broader contention pattern:
SELECT wait_type, waiting_tasks_count, wait_time_ms,
       wait_time_ms - signal_wait_time_ms AS resource_wait_ms
FROM sys.dm_os_wait_stats
WHERE wait_type LIKE 'LCK_M_%'
ORDER BY wait_time_ms DESC;

-- Currently active blocking chains (a near-miss for a future deadlock):
SELECT
    r.session_id AS blocked_session,
    r.blocking_session_id AS blocker,
    r.wait_type,
    r.wait_time / 1000 AS wait_seconds,
    DB_NAME(r.database_id) AS database_name
FROM sys.dm_exec_requests r
WHERE r.blocking_session_id <> 0
ORDER BY r.wait_time DESC;

If the ring buffer has rolled over and the graph is gone, fall back to the event_file target. The system_health event file retains up to 10 files of 100 MB each on Standard and Enterprise editions, or 4 files of 5 MB on other editions.

On a busy instance with a high deadlock rate, even the event file can roll over within hours. On Azure SQL Database the system_health session does not exist; you must create your own Extended Events session targeting an Azure Storage blob.

How to diagnose it

  1. Confirm the deadlock actually happened. Take two samples of Number of Deadlocks/sec 60 seconds apart and compute the rate. If the rate is zero, what the application reported as a deadlock may have been a timeout or a wrapped exception.
  2. Pull the deadlock graph from system_health. Use the ring-buffer query above, or fall back to the event file if the ring buffer has rolled over. If neither is available, check Query Store history for the query the victim was running.
  3. Identify the victim. The XML report identifies the victim process by SPID. Cross-reference it to get the input buffer, isolation level, login, and client hostname.
  4. Identify the resources. The report lists each lock involved and whether each process held or wanted it. Note the resource type (object, hobt, page, key, RID) and the lock mode (S, X, U, and so on).
  5. Reconstruct the cycle. Trace which process held what and which process wanted what. The cycle is the bug. Two UPDATE procs touching the same two tables in opposite order is the textbook case. Lock escalation turning two row-lockers into one table-locker and one row-locker is the common modern case.
  6. Find the execution plans. For each process in the graph, pull the plan from Query Store or cache. Look for table scans, missing indexes, and UPDATE statements without a supporting index seek.
  7. Check for retry behavior in the application. If the application caught 1205 and retried in a loop, the same pair of transactions may have deadlocked repeatedly. Look for elevated Batch Requests/sec immediately after the original error, without a corresponding rise in Transactions/sec.

Metrics and signals to monitor

SignalWhy it mattersWarning sign
Number of Deadlocks/secDirect count of deadlocks; the only counter that proves they are happeningAny sustained nonzero value; more than 10 per minute is a storm
xml_deadlock_report XE eventThe only source of the actual resource list and victim identityMissing graphs mean the ring buffer rolled over
LCK_M_* waitsLock wait time indicates the contention that produces deadlocksSustained high average wait time per wait
Blocking chain depth and durationLong blocking chains are near-missesChains deeper than 5 sessions, blocks longer than 60 seconds
Lock escalation eventsSudden escalation to table locks changes the contention patternEscalation on a hot table during normal load
Batch Requests/sec vs Transactions/secA spike in batch requests without a matching rise in transactions suggests retry behaviorRatio change after the first deadlock
Error 1205 in the Windows Event LogMost monitoring tools expect it there; absent by defaultTools report “no deadlock” while retries pile up

To make error 1205 visible to monitoring tools that read the Windows Application Event Log, run this once per instance. This change is instance-wide, applies to every database, persists across restarts, and increases error-log volume. Review it with your DBA team before applying.

-- Instance-wide change. Increases SQL Server error log and Windows
-- Application Event Log volume for every 1205 occurrence.
EXEC master.sys.sp_altermessage
    @message_id = 1205,
    @parameter = 'WITH_LOG',
    @parameter_value = 'true';

Trace flags 1204 and 1222 are legacy approaches that write deadlock detail to the SQL Server error log. Extended Events is the recommended replacement. Avoid enabling these trace flags on busy systems.

Fixes

Inconsistent lock ordering

This is the only true deadlock fix at the application layer. If two transactions touch the same set of resources, they must acquire locks in the same order. Common cases:

  • Two stored procedures both UPDATE Order then OrderLine, but in opposite order. Reverse one.
  • A transaction that updates a parent and child table, and another that updates the same pair in reverse order. Standardize the order in a coding convention and enforce it in code review.
  • A trigger that updates another table, creating an implicit second access path. Trace the trigger’s lock acquisition and treat it as part of the outer transaction.

There is no server-side setting that fixes inconsistent ordering. The deadlock graph shows you the exact pair.

Missing indexes forcing wider locks

Without a supporting index, an UPDATE or DELETE that filters on a non-indexed column scans the table and acquires a lock on every row it touches. At approximately 5,000 locks on a single object, SQL Server escalates to a table lock, which then collides with any concurrent writer. Check sys.dm_db_missing_index_details for high-impact missing indexes on the tables in the deadlock graph. Add the index, then verify the victim’s execution plan uses a seek instead of a scan.

This is the most common fix in practice and the one with the broadest impact. It also tends to reduce LCK_M_* wait time and lock memory.

Lock escalation

Escalation itself is usually correct behavior: 5,000 row locks consume more memory than one table lock. The fix is almost never to disable escalation. The fix is to make the transaction take fewer locks in the first place:

  • Batch the operation (delete in 1,000-row chunks with a TOP clause and a loop).
  • Add the supporting index so the operation seeks instead of scans.
  • Move long-running reporting work to a readable AG secondary or to RCSI so it does not take shared locks.

Disabling escalation with ALTER TABLE ... SET (LOCK_ESCALATION = DISABLE) is a last resort. It prevents the table lock but allows the transaction to hold thousands of row locks, which increases lock memory pressure and shifts the deadlock to a different shape.

Retry strategy at the application layer

If deadlocks are rare and the application can safely retry, a retry policy with exponential backoff is acceptable. The risk is silent retries masking a growing problem. At minimum, log every 1205 with the SPID, timestamp, and the operation that failed, and alert on retry count. A retry policy that fires more than a handful of times per minute is a signal that the underlying ordering or index problem needs fixing, not hiding.

Prevention

  • Standardize lock acquisition order across stored procedures and application code that touches the same tables. Enforce in code review.
  • Add supporting indexes for UPDATE and DELETE predicates. Recheck sys.dm_db_missing_index_details after every schema change.
  • Keep transactions short. Every extra statement extends the window during which locks are held.
  • Consider RCSI (ALTER DATABASE ... SET READ_COMMITTED_SNAPSHOT ON) to eliminate reader-writer blocking. This does not fix writer-writer deadlocks but removes one entire class of contention. The cost is TempDB version-store overhead. Enabling RCSI requires exclusive database access on some SQL Server versions and changes concurrency semantics for all sessions using the default READ COMMITTED isolation level. Test before applying.
  • Capture every deadlock graph persistently. The system_health ring buffer is not durable enough for trend analysis. Set up an Extended Events session with an event_file target that writes xml_deadlock_report to disk, sized for your deadlock rate. Alternatively, ship the graphs to your monitoring system.
  • Alert on the Number of Deadlocks/sec counter, not on error log entries. The counter is always present; the error log entry is not.
  • Validate retry policy once per quarter. A retry policy that quietly masked a problem last quarter may now be hiding a regression.

How Netdata helps

  • The Number of Deadlocks/sec counter is collected per second and trended, so you can see the exact moment a deadlock storm starts and correlate it with batch requests, lock waits, and blocking.
  • LCK_M_* wait statistics are sampled with short deltas, exposing the contention that surrounds each deadlock rather than averaging it away over the uptime window.
  • Blocking chain depth and head-blocker identity surface alongside deadlocks, so a near-miss pattern (heavy blocking that did not quite deadlock) shows up in the same window.
  • Correlating deadlock spikes with Batch Requests/sec, Transactions/sec, and CPU reveals retry behavior: a deadlock followed by a request spike without a transaction spike is the signature of an application retry loop.
  • Per-second collection means you catch the 30-second deadlock storm that a 5-minute polling interval would average to zero.

Netdata’s Microsoft SQL Server monitoring brings these signals together with per-second metrics and anomaly detection.