Deadlocks, detection, and mitigation

Overview

A deadlock happens when two or more tasks each hold resources that the others need, creating a circular dependency. Because no participant can continue, SQL Server detects the cycle, chooses one participant as the deadlock victim, rolls back that transaction, and returns error 1205 to the application.

Deadlocks are different from ordinary blocking. Blocking can resolve when the blocking transaction commits or rolls back. A deadlock cannot resolve naturally because each participant is waiting for another participant in the same cycle.

This topic matters because deadlocks are common in busy OLTP systems. They can happen even when every individual query is valid. Typical causes include inconsistent object access order, long transactions, missing indexes, high isolation levels, lookup-heavy plans, competing updates, and read/write interactions.

For interviews, strong candidates can explain what a deadlock is, how SQL Server chooses a victim, how to read a deadlock graph, how to handle error 1205 safely, and how to reduce deadlocks through transaction design, indexing, access order, batching, row versioning, and retry logic.

Core Concepts

What A Deadlock Is

A deadlock is a circular wait.

Example:

Session A holds a lock on row 1 and wants row 2.
Session B holds a lock on row 2 and wants row 1.
Neither session can continue.

SQL Server's deadlock monitor detects this cycle and breaks it by choosing a victim.

Example pattern:

-- Session A
BEGIN TRANSACTION;
UPDATE dbo.Accounts SET Balance = Balance - 100 WHERE AccountId = 1;
UPDATE dbo.Accounts SET Balance = Balance + 100 WHERE AccountId = 2;
COMMIT TRANSACTION;

-- Session B, at the same time
BEGIN TRANSACTION;
UPDATE dbo.Accounts SET Balance = Balance - 50 WHERE AccountId = 2;
UPDATE dbo.Accounts SET Balance = Balance + 50 WHERE AccountId = 1;
COMMIT TRANSACTION;

The sessions access the same resources in opposite order, which creates a classic deadlock risk.

Deadlock Vs Blocking

Blocking:

Session B waits for Session A.
Session A can still complete.

Deadlock:

Session A waits for Session B.
Session B waits for Session A.

SQL Server resolves deadlocks automatically by rolling back one transaction. It does not automatically resolve ordinary blocking unless the blocking transaction completes, times out, disconnects, is killed, or rolls back.

Deadlock Victim

When SQL Server detects a deadlock, it chooses a victim.

The victim's transaction is rolled back, locks are released, and the other participant can continue. SQL Server returns error 1205 to the victim session.

Example error:

Transaction (Process ID 51) was deadlocked on lock resources with another process
and has been chosen as the deadlock victim. Rerun the transaction.

Victim selection considers deadlock priority and rollback cost. If one session has lower deadlock priority, it is more likely to be chosen. If priorities are equal, SQL Server generally chooses the transaction that is cheaper to roll back.

SET DEADLOCK_PRIORITY

SET DEADLOCK_PRIORITY influences which session is chosen as the victim.

Example:

SET DEADLOCK_PRIORITY LOW;

This can be appropriate for background cleanup work that should yield to user-facing transactions.

Example:

SET DEADLOCK_PRIORITY HIGH;

This may be appropriate for critical short transactions, but it should be used sparingly. It does not prevent deadlocks; it only affects victim choice.

Common Deadlock Pattern: Opposite Access Order

Deadlock-prone pattern:

-- Session A
BEGIN TRANSACTION;
UPDATE dbo.Customers SET LastUpdatedAt = SYSUTCDATETIME() WHERE CustomerId = 1;
UPDATE dbo.Orders SET Status = N'Reviewed' WHERE CustomerId = 1;
COMMIT TRANSACTION;

-- Session B
BEGIN TRANSACTION;
UPDATE dbo.Orders SET Status = N'Packed' WHERE CustomerId = 1;
UPDATE dbo.Customers SET LastUpdatedAt = SYSUTCDATETIME() WHERE CustomerId = 1;
COMMIT TRANSACTION;

