Module P-1·26 min read

Read-committed vs repeatable-read vs serializable, and what lost updates and write skew look like when they hit real money.

JJS

Written by Jatin Jain Saraf · Senior Software Engineer

Module P-1 — ACID, Transactions, and Isolation Levels

Q: A flash sale oversells: 40 units ship while the `stock` counter drops by 3, briefly going negative. The code reads `stock`, checks it is at least 1, then writes `stock - 1`. Postgres, default isolation level. What happened, and what is the cheapest correct fix?

A lost update: many transactions read the same `stock` and each writes a result computed from that stale read, so all but one decrement is overwritten. A single atomic `UPDATE items SET stock = stock - 1 WHERE id = $1 AND stock >= 1` re-evaluates the predicate under the row lock at write time, and the affected row count says when it sold out. — The module's canonical lost update: a read-modify-write where the value written is computed from a read that is already stale. The atomic `UPDATE` removes the cycle — the row lock is taken and `stock >= 1` re-evaluated at the moment of the write, so a concurrent decrement blocks and then sees the new value. It is correct at `READ COMMITTED` with no retry logic, which is why the module calls it the cheapest correct answer. Option B misreads a lost write as a read anomaly and reaches for a transaction, which gives atomicity and isolation but not mutual exclusion. Option C is the genuine near-miss: Postgres's `REPEATABLE READ` does prevent same-row lost updates, but by raising `40001` and aborting rather than by fixing the arithmetic, so it demands the retry logic the option assumes away. Option D is wrong on the facts — Postgres permits no dirty reads at any level.

Q: A £100 order is refunded £120. Two agents each created a £60 refund within the same second. Refunds are separate rows, and the rule "sum of refunds must not exceed the order total" is checked in application code inside a transaction. The team had already raised the level to `REPEATABLE READ` after an earlier incident. Why did that not help?

Write skew: each transaction sums the refunds in its own snapshot, sees £0, and inserts a *different* row. Snapshot isolation detects conflicts on rows that were written, and there is no row in common, so nothing fires — the invariant spans a set of rows, not one row. It needs `SERIALIZABLE` with retries, or `SELECT … FOR UPDATE` on the parent `orders` row to give both transactions a shared object to contend on. — The module's rule is that snapshot isolation protects invariants living in one row and fails invariants living in a set of rows. Two inserts of different rows produce no write conflict, so first-updater-wins has nothing to fire on and both commit. The fixes named are `SERIALIZABLE`, whose predicate locks cover the read set — exactly what SSI adds over snapshot isolation — and locking the parent `orders` row to manufacture the shared object row locks require. Option A is wrong twice: Postgres's `REPEATABLE READ` does not permit phantoms, and `READ COMMITTED` weakens isolation rather than strengthening it. Option C inverts the facts; no Postgres level permits dirty reads. Option D is the plausible near-miss, since a constraint is the module's preferred tool elsewhere — but `UNIQUE` expresses only index-shaped rules like "at most one of these," and a sum limit is not one. Two £40 refunds would pass straight through it.

Q: A team moves checkout to `SERIALIZABLE` to fix a write-skew bug. The bug is gone, but roughly 2% of checkouts now fail with `could not serialize access due to read/write dependencies among transactions` (`40001`), and a few customers got two confirmation emails. The team proposes reverting. What is the correct read?

The aborts are `SERIALIZABLE`'s designed interface, not a fault: the application must retry the *whole* transaction with backoff and jitter. The duplicate emails are the second symptom of the same gap — side effects inside the transaction repeat on retry, so they belong after commit and behind an idempotency key. Reverting trades a visible, retryable failure for a silent money bug. — The module states this as the first of `SERIALIZABLE`'s four costs: serialisation failures occur in normal operation with no bug present, and the retry loop is the implementation cost of the guarantee. It names the duplicate-email failure directly — side effects inside a transaction happen again on every retry, which is why they move after commit and behind an idempotency key (Module P-16) — and it calls for jitter, since lockstep retries collide and abort each other again. Option A misreads a designed abort as a bug; raising `max_pred_locks_per_transaction` does reduce the *false-positive* aborts caused by tracking escalation, so it is a real tuning lever, but it cannot remove true conflicts or the need to retry. Option B tunes lock waiting, a different failure: SSI aborts at commit on dependency analysis, not after waiting on a lock. Option D is the near-miss that looks like a saving — an aborted transaction can execute nothing further, so the retried statement returns `25P02`.

