MVCC and isolation levels

In short: MVCC (multi-version concurrency control) keeps several versions of a row so readers do not wait for writers. Isolation levels determine which anomalies parallel transactions may see.

In more detail: Each transaction works on a snapshot. An update creates a new row version; the old one stays visible to running readers until nobody needs it (vacuum). Readers do not block writers and vice versa.

In Depth

• Anomalies: Dirty read (reading uncommitted), non-repeatable read, phantom read, write skew.
• Read committed: Each query sees the latest commit. Default in PostgreSQL.
• Repeatable read/snapshot: The whole transaction sees the same snapshot.
• Serializable: Behaves as if transactions ran one after another (for example SSI in PostgreSQL).
• Write skew: Two transactions read the same data and change different rows, breaking a shared rule.
• Locks: SELECT … FOR UPDATE for explicit locking.
• Costs: Space for versions, cleanup (vacuum), retries on conflict.

See also: Database transactions and ACID, Write-ahead log and recovery