Handling Deadlocks and Transaction Blocking in Microsoft SQL Server

• Distinguish normal blocking from deadlocks.
• Identify what is blocked, who is blocking, and why.
• Use modern SQL Server tooling (DMVs and Extended Events) to capture evidence.
• Apply practical prevention and mitigation patterns in SQL Server 2025-era environments.

• Reducing transaction duration (commit sooner, avoid user interaction inside a transaction).
• Reducing lock footprint (touch fewer rows; use appropriate indexes; avoid unnecessary scans).
• Choosing an isolation strategy that fits the workload (for example, row versioning to reduce reader/writer conflicts).

1. Transaction A holds a lock on Resource 1 and requests a lock on Resource 2.
2. Transaction B holds a lock on Resource 2 and requests a lock on Resource 1.

• Blocking: SQL Server does not “fix” blocking—blocking ends when the blocker commits/rolls back, or when the waiting session is canceled (by the user, by the app, or by a lock timeout). Blocking is “by design” in a lock-based RDBMS. :contentReference[oaicite:2]{index=2}
• Deadlocks: SQL Server detects deadlocks and automatically selects a deadlock victim; the victim receives error 1205 and its transaction is rolled back. :contentReference[oaicite:3]{index=3}

-- Wait up to 5 seconds for locks, then cancel the waiting statement
SET LOCK_TIMEOUT 5000;

-- Confirm the current session setting (-1 means "not set")
SELECT @@LOCK_TIMEOUT AS lock_timeout_ms;

1. DMVs for real-time blocking analysis (what is blocked right now).
   • sys.dm_exec_requests to see running requests, waits, and blockers. :contentReference[oaicite:6]{index=6}
   • sys.dm_os_waiting_tasks to see task-level waits and blocking chains. :contentReference[oaicite:7]{index=7}
   • sys.dm_tran_locks to see lock requests, lock modes, and lock status. :contentReference[oaicite:8]{index=8}
2. Extended Events for deadlock forensics (what happened earlier).
   • Deadlocks generate error 1205 for the victim. SQL Server records deadlock details, and you typically capture the deadlock graph using Extended Events and incident workflows. :contentReference[oaicite:9]{index=9}

Quick “who is blocking whom” starter query

-- Starter snapshot: find blockers and blocked sessions
SELECT
  r.session_id,
  r.blocking_session_id,
  r.status,
  r.wait_type,
  r.wait_time,
  r.wait_resource,
  r.command
FROM sys.dm_exec_requests AS r
WHERE r.blocking_session_id <> 0
ORDER BY r.wait_time DESC;

1) Keep transactions short and deterministic

• Do not hold locks while waiting for external inputs (UI prompts, network calls, file I/O).
• Touch rows in a predictable order (for example, always update parent then child, or always update tables in the same sequence).

2) Index for the access path you want

• Deadlocks often happen because queries scan too many rows and hold locks longer than necessary.
• Ensure predicates are sargable and that join/filter columns are indexed so SQL Server can seek rather than scan.

3) Use row versioning to reduce reader/writer blocking (when appropriate)

-- Enable row versioning options (run once per database, in a maintenance window)
ALTER DATABASE YourDatabaseName SET ALLOW_SNAPSHOT_ISOLATION ON;
ALTER DATABASE YourDatabaseName SET READ_COMMITTED_SNAPSHOT ON;

4) If you must, control victim selection with DEADLOCK_PRIORITY

-- Example: make this session more likely to be chosen as the victim
SET DEADLOCK_PRIORITY LOW;

5) Build safe retry logic for 1205

• Deadlocks are a normal failure mode under concurrency; robust applications catch error 1205 and retry a bounded number of times.
• Retry only idempotent or well-scoped units of work; avoid “retry everything” loops.

1. Confirm whether it is blocking or deadlock. Deadlock yields 1205 for the victim; blocking shows wait chains and waiting tasks. :contentReference[oaicite:12]{index=12}
2. Identify the blocker and the statement. Use DMVs to find the blocking_session_id and wait_resource.
3. Capture evidence. For deadlocks, capture the deadlock graph via Extended Events workflows; for blocking, snapshot DMVs and the executing SQL.
4. Fix the root cause. Shorten transactions, add/adjust indexes, enforce consistent access order, and reconsider isolation strategy.
5. Optionally mitigate. Use lock timeouts (and app retry logic) to prevent long waits from cascading through the system. :contentReference[oaicite:13]{index=13}