MVCC provides the mechanism for isolation, but Isolation Levels let you choose how strict that isolation is. Stricter isolation means fewer anomalies but more overhead and potential for transaction failures. This is a deliberate trade-off the DBRE and application architect must make together.
The Anomalies You're Protecting Against
| Anomaly | Description | Example |
|---|---|---|
| Dirty Read | Reading uncommitted data from another transaction | Seeing a balance update that gets rolled back |
| Non-Repeatable Read | Same query returns different rows within one transaction | Row changes between two SELECTs in same transaction |
| Phantom Read | Same query returns different set of rows | New rows appear between two SELECTs in same transaction |
| Serialization Anomaly | Concurrent transactions produce a result impossible in any serial order | Two transactions each read a value and write based on it, creating a cycle |
The Four Standard Isolation Levels
1. Read Uncommitted (rarely used in practice)
- Can read uncommitted changes from other transactions (dirty reads).
- PostgreSQL implements this as Read Committed — it doesn't actually allow dirty reads.
- Use case: Almost never. The risk of reading rolled-back data is too high.
2. Read Committed (PostgreSQL default)
- Each statement within a transaction sees a fresh snapshot of committed data.
- Prevents dirty reads. Allows non-repeatable reads and phantom reads.
- Implication: If you run the same SELECT twice in one transaction, you might get different results if another transaction committed between them.
- Use case: Most OLTP workloads. Good balance of performance and correctness.
3. Repeatable Read / Snapshot Isolation
- The entire transaction sees a single consistent snapshot taken at transaction start.
- Prevents dirty reads and non-repeatable reads. Prevents most phantom reads in PostgreSQL's implementation.
- Implication: Long-running transactions hold their snapshot, preventing Vacuum from cleaning up old row versions they might still need.
- Use case: Reports, analytics queries, any transaction that reads the same data multiple times and needs consistency.
4. Serializable (strictest)
- Guarantees the result is identical to some serial execution of all concurrent transactions.
- Prevents all anomalies including serialization anomalies.
- Implication: The database detects potential conflicts and may abort transactions with a serialization error (ERROR: could not serialize access due to concurrent update). Applications must be prepared to retry.
- Use case: Financial systems, inventory management — anywhere correctness is more important than throughput.
The isolation level can be set per-transaction (SET TRANSACTION ISOLATION LEVEL ... right after BEGIN) or as a session-wide default (SET default_transaction_isolation = ...).