What this module covers: Transactions are the feature engineers trust most and understand least, and the gap surfaces as a single-use coupon redeemed twice or a refund total exceeding the order. This module covers what each ACID letter guarantees — including the vocabulary collision hiding in the C — the three read anomalies plus lost update and write skew, which isolation levels PostgreSQL actually implements versus what the standard describes, what snapshot isolation does and does not prevent, the real price of SERIALIZABLE, and the four-tool toolkit for a correct concurrent write. It also settles the check-then-insert race Module F-13 left open.

What ACID Promises, and the Letter That Misleads

The characteristic transaction bug passes every test, then appears the day a campaign goes out as a number that shouldn't exist: 1,003 redemptions of a single-use code, stock = -4, two refunds against one payment summing to more than the charge. Each needs two transactions to overlap in a window microseconds wide — a window load buys you thousands of times a second. Only one of ACID's four letters is involved, and it's the only one you have to make a decision about.

Atomicity — all of it or none of it. On abort every change is discarded. Note what it is not: atomicity says nothing about concurrency. It covers the failure path — a crash, a constraint violation, an exception between two UPDATEs. Without it the symptom is money that left one account and never arrived at the other, found by a 3am reconciliation job.

Consistency — your declared constraints hold at commit. The weakest letter, and the source of the worst vocabulary collision in the field.

The collision, stated once so it stops confusing you: the C in ACID means constraint preservation — your NOT NULL, CHECK, FOREIGN KEY, and UNIQUE declarations are true at commit. It is unrelated to the "consistency" of Module P-11 (Consistency Models) or the C in CAP (Module P-10), which concern whether different readers of a replicated system see the same value. A single-node Postgres delivers ACID's C completely and has no distributed consistency to speak of; a globally replicated store can be eventually consistent in P-11's sense while enforcing every constraint you declared. Establish which one someone means before designing anything.

Isolation — concurrent transactions behave as if they ran one at a time. A dial rather than a boolean, and the rest of this module.

Durability — a commit survives a crash, at whichever point on Module F-2's spectrum you chose: page cache, fsynced to device, or acknowledged by a replica in another AZ (Module P-5).

A and D the engine largely handles; C follows from the constraints you bothered to declare (Module F-13). I is where the default is not the safe option.

The Anomalies, and the Two That Take Your Money

Dirty read — reading another transaction's uncommitted write; if it rolls back you acted on a value that never existed. A report reads an in-flight import and publishes totals for rows later discarded.

Non-repeatable read — reading one row twice in a transaction and getting different values. A fee calculation reads account.tier to pick a rate, then reads it again to write the audit line; the customer upgraded in between, so the audit record disagrees with the charge.

Phantom read — re-running the same query and getting a different set of rows. An invoice job counts billable events, then sums them; four arrived in between, so the count and the sum disagree.

Those three are reads going strange. The next two silently discard writes, and they are the ones on the incident review.

Lost update — two transactions each read a value, compute from it, and write back; the second overwrites the first, which is simply gone. The read-modify-write cycle, and the most common concurrency bug in application code:

typescript

const { stock } = await db.one('SELECT stock FROM items WHERE id = $1', [id]);
if (stock < 1) throw new OutOfStock();
// ── Another request runs this whole block in the gap on this line. ──
await db.none('UPDATE items SET stock = $1 WHERE id = $2', [stock - 1, id]);

With 500 concurrent buyers, dozens read the same stock and all write stock - 1 computed from it. You sell 40 units, the counter drops by 3, and the warehouse finds the oversell.

Write skew — two transactions each read a set of rows, each check an invariant spanning that set, each see it satisfied, and each write to a different row. Nothing conflicts, and the invariant breaks anyway. The rule is "total refunded must not exceed the order total," and refunds are separate rows. Two agents each open a £60 refund against a £100 order; each sums the existing refunds (£0), sees £60 ≤ £100, inserts its own row. £120 refunded. The invariant was never "this row must not change" — it was a statement about a set, and set-level statements are what write skew defeats.

Why this matters in production: these two survive the isolation level you are almost certainly running now, while dirty reads are a solved problem nobody meets on Postgres. If a postmortem says "race condition" and the affected number was money, inventory, or entitlement, it was one of these — and the fix differs for each.

What Postgres Implements Is Not What the Standard Describes

Level	Standard permits	Postgres actually permits
`READ UNCOMMITTED`	dirty, non-repeatable, phantom	behaves as `READ COMMITTED` — no dirty reads
`READ COMMITTED` (default)	non-repeatable, phantom	non-repeatable, phantom, lost update, write skew
`REPEATABLE READ`	phantom	no phantoms — snapshot isolation; write skew remains
`SERIALIZABLE`	nothing	nothing, at the cost of serialisation failures

