MVCC & Locking — Snapshots, Row Locks & Deadlocks

How databases give isolation without everyone blocking

If every read took a lock, one long report would freeze every writer. Modern databases (Postgres, InnoDB, Oracle) avoid this with MVCC — Multi-Version Concurrency Control. A write never overwrites a row in place; it produces a new version and leaves the old one intact. Each transaction reads against a snapshot — the set of row versions that were committed and visible when the snapshot was taken. The payoff is the single sentence to remember: readers never block writers, and writers never block readers. They only ever touch the version their own snapshot points at.

The mechanism (PostgreSQL model — the clearest one)

Every row version (a tuple) carries two hidden transaction-id fields:

• xmin — the id of the transaction that created this version.
• xmax — the id of the transaction that deleted or superseded it, or 0 (null) while the version is still live.

The visibility rule. A reader with a given snapshot sees a version if and only if: (1) its xmin is committed and visible to the snapshot — that transaction had committed before the snapshot was taken and is not in the snapshot's in-progress list; and (2) its xmax is not visible to the snapshot — either xmax=0, or the deleting transaction is still uncommitted / in the in-progress list. In one line: visible = creator is committed-and-in-the-past AND deleter is not.

A snapshot is not a single number. It is really three things: the lowest still-active txid, the next txid to be handed out, and — crucially — the list of txids in progress when the snapshot was taken. A version is visible only if its xmin committed and is not in that in-progress list. That in-progress list, not id order alone, is why a transaction that started before you but committed after your snapshot stays invisible to you.

What an UPDATE actually does. It does not mutate the row. It performs two moves atomically: it stamps the old version's xmax with the updating transaction's id (marking it superseded), and it inserts a brand-new version whose xmin is that same updating transaction's id. Until the writer commits, every reader keeps seeing the old version — no read lock is ever taken. That is precisely why readers don't block writers and writers don't block readers.

Airtight trace: one row, a writer, and a concurrent reader

Row id=7, starting balance=100. Version v1 was created by an earlier, already committed transaction id=5 — so v1 begins life as xmin=5, xmax=0 (live). Now Txn A (id=10) updates the balance from 100 to 150, while Txn B (id=11) reads concurrently. Assume B runs at REPEATABLE READ, so B takes its snapshot once at BEGIN and keeps it for the whole transaction (this is what makes the repeated reads below stable — see the isolation caveat after the table). Watch every xmin/xmax value at each step.

Isolation caveat (important): the stable re-reads at t4/t6 assume B is at REPEATABLE READ or SERIALIZABLE, where the snapshot is fixed for the whole transaction. Under PostgreSQL’s default READ COMMITTED, each statement takes a fresh snapshot — so B’s second read at t6 (after A commits at t5) would see v2 = 150, not v1 = 100. The MVCC bookkeeping (xmin/xmax) is identical either way; only which snapshot B compares against changes.

Read the bookkeeping off the table: every xmin/xmax equals the id of the transaction that created or superseded that version. Txn 5 created v1. Txn A (id=10) superseded v1 (so v1.xmax=10) and created v2 (so v2.xmin=10). Txn B (id=11) never appears in any tuple field — because B only reads, it creates and supersedes nothing. Final resting state: v1: xmin=5, xmax=10 and v2: xmin=10, xmax=0.

Why this matters: at t4 and t6, B reads the pre-update balance even though A has already written — and even after A commits — because A was in progress when B's snapshot was taken. No read lock was ever needed. The old version is kept only because some snapshot may still need it, and is reclaimed the moment none can.

Locking still exists — for writes

MVCC solves read/write concurrency. It does not solve write/write concurrency. Two transactions updating the same row cannot both fork a new version off it — that would be a lost update. So the engine takes a row-level exclusive lock: the first updater holds it until commit, and the second updater waits (blocks) until the first commits or rolls back, then re-reads and proceeds. This is the one place a writer blocks — on another writer of the same row.

