A transaction is a promise: a bundle of reads and writes that happens all-or-nothing, and that behaves sensibly even while thousands of other transactions run at the same instant. The second half of that promise — isolation — is the deep one, because it is not a single guarantee but a dial, trading correctness for concurrency at levels you must choose on purpose. This chapter walks that dial and the machinery underneath it: locking and, above all, MVCC.
Of the four ACID properties, atomicity and durability are mechanical, delivered by the WAL (Chapter 4). Consistency is mostly the application's job: the database enforces only the invariants you declared as constraints, and every other invariant is protected only as well as your isolation level protects it. That last point is the thread through this chapter.
Isolation is the odd one out: a dial of weaker levels that permit named misbehaviours in exchange for concurrency. Choosing a setting on that dial is arguably the most consequential correctness decision you make against a database. Most teams never make it on purpose; they inherit the engine default and the ORM's habits.
Atomicity is less uniform than the acronym suggests, and the differences leak into application code. When a statement fails mid-transaction, Oracle, MySQL and (for most errors) SQL Server roll back that statement and leave the transaction usable. Postgres marks the whole transaction aborted, and every later command fails until ROLLBACK. Frameworks paper over this by wrapping statements or nested blocks in savepoints, and in Postgres each savepoint that writes is a subtransaction with its own txid. A backend caches up to 64 subtransaction ids in shared memory. Beyond that its snapshot is "suboverflowed", and every visibility check that meets one of its txids has to consult the pg_subtrans SLRU instead. One long transaction that loops over 1,000 rows with a savepoint per row can therefore degrade reads cluster-wide, on the primary and on hot standbys, while its own latency looks fine. The review question is not "do we use transactions" but "how many savepoints does our busiest code path open per transaction, and did we choose that or did the ORM?"
A
Atomicity
all-or-nothing — the whole bundle commits or none of it does
C
Consistency
every commit leaves the DB satisfying its invariants & constraints
I
Isolation
concurrent txns don't corrupt each other — the graded guarantee
D
Durability
once committed, it survives a crash (Chapter 4's job)
The mental frame for the rest of the chapter: isolation is defined by the anomalies it forbids. So we first meet the anomalies, then the ladder of levels that rule them out one by one, then the two mechanisms — locking and MVCC — that actually do the ruling.
The standard's three anomalies (dirty read, non-repeatable read, phantom) are all about reads changing under you, and in an MVCC engine the first is effectively extinct: no mainstream MVCC engine exposes uncommitted versions to other sessions, and Postgres silently upgrades READ UNCOMMITTED to READ COMMITTED. The anomalies that cause incidents are the ones where a read feeds a write. Write skew (3.6) is the general form: two transactions read an overlapping set, each writes based on what it saw, and a shared invariant slips between two legal-looking writes.
T1 reads a value, T2 commits a change, T1 re-reads and sees a different value — the row it already saw is no longer stable. Read Committed allows this; Repeatable Read / Snapshot does not, because it pins T1 to one consistent snapshot for its whole life.
Two anomalies the ANSI list omits cause more production incidents than phantoms do. Lost update: two sessions read stock=10, each computes 10 − 1 in application code and writes 9 — one sale vanishes. It is the default behaviour of every ORM "load entity, mutate, save" path at Read Committed, and it never throws. Read skew: a statement-level snapshot reads account A before a transfer commits and account B after it, so a report sums to a total that never existed. The ANSI definitions were written in lock-based terms, and critiques of them (Berenson et al.'s "A Critique of ANSI SQL Isolation Levels") are why snapshot isolation and these extra anomalies got names at all. Treat each anomaly as a permitted behaviour you are signing up for, not a bug you will be told about.
The expensive property of lost updates is that they are invisible. No error, no log line, no metric: you find them months later when a reconciliation job disagrees with a counter. Put rough numbers on the exposure. If a read-modify-write path holds its read for an illustrative 20 ms before writing, and a given row receives concurrent writes at 5/s, the chance that another write lands inside that window is about 1 − e−0.1 ≈ 10%. That is roughly one silently lost write in ten on the hottest rows, and effectively zero on the long tail, which is exactly why load tests on uniform data never see it. The portable fix is not an isolation level but a compare-and-set: UPDATE … SET qty = qty − 1, or a version column with WHERE id = $1 AND version = $n and a check that one row changed. That works identically on Postgres at Read Committed (the WHERE is re-evaluated against the newest committed version after the wait) and on InnoDB (UPDATE reads the latest row anyway). Code that relies on the level to catch the conflict is correct on one engine and silently wrong on the next.
The standard's four levels read as a ladder, and Read Committed is the default in Postgres, Oracle and SQL Server. Under MVCC it takes a fresh snapshot per statement, which is also why it cannot give you read skew protection across two statements. SQL Server is the outlier worth knowing: its Read Committed is lock-based unless READ_COMMITTED_SNAPSHOT is enabled for the database, so the same application sees readers blocking behind writers on one deployment and not on another, depending on a database-level flag that nobody reads in code review.
Reality is messier than the standard, and the mismatch is where the bugs live. Postgres's Repeatable Read is Snapshot Isolation. Oracle's SERIALIZABLE is also snapshot isolation and permits write skew — the strongest level it offers does not meet the standard's definition of the name. MySQL/InnoDB defaults to Repeatable Read, but only plain SELECTs read the snapshot; UPDATE, DELETE and locking reads operate on the latest committed row and take next-key (gap) locks. So a transaction can run UPDATE … WHERE status='new' and modify rows that its own earlier SELECT with the same predicate never returned. Those rows then become visible to it, in the middle of a supposedly frozen snapshot.
Reading down, each level prevents strictly more — on paper. Two traps the standard's labels hide: lost update (two read-modify-write cycles, second overwrites first) is allowed at Read Committed, and MySQL/InnoDB's Repeatable Read still permits it, because its UPDATE reads the latest committed row rather than the snapshot and never aborts on the conflict. SI's ✓ for phantoms covers reads; a phantom that feeds a write decision is just write skew by another name. Only Serializable closes all five.
The cost side depends on mechanism, not level name. Under locking, a stronger level adds blocking and deadlocks whose cost scales with transaction duration. Under optimistic MVCC it adds aborts whose cost scales with contention. Most engines default to Read Committed. Distributed SQL systems such as CockroachDB chose Serializable as their default instead, and pushed the retry cost onto the client. In both cases, what matters in review is not "which level" but "what does our code do when the database says no" — and for the first engine row below, the database never says no.
The row that bites migrations: code written against Postgres RR relies on a 40001 to stop a lost update; ported to InnoDB RR the same code silently overwrites. Code written against InnoDB's gap locks relies on blocking; ported to Postgres it gets no gap locks at all. Behaviour here is the documented default of each engine — verify against the version you run.
How does a database give a transaction a stable snapshot without freezing everyone else out? The dominant answer is Multi-Version Concurrency Control. Instead of overwriting a row in place, an UPDATE writes a new version of the row and leaves the old one intact. Each version is stamped with the transaction id that created it (xmin) and, once superseded, the id that ended it (xmax). A row is therefore not a single value but a chain of versions stretching back through time.
A reader takes a snapshot (at its first statement, not at BEGIN, in Postgres and InnoDB alike: an idle BEGIN pins nothing until it runs a query) and picks, for each row, the one version whose creator had committed from the snapshot's point of view and whose deleter had not. No locks and no waiting, but not free either. Deciding "had committed" means looking the txid up in the commit log. Postgres caches the answer in hint bits on the tuple, and the first reader to do so dirties the page. The first full scan after a bulk load therefore writes: with checksums or wal_log_hints on, that includes a full-page image in the WAL for each page touched since the last checkpoint. A read-only analytics query that suddenly generates gigabytes of WAL and replica lag is usually this.
v1 · bal=100
xmin 10
xmax 42
v2 · bal=80
xmin 42
xmax 57
v3 · bal=95
xmin 57
xmax —
A reader whose snapshot was taken when the next txid was 50 sees v2 (bal=80): its creator (42) had committed and was not in the snapshot's in-progress list, and its deleter (57) is in the reader's future. It ignores v3 (txid 57 did not exist yet) and v1 (superseded by committed txid 42). The rule is not a simple xmin ≤ 50 < xmax range test — a txid 45 that was still running at snapshot time is invisible even though 45 < 50.
Every row version carries xmin (the txid that created it) and xmax (the txid that deleted/superseded it). Visibility is pure arithmetic against the reader's snapshot — no locks, no blocking. The cost: old versions linger as dead tuples until VACUUM (Postgres) or the undo log / purge (InnoDB) reclaims them.
Where the old versions live decides who pays. Postgres writes each new version into the heap as a full row. Every index gets a new entry too, unless the update is HOT: no indexed column changed and the same page has free space, which is why fillfactor below 100 pays off on update-heavy tables. Readers pay by skipping dead tuples, and VACUUM pays to remove them. InnoDB updates the clustered row in place and pushes the before-image to the undo log. Current reads are cheap, but a reader with an old snapshot rebuilds its version by walking undo records backwards. A long report therefore slows down while it runs, as the history list it must traverse grows. Both designs have the same continuous cleanup obligation as LSM compaction from Chapter 1. They differ in whether the debt shows up as heap bloat or as undo-chain length.
Postgres · HOT update
heap page 812
v1 (dead) → v2 (live)
indexes ×7
untouched — still point at v1, chain hops to v2
Needs: no indexed column changed and free space on the same page. Cost ≈ 1 tuple write; dead v1 pruned on page access, no VACUUM needed.
Postgres · non-HOT
heap page 812 → page 4,031
v1 (dead) … v2 (live, elsewhere)
indexes ×7
+7 new entries, 7 old ones now dead
Cost ≈ 1 tuple + 7 random index-leaf writes, plus later VACUUM of all 8. Amplification scales with index count.
InnoDB · in place + undo
clustered (PK) leaf
v2 overwrites · roll_ptr ↓
undo log
before-image v1 ← v0 ← …
Secondaries change only if their column changed. Old readers pay: they rebuild v1 by walking undo; history list length is the debt gauge.
Illustrative table: primary key plus six secondary indexes. Postgres makes writers pay per index unless the update qualifies as HOT; InnoDB makes old readers pay per intervening version. Neither is free under a pinned horizon: one accumulates dead heap and index tuples, the other an ever-longer undo history that purge cannot trim.
The write-amplification arithmetic is what should drive schema decisions. Take an illustrative table with six secondary indexes and 200-byte rows, updated at 3,000 rows/s. If the updates are HOT, each writes one heap tuple on the same page: roughly 600 KB/s of new tuple data and no index churn. Add a last_seen_at column to one of those six indexes and every update stops being HOT, because an indexed column changed. Now each update writes a heap tuple on whatever page has room plus seven index entries (the primary key and all six secondaries), each of which dirties a random leaf page. The cost did not go up by the size of the new column; it went up by a factor that tracks the index count, and it shows up as checkpoint I/O and WAL volume rather than as query latency. Two cheap signals catch it: the HOT ratio (n_tup_hot_upd / n_tup_upd) per table, and index growth rate against heap growth rate. Indexing a frequently updated column is a decision about MVCC, not just about reads; the Indexing chapter covers the read side.
The visibility map is the other hidden coupling. Postgres can answer an index-only scan without touching the heap only for pages marked all-visible, and only VACUUM sets that bit. A table that vacuum cannot keep up with silently converts its index-only scans into index scans with a heap fetch per row. The plan does not change; only the Heap Fetches counter in EXPLAIN ANALYZE does. That is why a pinned horizon (below) degrades reads in a way that outlives the write burst: every page touched since the horizon froze stays not-all-visible, so index-only scans over recently written data quietly turn into heap scans.
The failure mode that matters in production is not "vacuum is slow". It is the horizon. Cleanup may only remove a version that no live snapshot could still need. That line is set by the oldest snapshot in the whole cluster, not the oldest one on this table. A snapshot is not a single cutoff; it is a triple: xmin, xmax and the list of txids in progress when it was taken. The xmin of the oldest one, from any session, replication slot or standby with feedback enabled, freezes cleanup for every table in the database.
The cascade is slow and quiet, which is what makes it dangerous. Nothing errors. Latency drifts for hours, and the table is permanently larger afterwards.
Back-of-envelope: dead versions ≈ update rate × horizon age. At an illustrative ~150 bytes per version plus index entries, 2,000 updates/s for 6 hours is ~43M versions and several GB of heap alone. The fix is policy, not tuning: idle_in_transaction_session_timeout, a statement timeout for ad-hoc users, and alerting on the age of the oldest xmin — not on table size, which lags the cause by hours.
The same pinned horizon also blocks freezing, Postgres's defence against 32-bit txid wraparound. With roughly 2.1 billion txids of usable distance, the arithmetic is simple. At an illustrative 5,000 write transactions/s, the default autovacuum_freeze_max_age of 200M is reached in about 11 hours, triggering aggressive anti-wraparound vacuums. The hard limit is roughly 4.9 days away if freezing cannot advance, and at that point the server stops assigning txids and refuses writes. Batching many single-row writes into one transaction reduces txid burn; read-only transactions do not consume a txid at all. Alert on age(datfrozenxid) and on the oldest snapshot age, with thresholds set in hours of headroom at your write rate rather than a fixed count.
The slogan that readers never block writers is true for row data and false for everything else, and the "everything else" is where MVCC systems have their worst locking outages. Row versions make plain reads lock-free. They do nothing for schema. Every Postgres query takes a lightweight table-level lock (ACCESS SHARE) for its duration, and most ALTER TABLE forms need ACCESS EXCLUSIVE, which conflicts with it. The lock manager queues requests roughly in arrival order, so a waiting exclusive request blocks every later request that conflicts with it, including plain SELECTs that would have been compatible with the long reader actually holding the lock.
Locking
MVCC
block each other (shared/exclusive locks)
never block — reader sees a snapshot
acquire & release locks
version visibility check
lock table in memory
dead row versions on disk
locks released at commit
VACUUM / purge old versions
every conflicting read and write
writer vs writer, and all DDL / table locks
SQL Server RC without RCSI, DB2
Postgres, InnoDB, Oracle, SQL Server RCSI
MVCC moves the cost of isolation from a lock table in memory to version storage and cleanup on disk. The read path for row data becomes lock-free; table-level locks, writer-writer row locks and deadlocks remain, and they are where MVCC systems still fail.
200 connections ÷ 500 queries/s ≈ 0.4 s from the ALTER queuing to a fully parked pool. lock_timeout on the DDL session turns this into a failed migration step that retries, instead of a one-minute outage with an idle database.
The cascade in numbers, with illustrative figures. A 60-second analytics query holds ACCESS SHARE on orders. A migration issues ALTER TABLE orders ADD COLUMN …, a change that finishes in milliseconds once it has the lock, and queues. The table serves 500 queries/s from a pool of 200 connections, so every connection is parked behind the ALTER in about 0.4 s. For the remaining ~55 s the application sees what looks like a total database outage, while the database itself is idle: CPU low, no slow queries in the log, one "harmless" DDL statement waiting. The fix is mechanical and should be enforced by the migration framework, not remembered: SET lock_timeout = '2s' on every DDL session, retry with backoff, and a statement timeout on reporting roles so no reader can hold a table-level lock for minutes. The same shape recurs with TRUNCATE, REINDEX without CONCURRENTLY, and (in InnoDB) the metadata lock that online DDL still needs at its start and end.
Row locks in an MVCC engine also cost more than people expect, because in Postgres they are not kept in a memory table at all: SELECT … FOR UPDATE writes the locker's txid into the tuple header, dirtying the page and generating WAL. Locking 100k rows to "be safe" is 100k page modifications. When more than one transaction holds a share lock on the same row, the header points to a MultiXact, a separately stored member list with its own SLRU caches and its own wraparound horizon. Foreign keys create these implicitly: every insert into a child table takes FOR KEY SHARE on the parent row, so a hot parent (one tenant, one merchant) with hundreds of concurrent child inserts per second turns into MultiXact churn and SLRU contention that no query plan will show. Two cheap mitigations: plain UPDATEs that do not touch key columns take FOR NO KEY UPDATE, which does not conflict with those key-share locks, so do not "upgrade" them to FOR UPDATE by habit; and question whether a foreign key on a very hot parent row is buying integrity you cannot get more cheaply by other means.
Deadlocks survive MVCC too, because writers still lock each other, and the engines differ in how fast they notice. Postgres waits deadlock_timeout (1 s by default) before running detection, so a deadlock costs at least a second of held locks and everything queued behind them. InnoDB detects eagerly, which at very high concurrency on one hot row can itself become a CPU problem; that is why it lets you turn detection off and rely on innodb_lock_wait_timeout (50 s by default, far too long for OLTP). The robust prevention is ordering, not tuning: acquire row locks in a canonical order (sort by primary key before a multi-row FOR UPDATE), and for work queues use FOR UPDATE SKIP LOCKED so consumers never wait on each other at all.
Snapshot Isolation (SI) — what most MVCC databases give you at their "repeatable read" level — is powerful: each transaction sees a frozen, consistent snapshot, and it prevents dirty reads, non-repeatable reads, and (in Postgres) phantoms. It feels serializable. But it has one famous hole: write skew. Two transactions read an overlapping set of rows, each makes a decision based on what it read, and each writes to a different row — so there is no write-write conflict for SI to catch — yet together they violate an invariant that each individually preserved.
The classic example is the on-call doctors: an invariant says at least one doctor must remain on-call, two are currently on, and two transactions concurrently each take one doctor off-call. Each reads "two on-call, safe to leave," each writes to a different doctor's row, both commit under SI — and now zero doctors are on-call. Snapshot isolation is blind to this because the conflict is a read-write dependency, not a write-write one.
Invariant: at least ONE doctor must stay on-call. Currently Alice & Bob are both on-call.
T1 · Alice goes off-call
T2 · Bob goes off-call
Result: zero doctors on-call. Each transaction read a valid snapshot and made a legal write to a different row — no write-write conflict — so Snapshot Isolation permits it. Only Serializable detects the read-write dependency cycle and aborts one.
There are three ways to close the gap, and each fails differently. Serializable Snapshot Isolation (Postgres's SERIALIZABLE) runs like SI but records what each transaction read as SIRead predicate locks. It aborts one transaction when it sees two consecutive read-write dependencies that could form a cycle. It is conservative, so some aborts are false positives. Tracking granularity is set by your access paths. An indexed lookup records tuples or pages; a sequential scan records the whole relation, so any concurrent write to that table becomes a potential conflict. When predicate-lock memory runs out, locks are promoted from tuple to page to relation, and the abort rate rises with load. Under SSI a missing index costs you aborts, not just latency. For long read-only work, SERIALIZABLE READ ONLY DEFERRABLE waits for a safe snapshot and then can never be aborted.
SELECT … FOR UPDATE is the targeted fix, and it works for the doctors because the rows it depends on exist. It fails silently for the other half of write skew: invariants about rows that are absent. "No booking overlaps 14:00–15:00 for room 7, so insert mine" locks nothing, because the conflicting row has not been written yet. For that shape you need one of three things. You can materialize the conflict (lock a room or a slot row that always exists), push the invariant into a constraint (a unique index, or Postgres's EXCLUDE USING gist over a time range), or use SERIALIZABLE. Constraints are the most robust option because they hold regardless of which code path or isolation level wrote the row.
Every optimistic option turns correctness into a retry budget. If an attempt aborts with probability p, expected attempts are 1 / (1 − p): 1.1 at p = 0.1 and 2 at p = 0.5. The model understates the danger because p is not constant. Retries lengthen the window in which transactions overlap, which raises p, a positive feedback loop that turns a hot row into an abort storm. The operational rules follow from that. Retry the entire transaction, including its reads. Keep external side effects (HTTP calls, emails, queue publishes) outside the retry loop. Cap attempts with jittered backoff, and expose abort rate per transaction type as a metric. An abort rate that stays above a few percent on one code path is a schema-design signal, usually a single counter or status row that everything contends on, not a database tuning problem.
The feedback loop can be made quantitative with a deliberately crude model. Let p₀ be the abort probability with no retries in flight, and assume conflicts scale with the number of concurrent attempts, so the effective rate is p = p₀ × A, where A = 1/(1 − p) is expected attempts. Solving gives p = (1 − √(1 − 4p₀)) / 2, which has a real solution only while p₀ ≤ 0.25. At p₀ = 0.1 the loop costs you 1.13 attempts instead of the naive 1.11, so it is barely visible. At p₀ = 0.2 it is 1.38 instead of 1.25. At 0.25 it is 2.0 instead of 1.33, and just past that there is no steady state at all: the system does not get slower, it stops converging. The exact threshold is an artefact of the model; the shape is not. Optimistic concurrency has a cliff, not a slope, and the cliff sits at a first-attempt abort rate that looks entirely survivable on a dashboard the day before.
The naive curve says retries are cheap all the way up. Once retries themselves raise contention, the curve bends upward and ends. Real systems degrade less cleanly, but the qualitative lesson holds: watch first-attempt abort rate per transaction type and treat double digits as an alarm, not a tuning target.
Two design consequences follow. First, the retry cap is a correctness tool, not a politeness: uncapped immediate retries are what push p over the edge, and an attempt cap with jittered, growing backoff keeps offered concurrency bounded when p rises. Second, SSI's false positives count toward p just like real conflicts, so the access-path advice above (index the predicates serializable transactions read, keep them short, mark read-only work as read-only) directly moves you away from the cliff. A serializable workload that is healthy at 20% of peak and melts at 80% has usually not hit a lock; it has crossed this threshold on one transaction type.
The working method falls out of the ladder. Identify the invariants your application depends on across concurrent access. For each, ask which anomaly could break it: is stale data merely ugly (tolerate Read Committed) or actually incorrect? Do you re-read values and expect stability (need Repeatable Read/SI)? Do you make decisions on a read that another transaction could invalidate via a different row (write skew — need Serializable or explicit locks)? Then pick the weakest level that survives, because every step up the ladder costs concurrency and adds retries.
Some rules of thumb, as starting positions rather than laws. Keep Read Committed, and write read-modify-write paths as single atomic statements or behind FOR UPDATE, when conflicting updates are rare and each invariant touches one row. Move to SI or Serializable with a retry loop when invariants span rows and contention is low: a measured abort rate in the low single-digit percent is cheap insurance. When one row or range is hot, favour explicit locks or a constraint. Optimistic schemes degrade non-linearly there, while a queue on a lock degrades predictably. Serializing a hot key costs one lock-hold time per transaction, so a 5 ms hold caps that key at about 200 transactions/s whichever isolation level you pick. At that point the fix is to split the key (sharded counters, per-slot rows), not to change the level.
Thresholds are starting points derived from the arithmetic in this chapter (lock-hold ceiling, retry cliff, horizon growth), not vendor limits. Measure them on your own workload before relying on them.
The matrix is organised by invariant shape rather than by isolation level on purpose. The same service usually needs three or four of these rows at once, and the mistake is choosing one global level and expecting it to cover all of them. Isolation level is a per-transaction setting in every engine discussed here. The pragmatic pattern is Read Committed as the connection default, with SERIALIZABLE (or explicit locks) opted into by the handful of transaction types that carry multi-row invariants, each with its own retry wrapper and its own abort-rate metric. That keeps the retry cost where the correctness requirement is, instead of spreading false-positive aborts across every request.
Where the conventional advice is wrong
Questions to ask in a design review
MVCC's dead-version cleanup mirrors LSM compaction (Ch. 1), its version stamps ride the WAL that provides durability (Ch. 4), and its snapshots are what read replicas serve (Ch. 6).
Oldest xmin
Sets cleanup for all
p₀ ≈ 0.25
Retry cliff (crude model)
64
PG cached subxids / backend
lock_timeout
On every DDL session
Where next