7.4.2 — MVCC, Locking and Deadlocks

A report reads ten million rows for four minutes. During those four minutes, thousands of updates land on the same tables. The report is not blocked, the updates are not blocked, and the report still returns a perfectly consistent picture of one moment in time.

That is not a compromise anyone tuned. It is a structural property of multi-version concurrency control, and the idea is one sentence: an update does not overwrite a row, it creates a new version of it. Readers then read the version that was current when they started.

1. How a snapshot works

Every transaction gets an id, allocated in increasing order. Every row version stores two of them:

• xmin — the transaction that created this version.
• xmax — the transaction that deleted or superseded it, or empty if it is still current.

You can look at them directly:

SELECT xmin, xmax, id, status FROM orders WHERE id = 42;

An UPDATE is a delete plus an insert. It sets xmax on the old version and writes a new version with xmin set to the current transaction. Both versions are physically in the table at the same time.

Three versions of one row coexist. Which one a transaction sees is decided entirely by comparing its snapshot against each version's xmin and xmax.

A snapshot is the list of transactions that had committed when it was taken, plus the set that were still in progress. The visibility test for a row version is then straightforward:

Show me this version if its xmin committed before my snapshot, and its xmax is empty or belongs to a transaction that had not committed when my snapshot was taken.

Every consequence in Chapter 7.4.1 falls out of that one rule. Read committed takes a new snapshot per statement, so a second SELECT can see newer versions. Repeatable read takes one snapshot for the whole transaction, so it cannot — which is why read skew and phantoms both disappear at that level: rows committed later are simply not in the snapshot.

And the headline property: readers never take locks and never block writers. A reader is looking at versions that are already fixed. This is the single biggest practical difference between an MVCC engine and an old lock-based one, where a long report would block every writer on the table.

2. What MVCC costs

Dead versions accumulate. Once no running transaction could possibly need version 1 of a row, it is garbage. VACUUM finds and reclaims it. Without vacuum, tables and indexes bloat (Chapter 7.3.1) and every scan reads more pages for the same data.

A long transaction pins everything. The vacuum cutoff is the oldest snapshot in the system. One transaction open for two hours means no dead row anywhere in the database can be reclaimed for two hours, including in tables it never touched. That is the mechanism behind Chapter 7.4.1's warning, stated precisely.

Transaction ids run out. PostgreSQL's are 32-bit, so after about 4 billion they wrap around, and a wrapped id would make old rows appear to be from the future — invisible. The defence is freezing: vacuum marks very old, definitely-visible rows as frozen, exempting them from the comparison. If autovacuum cannot keep up the server warns, and eventually refuses new writes to protect the data. "Database is not accepting commands to avoid wraparound data loss" is a real outage that happens to systems with vacuum disabled or a permanently open transaction. PostgreSQL 18 tracks 64-bit ids internally, which reduces but does not remove the concern for existing deployments.

A note on counting. SELECT COUNT(*) cannot use a stored total, because how many rows exist depends on who is asking. Every transaction may have a different answer. That is why an exact count on a large table is a full scan, and why the usual answer is an approximation from statistics or a separately maintained counter.

InnoDB does it differently, and the difference is visible. Instead of keeping old versions in the table, it updates the row in place and writes the previous version into an undo log. Reads that need an older version reconstruct it by walking the undo entries backwards. The trade: the table stays compact and vacuum-style bloat is smaller, but a long-running transaction grows the undo log instead — the "history list length" that MySQL operators watch — and reconstructing very old versions gets slower the further back you go.

3. Locks: what actually blocks what

MVCC removes read-write conflicts. It does not remove write-write conflicts, and those are handled by locks.

Row-level locks are taken automatically by every UPDATE and DELETE, and explicitly by SELECT … FOR …:

Statement	Blocks	Use for
FOR UPDATE	Other FOR UPDATE/FOR SHARE, updates, deletes	Read-then-write on one row
FOR NO KEY UPDATE	Same, except FOR KEY SHARE	What a plain UPDATE of non-key columns takes
FOR SHARE	Writers, not other readers	Read that must stay stable
FOR KEY SHARE	Only key changes	What a foreign key check takes

The two weaker modes exist for one specific reason. When you insert a child row, the engine must ensure the parent still exists, so it takes FOR KEY SHARE on the parent. If a plain UPDATE on the parent took a full exclusive row lock, updating a customer's name would block inserting their orders. The finer modes let the two coexist. This is worth knowing because the symptom — inserts on a child table blocking behind an unrelated update on the parent — is otherwise baffling.

Table-level locks are taken by DDL. ALTER TABLE, DROP, and a plain CREATE INDEX take strong modes that conflict with ordinary queries. The important operational fact from Chapter 7.2.4 restated: a strong lock request queues behind running queries, and everything arriving afterwards queues behind it. A migration waiting on a lock does not just delay itself; it stops the table. Hence lock_timeout.

