Part 3 — Changing data › Chapter 23
Locks, blocking and deadlocks
Shared and exclusive, lock escalation, and the deadlock you fix by swapping the order of two statements.
“Esperienza su ambienti di produzione” — “the application froze at 09:15 and cleared by itself” is a blocking chain, and diagnosing one is a rite of passage.
Two lock modes, one rule
Lots of people can read the same page over each other's shoulders at once. Only one person can write on it, and while they are writing nobody else may read it. That is the whole rule; everything else is bookkeeping about which pages are involved.
| Mode | Taken by | Compatible with |
|---|---|---|
| Shared (S) | Reads, under pessimistic isolation | Other S locks |
| Exclusive (X) | Any write | Nothing |
| Update (U) | The search phase of an update, in SQL Server | S but not U — which is precisely what stops a common deadlock |
| Intent (IS/IX) | The table, when a row inside it is locked | Other intent locks |
Without them, a transaction wanting to lock a whole table would have to check every row to see whether anyone holds a lock on it. An intent lock on the table is a flag saying “somebody has a row lock in here”, so the table-level request can be refused in O(1). They exist to make lock checking cheap, not to protect data — a genuinely satisfying answer to “what is an intent lock?”.
Lock escalation
Row locks cost memory. Above a threshold (about 5 000 locks in one statement in SQL Server) the
engine escalates to a table lock: cheaper for it, catastrophic for everyone
else, because a single large UPDATE now blocks the whole table. PostgreSQL does not
escalate — it stores lock information in the row itself — which is one of the concrete
behavioural differences worth knowing.
Batch the write. Update 2 000 rows at a time in separate transactions rather than two million in one. Each batch stays under the escalation threshold, each commits and releases its locks, and other work gets in between batches. It is slower in total and vastly better for everyone — and “I would batch it” is the expected answer to “how would you update ten million rows in production?”.
Deadlocks
Blocking is one session waiting for another; it resolves by itself when the first commits, and it can last as long as the transaction does. A deadlock is a cycle of waits that can never resolve, so the engine detects it and kills one participant. Blocking is usually the more expensive problem in practice — deadlocks at least announce themselves and get cleaned up in milliseconds; a blocking chain just makes everything slow and produces no error to search for.
The four causes, and their fixes
| Cause | Fix |
|---|---|
| Inconsistent access order — A does orders then customers, B does customers then orders | Always touch tables (and rows) in the same order everywhere. Often a one-line change |
| Lock upgrade — both hold S on a row and both then want X | Take the stronger lock up front: SELECT … FOR UPDATE, or SQL Server's UPDLOCK hint |
| Missing index — a scan locks far more rows than it needed to | Index the predicate. A seek locks one row; a scan locks the range it walked |
| Long transactions — locks held far longer than necessary | Shorten them; never wait on a network call or a user inside one |
“A deadlock is not a bug in the database; it is two transactions taking the same locks in a different order. My first check is whether there is a consistent access order I can impose, and my second is whether a missing index is causing a scan to lock rows it never needed. And the application must retry — a deadlock victim is a transient failure, and code that does not retry it turns a millisecond event into a user-facing error.”
Finding them
-- SQL Server: who is blocking whom, right now
SELECT r.session_id, -- the waiting session
r.blocking_session_id, -- the one it is waiting for
r.wait_type, r.wait_time, -- what kind of wait, and how long
DB_NAME(r.database_id) AS db, -- id → readable name
t.text -- the statement itself
FROM sys.dm_exec_requests r -- what is running right now
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t -- APPLY: one call per row
WHERE r.blocking_session_id <> 0; -- 0 means "not blocked"
-- Deadlock HISTORY lives in the system_health extended event session. It is on by
-- default and nobody knows, which is why "we have no deadlock information" is
-- almost always wrong.
-- PostgreSQL: the blocking chain
SELECT pid, state, wait_event_type, wait_event, -- what this backend is doing
pg_blocking_pids(pid) AS blocked_by, -- an ARRAY of blocking pids
left(query, 80) AS query -- the first 80 characters of the SQL
FROM pg_stat_activity -- one row per connection
WHERE cardinality(pg_blocking_pids(pid)) > 0; -- keep only those actually blocked
-- log_lock_waits = on writes the details to the log after deadlock_timeout.
-- MySQL
SHOW ENGINE INNODB STATUS; -- read the LATEST DETECTED DEADLOCK section
blocking_session_idis a chain, not a pair: A waits for B, which waits for C. Follow it to the end — the session at the head is usually the one to look at, and it is often idle inside an open transaction.pg_blocking_pids()returns an array, socardinality(...) > 0is the way to say “blocked by at least one”. Calling it twice per row is fine here and wasteful in a monitoring query — compute it once in a subquery.CROSS APPLY sys.dm_exec_sql_text(...)is a function called once per row with that row's handle — SQL Server'sLATERAL. A plain join cannot do it.- These are all right now views. A deadlock is already over by the time you look, which is why the history sources in the comments matter more than the live queries.
Retrying a deadlock victim immediately, in a tight loop, against the same contended rows, reproduces the deadlock. Retry with a short randomised backoff and a bounded attempt count, and only for the transient error codes — 1205 in SQL Server, 40P01 in PostgreSQL, 1213 in MySQL. Retrying a constraint violation forever is a different bug.
- Shared locks share; exclusive locks share with nothing. Everything else is bookkeeping.
- Intent locks exist so a table-level lock request can be answered without inspecting every row.
- Lock escalation turns thousands of row locks into one table lock. Batch large writes to stay under the threshold.
- PostgreSQL does not escalate; it stores lock information in the row.
- Blocking is a wait and resolves itself; a deadlock is a cycle and the engine kills a victim.
- The commonest deadlock cause is inconsistent access order, and the fix is usually one line.
- A missing index causes a scan, and a scan locks rows a seek would never have touched.
- A deadlock victim is a transient error. The application must retry, with randomised backoff and a bounded count.
- SQL Server's system_health session already recorded the deadlock graph. Look before saying there is no information.
Interview questions this chapter answers
What is a deadlock and how do you fix one?
Two transactions each holding a lock the other needs — a cycle, which the engine breaks by rolling one back. I would read the deadlock graph to see which resources and which statements, then look for an inconsistent access order to normalise, and for a missing index causing a scan to lock more than necessary. And the application retries, because a victim is a transient failure.
Difference between blocking and deadlocking?
Blocking is one session waiting for a lock another holds; it clears when that transaction ends. A deadlock is a cycle of waits that cannot clear, so it is detected and one party is killed. Blocking is often the more damaging in practice because it produces no error — just slowness.
How would you update ten million rows in production?
In batches — a few thousand rows per transaction, in a loop, driven by an indexed key range. That keeps each transaction under the lock escalation threshold, releases locks between batches, keeps the transaction log manageable, and lets other work through. One statement would escalate to a table lock and hold it for the duration.