Transaction isolation levels

There are four SQL isolation levels: Read Uncommitted, Read Committed, Repeatable Read, Serializable. Each prevents more anomalies, costs more, and aborts more. Postgres skips Read Uncommitted (treats it as Read Committed).

The four levels and what they prevent

Level	Dirty read	Non-repeatable read	Phantom read	Write skew
Read Uncommitted	allowed	allowed	allowed	allowed
Read Committed	prevented	allowed	allowed	allowed
Repeatable Read	prevented	prevented	prevented in Postgres	allowed
Serializable	prevented	prevented	prevented	prevented

Postgres's Repeatable Read is actually Snapshot Isolation, which is stronger than ANSI Repeatable Read (it prevents phantom reads) but still allows write skew.

The anomalies, in English

• Dirty read: you see another transaction's uncommitted write.
• Non-repeatable read: same SELECT returns different values within one transaction.
• Phantom read: same SELECT returns different rows (new rows appear) within one transaction.
• Write skew: two transactions read overlapping data, each decides independently it is safe to write, both commit, invariant broken.

What Postgres actually does

• Read Committed (default): each statement gets a fresh snapshot. You can see different data across two SELECTs in the same transaction. Cheap. Use for most OLTP.
• Repeatable Read: snapshot is taken at the first statement and frozen. Write conflicts abort the later transaction with could not serialize access. Use when you need a consistent view across multiple reads.
• Serializable: Repeatable Read plus Serializable Snapshot Isolation (SSI). Tracks read-write dependencies between concurrent transactions and aborts one if a dangerous pattern (rw-antidependency cycle) is detected. Use when invariants span rows.

The interview line

"Postgres defaults to Read Committed which prevents dirty reads but allows non-repeatable reads and write skew. Repeatable Read is actually Snapshot Isolation in Postgres which is stronger than the SQL standard. Serializable uses SSI to detect dangerous read-write patterns and abort one transaction. The cost of Serializable is retries on conflict, usually under 10 percent overhead, but it gives you real serializability without 2-phase locking."