Transaction Isolation Levels

1. Introduction

Isolation levels define how concurrent transactions interact with each other's data. The SQL standard defines four levels: Read Uncommitted, Read Committed, Repeatable Read, and Serializable. Higher isolation prevents more anomalies but increases lock contention. PostgreSQL implements these using MVCC, with its own interpretation that differs from lock-based databases like MySQL/InnoDB.

2. Why It Matters

Choosing the wrong isolation level can cause silent data corruption. A reporting query using Read Committed might see different balances for the same account within one query (non-repeatable read). An inventory system might oversell if phantom reads allow new rows to appear between a check and an insert. Understanding these anomalies helps you choose the correct level for each use case.

3. Real-World Analogy

Imagine two people editing the same Google Doc simultaneously. Read Committed is like seeing the latest saved version each time you refresh. Repeatable Read is like locking your view to the version you opened — you won't see others' edits until you reopen. Serializable is like taking exclusive editing turns, ensuring no conflicts are possible.

4. How It Works

Three key anomalies define isolation levels:

• Dirty Read: Reading uncommitted changes of a transaction that later rolls back. PostgreSQL prevents this at ALL levels (MVCC never exposes uncommitted data).
• Non-Repeatable Read: A row read twice in the same transaction returns different values because another transaction committed an UPDATE in between.
• Phantom Read: A range query returns different rows on re-execution because another transaction committed an INSERT or DELETE.

PostgreSQL's levels:

• Read Committed (default): Each statement sees a new snapshot. Non-repeatable reads and phantoms are possible.
• Repeatable Read: The transaction uses one snapshot taken at the first query. Prevents non-repeatable reads. PostgreSQL also prevents phantoms at this level (stricter than SQL standard).
• Serializable: Adds serialization failure detection. If concurrent transactions would produce a different result than serial execution, PostgreSQL aborts one with a serialization failure error.

5. Internal Architecture

MVCC snapshots are built from the ProcArray — a shared memory structure listing all active XIDs. In Read Committed, a new snapshot is taken at the start of each statement. In Repeatable Read, the snapshot is taken at the first statement and reused. Serializable uses Serializable Snapshot Isolation (SSI), which tracks read-write dependencies between transactions in a conflict graph. If a cycle is detected, one transaction is aborted.

6. Visual Explanation

The diagram shows a timeline comparison of two concurrent transactions under each isolation level, highlighting which anomalies are possible or prevented at each level.

7. Practical Example

8. Common Mistakes

• Using Read Committed for financial reports: Aggregations may see inconsistent snapshots across joined tables.
• Not retrying on serialization failures: Serializable transactions can be aborted — applications must implement retry logic.
• Assuming Repeatable Read prevents write skew: Write skew (two transactions reading overlapping data then writing non-overlapping changes) requires Serializable in PostgreSQL.
• Setting Serializable globally: The performance overhead of SSI is significant. Use it only for transactions that need it.

9. Quick Quiz

10. Scenario-Based Challenge

Two hospital scheduling transactions concurrently check if at least one doctor is on-call, then each tries to take their own doctor off-call. Under Repeatable Read, both succeed and no doctors are on-call (write skew). Demonstrate this anomaly, then fix it using Serializable isolation and implement retry logic in pseudocode.

11. Debugging Exercise

12. Interview Questions

• Q: Explain write skew and which isolation level prevents it.
  A: Write skew occurs when two transactions read overlapping data, make independent decisions, and write non-overlapping changes that together violate a constraint. Only Serializable (SSI) detects and prevents it.
• Q: Why doesn't PostgreSQL have Read Uncommitted?
  A: MVCC always provides statement-level snapshots that exclude uncommitted changes. There's no mechanism to read dirty data, so Read Uncommitted is mapped to Read Committed.

13. Production Considerations

• Performance cost: Serializable tracks read-write dependencies in a predicate lock table (pg_locks with mode SIReadLock). High-concurrency workloads may see increased abort rates.
• Retry logic is mandatory: Applications using Serializable must catch SQLSTATE 40001 (serialization_failure) and retry the entire transaction.
• Hot Standby: Read replicas use snapshot isolation inherently. Long-running queries on replicas can delay WAL application and cause replication lag.
• SET SESSION CHARACTERISTICS: Set the default isolation level at the session or database level to avoid specifying it in every BEGIN statement.