Common symptoms

  • The application shows the error "Transaction (Process ID X) was deadlocked on lock resources"
  • Operations that normally take seconds remain suspended indefinitely
  • sys.dm_exec_requests shows sessions with blocking_session_id > 0 and wait_type = 'LCK_M_X' or similar
  • Users report random timeouts that resolve on their own after several seconds
  • Monitoring metrics show spikes in LCK_M_X or LCK_M_U in wait stats

Business risks

  • Business transactions aborted by deadlock generate inconsistent data if the application doesn't retry correctly
  • Cascading locks can paralyze dozens of sessions simultaneously
  • A blocking session with a long transaction can block all concurrency on a critical table
  • Frequent deadlocks indicate a design problem that worsens with growing load

Detailed technical checklist

  • 1. Identify the head of the blocking chain The root session is the one with blocking_session_id = 0 but other sessions point to it as the blocker. It is the one that must be resolved.
  • 2. Read deadlocks from the system_health session (no extra configuration required) SQL Server 2012+ automatically captures deadlocks in the Extended Event system_health.
  • 3. Verify if RCSI is enabled RCSI eliminates locks between readers and writers without changing application code.
  • 4. Enable RCSI if not active Requires a momentary exclusive access. Run carefully in production, ideally in a maintenance window.
  • 5. Review transactions open without activity (idle in transaction) A transaction that was not properly closed can hold locks for hours.
  • 6. Manually terminate a blocking session if safe Only if the blocking session is inactive and the business requires it. First verify what transaction it has open.
  • 7. Configure Blocked Process Report for automatic alerts Activates an alert when a session has been blocked for more than N seconds.
  • 8. Review the isolation level of the most frequent transactions READ COMMITTED (default) can generate reader-writer locks. SNAPSHOT or RCSI eliminates them.

Long-term prevention strategies

Recurring locks generally have structural causes that must be addressed in code and configuration:

  • Enable RCSI: eliminates reader-writer locks without changing code. It is the most impactful improvement in most cases.
  • Reduce transaction duration: never start a transaction before having everything ready to execute. Avoid user interaction within an open transaction.
  • Add appropriate indexes: a Table Scan within a transaction locks many more rows (or the entire table with lock escalation) than an Index Seek.
  • Review table access order: two processes that access the same tables in reverse order generate deadlocks. Standardize the access order of shared resources.
  • Configure SET LOCK_TIMEOUT: so queries fail quickly instead of waiting indefinitely, reducing the impact of locks.
T-SQL 
-- Who is blocking whom right now
SELECT session_id, blocking_session_id, wait_type, wait_time
FROM sys.dm_exec_requests
WHERE blocking_session_id <> 0;

When to escalate to a specialist DBA

  • Frequent deadlocks (more than 1 per hour) affecting critical business transactions
  • Cascading locks that paralyze dozens of sessions and require repeated manual kills
  • The blocking chain has a root session that cannot be identified or terminated without risk
  • Locks reappear minutes after being manually resolved, indicating a structural problem

Frequently asked questions

What is the difference between a lock and a deadlock?

A lock (blocking) occurs when a session waits for another to release a resource. If the blocking session finishes, the lock resolves itself and the waiting session continues. A deadlock occurs when two or more sessions mutually block each other in a cycle: session A waits for B to release a resource, and B waits for A to release another. SQL Server automatically detects these cycles and selects a victim (the session with the lowest rollback cost) to terminate it and break the cycle.

What is RCSI and when should I enable it?

Read Committed Snapshot Isolation (RCSI) makes readers use row versions stored in tempdb instead of waiting for writers to release locks. This eliminates reader-writer locks without modifying application code. It's worth enabling when frequent locks are between read queries (SELECT) and concurrent writes (INSERT/UPDATE/DELETE). The cost is higher tempdb usage to store row versions.

How do I identify which session is the root of the blocking chain?

The root session is the one with blocking_session_id = 0 in sys.dm_exec_requests but pointed to by other sessions as their blocker. In a long chain (A blocks B, B blocks C), session A is the root and must be resolved — terminating B or C does not release the others.

Can KILLing a session cause data inconsistencies?

No, SQL Server does a complete rollback of the open transaction before terminating the session. The data is left in the state it was before the transaction started. The risk is the time a rollback can take on long transactions, during which the session appears as rollback in progress and resources remain locked. That is why it is important not to KILL sessions lightly when large transactions are in progress.

Related resources