Deadlock Graphs, Blocking Chains, Timeouts, Retry Policies, and Contention Testing

Learning outcomes

Two ServiceHub workers update the same two inventory rows in opposite order. Each holds one lock and waits for the other: a real deadlock. A different request merely waits behind a blocker; another stops waiting because LOCK_TIMEOUT expires. These outcomes have different evidence and different remediation.

1. Three timelines, three diagnoses

Blocking is a wait where the blocker can still progress. A lock timeout happens when the waiting session exceeds its configured LOCK_TIMEOUT, commonly producing error 1222. A deadlock is a dependency cycle with no natural progress path; SQL Server chooses a victim and returns error 1205. A timeout is not evidence that a deadlock occurred.

-- Session ABEGIN TRANSACTION;UPDATE lab07.Inventory SET on_hand=on_hand WHERE part_id=10;-- keep open briefly-- Session B-- SET LOCK_TIMEOUT 3000;-- SELECT * FROM lab07.Inventory WHERE part_id=10;-- reset afterward: SET LOCK_TIMEOUT -1;

2. Create the classic opposite-order cycle

BEGIN TRY  BEGIN TRANSACTION;  UPDATE lab07.Inventory SET on_hand=on_hand WHERE part_id=10;  WAITFOR DELAY '00:00:03';  UPDATE lab07.Inventory SET on_hand=on_hand WHERE part_id=20;  COMMIT;END TRYBEGIN CATCH  SELECT ERROR_NUMBER() AS error_number,ERROR_MESSAGE() AS error_message,XACT_STATE() AS xact_state;  IF XACT_STATE()<>0 ROLLBACK;END CATCH;GO

BEGIN TRY  BEGIN TRANSACTION;  UPDATE lab07.Inventory SET on_hand=on_hand WHERE part_id=20;  WAITFOR DELAY '00:00:03';  UPDATE lab07.Inventory SET on_hand=on_hand WHERE part_id=10;  COMMIT;END TRYBEGIN CATCH  SELECT ERROR_NUMBER() AS error_number,ERROR_MESSAGE() AS error_message,XACT_STATE() AS xact_state;  IF XACT_STATE()<>0 ROLLBACK;END CATCH;GO

Start both batches close together. One should receive 1205. Do not promise which session loses: victim choice can depend on deadlock priority and estimated rollback cost.

3. system_health already captures the recommended event

Microsoft recommends the xml_deadlock_report Extended Event. The default system_health session captures it, so an operator often has deadlock graphs without enabling SQL Trace/Profiler or creating a broad custom session. A deadlock graph contains a victim list, process list, and resource list that reconstruct the cycle.

WITH x AS(  SELECT CAST(t.target_data AS xml) AS target_data  FROM sys.dm_xe_session_targets AS t  JOIN sys.dm_xe_sessions AS s ON s.address=t.event_session_address  WHERE s.name=N'system_health' AND t.target_name=N'ring_buffer')SELECT n.event_data.query('.') AS deadlock_eventFROM xCROSS APPLY target_data.nodes('//RingBufferTarget/event[@name="xml_deadlock_report"]') AS n(event_data);GO

Ring-buffer history is finite. Export incident evidence promptly; deadlock XML can include SQL text, object names, host/application details, and therefore sensitive operational information.

4. Repair resource order before adding retry

The direct repair for this lab is consistent ordering: both transactions update part 10 before part 20. Real graphs may instead point to broad scans, missing indexes, long transactions, inconsistent object order, lookup/update interactions, or inappropriate isolation. Repair must follow the actual graph.

BEGIN TRANSACTION;UPDATE lab07.Inventory SET on_hand=on_hand WHERE part_id=10;UPDATE lab07.Inventory SET on_hand=on_hand WHERE part_id=20;COMMIT;GO

5. Retry is an application correctness policy

A deadlock victim transaction is rolled back by SQL Server. A bounded retry can be reasonable for error 1205 when the complete operation is idempotent or otherwise safe to replay. Use backoff/jitter, an overall deadline, cancellation, and correlation telemetry. If the operation charges a card, sends email, or calls an external service outside the database atomic boundary, retry needs an idempotency/outbox design.

for attempt in 1..max_attempts:    try:        begin transaction        execute replay_safe_unit_of_work        commit        break    catch sql_error 1205:        rollback if needed        emit telemetry(correlation_id, attempt)        if attempt == max_attempts: raise        sleep(jittered_backoff(attempt))    catch other_error:        rollback if needed        raise

Do not automatically treat lock-timeout error 1222 as identical to deadlock 1205. Timeout can be a service deadline or a chronic blocking symptom that immediate retry worsens.

6. Cleanup and bridge to storage internals

USE ServiceHubLab;GOIF @@TRANCOUNT>0 ROLLBACK;DROP TABLE IF EXISTS lab07.WorkAudit;DROP TABLE IF EXISTS lab07.Inventory;GOIF SCHEMA_ID(N'lab07') IS NOT NULLAND NOT EXISTS(SELECT 1 FROM sys.objects WHERE schema_id=SCHEMA_ID(N'lab07'))  EXEC(N'DROP SCHEMA lab07;');GO

Chapter 07 established a concurrency evidence model: transaction state, lock waits, isolation/versioning, version retention, and deadlock graphs. Chapter 08 descends into pages, extents, heaps, B-trees, and the transaction log—the storage structures behind durable changes and recovery.

Authoritative references

• Deadlocks guide — xml_deadlock_report and system_health
• SET LOCK_TIMEOUT — timeout behavior
• Extended Events — diagnostic framework
• sys.dm_exec_requests — blocking evidence
• Locking guide — lock mechanics
• SQL Server 2025 builds — CU baseline