Row locks are not stored in a lock table. PostgreSQL records the locking transaction id in the row itself, which is why locking a million rows costs nothing extra in memory. InnoDB does keep an in-memory lock structure, which is why locking huge ranges there is more expensive.

4. Gap locks, and why MySQL behaves differently

InnoDB's repeatable read blocks phantoms, which the standard says it need not. The mechanism is gap locks: a lock not on a row but on the space between index entries, so nobody can insert into a range you have read.

-- InnoDB, REPEATABLE READ
SELECT * FROM bookings WHERE room = 4 AND day BETWEEN '2026-08-01' AND '2026-08-07'
FOR UPDATE;

This locks the existing rows and the gaps around them, so a concurrent insert of a booking on 3 August waits. That is a real feature — it prevents phantoms and makes some booking logic simple.

The cost is a lot more contention and many more deadlocks. Two transactions inserting into nearby ranges in different orders can deadlock even though they touch no common row. Many MySQL deadlock reports are gap-lock deadlocks, and they surprise people who reason only about rows.

PostgreSQL has no gap locks. It blocks phantoms at repeatable read through snapshot visibility instead, and detects the remaining anomalies at serializable through SSI. Same guarantee, different mechanism, different failure mode: PostgreSQL aborts a transaction at commit, MySQL blocks it earlier and may deadlock.

5. Deadlocks

A deadlock is a cycle in the waits-for graph: A holds row 1 and wants row 2; B holds row 2 and wants row 1. Neither can proceed.

Chapter 9.5.3 covers Coffman's conditions in full. In a database, three practical facts matter.

The database detects and resolves it. Every second or so it looks for a cycle and kills one transaction — the victim — with deadlock detected. The application must retry. This is the same retry loop as the serialization failure in Chapter 7.4.1; error code 40P01 alongside 40001.

Consistent ordering prevents it. If every transaction touches rows in the same order, a cycle is impossible — the second transaction always waits for the first at the same point rather than crossing over.

// Claim stock for an order. Sorting is the whole fix.
const skus = [...order.lines.map(l => l.sku)].sort();          // (1)
for (const sku of skus) {
  const r = await tx.query(
    `UPDATE products SET stock = stock - $2
     WHERE sku = $1 AND stock >= $2`, [sku, qtyFor(sku)]);     // (2)
  if (r.rowCount === 0) throw new OutOfStock(sku);             // (3)
}

(1) Every transaction in the system claims SKUs in ascending order, so two orders sharing items always contend at the same SKU and one simply waits. Without the sort, one order taking A then B and another taking B then A deadlock roughly whenever they overlap. (2) The conditional update is the lost-update fix from Chapter 7.4.1, doing the check and the change in one statement. (3) Zero rows means insufficient stock, and throwing rolls back everything claimed so far — atomicity doing its job. Chapter 9.7.24 works this design through in full.

Read the deadlock log, because it names the exact statements. PostgreSQL prints both transactions' queries and which locks each held and wanted; MySQL's SHOW ENGINE INNODB STATUS shows the last deadlock. That is usually enough to see which two code paths take locks in opposite orders.

Other reducers: keep transactions short so windows are small, take the strongest lock you will need first rather than upgrading a shared lock to exclusive mid-transaction (upgrades are a classic cycle source), and avoid touching many rows in an unspecified order.

6. SKIP LOCKED: a job queue in plain SQL

Two lock modifiers turn a table into a work queue, and this is one of the most useful things in this chapter.

BEGIN;
SELECT id, payload FROM jobs
WHERE status = 'pending'
ORDER BY created_at
FOR UPDATE SKIP LOCKED                    -- (1)
LIMIT 1;

UPDATE jobs SET status = 'running', started_at = now() WHERE id = $1;
COMMIT;                                   -- (2)

(1) SKIP LOCKED means ignore rows another transaction has locked and give me the next free one. Without it, ten workers all queue behind the same oldest row and the queue processes one job at a time. With it, ten workers take ten different jobs with no coordination, no broker and no polling storm. (2) Committing releases the lock; the row's status now keeps other workers away.

NOWAIT is the other modifier: fail immediately rather than wait for a lock. Useful when a request would rather return "busy" than hold a connection.

Be honest about the ceiling. A SQL queue is excellent up to a few thousand jobs a second, is transactional with the rest of your data — which removes the dual-write problem entirely, because enqueueing and the business change commit together — and needs no extra infrastructure. Beyond that, or when you need fan-out to many consumers, replay, or partition-ordered delivery, you want a real broker (Chapter 10.8.1). Reaching for Kafka before the SQL queue has been measured is a common and expensive over-build.

7. Advisory locks

Sometimes the thing you need to lock is not a row. "Only one process may run the nightly reconciliation" has no row to lock.

SELECT pg_try_advisory_lock(42);    -- returns true or false immediately
-- … do the work …
SELECT pg_advisory_unlock(42);

An advisory lock is a named lock the database manages and enforces nothing about — the meaning is entirely yours. It is a genuinely useful distributed mutex when you already have a database and do not want another dependency for a leader election.