You cannot get a dirty read in Postgres at any level. READ UNCOMMITTED is accepted and silently gives you READ COMMITTED. Not laziness: MVCC (Module F-13) hands a reader a row version committed as of some instant, so no code path exposes an uncommitted one. Tuning down for performance changes nothing.

Postgres's REPEATABLE READ is stronger than the standard requires — one snapshot for the whole transaction, so phantoms can't occur either, putting it between the standard's REPEATABLE READ and its SERIALIZABLE.

That snapshot is the mechanism, and when it's taken is the only difference between the two useful levels. READ COMMITTED takes a fresh one at the start of every statement, which is why two SELECTs can disagree. REPEATABLE READ takes one at the first statement and holds it for the whole transaction — consistent, and increasingly stale.

Analogy: isolation levels are the rules of a shared workshop with one whiteboard of measurements. READ COMMITTED means glancing up at the board each time you need a figure: every glance is current, and two glances may disagree, because colleagues keep rubbing things out. REPEATABLE READ hands you a photograph of the board taken as you walked in — internally consistent all day, quietly out of date by lunch. SERIALIZABLE adds a supervisor who watches everyone's photographs and, when two people have acted on figures the other has since changed, tears up one person's work and sends them back to the door. Nobody's work comes out corrupted; somebody's work gets thrown away, and noticing that is your job.

Why `REPEATABLE READ` Doesn't Save the Balance Check

Snapshot isolation is a large upgrade and an incomplete one.

It fixes non-repeatable reads and phantoms. Every read agrees; your count and your sum cover the same rows.

It fixes lost update on the same row — by aborting you. Postgres's REPEATABLE READ is first-updater-wins: if your UPDATE targets a row another transaction modified and committed since your snapshot, Postgres raises could not serialize access due to concurrent update (SQLSTATE 40001) and your transaction is dead. Correct, and underappreciated — REPEATABLE READ already obliges you to write retry logic. Teams that switch for the consistent reads and skip retries have converted a corruption bug into a user-visible 500: an improvement, but not the one they expected.

It does not fix write skew, for a mechanical reason. Snapshot isolation detects conflicts on rows that were written, and the two refund transactions above wrote different rows, so there is nothing for first-updater-wins to fire on and both commit.

The rule to carry: snapshot isolation protects an invariant living in one row and fails one living in a set of rows. "This balance must not go negative" is a one-row invariant. "These must sum to no more than that" and "at least one of these must stay active" are set invariants, and they need SERIALIZABLE or a lock on something both transactions must pass through.

`SERIALIZABLE` Is Correct, and You Pay in Retries

Postgres's SERIALIZABLE uses Serialisable Snapshot Isolation: the same snapshot, plus tracking of read/write dependencies between concurrent transactions, aborting one at commit when the tracked pattern could only arise from a non-serialisable execution. It tracks reads too, via predicate locks covering the rows and ranges a query examined — which is why it catches write skew.

The guarantee is the strongest available: any set of concurrent transactions that all commit produced a result identical to some serial ordering, so you may reason about your transaction as if it ran alone. The costs, because "just use SERIALIZABLE" is not free:

Serialisation failures are the interface, not an error, and the application MUST retry. 40001 occurs in normal operation with no bug anywhere. Without retries you have a system that returns 500s in proportion to concurrency.
Retries are whole-transaction retries. An aborted transaction can execute nothing further; re-running the failed statement inside it returns 25P02. Any side effect outside the database — an email, a charge, a queue publish — repeats on retry, so side effects belong after commit and behind an idempotency key (Module P-16).
Throughput collapses under contention, because every abort is work discarded: useful throughput falls while CPU stays pinned. Good for a few correctness-critical paths, poor as a default for a hot one.
Predicate tracking degrades. Read dependencies live in bounded memory (max_pred_locks_per_transaction); read too much and tracking escalates from tuple to page to relation granularity, and coarser tracking means false positives — aborts of transactions that were genuinely serialisable.

typescript

async function inSerializable<T>(fn: (tx: Tx) => Promise<T>, attempts = 5): Promise<T> {
  for (let i = 0; i < attempts; i++) {
    try { return await db.tx({ isolation: 'serializable' }, fn); }
    catch (e) {
      // 40001 serialisation failure, 40P01 deadlock: nothing happened, retry the lot.
      if ((e.code !== '40001' && e.code !== '40P01') || i === attempts - 1) throw e;
      await sleep(2 ** i * 10 + Math.random() * 10); // jitter, or the retries re-collide
    }
  }
  throw new Error('unreachable');
}
// Emails, charges, and queue publishes go AFTER this returns — never inside fn.

