Blocking chains

In one line: one session holds a lock and does nothing, a queue of sessions forms behind it, and more queue behind those. The fix is always at the head of the chain: the session that blocks others but isn’t blocked itself.

Run it yourself: the scripts that reproduce everything on this page are in sqlserver-demos/blocking-chains on GitHub.

What you’ll see

  • “The database is slow” or “everything is hanging”, with low CPU.
  • Many sessions waiting on LCK_M_X, LCK_M_S and similar, each pointing at another session in blocking_session_id.
  • Sessions stuck on rows nobody is using. In the demo, a reader waited almost 6 seconds for a row it shared with no one, because it was queued behind a session that was queued behind an idle one.

Why it happens

Locks are held until the transaction ends. A session that opens a transaction and then waits (on the user, the network, a WAITFOR) keeps its locks the whole time. Anyone who needs those rows waits. Anyone who needs the rows those sessions hold waits too.

blocking_session_id names the request ahead of you in the queue, which is not always the lock holder. In the demo, a reader that wanted a row the head held was reported as blocked by a different session, one that never held that row but had queued for it first.

What to do

  • Find the head: a session that blocks someone and is blocked by no one. Start from sys.dm_exec_sessions, not sys.dm_exec_requests. The most common real head is an application that began a transaction and went quiet, and it has no request at all.
  • Fix the head, not the chain. End or kill that session, then fix the code that left the transaction open. Nothing further down the chain is worth killing.
  • Shorten transactions. Don’t wait on users or other systems while holding locks.
  • SET LOCK_TIMEOUT for a caller that would rather fail fast than queue. The statement fails with Msg 1222 (after 1,003 ms with a 1-second timeout in the demo).
  • Row versioning (RCSI) keeps readers out of the queue entirely.

What doesn’t help

  • Killing the first blocked session you see. It’s a victim, not the cause.
  • Expecting a chain to resolve itself. It drains only when the head ends its transaction. A chain that loops back on itself is a deadlock, which the engine does resolve, by failing one transaction with Msg 1205 (in about 5 seconds in the demo).

Check your server

-- Heads of blocking chains: block someone, blocked by no one.
-- Includes idle sessions with an open transaction, which have no request.
WITH links AS
(
    SELECT s.session_id, BlockedBy = ISNULL(r.blocking_session_id, 0)
    FROM   sys.dm_exec_sessions AS s
    LEFT JOIN sys.dm_exec_requests AS r ON r.session_id = s.session_id
),
heads AS
(
    SELECT l.session_id FROM links AS l
    WHERE  l.BlockedBy = 0
      AND  EXISTS (SELECT 1 FROM links AS x WHERE x.BlockedBy = l.session_id)
),
chain AS
(
    SELECT session_id, Depth = 1 FROM heads
    UNION ALL
    SELECT l.session_id, c.Depth + 1 FROM links AS l JOIN chain AS c ON l.BlockedBy = c.session_id
)
SELECT c.Depth, c.session_id, s.login_name, s.host_name, s.program_name,
       s.status, s.open_transaction_count, s.last_request_end_time
FROM chain AS c
JOIN sys.dm_exec_sessions AS s ON s.session_id = c.session_id
ORDER BY c.Depth;

See it happen

The blocking-chains demo builds a real four-session chain from a single script, using Service Broker activation for the background sessions. It finds the head, watches the chain drain, and adds a lock timeout and a deadlock.

Related: deadlock patterns · lock escalation · RCSI and snapshot isolation · wait stats triage

Measured on SQL Server 2022 CU25 (16.0.4255.1). Your numbers will differ; the behaviour shouldn’t.

Part of SQL Server Demos: short explanations of SQL Server problems, each backed by a runnable demo in MarlonRibunal/sqlserver-demos on GitHub. Written with the help of AI; every number comes from running the demo on SQL Server. How this was made.

Verified by MonsterInsights