• Shared (read) lock vs exclusive (write) lock: many transactions may hold a shared lock on a row at once (compatible with each other), but an exclusive lock is incompatible with any other lock. Under MVCC, plain SELECT takes no lock at all — it reads its snapshot — so shared locks matter mainly when you ask for them explicitly.
• SELECT ... FOR UPDATE acquires the row's exclusive lock now, without changing data, so a read-modify-write in application code is serialized against other writers. FOR SHARE takes a shared lock (block writers, allow co-readers).
• The write-write conflict: if A updates row 7 and B tries to update row 7 before A commits, B blocks on A's row lock. When A commits, B wakes, re-reads the now-current version, and applies its change on top.

MVCC alone does not prevent every anomaly

A snapshot read is stale by construction, which opens two classic hazards under the common Read Committed / Repeatable Read levels:

• Lost update: two sessions each read balance=100, each compute 100-10 in application code, each write 90 — one update is silently lost (should be 80). MVCC did not catch it because neither write conflicted at snapshot time. Fix: do the arithmetic in SQL (SET balance = balance - 10, which re-reads under the row lock), or lock first with SELECT ... FOR UPDATE.
• Write skew: two transactions each read an overlapping set, each check an invariant that still holds in their own snapshot, and each write a different row — together violating the invariant (e.g. two doctors both go off-call because each sees the other still on-call). No row conflicts, so row locks and snapshots miss it.

To close these you need either explicit locking (FOR UPDATE) or the SERIALIZABLE isolation level. In PostgreSQL that is SSI (Serializable Snapshot Isolation): it runs on MVCC snapshots (reads still never block) but tracks read/write dependencies and aborts a transaction whose commit would create a non-serializable cycle — so you must wrap transactions in retry-on-serialization-failure logic.

Deadlock: when two writers wait on each other

Row locks introduce the possibility of a cycle: S1 holds row a and wants row b; S2 holds row b and wants row a. Here the DB does what your application can't — it detects the wait-for cycle and aborts one transaction as the victim (you get a deadlock error; retry it). Same cycle as the Dining Philosophers; same fix — a consistent lock ordering.

-- deadlock: two sessions, opposite update order
-- S1: UPDATE a; UPDATE b;     S2: UPDATE b; UPDATE a;
-- Postgres: "ERROR: deadlock detected" -> one is rolled back.
-- Fix: always update rows in a consistent order, e.g. ascending id.

Selection & trade-offs: MVCC vs strict two-phase locking

The alternative to MVCC is strict two-phase locking (S2PL) — the model behind SQL Server's REPEATABLE READ/SERIALIZABLE (its default is lock-based READ COMMITTED with short-duration read locks, not S2PL) and MySQL's SERIALIZABLE — where reads take shared locks and writes take exclusive locks, all held to commit.

• MVCC (optimistic-leaning on reads): readers never block writers and vice-versa, so read-heavy workloads scale beautifully. You pay for it in storage and maintenance: dead versions accumulate and must be reclaimed (VACUUM in Postgres; undo-segment growth in InnoDB/Oracle). It also does not stop lost update / write skew on its own.
• Strict 2PL (pessimistic): serializes by blocking, so it prevents anomalies without extra versions and without retry logic — but reads slow down writers, lock convoys form under contention, and you can deadlock more readily. No version bloat, but far worse read concurrency.

How to decide: choose MVCC (the default in Postgres/InnoDB/Oracle) when reads dominate and you can afford vacuum/undo maintenance — almost all OLTP and reporting mixes. Reach for explicit FOR UPDATE locks or SERIALIZABLE only on the specific transactions whose correctness needs it. Prefer pessimistic S2PL-style locking when write contention is high and constant and you'd rather block than pile up retries. Optimistic (MVCC + retry) wins when conflicts are rare; pessimistic (lock-first) wins when they're common.

Pitfalls a working engineer hits

