7.4.1 — ACID and Isolation Levels

Two support agents open the same £40 refund at the same moment. Both see "not yet refunded". Both click Refund. The customer receives £80.

Neither agent did anything wrong, and neither did the code — each request read the row, saw a valid state, and wrote. The gap is between them, and closing it is what transaction isolation is for.

1. ACID, with the letter everyone misreads

Atomicity — all or nothing. A transaction's changes either all apply or none do. If the process dies between the debit and the credit, the debit is undone. Chapter 7.3.3's undo log is the mechanism.

Consistency — the database moves from one valid state to another. This is the odd letter, and it is worth being precise: consistency is mostly your job, not the database's. The engine enforces the constraints you declared — foreign keys, CHECK, uniqueness — and nothing more. If your business rule is "an account balance may not go negative" and you did not write it as a constraint, no isolation level will enforce it. The C in ACID is the weakest letter and the one people lean on hardest. (It is also unrelated to the C in CAP, which is about replicas agreeing — Chapter 10.7.1 separates them.)

Isolation — concurrent transactions do not corrupt each other. This is the letter with a dial on it, and the rest of this page is about that dial.

Durability — once committed, it survives a crash. Chapter 7.3.3 is the whole story.

2. The anomalies, each with the code that produces it

Isolation levels are defined by which of these they permit. Learn the anomalies and the levels become obvious rather than memorised.

Dirty read — reading data another transaction has written but not committed.

T1: UPDATE accounts SET balance = 0 WHERE id = 7;   -- not committed
T2: SELECT balance FROM accounts WHERE id = 7;      -- reads 0
T1: ROLLBACK;                                       -- that 0 never existed

T2 acted on a fact that was never true. Essentially no production engine allows this by default.

Non-repeatable read — reading the same row twice in one transaction and getting different values, because someone committed a change in between. A report that reads a total, does some work, re-reads it and gets a different number is internally inconsistent.

*Phantom read — re-running the same query and getting different rows, because someone inserted a row matching your condition. SELECT COUNT(*) FROM bookings WHERE room = 4 returns 3, then 4. The difference from a non-repeatable read is that no row you saw changed — a new one appeared.

Lost update — two transactions read the same value, both modify it, and the second write silently erases the first. This is the opening story.

T1: SELECT stock FROM products WHERE sku='A';   -- 10
T2: SELECT stock FROM products WHERE sku='A';   -- 10
T1: UPDATE products SET stock = 9  WHERE sku='A';
T2: UPDATE products SET stock = 9  WHERE sku='A';   -- should be 8

Two units sold, one unit deducted.

Read skew — reading two related rows at different points in time, so together they describe a state that never existed. Read account A (£500), someone transfers £100 from A to B, read account B (£600). Your total is £1,100 when it was £1,000 throughout.

Write skew — the one the classic table omits, and the one that bites hardest. Two transactions each read something, each check a rule, each write to a different row, and the rule is broken by the combination.

Write skew: each transaction checks a rule, each writes a different row, and the rule is broken only by the pair. No lock on a single row can prevent it.

Notice what makes write skew different. In a lost update, both transactions write the same row, so any row-level protection catches it. In write skew they write different rows, so nothing at the row level notices. It needs either a real serializable level or an explicit lock on whatever the rule is about.

3. The four standard levels

The SQL standard defines four levels by which anomalies they permit.

Level	Dirty read	Non-repeatable read	Phantom
Read uncommitted	Possible	Possible	Possible
Read committed	No	Possible	Possible
Repeatable read	No	No	Possible
Serializable	No	No	No

The table is famously incomplete. It was written around lock-based implementations and does not mention lost update, read skew or write skew at all — which is why a system can be at "repeatable read" and still produce wrong answers. Berenson et al.'s 1995 paper A Critique of ANSI SQL Isolation Levels made this point and it has held ever since.

What engines actually do:

Engine	Default	Notes
PostgreSQL	Read committed	"Repeatable read" is snapshot isolation and blocks phantoms; "serializable" is true serializability via SSI
MySQL / InnoDB	Repeatable read	Uses gap locks, so phantoms are blocked too
Oracle	Read committed	Serializable is really snapshot isolation
SQL Server	Read committed (locking)	Optional read-committed snapshot mode

Read committed — every statement sees a fresh snapshot of committed data. Two SELECTs in one transaction can see different data. This is the default nearly everywhere because it is cheap and rarely surprising for short transactions.

