Isolation Levels and Anomalies

In one line

Isolation levels trade anomalies for concurrency: Read Committed (PostgreSQL's default) prevents dirty reads; Repeatable Read (snapshot isolation in PostgreSQL) also prevents non-repeatable reads and phantoms but allows write skew; Serializable prevents all anomalies by aborting transactions that could produce a non-serial result. Lost updates and write skew are the bugs that matter most in practice.

0/5 · 0%

Think of it like this

Two doctors on call. The rule is "at least one doctor stays on call". Both check the rota at the same moment, both see the other is on call, and both sign off. Each decision was fine alone; together they broke the rule. That is write skew.

Key ideas

01
Dirty read: seeing another transaction's uncommitted data. PostgreSQL never allows it (Read Uncommitted behaves like Read Committed).
02
Non-repeatable read: reading a row twice in one transaction and getting different values because another transaction committed in between. Allowed in Read Committed, where each statement gets a fresh snapshot.
03
Phantom: re-running a range query returns new rows. Allowed by the SQL standard at Repeatable Read, but PostgreSQL's snapshot-based Repeatable Read doesn't show phantoms.
04
Lost update: two transactions read a value, both compute a new value, and both write, so one change is lost. Prevent with atomic updates (SET qty = qty - 1), SELECT ... FOR UPDATE, optimistic version checks, or Repeatable Read (PostgreSQL aborts the second writer with a serialization error).
05
Write skew: two transactions read overlapping data and write different rows based on what they read, jointly violating an invariant. Snapshot isolation allows it. Prevent with Serializable (SSI in PostgreSQL), locking the rows that were read (FOR UPDATE), or a constraint that makes the invariant a single-row or unique check.
06
Read skew: reading related rows at different moments (balance A before a transfer, balance B after). Snapshot isolation (Repeatable Read) prevents it for a whole transaction.

Code & diagrams

anomalies.txttext

Level              Dirty  Non-repeatable  Phantom       Lost update     Write skew
Read Uncommitted*  no     yes             yes           yes             yes
Read Committed     no     yes             yes           yes             yes
Repeatable Read    no     no              no (PG)       no (PG aborts)  yes
Serializable       no     no              no            no              no

* PostgreSQL treats Read Uncommitted as Read Committed.
MySQL InnoDB defaults to Repeatable Read, with different locking behaviour (gap/next-key locks).

write-skew.sqlsql

-- Session A                                   -- Session B
BEGIN ISOLATION LEVEL REPEATABLE READ;          BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT count(*) FROM doctor
 WHERE shift = 'night' AND on_call;  -- 2       SELECT count(*) FROM doctor
                                                 WHERE shift = 'night' AND on_call;  -- 2
UPDATE doctor SET on_call = false
 WHERE name = 'Alice';                           UPDATE doctor SET on_call = false
                                                 WHERE name = 'Bob';
COMMIT;                                          COMMIT;   -- both succeed: nobody on call!

-- Same script at SERIALIZABLE: the second COMMIT fails with
-- ERROR: could not serialize access due to read/write dependencies among transactions
-- (SQLSTATE 40001) -> the application retries the whole transaction.

Interview problem

The problem

Name the anomaly and the fix

(a) Two admins raise a product price by 10% simultaneously via read-then-write; only one raise sticks. (b) Two users claim the last two free seats in a class of 30 by counting enrolments first; the class ends up with 31. (c) A report reads account totals while transfers run and the sum doesn't balance.

When it breaks

Moving to Serializable without retry logic

What you see

Under contention some transactions fail with SQLSTATE 40001; the application surfaces them as 500 errors.

Fix & prevent

Wrap transactions in a retry loop (with backoff and a cap) for 40001 and 40P01; keep transactions short so retries are cheap.

Explain it without notes

Why doesn't snapshot isolation prevent write skew?

What is PostgreSQL's default isolation level and what does it guarantee?

Practice

Reproduce a lost update in two psql sessions at Read Committed using read-then-write, then fix it two ways.

Trade-offs

↔
Stronger isolation removes anomaly classes but adds aborts (Serializable) or lock waits; many systems use Read Committed plus targeted locking or constraints.

Done when you can

I can name anomalies from a scenario and choose the minimal isolation or locking fix.

Command Palette