Why this matters in production: that loop and its jitter are the entire implementation cost of SERIALIZABLE. Skipping the jitter reproduces Module F-12's thundering herd at the transaction layer — aborted transactions retry in lockstep and abort each other again.

Four Tools, Four Different Jobs

Most correctness problems need the right one of these on one code path, not a global isolation change.

Tool	Use when	Cost
Atomic `UPDATE`	The change fits one statement over one row	No application logic between read and write
`SELECT … FOR UPDATE`	You must read, decide in code, then write	Serialises that path until commit; deadlock risk
Version column	Contention is low and conflicts should reach the user	Work discarded on conflict; decorative unless used on every write path
`UNIQUE` / `EXCLUDE`	The invariant is "at most one of these"	Only index-shaped invariants; you handle the error

Atomic UPDATE — the fix for lost update, and the one people skip. Delete the read-modify-write and let the database do the arithmetic under its own row lock:

sql

-- The row lock is taken and stock >= 1 re-evaluated at the moment of the write,
-- so a concurrent decrement cannot be lost and stock cannot cross zero.
UPDATE items SET stock = stock - 1 WHERE id = $1 AND stock >= 1;

Zero rows affected means sold out — an expected outcome, not an exception. Correct at READ COMMITTED with no retry logic, because a concurrent UPDATE on that row blocks until the first commits and then re-evaluates the WHERE clause. The cheapest correct answer available, and unavailable the moment you need application logic between read and write.

SELECT … FOR UPDATE — pessimistic, for decisions you genuinely must make in code: a pricing rule, a validation against another table. It is also the standard write-skew fix when a natural parent row exists — lock the orders row before summing its refunds, and both agents contend on a shared row, which snapshot isolation can see. Costs: that path is now deliberately serialised, its throughput bounded by transaction duration, and inconsistent lock ordering across code paths produces deadlocks. Lock in a deterministic order and set lock_timeout, so a stuck lock fails fast rather than holding a connection into F-13's cascade.

Optimistic locking — no locks, detect and retry. Carry a version column, include it in the WHERE, bump it on write; zero rows affected means someone wrote first and the work in between is discarded. That suits low-contention, human-paced edits — a document, a settings page — where "this changed while you were editing" beats a silent overwrite. Poor for a hot counter, where nearly every attempt loses.

A UNIQUE or EXCLUDE constraint — the only one that cannot be bypassed, because it lives in the index, below the application, and holds against every writer and every migration script (Module F-13). Its limit is expressiveness: "at most one pending cart per customer" fits, "these must sum to no more than that" does not.

Finishing F-13: Why `READ COMMITTED` Doesn't Save Check-Then-Insert

Module F-13 left a promise here: SELECT to check no pending cart exists, then INSERT one, duplicates appear, and wrapping both in a transaction doesn't help. The reason, precisely:

Under READ COMMITTED, T1's SELECT sees a snapshot containing only rows committed before that statement began, so T2's uncommitted INSERT is invisible. And here is the load-bearing detail: a row you cannot see is a row you cannot lock. Row locks, FOR UPDATE included, attach to existing visible tuples. There is nothing to lock on a row that does not yet exist from your point of view, so no conflict exists for the engine to detect. Both transactions correctly observe an empty result, both insert, both commit — each got exactly the isolation level it asked for. Raising to REPEATABLE READ changes nothing, because this is write skew with an empty read set.

Three things work:

A UNIQUE or EXCLUDE constraint. Both inserts pass through the same index, supplying the shared physical structure row locks lacked: the second blocks on the first's index entry, then fails with 23505. Cost: handling that error — ON CONFLICT DO NOTHING, or a mapped 409.
SERIALIZABLE, whose predicate locks cover the absence of matching rows. Cost: the retry loop above.
Locking a row that does exist — SELECT … FROM customers WHERE id = $1 FOR UPDATE before the check. Cost: cart creation for that customer is serialised, and every path must remember to do it.

The ranking is deliberate: the constraint is the only one a code path nobody has written yet cannot forget.

Long Transactions Are a Blast Radius, Not a Style Choice

Locks are held until commit, not until the statement ends. A FOR UPDATE taken in the first millisecond of a transaction that then makes a 900 ms call to a payment provider has locked that row for 900 ms, and everything touching it queues — F-13's cascade, with the insult that the latency is someone else's. Never hold a transaction across a network call you don't control.

Vacuum is blocked database-wide. F-13 established the mechanism; the point here is that the usual culprit isn't a slow query but an idle in transaction connection — a BEGIN with an early return before COMMIT. Set idle_in_transaction_session_timeout and statement_timeout on every application role; the cost is that a genuinely long report needs its own role with different limits, deliberately.