Two cautions. A session-level advisory lock is held until released or the connection closes, so a crashed worker holds it until the connection is reaped — set a TCP keepalive or use the transaction-scoped variant pg_advisory_xact_lock, which releases at commit. And advisory locks are per-database-instance, so they do not survive a failover to a replica.

8. Diagnosing a stuck system

-- Who is blocking whom, right now
SELECT blocked.pid   AS blocked_pid,
       blocked.query AS blocked_query,
       blocking.pid  AS blocking_pid,
       blocking.query AS blocking_query,
       blocking.state
FROM pg_stat_activity blocked
JOIN pg_stat_activity blocking
  ON blocking.pid = ANY(pg_blocking_pids(blocked.pid))   -- (1)
WHERE blocked.wait_event_type = 'Lock';                  -- (2)

(1) pg_blocking_pids gives the sessions directly blocking a given one, which is what turns "everything is slow" into a named culprit. (2) Only sessions actually waiting on a lock.

Read the result for the head of the chain, not the tail. Twenty blocked queries usually have one root: often a session in state idle in transaction, which means it holds locks and is doing nothing — a forgotten COMMIT, or a transaction waiting on a slow external call.

Then: SELECT pg_cancel_backend(pid) cancels the query, pg_terminate_backend(pid) ends the session. Cancel first; terminate only if it does not respond.

What the interviewer will push on

"How does MVCC let a long report run without blocking writes?" Updates create new row versions with xmin/xmax transaction ids, and a transaction's snapshot decides which version it sees. Readers take no locks because they read versions that are already fixed. The tell is stating the visibility rule rather than saying "it keeps old copies".

"What does MVCC cost?" Dead versions that need vacuum, plus the fact that a long transaction pins the vacuum cutoff database-wide. Then the one that shows depth: transaction id wraparound, freezing, and the server refusing writes to protect data if autovacuum falls far enough behind.

"Why is COUNT(*) slow on a large table?" Because how many rows exist depends on the asking transaction's snapshot, so there is no single stored total to read. Approximate from statistics or maintain a counter if you need it fast.

"How does MySQL block phantoms at repeatable read?" Gap locks — locking the space between index entries so nobody can insert into a range you read. Then price it: much more contention and a whole category of deadlocks between transactions that share no rows. PostgreSQL gets the same guarantee from snapshot visibility with no gap locks and a different failure mode.

"How do you prevent deadlocks?" Consistent lock ordering — sort the keys before touching them — plus short transactions and taking the strongest lock first rather than upgrading. Then add that the database detects cycles and kills a victim, so the application still needs a retry loop; prevention reduces the rate, it does not remove the need.

"How would you build a job queue?" SELECT … FOR UPDATE SKIP LOCKED LIMIT 1 inside a transaction. Explain what SKIP LOCKED buys — without it every worker queues on the same oldest row — and give the honest ceiling: fine to a few thousand jobs a second and transactional with your data, which removes the dual-write problem; beyond that, or for fan-out and replay, use a broker.

One thing to volunteer: mention that PostgreSQL has weaker row lock modes (FOR KEY SHARE, FOR NO KEY UPDATE) specifically so that updating a parent row does not block inserting child rows that reference it. It explains an otherwise baffling production symptom and shows you have read the lock modes rather than only FOR UPDATE.

Recall

• MVCC: an update writes a new row version with xmin/xmax transaction ids. A snapshot plus those ids decides visibility, so readers take no locks and never block writers.
• Read committed = a new snapshot per statement; repeatable read = one snapshot per transaction, which is why read skew and phantoms vanish there.
• Costs: dead versions need VACUUM; one long transaction pins the vacuum cutoff for the whole database; 32-bit transaction ids need freezing, and a far-behind autovacuum makes the server refuse writes to avoid wraparound.
• COUNT(*) is a scan because the row count depends on who is asking.
• InnoDB keeps old versions in an undo log rather than in the table: less bloat, but long transactions grow the undo history and old-version reads get slower.
• Row locks come in four strengths so that updating a parent row does not block inserting its children. DDL takes table locks, and a queued strong lock stops everything behind it — set lock_timeout.
• InnoDB's gap locks block phantoms by locking the space between index entries, at the price of far more deadlocks between transactions sharing no rows.
• Deadlocks are cycles; the engine kills a victim and the application must retry. Prevent by touching rows in a consistent (sorted) order, keeping transactions short, and never upgrading a shared lock to exclusive.
• FOR UPDATE SKIP LOCKED turns a table into a work queue with no broker — transactional with your data, good to a few thousand jobs a second. pg_advisory_lock locks things that are not rows, and dies with the connection.

Self-test: State the visibility rule for a row version · Why does one idle transaction bloat unrelated tables? · What is freezing protecting against? · What do gap locks buy and cost? · Why sort keys before updating them? · What does SKIP LOCKED change about ten workers on one queue?

Next: 7.5.1 leaves the relational world — what a key-value store and a document store actually are underneath, what they give up, and the modelling rule that decides whether a document is the right shape.