Isolation Levels & Read Anomalies

A menu of how much transactions see of each other

Isolation levels are a menu of how much concurrent transactions are allowed to see of each other's in-flight work. The ANSI SQL levels, weakest to strongest, are Read Uncommitted, Read Committed, Repeatable Read, and Serializable. Each level forbids more anomalies and costs more throughput.

The anomalies, in order of nastiness

• Dirty read: you read another transaction's uncommitted write, which may roll back. Forbidden at Read Committed and above. Almost no real database defaults to allowing it.
• Non-repeatable read: you read a row, another transaction commits an update to it, you read it again in the same transaction and get a different value. Forbidden at Repeatable Read and above.
• Phantom read: you run a range query (WHERE status = 'pending'), another transaction inserts a new matching row, you re-run and a new row appears. Classically forbidden only at Serializable.
• Lost update: two transactions read the same value, both compute a new value from it, both write; one overwrites the other. balance = balance + x done as read-modify-write in the app is the usual victim.
• Write skew: two transactions each read an overlapping set, each individually keeps an invariant true, but their combined effect violates it. The textbook case: two doctors both on call, each checks "at least one other doctor is on call" (true), each takes themselves off shift, and now zero doctors are on call.

Defaults matter and differ: Postgres defaults to Read Committed, MySQL InnoDB defaults to Repeatable Read. So the same application code can behave differently on the two databases under load, which is a real production trap.

The most-probed subtlety: snapshot isolation and write skew

Snapshot isolation (what Postgres calls Repeatable Read, and what many systems mean by "Repeatable Read") gives every transaction a consistent snapshot as of its start, which kills dirty, non-repeatable, and phantom reads for reads. But snapshot isolation still permits write skew, because each transaction validates against a stale snapshot that does not see the other's write. Only true serializable forbids write skew, implemented either by Serializable Snapshot Isolation (SSI, which detects dangerous read-write dependency cycles and aborts one transaction) or by strict two-phase locking (2PL). Serializable costs throughput: SSI adds abort-and-retry churn under contention, 2PL adds lock waits.

Interview nuance: If you claim "Repeatable Read fixes it," the interviewer will ask "which anomaly, and are you sure Repeatable Read covers it?" If the bug is write skew, Repeatable Read (snapshot) does not fix it and you need Serializable or an explicit lock. Naming the exact anomaly is the whole game.

Fixing a concurrency bug without global Serializable

• SELECT ... FOR UPDATE: take a pessimistic row lock on the rows you are about to modify, forcing concurrent transactions to queue. Fixes lost update and many oversell cases with surgical scope.
• Optimistic version column: add version, read it, and on write do UPDATE ... WHERE version = :read_version; if 0 rows change, someone beat you, so retry. Cheap under low contention.
• Unique constraint: sometimes the cleanest fix. If "one seat per booking" must hold, a unique index enforces it regardless of isolation level.

Recap: pick the isolation level (or explicit lock) by naming the exact anomaly; snapshot isolation stops dirty, non-repeatable, and phantom reads but still allows write skew, which only true Serializable or targeted locking prevents.