Why this matters in production: duration couples everything — lock hold time, the vacuum horizon, and, at REPEATABLE READ and above, abort probability, since an older snapshot means more concurrent commits to be checked against. It is also the variable that never appears in code review, because the two statements look adjacent on screen and the await between them is invisible.

Where This Shows Up in Your Stack

PostgreSQL: default_transaction_isolation is read committed; set the level per transaction so the cost lands only where needed. pg_stat_activity filtered to state = 'idle in transaction' with xact_start finds long transactions, and pg_locks joined to it shows who waits on whom. Rising xact_rollback after a SERIALIZABLE rollout is retries, not breakage. FOR UPDATE SKIP LOCKED is the queue-consumer pattern Module P-19 builds on.
Node.js: a transaction is a connection, held for the whole surrounding await chain, so an await fetch(...) inside one ties a database connection and its locks to a remote service's p99 (Module P-4). ORM @Transactional-style decorators make the boundary invisible, which is exactly how a transaction ends up wrapping an HTTP call.
Redis: MULTI/EXEC is not a transaction in this module's sense — it batches commands atomically with no rollback and no isolation levels, and WATCH is optimistic locking with the trade-off above. A Redis lock is not a substitute for a database constraint, because it cannot guarantee mutual exclusion across a failover (Module P-12).
MySQL/InnoDB: defaults to REPEATABLE READ and takes next-key (gap) locks on locking reads, blocking some phantom-shaped anomalies Postgres handles by aborting instead. A check-then-insert that "works" on InnoDB because of gap locks can start failing when ported to Postgres — and the constraint would have been correct on both.

Summary

ACID's letters are four separate guarantees: atomicity covers the failure path, durability is a spectrum (Module F-2), consistency is your declared constraints, and isolation is the only one you must actively choose.
ACID's "consistency" means constraint preservation, not the distributed consistency of Module P-11 or CAP. Same word, unrelated concept — disambiguate before designing.
Dirty, non-repeatable, and phantom reads are read anomalies; lost update and write skew cost money, and they are the two that survive the default level.
Postgres permits no dirty reads at any level, and READ UNCOMMITTED is silently READ COMMITTED. Its REPEATABLE READ is full snapshot isolation and prevents phantoms, which the standard does not require.
READ COMMITTED snapshots per statement, REPEATABLE READ per transaction — and the latter stops same-row lost updates by aborting you with 40001, so it needs retry logic too. It protects one-row invariants and fails set invariants, which is why write skew survives it.
SERIALIZABLE/SSI is correct and costs four things: mandatory whole-transaction retries with jitter, side effects moved after commit, throughput loss under contention, and false-positive aborts when predicate tracking escalates.
Match the tool to the invariant: atomic UPDATE … WHERE x >= 1 for counters, FOR UPDATE when you must decide in code, a version column for low-contention human edits, and a UNIQUE/EXCLUDE constraint for "at most one" — the only tool a future code path cannot forget.
Check-then-insert races because an invisible row cannot be locked. Both READ COMMITTED and REPEATABLE READ permit it; a constraint, SERIALIZABLE, or locking an existing parent row fixes it.
Transaction duration couples lock hold time, vacuum horizon, and abort probability. Never hold one across an external call.

The next module, Storage Engines: B-Trees vs LSM-Trees, drops a layer from these guarantees to the machinery that delivers them. Every snapshot here is a row version somewhere on disk, and every commit a write into a structure with its own opinion about whether that write lands sequentially or randomly — which is where Module F-2's sequential-beats-random result gets paid off, and why a write-heavy table resents the B-tree that made these transactions cheap to reason about.

Knowledge Check

A flash sale oversells: 40 units ship while the stock counter drops by 3, briefly going negative. The code reads stock, checks it is at least 1, then writes stock - 1. Postgres, default isolation level. What happened, and what is the cheapest correct fix?

A £100 order is refunded £120. Two agents each created a £60 refund within the same second. Refunds are separate rows, and the rule "sum of refunds must not exceed the order total" is checked in application code inside a transaction. The team had already raised the level to REPEATABLE READ after an earlier incident. Why did that not help?

A team moves checkout to SERIALIZABLE to fix a write-skew bug. The bug is gone, but roughly 2% of checkouts now fail with could not serialize access due to read/write dependencies among transactions (40001), and a few customers got two confirmation emails. The team proposes reverting. What is the correct read?

Test your knowledge with more question sets

Sign in to keep reading

The rest of this module is free — sign in with Google to unlock it and track your progress.

PreviousModule F-16: Capstone: Design a URL Shortener Next Module P-2: Storage Engines: B-Trees vs LSM-Trees

Discussion

Join the discussion

Loading comments...