Repeatable read / snapshot isolation — the whole transaction sees one consistent snapshot taken at its start. Read skew disappears; a report is internally consistent by construction. In PostgreSQL this also eliminates phantoms, exceeding the standard's requirement, because the snapshot simply does not contain rows committed later.

Serializable — the result is guaranteed to match some serial order of the transactions. This is the only level that prevents write skew.

4. How serializable is actually implemented

Two very different mechanisms, and the difference decides how it behaves under load.

Two-phase locking (2PL), the classic approach used by SQL Server's serializable and by Oracle-style lock managers: acquire locks as you go, release them all at commit. Readers block writers and writers block readers. Correct, and it converts contention into waiting and deadlocks.

Serializable snapshot isolation (SSI), PostgreSQL's approach since 9.1: run optimistically on a snapshot, track the read-write dependencies between transactions, and abort a transaction if the pattern that produces an anomaly is detected. Readers never block. The price is that a transaction can fail at commit time with a serialization error.

ERROR: could not serialize access due to read/write dependencies among transactions

That is not a bug — it is the mechanism working, and it means using serializable requires a retry loop:

async function withRetry<T>(fn: () => Promise<T>, attempts = 5): Promise<T> {
  for (let i = 0; i < attempts; i++) {
    try {
      return await fn();                                   // (1)
    } catch (e: any) {
      if (e.code !== '40001' && e.code !== '40P01') throw e; // (2)
      await sleep(2 ** i * 10 + Math.random() * 10);        // (3)
    }
  }
  throw new Error('too many serialization failures');
}

(1) The whole transaction runs inside, because a retry must redo the reads as well as the writes — retrying only the write would use stale values and reintroduce the anomaly. (2) 40001 is serialization failure, 40P01 is deadlock; both mean this transaction did not happen, try again. Any other error is a real failure and must propagate. (3) Exponential backoff with jitter, because retrying immediately makes contention worse — the same reasoning as Chapter 10.9.

The retry loop must be at the transaction boundary, and this is the detail that decides whether serializable is usable. If your framework opens transactions somewhere you cannot wrap, you cannot safely use this level.

5. Choosing a level, and what to do at read committed

The practical position most systems land on:

• Read committed for most work. Short transactions, single-row updates, and any lost update handled explicitly by the techniques below.
• Repeatable read for reports and multi-table reads that must be internally consistent — end-of-day balances, exports, anything where read skew produces a visibly wrong document.
• Serializable for the small number of operations with a real cross-row rule, with a retry loop. Booking the last seat, an on-call rota, an overdraft limit across accounts.

At read committed, you must handle lost update yourself. Four ways, in increasing order of strength:

1. Let the database do the arithmetic. The best fix, and it removes the read entirely.

UPDATE products SET stock = stock - 1 WHERE sku = 'A' AND stock >= 1;

The row is locked for the duration of the statement, the read and write are one operation, and rowCount = 0 tells you it failed because stock was insufficient. Whenever the new value is a function of the old one, write it as one statement.

2. Optimistic concurrency with a version column.

UPDATE orders SET status = 'shipped', version = version + 1
WHERE id = 42 AND version = 7;

Zero rows affected means someone else updated it first; re-read and decide. This is the same mechanism as HTTP's If-Match and ETags (Chapter 5.6.2), and it is right when conflicts are rare.

3. Pessimistic locking with SELECT … FOR UPDATE.

BEGIN;
SELECT stock FROM products WHERE sku = 'A' FOR UPDATE;   -- (1)
-- application logic here
UPDATE products SET stock = $1 WHERE sku = 'A';
COMMIT;                                                   -- (2)

(1) The row is locked for writing until this transaction ends; any other FOR UPDATE on it waits. (2) The lock is released here — so the lock is held for as long as the transaction, including any application logic, network call or user think time inside it. That is the danger, and Chapter 7.4.2 covers the variants (NOWAIT, SKIP LOCKED) and the deadlocks this creates.

4. Serializable, when the rule spans rows and none of the above can express it.

The rule of thumb worth carrying: if the invariant is about one row, a conditional UPDATE or a version column is enough. If it is about a set of rows — "at least one doctor on call", "no overlapping bookings", "total under the credit limit" — you need serializable, or an explicit lock on something that represents the set.

That last option is often the cheapest in practice: lock the parent row, or take a named advisory lock on the rota id, and the set becomes a single point of contention you control.