• Long-running transactions bloat the table. An open transaction (or an idle-in-transaction connection, or a forgotten REPEATABLE READ reporting query) holds a snapshot that keeps old versions "maybe still needed," so VACUUM cannot reclaim them → table and index bloat, slower scans. This is the concrete reason "keep transactions short" is a rule, not a slogan.
• Assuming MVCC prevents lost update. It doesn't. A read-modify-write in application code under Read Committed will happily lose an update. Do arithmetic in SQL, use FOR UPDATE, or go SERIALIZABLE.
• Forgetting FOR UPDATE before a compute-then-write. The window between your SELECT and your UPDATE is exactly where another writer sneaks in. Lock at the read if the write depends on what you read.
• No retry loop under SERIALIZABLE/SSI. SSI aborts conflicting transactions by design; without a retry-on-40001 loop, those aborts surface as user-facing errors.

Recall question

Txn A updates a row and commits. Txn B (running at REPEATABLE READ) started before A committed and is still open. Why does B still see the old version? Answer: B's snapshot captured the list of transactions in progress at B's start, and A was in it. Visibility is decided by snapshot + commit order (specifically that in-progress list), not by wall-clock time. Because REPEATABLE READ freezes that snapshot for the whole transaction, A's new version stays invisible to B until B ends. (At READ COMMITTED, B's next statement would take a fresh snapshot and see the new version.) The old version is reclaimed by VACUUM only once no live snapshot can see it.

Takeaways

• MVCC = versioned rows tagged with xmin/xmax + per-transaction snapshots → readers and writers never block each other. An UPDATE stamps the old version's xmax and inserts a new version with the updater's id as xmin.
• Writes to the same row still serialize via a row-level exclusive lock; the second writer waits. Cross-row lock cycles deadlock, and the DB aborts a victim — retry.
• MVCC does not stop lost update or write skew; use SELECT ... FOR UPDATE or SERIALIZABLE (SSI in Postgres, with retry logic) where correctness needs it.
• The MVCC tax is version bloat: long transactions block VACUUM → keep transactions short.

Scaling the hot counter: the single row that serializes everyone

Above we saw that writers to the same row serialize on that row's exclusive lock — the second updater blocks until the first commits. That is usually fine. It becomes a wall when the row is hot: a global view/like/inventory counter taking 10,000 increments/second. Each UPDATE counters SET n = n + 1 WHERE id = 'views' grabs the row's X-lock, so every one of those 10k writers per second is forced through a single lock, one at a time. Throughput collapses to 1 / (lock-hold time) no matter how many cores or connections you throw at it; the rest pile up waiting. MVCC does not save you here — MVCC only stops readers and writers blocking each other, and this is pure write/write contention on one row.

The insight: the lock is on the row, not on the logical counter. So stop making one row carry all the writes. Three fixes, in increasing order of how much accuracy you trade away.

Fix 1 — sharded (striped) counter: split the row into K sub-rows

Represent one logical counter as K physical rows. A writer increments one of the K chosen by a hash of something it already has (its worker/thread id, connection pid, or a random pick), so writes spread across K independent row-locks. The true value is the SUM across shards, computed only at read time.

CREATE TABLE counter_shard (
  counter_id text   NOT NULL,
  shard      int    NOT NULL,
  value      bigint NOT NULL DEFAULT 0,
  PRIMARY KEY (counter_id, shard)
);
-- seed K=16 shards once
INSERT INTO counter_shard (counter_id, shard, value)
SELECT 'views', g, 0 FROM generate_series(0, 15) AS g;

-- WRITER: hit exactly one of the 16 shard rows.
-- Pick the shard from something the writer already holds (thread/worker id),
-- so contention on any single row drops by ~16x.
UPDATE counter_shard
   SET value = value + 1
 WHERE counter_id = 'views'
   AND shard = (hashtext(pg_backend_pid()::text) % 16 + 16) % 16;  -- non-negative 0..15

-- READER: the logical total is the sum over shards.
SELECT sum(value) AS views FROM counter_shard WHERE counter_id = 'views';

Write contention on any one row falls by roughly K× (16 lock queues instead of 1). The cost moves to the read: a total now scans/sums K rows instead of reading 1. That is the whole trade — you buy write scalability with a K×-more-expensive read. Pick K near your write concurrency (tens, not thousands): too small and you still queue, too large and reads and storage bloat for no benefit.

