Locks, blocking, deadlocks: the daily triangle

The moment Runehold went above ten concurrent orders per second, the customer support dashboard started timing out at random. The developer who built the dashboard swore the query was fast. The ops engineer who ran the order-processing job swore it wasn’t the job’s fault. They were both right. The actual problem was that the dashboard held a SELECT lock on the customer table, the order job held an UPDATE lock, and for three seconds out of every minute they were fighting over the same rows.

That’s locking, blocking, and the subtle difference between them. Lesson 19 talked about isolation levels (the policy); today we talk about locks (the implementation) and the daily reality of running an OLTP system at scale.

What a lock is

A lock is a marker SQL Server puts on a database resource — a row, a page, an index, a table — saying “I’m using this, you have to wait (or move).”

Locks are acquired automatically by SQL Server during normal operation. You never LOCK TABLE directly in SQL Server (well, there are hints, but rarely). Read a row, SQL Server takes a shared lock on it. Update a row, SQL Server takes an exclusive lock. They’re released at transaction commit or rollback.

Types you’ll see:

• S (Shared) — for reads. Multiple sessions can hold shared locks on the same row at once.
• X (Exclusive) — for writes. Only one session can hold it, blocks everything else.
• U (Update) — a “reader who plans to upgrade to exclusive.” Prevents deadlocks where two sessions both read then both try to upgrade.
• IS, IX, IU (Intent) — “I have an S/X/U lock on something below this object.” Intent locks are on the table when individual rows are locked.
• Sch-S, Sch-M (Schema) — schema stability (read) and schema modification (DDL). Dropping a column takes Sch-M; every other query takes Sch-S.
• Key-range — SERIALIZABLE-specific; locks ranges of keys to prevent phantoms.

You don’t need to memorize all of these. Shared and Exclusive are 90% of what you’ll see.

Compatibility

Two locks on the same resource coexist only if they’re compatible:

Held → / Requested ↓	S	U	X
S	OK	OK	Wait
U	OK	Wait	Wait
X	Wait	Wait	Wait

So: many readers (S) coexist. A reader (S) + writer (X) do not — reader waits. Two writers (X) don’t coexist; later writer waits.

Under RCSI (lesson 19), readers don’t take S locks at all — they read from a snapshot. Writers still take X. Concurrency goes way up.

Blocking vs deadlock

• Blocking is transient. Session B is waiting for session A to release a lock. B is idle until A commits.
• Deadlock is permanent without intervention. A is waiting for B’s lock, B is waiting for A’s lock. Neither can proceed.

SQL Server detects deadlocks automatically (every ~5 seconds) and kills the cheaper of the two with error 1205. Blocking is never automatically resolved — if A never commits, B waits forever (or until query timeout).

Finding who is blocking whom — the classic queries

Old school: sp_who2

EXEC sp_who2;

Shows all sessions, with a BlkBy column. If a session has a number there, that’s the session that’s blocking it. Legible on a quiet server, unreadable on a busy one.

Modern: DMVs

SELECT
    s.session_id,
    s.login_name,
    s.status,
    r.command,
    r.wait_type,
    r.wait_time,
    r.blocking_session_id,
    SUBSTRING(t.text,
              r.statement_start_offset / 2 + 1,
              (CASE r.statement_end_offset
                   WHEN -1 THEN DATALENGTH(t.text)
                   ELSE r.statement_end_offset
               END - r.statement_start_offset) / 2 + 1) AS current_statement
FROM sys.dm_exec_sessions  AS s
LEFT JOIN sys.dm_exec_requests AS r ON r.session_id = s.session_id
OUTER APPLY sys.dm_exec_sql_text(r.sql_handle) AS t
WHERE s.is_user_process = 1
ORDER BY r.blocking_session_id DESC;

One row per active user session. The blocking_session_id tells you who’s blocked. The current_statement tells you what they were trying to do. This is the single most useful query a DBA runs on a cluttered server.

For an even prettier tool, lesson 39 will introduce sp_WhoIsActive — Adam Machanic’s gift to humanity.

What are they waiting on?

SELECT wait_type, COUNT(*) AS sessions, SUM(wait_time) AS total_wait_ms
FROM sys.dm_os_waiting_tasks
GROUP BY wait_type
ORDER BY total_wait_ms DESC;

LCK_M_* wait types mean “waiting on a lock.” PAGEIOLATCH_* means “waiting for a page from disk.” SOS_SCHEDULER_YIELD means “CPU contention.” Each wait type tells a story.

Reading a deadlock graph

When a deadlock fires, SQL Server emits a “deadlock graph” as XML. It’s ugly but informative.

Enable capture with a Extended Events session (modern) or Trace Flag 1222 (old). The default system_health session captures deadlocks automatically:

SELECT TOP (5)
    XEvent.value('(@timestamp)[1]', 'DATETIME2') AS ts,
    XEvent.query('.')                              AS deadlock_xml