If Session A locks Customers first and Session B locks Orders first, they can deadlock.

Mitigation:

• Access objects in the same order in all code paths.
• Standardize updates through stored procedures or shared data-access patterns.
• Keep transactions short.
• Add indexes so each update touches fewer rows.

Common Deadlock Pattern: Lookup Deadlocks

Lookup-heavy plans can deadlock when sessions access indexes and base rows in conflicting orders.

Example symptoms in a deadlock graph:

• One process owns a key lock in one index and waits on another key or clustered index row.
• Another process owns the second resource and waits on the first.
• Plans include nested loops and key lookups.

Mitigation:

• Add included columns to cover important queries.
• Update indexes so access paths are consistent.
• Reduce lookup counts.
• Review actual execution plans.
• Avoid returning unnecessary columns.

Common Deadlock Pattern: Range Checks

Check-then-insert logic can deadlock or race when ranges are not protected consistently.

Example:

BEGIN TRANSACTION;

SELECT *
FROM dbo.Appointments
WHERE DoctorId = @DoctorId
  AND StartTime < @RequestedEnd
  AND EndTime > @RequestedStart;

INSERT dbo.Appointments (DoctorId, StartTime, EndTime)
VALUES (@DoctorId, @RequestedStart, @RequestedEnd);

COMMIT TRANSACTION;

If multiple sessions run this at once, they can conflict. Depending on the business rule, you may need proper indexes plus SERIALIZABLE or locking hints such as UPDLOCK, HOLDLOCK on the range.

Common Deadlock Pattern: Reader And Writer Deadlocks

Reader/writer deadlocks can happen when read queries take shared locks and write queries take exclusive or update locks in a conflicting order.

Mitigation options:

• Keep read and write transactions short.
• Add indexes to reduce scan size.
• Avoid higher isolation levels unless needed.
• Use row versioning isolation, such as RCSI, when statement-level committed snapshots are acceptable.
• Avoid large reports against hot OLTP tables under locking reads.

Row versioning can reduce read/write deadlocks, but writers can still deadlock with writers.

Detecting Deadlocks

The recommended way to capture deadlock details is the xml_deadlock_report Extended Event.

SQL Server's system_health Extended Events session captures deadlock graphs by default in many SQL Server environments.

Deadlock information can include:

• Victim process.
• Processes involved.
• Resources involved.
• Locks held.
• Locks requested.
• Statements or execution stack.
• Isolation level.
• Deadlock priority.
• Transaction start time.
• Client application and host.

Do not troubleshoot only from the 1205 error message. The deadlock graph contains the evidence.

Reading A Deadlock Graph

A deadlock graph usually has three major parts:

• Victim list.
• Process list.
• Resource list.

Process list shows sessions involved, including:

• Session ID.
• Transaction name.
• Isolation level.
• Current statement or input buffer.
• Wait resource.
• Lock mode requested.
• Deadlock priority.

Resource list shows:

• The locked resource.
• The owner process.
• The waiter process.
• Lock modes held and requested.
• Object and index names when available.

Interview answer: find what each process owns, what it waits for, and why the access pattern created a cycle.

Capturing Deadlocks From system_health

Example query pattern:

SELECT
    xdr.value('@timestamp', 'datetime2') AS deadlock_time,
    xdr.query('.') AS deadlock_xml
FROM
(
    SELECT CAST(target_data AS XML) AS target_data
    FROM sys.dm_xe_sessions AS xs
    JOIN sys.dm_xe_session_targets AS xst
        ON xs.address = xst.event_session_address
    WHERE xs.name = N'system_health'
      AND xst.target_name = N'ring_buffer'
) AS data
CROSS APPLY target_data.nodes('RingBufferTarget/event[@name="xml_deadlock_report"]') AS XEventData(xdr)
ORDER BY deadlock_time DESC;

