Isolation Levels and Anomalies

Dirty read: T1 reads uncommitted data from T2. If T2 aborts, T1 used data that never existed.

Non-repeatable read: T1 reads row R, T2 updates+commits R, T1 reads R again — different value.

Phantom read: T1 queries rows matching a predicate, T2 inserts+commits a matching row, T1 re-runs — sees new row.

Write skew: T1 and T2 both read overlapping data, each writes based on what they read, the combined result violates an invariant. Neither modifies the same row. Example: two on-call doctors both see the other is on-call and both request time off — neither write conflicts directly, but the result leaves nobody on-call.

sequenceDiagram participant T1 as T1 (Dr. A) participant DB participant T2 as T2 (Dr. B) T1->>DB: read on_call_count = 2 T2->>DB: read on_call_count = 2 Note over T1,T2: both see "2 on-call, safe to go off" T1->>DB: UPDATE dr_a SET off_call = true T2->>DB: UPDATE dr_b SET off_call = true Note over DB: both commit — neither write touched<br/>a row the other read, so no lock conflicted.<br/>Invariant "at least 1 on-call" is now violated.

Row-level locking never catches this — T1 and T2 truly modified different rows, so there was nothing to lock against. Only Serializable isolation (below) — or an explicit constraint/check that spans both rows — prevents it.

Lost update: T1 and T2 both read X=10, both compute X+1=11, both write 11. One update is lost; correct result is 12.

Read Uncommitted: allows dirty reads. PostgreSQL degrades to Read Committed.

Read Committed (PostgreSQL default): each statement takes a fresh snapshot. Prevents dirty reads. Allows non-repeatable reads and phantoms. Sufficient for most OLTP operations where no single transaction needs consistent multi-statement views.

Repeatable Read: transaction-level snapshot at first read. Prevents dirty reads and non-repeatable reads. In PostgreSQL, MVCC also prevents phantoms (fixed snapshot = no new rows visible). Does NOT prevent write skew.

Serializable (SSI): PostgreSQL 9.1+ detects serialization conflicts by tracking read-write dependencies between transactions. Aborts transactions whose execution would be non-serializable. Zero extra locking — optimistic detection. Application must retry aborted transactions. Prevents all anomalies including write skew.

Trade-offs: higher isolation = more aborts/retries, lower throughput. Read Committed is the default because it handles most use cases with minimal overhead. Use Repeatable Read when a transaction needs a consistent view across multiple statements (reporting). Use Serializable when correctness requires it (financial transactions with complex invariants).

Common pitfall

Assuming Repeatable Read prevents write skew because it prevents non-repeatable reads and phantoms is the most common mistake with this topic — as the doctors example above shows, write skew involves each transaction reading and writing different rows, so none of Repeatable Read's snapshot guarantees ever trigger a conflict. If an invariant spans multiple rows read by more than one transaction, Serializable is the only isolation level that catches a violation — anything below it can look correct in testing (where conflicts are rare) and still produce a real violation in production under real concurrency.