Append-only variant. Instead of updating K fixed rows, some systems INSERT a delta row per event (never touching an existing row, so no row-lock contention at all) and periodically roll them up:

-- each increment is a brand-new row: zero contention on existing rows
INSERT INTO counter_delta (counter_id, delta) VALUES ('views', 1);
-- read = sum of un-rolled deltas + the rolled-up base
SELECT (SELECT base FROM counter_rollup WHERE counter_id = 'views')
     + COALESCE((SELECT sum(delta) FROM counter_delta WHERE counter_id = 'views'), 0);

This trades the row-lock away entirely for table growth and a background rollup job — the read is expensive until you compact, so it suits write-dominant, read-rare counters.

Fix 2 — write-batching / aggregation: fewer, fatter writes

Do not send one UPDATE +1 per event. Have each app instance accumulate increments in memory for a short window (e.g. 100 ms) and flush a single UPDATE ... SET value = value + :batch. 10k events/s becomes a handful of writes per second per instance, each holding the lock briefly. You give up real-time exactness (a crash loses the un-flushed batch) and add a small delay, in exchange for a massive drop in lock traffic. This composes with sharding.

Fix 3 — approximate counters: drop exactness on purpose

When the answer only needs to be roughly right — "2.4M views," unique-visitor estimates, dashboard cardinality — use a probabilistic structure instead of an exact sum. HyperLogLog estimates the count of distinct items in a few KB with a small, bounded error, and its registers merge associatively, so you can maintain per-shard sketches and union them on read with no coordination. For a plain event count, sampled or probabilistic ("Morris") counters increment only occasionally and scale the reported value. You trade a few percent of accuracy for near-unlimited write throughput and tiny storage.

Selection & trade-off

Rule of thumb: reach for sharding first when you must stay exact and reads can afford the SUM; add batching to cut lock traffic further; drop to approximate only when the business genuinely does not need the exact number.

Work-claiming queues: SELECT ... FOR UPDATE SKIP LOCKED

Now the mirror-image problem. You want a job queue inside the database (a jobs table) with a pool of workers pulling tasks. The naive claim is a transaction that locks the next ready row so no one else takes it:

-- NAIVE: every worker races for the SAME oldest ready row
BEGIN;
SELECT id, payload FROM jobs
 WHERE state = 'ready'
 ORDER BY created_at
 FOR UPDATE          -- lock the row I intend to claim
 LIMIT 1;
-- ... work ...
COMMIT;

This is correct but it does not scale the workers. Every idle worker evaluates the same ORDER BY created_at LIMIT 1 and all of them try to lock the same oldest row. One wins the X-lock; the rest block on it (exactly the hot-row serialization from above). Adding workers adds waiters, not throughput.

SKIP LOCKED changes the semantics of the lock attempt: instead of blocking on a row another transaction already holds, the scan skips past it and returns the next row nobody has locked. So each worker atomically claims a different unlocked job, and N workers do N jobs in parallel with no contention.

-- WORKER: atomically claim one job nobody else holds, and mark it, in one statement.
UPDATE jobs
   SET state = 'processing', locked_at = now()
 WHERE id = (
        SELECT id FROM jobs
         WHERE state = 'ready'
         ORDER BY created_at
         FOR UPDATE SKIP LOCKED    -- skip rows other workers already locked
         LIMIT 1
      )
RETURNING id, payload;            -- returns 0 rows if the queue is empty

The inner SELECT ... FOR UPDATE SKIP LOCKED LIMIT 1 finds and locks the first row no one else holds; the outer UPDATE ... RETURNING stamps it and hands the worker its payload — all atomic, so two workers can never get the same job. When the worker finishes it sets state = 'done' (or deletes the row) and commits, releasing the lock. A crash before commit rolls the lock back and the job becomes claimable again.

NOWAIT is the third option. Plain FOR UPDATE waits for the lock; SKIP LOCKED skips the locked row; FOR UPDATE NOWAIT errors immediately (55P03 lock_not_available) if the row is held. Use NOWAIT when you want a specific row and would rather fail fast and handle it than block — e.g. "grab job 42 or tell me it's busy right now."