In production, an event file target is often better than relying only on ring buffer history because the ring buffer can roll over.

Handling Error 1205

Applications should handle SQL Server deadlock error 1205.

Basic pattern:

• Catch error 1205.
• Roll back any active transaction.
• Wait briefly.
• Retry if the operation is safe to repeat.
• Use a bounded retry count.
• Log repeated failures with correlation IDs and query context.

Retry logic must be safe. If the operation has external side effects, use idempotency keys or an outbox pattern so a retry does not duplicate work.

Retrying Deadlocks

Deadlock retry is normal because SQL Server already rolled back the victim transaction.

Good retry behavior:

• Retries only known transient failures.
• Uses exponential backoff or random jitter.
• Has a maximum attempt count.
• Logs final failure.
• Does not retry non-idempotent external side effects blindly.

Example application-level idea:

try operation
if SQL error is 1205:
    wait random short delay
    retry up to configured limit
else:
    fail normally

The database code should still be improved. Retry is resilience, not the only mitigation.

Preventing Deadlocks By Access Order

A reliable mitigation is consistent access order.

Instead of:

Path 1: update Customer, then Order.
Path 2: update Order, then Customer.

Prefer:

All paths: update Customer, then Order.

Consistent ordering reduces circular waits because sessions queue in the same order instead of crossing.

Stored procedures can help standardize access order for important write workflows.

Preventing Deadlocks With Indexes

Indexes reduce deadlocks by reducing the number of rows and pages touched.

Example:

CREATE INDEX IX_Orders_Customer_Status
ON dbo.Orders (CustomerId, Status)
INCLUDE (OrderDate);

This can reduce a broad scan:

UPDATE dbo.Orders
SET Status = N'Archived'
WHERE CustomerId = @CustomerId
  AND Status = N'Completed';

Better indexing can:

• Shorten transaction duration.
• Reduce lock footprint.
• Avoid unnecessary key lookups.
• Make range locks narrower under SERIALIZABLE.
• Make access order more predictable.

Preventing Deadlocks With Short Transactions

Long transactions create more opportunity for circular waits.

Avoid:

• User interaction inside transactions.
• External HTTP calls inside transactions.
• Large batch updates in one transaction.
• Reports mixed into write transactions.
• Holding transactions open while the application processes result sets slowly.

Prefer:

• Validate before opening the transaction.
• Open transaction only for required data changes.
• Commit or roll back quickly.
• Batch large writes.
• Use asynchronous outbox messages for external work.

Row Versioning And Deadlocks

RCSI and SNAPSHOT can reduce deadlocks between readers and writers because readers can use row versions instead of shared locks.

However:

• Writers still take locks.
• Writer/writer deadlocks can still happen.
• SNAPSHOT write conflicts need handling.
• Long-running versioned transactions create version store pressure.
• Some locks, such as schema locks, still matter.

Row versioning is a useful tool, not a replacement for good transaction design and indexing.

Common Mistakes

Common mistakes include:

• Treating deadlocks as random instead of analyzing the graph.
• Retrying forever without fixing the cause.
• Retrying non-idempotent workflows.
• Ignoring inconsistent table access order.
• Ignoring missing indexes and lookup-heavy plans.
• Holding transactions open during user interaction or external calls.
• Using high isolation levels broadly.
• Using NOLOCK as a deadlock cure.
• Not capturing deadlock graphs.
• Not logging enough application context to connect deadlocks to code paths.

Best Practices

Best practices:

• Capture xml_deadlock_report with Extended Events.
• Read the victim, process, and resource lists.
• Standardize object access order.
• Keep transactions short and in one batch when possible.
• Add targeted indexes to reduce lock footprint.
• Use row versioning where it matches correctness requirements.
• Use SET DEADLOCK_PRIORITY sparingly for background work.
• Handle error 1205 with bounded safe retries.
• Use idempotency keys for retryable business operations.
• Test high-contention workflows with concurrent sessions.