FROM (
    SELECT CAST(target_data AS XML) AS td
    FROM sys.dm_xe_session_targets st
    JOIN sys.dm_xe_sessions s ON s.address = st.event_session_address
    WHERE s.name = 'system_health' AND st.target_name = 'ring_buffer'
) AS source
CROSS APPLY td.nodes('RingBufferTarget/event[@name="xml_deadlock_report"]') AS X(XEvent)
ORDER BY ts DESC;

Click the XML cell in SSMS and you get a visualization: two processes, two resources, arrows showing who wanted what. The process marked as “victim” was the one killed with error 1205.

You typically find deadlocks caused by:

• Inconsistent lock order. Session A locks table X then Y; session B locks Y then X. Classic deadlock.
• Index lookups. Key lookups can acquire locks on the base table and non-clustered index in different orders for different queries.
• Foreign key operations. Updating a child table can deadlock with updating the parent if both transactions touch both.
• Wide updates + small selects. A huge update touching many rows deadlocks with a small select grabbing one of them mid-update.

The retry pattern

Deadlocks happen. Your production app needs to retry the killed transaction:

// Pseudocode — similar pattern in any language
for (int attempt = 0; attempt < 3; attempt++) {
    try {
        using (var tx = conn.BeginTransaction()) {
            // ... work ...
            tx.Commit();
            break;
        }
    }
    catch (SqlException ex) when (ex.Number == 1205 && attempt < 2) {
        Thread.Sleep(50 * (1 << attempt));   // exponential backoff
        continue;
    }
}

Three attempts, exponential backoff, then surface the error. Ninety-something percent of deadlocks are resolved by a single retry because by the time you retry, the other transaction has committed and the contention is gone.

Lock escalation: SQL Server’s “give up on rows”

When SQL Server is holding thousands of row locks on the same table in a single transaction, it can decide “holding 5,000 row locks is more expensive than one table lock” and escalate to a single table-level lock. Threshold is ~5,000 locks by default.

Consequences:

• Your transaction now blocks everyone else on the whole table.
• Readers can’t read (under lock-based isolation).
• The whole server feels slow.

Prevention:

• Batch big updates. We covered this in lesson 17. UPDATE TOP (1000) in a loop never crosses the threshold.
• Consider partitioning. A partitioned table can escalate to partition-level rather than table-level locks.
• RCSI. Readers don’t block on writes, so escalation is less painful.

Common locking mistakes

WITH (NOLOCK) as a cure-all

A developer sees blocking, finds WITH (NOLOCK) on Stack Overflow, sprinkles it on every SELECT. Now reads are fast. Then someone reads duplicate rows or phantom rows during a page split. Or reports show wrong numbers at reconciliation. WITH (NOLOCK) is not a performance feature. It’s a correctness tradeoff.

Better: enable RCSI.

Holding a transaction across a user interaction

The textbook anti-pattern from lesson 18. Never open a transaction, wait for user input, commit. User goes for coffee. Every other user waits.

Self-blocking

Uncommon but amusing. A parallel query plan can hold incompatible locks within its own threads and stall itself. Usually a sign of an overly parallel execution plan and resolved by improving indexing.

Ignoring the transaction log

A giant transaction doesn’t just hold locks — it fills the transaction log. If the log fills up, every write on the database halts. We’ll cover log file sizing in lesson 30.

Run this on your own machine (needs two sessions)

USE Runehold;
GO

-- ========== Session A ==========
BEGIN TRAN;
UPDATE Sales.Customer SET Name = N'A has the lock' WHERE CustomerId = 1;
-- Don't commit.

-- ========== Session B (different window) ==========
-- This SELECT blocks until A commits (unless RCSI is on)
SELECT Name FROM Sales.Customer WHERE CustomerId = 1;

-- Meanwhile, from a third session, observe the blocking:
SELECT s.session_id, s.login_name, r.wait_type, r.blocking_session_id,
       DB_NAME(r.database_id) AS db_name
FROM sys.dm_exec_sessions  AS s
LEFT JOIN sys.dm_exec_requests AS r ON r.session_id = s.session_id
WHERE s.is_user_process = 1;

-- Tidy up
-- Session A:
ROLLBACK;

-- ========== Deadlock demo ==========
-- Session A:
BEGIN TRAN;
UPDATE Sales.Customer SET Name = N'A1' WHERE CustomerId = 1;

-- Session B:
BEGIN TRAN;
UPDATE Sales.Customer SET Name = N'B2' WHERE CustomerId = 2;

-- Session A:
UPDATE Sales.Customer SET Name = N'A2' WHERE CustomerId = 2;
-- blocks on B

-- Session B:
UPDATE Sales.Customer SET Name = N'B1' WHERE CustomerId = 1;
-- deadlock fires, one session dies with error 1205

-- Whichever died:
-- Msg 1205, Level 13, State 51, Line ...
-- Transaction (Process ID N) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

Module 3 is done. Between lessons 17-20 you now understand DML end-to-end: how to write data, how transactions work, what isolation does, how locks behave, and how to survive deadlocks gracefully.

Next up: indexes. Lesson 21 (already written — clustered and non-clustered indexes) sets the foundation; lessons 22-24 cover covering indexes, filtered indexes, columnstore, and the truth about fragmentation.