6. Long transactions are expensive in a way that is not obvious

A transaction that stays open for minutes is not merely slow. Under MVCC (Chapter 7.4.2) it holds back cleanup: no row version that its snapshot might need can be vacuumed, anywhere in the database. One idle transaction left open by a debugging session can bloat unrelated tables for hours.

Three rules follow:

Never hold a transaction open across a network call. Calling a payment provider inside a transaction ties the database's cleanup to a third party's response time.

Never hold one open for user interaction. "Open a transaction when the form loads, commit when they submit" is a design that fails on the first coffee break.

Set idle_in_transaction_session_timeout. It kills sessions that opened a transaction and went quiet. Without it, one forgotten BEGIN in a psql window degrades the whole database.

What the interviewer will push on

"What does ACID actually mean?" Give the four, and then be precise about C: consistency means the database enforces the constraints you declared and nothing else, so most business invariants are your job. That precision is what separates a real answer from a recitation, and it leads naturally into why isolation matters.

"Explain the isolation levels." The four levels by which anomalies they allow, then immediately note that the standard table is incomplete — it never mentions lost update or write skew — and that PostgreSQL's repeatable read is snapshot isolation, which blocks phantoms the standard permits. Knowing the map is not the territory is the point of the question.

"What is write skew and why is it different?" Two transactions read the same set, each check a rule, each write a different row, and only the pair breaks the rule. It is different because no row-level lock or version column can see it — both wrote different rows. It needs serializable, or an explicit lock on something representing the set.

"How would you prevent overselling the last item?" Best answer first: a single conditional UPDATE … WHERE stock >= 1, checking rows affected — atomic, no read, no lock held over application logic. Then the alternatives and when they are needed: version column for optimistic concurrency, FOR UPDATE when logic must happen between read and write, serializable when the rule spans rows.

"What happens when you use serializable in PostgreSQL?" Serializable snapshot isolation: optimistic, readers never block, and transactions can fail at commit with 40001. So it requires a retry loop at the transaction boundary, with backoff and jitter, retrying the reads as well as the writes. Saying "it's slow" without knowing the mechanism is the common weak answer.

"Why are long transactions harmful?" They hold back vacuum across the whole database, so unrelated tables bloat. Then name the two design rules: no network calls inside a transaction, and no user interaction inside a transaction, plus idle_in_transaction_session_timeout as the safety net.

One thing to volunteer: point out that a retry loop must wrap the entire transaction including its reads. Retrying only the failed write reuses stale values and reintroduces exactly the anomaly the level was preventing. It is the most common way a correct-looking retry implementation is silently wrong.

Recall

• ACID: atomicity (all or nothing), consistency (only the constraints you declared), isolation (the dial), durability (survives a crash). The C is the weakest letter and the one people lean on hardest.
• Anomalies: dirty read (uncommitted data), non-repeatable read (same row changes), phantom (new rows appear), lost update (second write erases the first), read skew (two rows from two moments), write skew (each checks a rule, each writes a different row).
• The standard's four-level table omits lost update and write skew entirely — which is why "repeatable read" can still give wrong answers.
• PostgreSQL defaults to read committed; its repeatable read is snapshot isolation and blocks phantoms. MySQL defaults to repeatable read with gap locks.
• Serializable is implemented either by two-phase locking (readers block writers) or SSI (optimistic, aborts on detected dependency cycles). SSI needs a retry loop on 40001, wrapping the whole transaction including the reads, with backoff and jitter.
• Lost update, in increasing strength: a conditional UPDATE stock = stock - 1 WHERE stock >= 1 · a version column checked in the WHERE · SELECT … FOR UPDATE · serializable.
• If the invariant is about one row, a conditional update suffices. If it is about a set of rows, you need serializable or a lock representing the set.
• Long transactions block vacuum database-wide. No network calls and no user interaction inside a transaction; set idle_in_transaction_session_timeout.

Self-test: Why is consistency mostly your responsibility? · What does write skew do that a version column cannot catch? · Why does PostgreSQL's repeatable read block phantoms when the standard says it need not? · Why must a retry loop redo the reads? · Give the one-statement fix for overselling · Why does one idle transaction bloat unrelated tables?

Next: 7.4.2 opens the machinery — how MVCC gives every transaction its own snapshot without locking readers, what the locks actually are, and how to read and prevent a deadlock.