Transactions and Isolation Levels: Graded Ways to Keep Concurrency from Fighting

· tech

#sql#concept

📑 Contents

Everything so far has been “how one query runs fast” (indexes, EXPLAIN); this post switches dimension: many transactions running at once — how do they avoid fighting each other? That’s transactions and isolation levels. (This covers single-node PostgreSQL in practice; distributed transactions and consistency are left to the sister DDIA series to go deeper, so the two don’t repeat.)

ACID: the point is really the I (isolation)

The four letters of a transaction’s ACID: Atomicity (all or nothing), Consistency (constraints never broken), Isolation (concurrent transactions don’t interfere), Durability (once committed, never lost). Three of them are fairly intuitive; the genuinely interesting one, and the one that goes wrong most often, is Isolation — because “perfect isolation” is expensive, so it’s cut into several grades for you to choose from.

The three anomalies concurrency throws up

Two transactions running at once, with insufficient isolation, produce three classic read anomalies. A concrete scene makes them vivid — you (T1) are checking an account balance while someone else (T2) is operating on it at the same time:

① Dirty Read T1 T2 balance → 200 (uncommitted) reads balance 200 ❌ ROLLBACK ② Non-repeatable Read T1 T2 reads balance 100 balance → 200, commits reads balance 200 again ❌ ③ Phantom Read T1 T2 finds 3 orders inserts 1 matching order, commits now finds 4 orders ❌ time →
Dirty read: reading a value someone hasn't committed yet (and may still roll back); non-repeatable read: reading the same row twice and someone changed its value in between; phantom read: running the same query twice and someone's insert changed the row count. Increasing in severity

Put the three into the scene and they’re obvious:

  • Dirty read: T2 changes the balance to 200 but hasn’t committed, and you already read 200 — then T2’s transaction fails and ROLLBACKs, so that 200 never truly existed. Yet you’ve already made a decision on a “ghost number” (say, “balance is sufficient, approve the withdrawal”).
  • Non-repeatable read: you check the balance twice within the same transaction, 100 the first time and 200 the second (T2 committed a transfer in between). Your logic assumed “within one transaction, the same row’s value won’t change”, and now reconciliation and totals are all off.
  • Phantom read: you count “all of today’s orders” and get 3, T2 inserts a new order and commits, you query again and get 4. The difference from a non-repeatable read is crucial: a non-repeatable read is “an existing row’s value changed”, a phantom is “a whole row appeared or vanished out of nowhere” — one concerns UPDATE, the other INSERT/DELETE, so the means of preventing them differ too (to block phantoms you lock a “range”, not just “those rows”).

Four isolation levels: a “safety vs performance” spectrum

An isolation level is a grade of “which of the anomalies above am I willing to tolerate, in exchange for how much concurrency”. Stricter is safer, but with less concurrency:

isolation level dirty read non-repeatable phantom Read Uncommitted possiblepossiblepossible Read Committed (PG default) preventedpossiblepossible Repeatable Read preventedpreventedprevented* Serializable preventedpreventedprevented ↓ further down is safer but less concurrent. * PG's Repeatable Read blocks phantoms too via MVCC (the standard only requires the first two)
Four levels from loose to strict. PostgreSQL's lowest is Read Committed (it doesn't offer Read Uncommitted), which is also the default; most OLTP is fine with it, and only where consistency truly matters (transfers, stock deduction) do you step up to Repeatable Read or Serializable

MVCC: how PostgreSQL makes “reads not block writes”

You might ask: to prevent these anomalies, don’t you just lock and make everyone queue? Wouldn’t concurrency then be terrible? PostgreSQL’s answer is MVCC (multi-version concurrency control), in one sentence: the same row can exist in several versions at once, and each transaction, when it reads, sees the version its “snapshot” should see.

Concretely? Every row hides two system columns: xmin (which transaction created this version) and xmax (which transaction invalidated it). So:

  • UPDATE doesn’t modify in place; it adds a new version (a new xmin) and marks the old version with xmax (meaning “invalid from this transaction on”).
  • DELETE doesn’t really delete either; it just marks the version with xmax.
  • A read takes the transaction’s own snapshot, compares each version’s xmin / xmax, and picks the version “visible to me”.
