Chapter 4: Isolation Levels and the Anomalies Each One Prevents

Why this matters

Chapter 3 established that "Isolation" is one of ACID's four guarantees, but glossed over an important detail: isolation is not one fixed guarantee — it's a spectrum, and different databases (and different transactions within the same database) can operate at different points on that spectrum, each preventing a different, specific set of concurrency anomalies. Choosing the right isolation level for a given operation is a real, consequential decision, not a default to leave unexamined.

The concurrency anomalies, each with a concrete example

[BEGINNER] Before the isolation levels themselves, it's worth seeing exactly what can go wrong without sufficient isolation — each anomaly is a specific, nameable failure pattern:

Dirty read — reading another transaction's uncommitted changes, which might later be rolled back:

-- Transaction A                          Transaction B
BEGIN;
UPDATE accounts SET balance = 0
  WHERE id = 1;
                                           BEGIN;
                                           SELECT balance FROM accounts
                                             WHERE id = 1;  -- reads 0 (A's UNCOMMITTED change!)
ROLLBACK;                                 -- A rolled back, but B already acted on the fake "0"

Non-repeatable read — reading the same row twice within one transaction and getting different values, because another transaction committed a change in between:

-- Transaction A                          Transaction B
BEGIN;
SELECT balance FROM accounts
  WHERE id = 1;  -- reads 100
                                           BEGIN;
                                           UPDATE accounts SET balance = 50
                                             WHERE id = 1;
                                           COMMIT;
SELECT balance FROM accounts
  WHERE id = 1;  -- reads 50 -- DIFFERENT from the first read, within the SAME transaction

Phantom read — re-running the same range query twice within one transaction and getting a different set of rows, because another transaction inserted or deleted a matching row in between:

-- Transaction A                          Transaction B
BEGIN;
SELECT count(*) FROM orders
  WHERE status = 'pending';  -- returns 5
                                           BEGIN;
                                           INSERT INTO orders (status) VALUES ('pending');
                                           COMMIT;
SELECT count(*) FROM orders
  WHERE status = 'pending';  -- returns 6 -- a "phantom" row appeared

The standard isolation levels, and what each one prevents

[BEGINNER] SQL defines four standard isolation levels, each preventing a progressively larger set of the anomalies above (PostgreSQL's actual default is noted, since it differs slightly from the SQL standard's baseline expectations):

Isolation level	Prevents dirty reads?	Prevents non-repeatable reads?	Prevents phantom reads?	Notes
Read Uncommitted	No	No	No	PostgreSQL treats this identically to Read Committed — it doesn't implement true dirty reads at all
Read Committed	Yes	No	No	PostgreSQL's default — each statement sees only committed data as of when that statement began
Repeatable Read	Yes	Yes	Mostly (PostgreSQL's implementation prevents phantoms too, stricter than the SQL standard requires)	The whole transaction sees a consistent snapshot from when it began
Serializable	Yes	Yes	Yes	Transactions behave as if executed one at a time, in some serial order — the strongest, most expensive guarantee

BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
-- ... operations ...
COMMIT;

The real trade-off: stronger isolation costs concurrency

[SENIOR INSIGHT] Higher isolation levels don't just "cost more" in some vague sense — they mechanically reduce how much concurrent work the database can do simultaneously, because preventing more anomalies requires more locking, more conflict detection, or more transaction aborts-and-retries. Serializable isolation, PostgreSQL's strongest level, is typically implemented as "optimistic" — transactions proceed as if there's no conflict, but the database detects conflicts at commit time and aborts one of the conflicting transactions, requiring the application to retry it. This means Serializable isolation trades a guarantee of correctness for a real, application-visible cost: your application code must be prepared to catch a serialization failure and retry the transaction, which is very different from Read Committed's "it just works, but with weaker guarantees" behavior.

-- Application code using Serializable isolation MUST handle this:
try:
    with connection.transaction(isolation_level="SERIALIZABLE"):
        # ... operations ...
except SerializationFailure:
    retry_the_entire_transaction()  # this is EXPECTED, not exceptional, at this isolation level

Choosing the right level for a given operation

[IN PRACTICE] Most application code, most of the time, runs comfortably at the database's default (Read Committed in PostgreSQL) — it's sufficient for the vast majority of operations where non-repeatable or phantom reads within a single transaction aren't actually a practical concern. Reach for Repeatable Read when a transaction performs multiple related reads that must be mutually consistent (a financial report that reads several related totals and needs them to reflect one consistent point in time). Reach for Serializable specifically when a transaction's correctness genuinely depends on behaving as if no other transaction ran concurrently at all — often for financial or inventory logic where the exact class of bug from Chapter 3's stock-oversell example is a real risk that SELECT ... FOR UPDATE alone doesn't fully address (some anomalies, like certain phantom-read-driven bugs, aren't prevented by row-level locking alone).

Common mistakes

1. Assuming the database's default isolation level (Read Committed) prevents all concurrency anomalies — it specifically does not prevent non-repeatable or phantom reads, which can matter for multi-step logic within a single transaction.
2. Reaching for Serializable isolation everywhere "to be safe," without building retry logic for serialization failures — Serializable transactions are expected to occasionally fail and need retrying; code that doesn't handle this will surface confusing, intermittent errors in production.
3. Not recognizing which specific anomaly a piece of application logic is actually vulnerable to, leading to either over-applying strong isolation where Read Committed would suffice, or under-applying it where an anomaly genuinely causes a bug.
4. Confusing isolation level guarantees with the row-locking behavior of SELECT ... FOR UPDATE from Chapter 3 — locking prevents a specific class of race (concurrent modification of the same row), while isolation level governs a broader set of read-consistency guarantees across the whole transaction.

Discussion / interview questions

1. "What's the difference between a non-repeatable read and a phantom read?" Strong answer: A non-repeatable read is re-reading the same specific row and getting a different value because another transaction committed a change to it in between; a phantom read is re-running the same range query and getting a different set of matching rows because another transaction inserted or deleted a row that matches the query's condition.

2. "What isolation level does PostgreSQL use by default, and what anomalies can still occur at that level?" Strong answer: Read Committed by default — dirty reads are prevented, but non-repeatable reads and phantom reads can still occur within a single transaction that performs multiple reads.

3. "What's the practical cost of using Serializable isolation, beyond 'it's slower'?" Strong answer: Serializable isolation can cause transactions to fail with a serialization error at commit time when a conflict is detected, requiring the application to explicitly catch this and retry the entire transaction — a fundamentally different failure mode than weaker isolation levels, which don't require this application-level handling.

4. "When would you choose Repeatable Read over the database's default isolation level?" Strong answer: When a transaction performs multiple related reads that need to reflect one mutually consistent snapshot of the data (e.g., a financial report reading several related totals) — Read Committed alone doesn't guarantee this consistency across multiple statements within the same transaction.

Chapter summary / cheat sheet

• Isolation is a spectrum, not a single guarantee: Read Uncommitted (weakest, not really implemented distinctly in PostgreSQL) → Read Committed (PostgreSQL's default) → Repeatable Read → Serializable (strongest).
• Three key anomalies: dirty read (uncommitted data), non-repeatable read (same row, different value on re-read), phantom read (same range query, different row set on re-read).
• Higher isolation levels reduce concurrency and, for Serializable specifically, require the application to handle and retry serialization failures as expected, routine behavior.
• Choose isolation level based on which specific anomaly a given piece of logic is actually vulnerable to — not by defaulting to either "whatever's fastest" or "the strongest available" without analysis.

---