Transaction Isolation Levels
SQL defines four isolation levels that control what anomalies concurrent transactions can see: READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, and SERIALIZABLE. Higher isolation prevents more anomalies but increases contention.
Isolation levels are privacy curtains. READ COMMITTED: you see the finished paintings on the wall (committed). REPEATABLE READ: you photograph the wall at the start — updates don't change your photo. SERIALIZABLE: you're in a sealed room; nobody paints while you're there.
Anomalies: Dirty Read (reading uncommitted changes — prevented at READ COMMITTED+), Non-Repeatable Read (reading same row twice gets different values — prevented at REPEATABLE READ+), Phantom Read (re-running a range query returns different rows — prevented at SERIALIZABLE). PostgreSQL default: READ COMMITTED. MySQL default: REPEATABLE READ. PostgreSQL's REPEATABLE READ is snapshot isolation (SI) — uses MVCC, not range locks. PostgreSQL SERIALIZABLE uses SSI (Serializable Snapshot Isolation), providing true serializability with minimal locking.
PostgreSQL doesn't implement READ UNCOMMITTED — it treats it as READ COMMITTED (dirty reads are never allowed). Snapshot Isolation (used by PostgreSQL's REPEATABLE READ): each transaction sees a snapshot of committed data at transaction start. This prevents non-repeatable reads and phantoms, but allows write skew — two transactions read overlapping rows, both decide to update, both commit, violating an invariant. SSI detects potential write skew patterns using the theory of serialization anomalies and aborts one conflicting transaction. The write skew example: two doctors simultaneously check on-call count (sees 2), both decide to go off-call, both commit — now 0 doctors on call (violates minimum 1 invariant). Only SERIALIZABLE prevents this.
I use READ COMMITTED (PostgreSQL default) for most OLTP — it prevents dirty reads with minimal overhead. REPEATABLE READ for read-heavy analytics transactions that mustn't see concurrent updates mid-read. SERIALIZABLE for financial transactions with correctness invariants (no overdrafts, no double-booking). PostgreSQL's SSI provides true serializability without reader-writer blocking, which is a major advantage over traditional lock-based serialization.
Thinking REPEATABLE READ prevents all concurrency anomalies. Write skew is still possible under snapshot isolation (REPEATABLE READ). Only SERIALIZABLE prevents it — at the cost of potential serialization failures that require retries.