T2 commits v1 · balance 100 superseded by T2 (xmax set) v2 · balance 200 created by T2, still valid reader A snapshot: before T2 commits → sees 100 reader B snapshot: after T2 commits → sees 200 UPDATE = add v2 + mark v1 invalid; each reader picks a version by its own snapshot → reads don't block writes
Two versions of the same row coexist, and readers each see the version their snapshot's moment should see. A "read" can always get a consistent old version without waiting for a "write" to release its lock — that's reads don't block writes, writes don't block reads

Isolation levels are really “when you take the snapshot”

MVCC makes the isolation-level table above easy to understand — the difference is just how often you take a fresh snapshot:

  • Read Committed (PG default): every SQL statement takes a new snapshot. So you read the latest “committed” data, but two statements in the same transaction may see different things → hence non-repeatable reads.
  • Repeatable Read: the whole transaction takes one snapshot at the start and uses it throughout. So reading the same row any number of times gives the same value (blocking non-repeatable reads), and even “the range of rows matching a condition” is frozen at that moment (PG blocks phantoms as a bonus).

In one sentence: Read Committed sees “the world right now”; Repeatable Read sees “the world at the moment the transaction began”.

Reads don’t block writes, but “writes still block writes”

Note that MVCC solves the “read vs write” conflict; it isn’t a cure-all. Two transactions changing the same row at the same time still block each other — the later one has to wait for the earlier to commit or roll back (and under Repeatable Read / Serializable it may be judged a conflict outright and aborted, asking you to retry). So “hot rows” (everyone fighting to change the same row, like a site-wide counter or flash-sale stock) remain a performance and conflict pain point under MVCC, and need other moves (splitting, queues, optimistic-lock retries) to resolve.

The price: old versions pile up, and VACUUM has to clear them

Multi-versioning isn’t free. Invalidated old versions (dead tuples) don’t vanish immediately; they stay in the table taking space until PostgreSQL’s VACUUM (usually the background autovacuum) reclaims them. If updates are heavy and VACUUM can’t keep up, the table bloats and scans slow down. That’s MVCC’s hidden bill — it buys high concurrency with “keep multiple versions”, and the price is making sure VACUUM keeps pace with writes. This “don’t overwrite, keep versions” way of thinking is in the same family as the append-only storage chapter of DDIA and SCD Type 2‘s “add a version instead of overwriting”.

Deadlock: circular waiting

Concurrency has one more classic nuisance: deadlock. T1 locks A and wants B next; T2 locks B and wants A next — each waits for the other to let go, and neither can move. The database detects the cycle and kills one of the transactions (returning a deadlock error) so the other can proceed. The key avoidance move is plain: have every transaction acquire locks in a fixed order (always lock the smaller id first, say), and the cycle can’t form.

Reflections

The isolation level is a spectrum you have to “choose yourself”

My instinct used to be “obviously pick the safest, Serializable”; only later did I understand that’s wrong — the safest is also the slowest, and under high concurrency a pile of transactions gets forced into serial execution, blocking each other. The right mindset: know which anomalies each level “lets through”, then upgrade only where things would truly go wrong (transfers, stock deduction, ticket grabbing), and use the default Read Committed elsewhere. It’s the same thinking as SRE’s error budget: not pursuing the extreme, but choosing a trade-off point worthy of the scenario. Cranking it to max blindly and ignoring it blindly are both failures to think.

MVCC turns “conflict” from “mutual exclusion now” into “version management”

MVCC’s “reads don’t block writes” looks counter-intuitive at first and beautiful once understood: it doesn’t resolve conflicts by “everyone queuing for one copy of the data”, but by keeping multiple versions and letting each transaction see its own snapshot. Conflict thereby changes from “do-or-die mutual exclusion in the moment” into “orderly version management”. That shift runs deep — much modern concurrency design (including the distributed consistency later in DDIA) is, at heart, a variation on this “multi-version + snapshot” theme. Swapping mutual exclusion for versions is one of the highest-return moves in concurrency design.

You don’t dodge deadlocks by luck, you dodge them with “fixed order”

The deadlock fix taught me a more general truth: many seemingly random, hard-to-reproduce concurrency bugs come down to a missing consistent order. Two transactions grabbing the same few locks in different orders will collide into a cycle sooner or later; the moment the whole system agrees to “always lock in the same order”, the cycle becomes mathematically impossible. It’s the same thing as my takeaway from the processing-order post, and even as a team needing one agreed ruler — a consistent order or standard is often the least effortful way of turning chaos into control. True of concurrency, true of collaboration.