SQL Server blocking and deadlocks explained

“The database keeps locking up” can describe several different problems, and the fix depends on which one you have. Locks are normal. Blocking is what happens when locks are held long enough to make other work queue. Deadlocks are a specific failure that SQL Server detects and ends by itself.

Locks, blocking and deadlocks

Locks are how SQL Server stops concurrent transactions from corrupting each other’s work. By default a read takes shared (S) locks. A modification takes update (U) locks while it finds the rows to change, exclusive (X) locks on the rows it changes, and intent locks on the pages and table above them. Whatever the isolation level, exclusive locks are held until the transaction commits or rolls back.

Blocking is one session waiting for a lock another session holds. It is not an error, and it clears as soon as the holder commits or rolls back. By default a blocked statement waits indefinitely, so users usually see the application’s command timeout. Brief blocking is normal on a busy system; the problem is blocking that lasts seconds or minutes.

Deadlocks are a cycle: session A holds a lock that B needs, while B holds a lock that A needs. SQL Server’s lock monitor checks for cycles every 5 seconds by default, more often while it keeps finding them, and breaks each one by choosing a victim: the session with the lower DEADLOCK_PRIORITY or, if priorities are equal, the one cheapest to roll back. The victim’s transaction is rolled back with error 1205 and the other session carries on.

In practice, blocking causes timeouts and means finding whoever holds the locks; deadlocks cause failed transactions and mean reading the deadlock graph.

Why blocking happens

Long transactions. Every exclusive lock lives until its transaction ends. Typical causes are a transaction spanning user think-time or a call to another service; an application that hit a timeout or error and never rolled back, leaving an idle connection with an open transaction; autocommit turned off in a driver or framework; and batch jobs changing millions of rows in one transaction. Code using TransactionScope without an explicit isolation level runs at SERIALIZABLE by default, holding shared and range locks until commit.

Missing indexes. To update or delete rows matching a condition, SQL Server has to find them. Without a suitable index it scans, taking update locks on rows it will not change and colliding with anyone else in the table, and it runs longer, so it holds its locks for longer. Predicates that cannot use an index, such as a function wrapped around a column, have the same effect.

Lock escalation. When a single statement acquires at least 5,000 locks on one reference to a table or index, SQL Server tries to replace them with one table lock (or a partition lock, with LOCK_ESCALATION = AUTO). Lock memory pressure can also trigger it. Escalation never goes from rows to pages, a ROWLOCK hint does not prevent it, and the resulting table lock blocks everyone else using the table.

Client behaviour. A session in ASYNC_NETWORK_IO is waiting for the application to read its results. Inside a transaction, its locks stay held meanwhile.

Finding the head blocker

In a blocking chain many sessions may be waiting, but usually only one or two are holding things up: the head blockers. Start with who is waiting on whom:

SELECT r.session_id,
       r.blocking_session_id,
       r.wait_type,
       r.wait_time AS wait_ms,
       r.wait_resource,
       DB_NAME(r.database_id) AS database_name,
       t.text AS batch_text
FROM sys.dm_exec_requests AS r
OUTER APPLY sys.dm_exec_sql_text(r.sql_handle) AS t
WHERE r.blocking_session_id > 0
ORDER BY r.wait_time DESC;

Follow blocking_session_id until you reach a session that is not itself blocked. That head blocker often does not appear in sys.dm_exec_requests, because it is idle with an open transaction. This query finds sessions blocking others without being blocked themselves, including idle ones, with the last batch each one sent:

SELECT s.session_id,
       s.status,
       s.login_name,
       s.host_name,
       s.program_name,
       s.open_transaction_count,
       s.last_request_end_time,
       ib.event_info AS last_batch
FROM sys.dm_exec_sessions AS s
OUTER APPLY sys.dm_exec_input_buffer(s.session_id, NULL) AS ib
WHERE EXISTS (SELECT 1 FROM sys.dm_exec_requests AS w
              WHERE w.blocking_session_id = s.session_id)
  AND NOT EXISTS (SELECT 1 FROM sys.dm_exec_requests AS r
                  WHERE r.session_id = s.session_id
                    AND r.blocking_session_id > 0);

A sleeping head blocker with an open_transaction_count above zero is the classic abandoned transaction. Ending it may be the immediate remedy, but check what it is first: killing a session rolls back its work, and a large rollback can take as long as the original changes. The lasting fix is in the application: roll back open transactions in every error handler, or use SET XACT_ABORT ON so a run-time error aborts the transaction instead of leaving it open.

Blocking is often over before anyone looks. The system_health session records sessions that have waited on locks for more than 30 seconds, and the blocked process report (off by default, enabled with the blocked process threshold (s) server option and captured with Extended Events) gives a fuller history.

Capturing deadlock graphs

You do not need to set anything up. The built-in system_health Extended Events session starts with SQL Server and records every deadlock as an xml_deadlock_report event, including the full graph, in a ring buffer and in event files. On SQL Server 2017 and later, this reads them from the files:

SELECT CAST(xed.timestamp_utc AS datetime2(3)) AS deadlock_time_utc,
       CAST(xed.event_data AS xml).query('(event/data/value/deadlock)[1]') AS deadlock_graph
FROM sys.fn_xe_file_target_read_file(N'system_health*.xel', NULL, NULL, NULL) AS xed
WHERE xed.object_name = N'xml_deadlock_report'
ORDER BY deadlock_time_utc DESC;

Management Studio shows each deadlock graphically if you open the system_health event file under Extended Events, or save the XML with an xdl extension and reopen it. The XML is worth reading too. The victim list names the process that was rolled back; each process shows its isolation level, its statement and the lock it was waiting for; and the resource list shows the object and index of each lock. The index often reveals whether a key lookup, a scan or a particular access path was involved.

The event files roll over, so older events age out on a busy server; save the graphs you want to keep. Microsoft advises against modifying system_health itself, so create a separate session if you need longer retention.

Isolation levels, RCSI and NOLOCK

The isolation level decides how long readers hold locks and what they see. The default, READ COMMITTED, takes shared locks as it reads and releases each once the read completes, so readers and writers block each other. REPEATABLE READ and SERIALIZABLE hold shared locks (and, for SERIALIZABLE, range locks) until the transaction ends, greatly increasing blocking and deadlock risk. The transaction_isolation_level column in sys.dm_exec_sessions shows what each session is using.

READ COMMITTED SNAPSHOT (RCSI)

With the READ_COMMITTED_SNAPSHOT database option on, READ COMMITTED reads use row versions instead of shared locks. Each statement sees data as it was committed when the statement started, so readers and writers stop blocking each other. Microsoft recommends it for most applications, and it is the default in Azure SQL Database. The trade-offs:

• Version store. Each modification keeps the previous row version, in tempdb or, with accelerated database recovery, in the database itself, and there must be room for it.
• Long transactions. Versions are kept while any active transaction might need them, so one long-running transaction can make the version store grow steadily. sys.dm_tran_version_store_space_usage shows tempdb version store usage per database.
• Row size. Rows gain up to 14 bytes of versioning information when they are next modified or inserted, which can cause page splits.
• Behaviour. Code relying on a read waiting for another transaction, such as checking a balance and then updating it, may behave differently and needs explicit locking, such as UPDLOCK or the READCOMMITTEDLOCK hint.
• Writers still block writers. RCSI does nothing for two sessions updating the same rows.
• Switching it on needs a moment with no other active connections in the database, so plan it and test the application first.

SNAPSHOT isolation (ALLOW_SNAPSHOT_ISOLATION) gives whole-transaction consistency instead, but applications must request it and handle update conflict errors (3960). SQL Server 2025 adds optimized locking, off by default, which needs accelerated database recovery, works best with RCSI and further reduces blocking and lock escalation. To see where each database stands:

SELECT name,
       is_read_committed_snapshot_on,
       snapshot_isolation_state_desc
FROM sys.databases;

Why NOLOCK is not a fix

Adding NOLOCK hints, or using READ UNCOMMITTED, makes blocking appear to vanish by letting queries ignore other transactions’ locks. The cost is correctness: queries can return changes that are later rolled back, miss rows or read rows twice while data moves, and fail with error 601. NOLOCK still takes schema stability locks, so schema changes still block it, and it does nothing about writers blocking writers. If readers and writers blocking each other is the issue, RCSI solves it without returning wrong answers.

Common patterns and typical fixes

Key lookup deadlocks

A query seeks on a nonclustered index, then looks up the remaining columns in the clustered index, holding a shared lock on the nonclustered key as it does. Meanwhile an update holds an exclusive lock on that clustered row and needs to change the nonclustered index. The graph typically shows key locks on two indexes of the same table. The usual fix is a covering index that adds the needed columns with INCLUDE, so the lookup disappears; RCSI also removes the reader’s shared locks.

Out-of-order access

One procedure updates orders and then customers; another updates customers and then orders. Under concurrent load they can deadlock. Access tables and rows in a consistent order throughout the codebase, and keep transactions short.

Read-then-update conversions

Two sessions read the same row under REPEATABLE READ or SERIALIZABLE, keep their shared locks, and then both try to update it. Neither can convert to an exclusive lock while the other’s shared lock remains. Reading with UPDLOCK makes the second session wait at the read instead of deadlocking at the write.

Escalation during batch work

Large deletes, archiving and imports are best broken into batches small enough to stay under the escalation threshold, each committed separately and supported by an index on the filter columns. Disabling escalation is possible but rarely the right first step, because every one of those locks then has to be held in memory.

Whatever the pattern, applications should treat error 1205 as retryable: the victim’s transaction was rolled back cleanly, and resubmitting it usually succeeds. Retry logic does not replace fixing frequent deadlocks, but it stops occasional ones reaching users.

If blocking or deadlocks are affecting a business-critical system, a focused performance investigation will usually identify the head blockers, the transactions and indexes involved, and the least disruptive fix. Talk to a SQL Server specialist at SQLCare and we can work through the evidence with you.