SQL Server / Advanced / Lesson 14

Deadlocks, waits, and incident evidence

Distinguish a dependency cycle from ordinary waiting and timeouts. Interpret deadlock evidence, identify root causes, and design bounded transaction retries.

About 5 min readContent reviewed 2026-10-23

On this page

Lesson goals

Separate errors 1205 and 1222 from client timeout.
Read owner-waiter relationships in a deadlock graph.
Retry only a complete eligible business transaction.

Classify the failure before fixing it

Blocking occurs when a task waits for an incompatible resource held by another task. A deadlock requires a cycle: A needs something held by B while B directly or indirectly needs something held by A. SQL Server detects the cycle, chooses a victim, rolls its transaction back, and reports error 1205. Ordinary blocking can end naturally when its owner commits.

Error 1222 reports a lock request timeout when a session's LOCK_TIMEOUT expires. A driver command timeout is another boundary and may cancel work without proving the transaction is already cleaned up. These failures require different handling. Record the exact error number, time, operation identifier, and transaction outcome instead of treating every slow query as a deadlock.

Trace a concrete two-resource cycle

Consider two transfers touching account 1 and account 2. Session A updates account 1 first; session B updates account 2 first. If each next requests the other account while holding its first resource, they can form a cycle. The conceptual batch shows only A's order; B reverses it. Do not run the illustration against an existing service.

A consistent access order, shorter transactions, and efficient selective access can reduce the opportunity for this pattern. Actual deadlocks can also involve indexes, key-range protection, parallelism, or other resources, so do not assume every graph is fixed by sorting two identifiers. Follow the graph's owners, requested modes, and resource identities to explain the specific cycle.

sql An access-order illustration

-- Conceptual session A only; requires isolated lab setup.
BEGIN TRANSACTION;
UPDATE dbo.TrainingAccount SET Balance=Balance-10 WHERE AccountId=1;
UPDATE dbo.TrainingAccount SET Balance=Balance+10 WHERE AccountId=2;
ROLLBACK TRANSACTION;
-- Session B taking 2 then 1 can create an opposite dependency.

No victim or timing is predicted. Whether a deadlock occurs depends on the controlled interleaving and access paths.

Read existing evidence without broad changes

SQL Server's system_health Extended Events session normally captures xml_deadlock_report events. An administrator can inspect existing captured graphs under appropriate permissions; retention is finite, so absence of a graph does not prove no deadlock occurred. Preserve the original graph and relevant query identifiers while restricting access to SQL text and parameter values that might contain sensitive information.

For current waits, sys.dm_exec_requests exposes blocker identifiers and wait information. A snapshot is transient evidence, not a historical explanation. A sleeping session with an open transaction can be a head blocker yet have no active request row. Combine active requests with transaction/session evidence through the approved diagnostic workflow instead of killing the first visible waiter.

sql Read current request dependencies

SELECT session_id,blocking_session_id,status,wait_type,
 wait_time,wait_resource,open_transaction_count
FROM sys.dm_exec_requests
WHERE session_id<>@@SPID
ORDER BY blocking_session_id,session_id;

Rows change constantly and may require server-state permissions. SQL Server 2022 changes permissions for several performance DMVs; verify the cited requirements.

Retry the whole eligible operation

After a 1205 victim rollback, an application can retry the entire transaction if the business operation is safe to repeat. Use a bounded attempt count with backoff and jitter to avoid synchronized collisions. Retrying only the final failed UPDATE can omit earlier changes that were rolled back. Preserve one operation identifier so duplicate side effects can be detected.

External actions such as charging a card or sending a notification cannot be undone by SQL rollback. Keep them outside the retried database unit and use an outbox or another reviewed coordination design. A lost connection during commit has an uncertain outcome and is not equivalent to a confirmed deadlock rollback. Reconcile by idempotency key before repeating work.

Verify cycle reduction and transaction cleanup

Reproduce the relevant interleaving in a disposable environment, then compare deadlock rate and latency under representative concurrency. Confirm that transaction state is clean after timeout or cancellation paths as well as after 1205. Increasing a timeout does not remove a dependency cycle, and changing DEADLOCK_PRIORITY only influences victim choice rather than curing the cause.

A durable incident record states the graph's cycle, the access-path or transaction change, the retry boundary, and measured follow-up. Track both reduced deadlocks and unintended blocking or resource regressions. Success is a safer business operation under concurrency, not merely the absence of one error string during a quiet test.

Common errors and fixes

Transaction was deadlocked and chosen as victim.

Why it happens: A dependency cycle required victim rollback.
How to fix it: Inspect the graph and retry the complete eligible transaction with bounds.
Prevent it next time: Use consistent access order and short transactions.

Lock request time out period exceeded.

Why it happens: LOCK_TIMEOUT expired while waiting.
How to fix it: Inspect the blocker and clean up the transaction according to its state.
Prevent it next time: Keep timeout and cleanup policies explicit.

Retry duplicated an external effect.

Why it happens: Only database work rolled back while the external action persisted.
How to fix it: Use durable operation identity and coordinated side effects.
Prevent it next time: Test retries with external boundaries.

Check your understanding

1. A waits for B, while B can continue and commit. Is this a deadlock?

Show worked solution

No cycle has been shown. It is ordinary blocking. Identify why B is slow or holds its transaction open; do not infer a deadlock from the wait duration alone.

2. A 1205 occurred after a debit and during a credit. Which statements are retried?