Selection & trade-off: DB-as-queue vs the alternatives

• vs plain polling / status-flag CAS: a UPDATE jobs SET state='processing' WHERE id=? AND state='ready' compare-and-swap also claims safely, but every worker still hammers the same rows — wasted updates, thundering herd, and you must scan past rows you failed to grab. SKIP LOCKED lets the database do the "find one nobody has" step for you in a single lock scan.
• vs advisory locks: pg_try_advisory_lock(key) locks an application-chosen integer key, not a row. Great for "only one worker runs job type X" or leader/singleton coordination, but you own the keyspace and must release explicitly (or rely on session end). SKIP LOCKED is better when the unit of exclusion is a queue row.
• vs a real broker (Kafka / SQS / RabbitMQ): the DB-as-queue is genuinely the right call when throughput is low-to-moderate (up to ~thousands/s), and especially when you want to enqueue the job in the same transaction as the business write — the job is committed if and only if the data change is, giving you transactional exactly-once enqueue with zero extra infrastructure and full SQL visibility into the backlog. Reach for a dedicated broker when you need high sustained throughput, fan-out to many consumers, partitioned ordering, long retention/replay, or you simply don't want queue churn competing with OLTP on the same database. Rough guide: Kafka for a high-throughput ordered, replayable log; SQS for managed at-least-once with no ops; RabbitMQ for rich routing. Start with the DB queue; graduate to a broker when one of those pressures shows up.

The unifying idea across both sections: a row lock is your friend for correctness and your enemy for throughput. When many actors want the same row (a hot counter), spread the writes so they stop sharing one lock. When many actors want any row (a work queue), use SKIP LOCKED so each grabs a different lock instead of queueing on the same one.

Reclaiming the space: VACUUM vs VACUUM FULL vs pg_repack

MVCC never overwrites a row — an UPDATE or DELETE leaves the old version as a dead tuple. Understanding what actually frees that space is the follow-up that catches people: plain VACUUM marks dead tuples reusable but does NOT return space to the OS — the file stays the same size; the freed slots are added to the table's free-space map for future inserts. So after deleting 200M rows and running VACUUM, SELECT pg_total_relation_size(...) is unchanged and the disk is still full. That is working as designed, not a bug.

To actually shrink the file you need a rewrite:

• VACUUM FULL rewrites the whole table into a new file and returns space to the OS — but takes an ACCESS EXCLUSIVE lock for the entire rewrite (reads and writes blocked), so it is a maintenance-window tool, not an online one.
• pg_repack does the same compaction online: it builds a shadow copy, keeps it in sync with triggers, and swaps with only a brief lock — at the cost of needing ~2× the table's disk during the operation (same shape as gh-ost for DDL).

Two follow-ups worth pre-empting: (1) a single giant DELETE in one transaction holds the xmin horizon back, so autovacuum can't even mark those tuples dead until it commits — batch the delete (keyset chunks) so the horizon advances and lag stays bounded; (2) transaction-ID wraparound is the emergency case — if autovacuum can't keep up, Postgres will eventually force a protective shutdown, so freeze-age monitoring is not optional at scale.

Selection: routine bloat → let autovacuum do its job (tune autovacuum_vacuum_scale_factor); need the disk back with a window → VACUUM FULL; need it back with no downtime → pg_repack; recurring bloat on a time-series table → partition and DROP/DETACH instead (metadata-only, no reclamation needed).

Synthesized from the PostgreSQL MVCC documentation (heap tuples, xmin/xmax, VACUUM), CMU 15-445 (Andy Pavlo) on MVCC and 2PL, and Designing Data-Intensive Applications (Kleppmann, Ch. 7 — snapshot isolation, lost update, write skew, SSI). Hot-counter sharding and SKIP LOCKED work-queue patterns from the PostgreSQL docs (row-level locking, FOR UPDATE ... SKIP LOCKED / NOWAIT) and standard high-write-throughput practice. Version-chain diagram hand-authored as SVG. See also: Isolation Levels & Anomalies, Transactions & ACID, and (Concurrency) Deadlock. Re-authored/Deepened for this guide.