Transaction Isolation Levels

The SQL Standard defines four isolation levels, but PostgreSQL’s implementation is unique and more robust than many other databases. The isolation level determines what data a transaction can “see” when other transactions are running concurrently.

[!TIP] Default Behavior The default isolation level in PostgreSQL is Read Committed. This is sufficient for 90% of web applications but can lead to subtle bugs in complex financial or inventory systems.

1. The Four Levels (Postgres Edition)

Unlike some databases that implement “Read Uncommitted” (allowing you to see dirty, uncommitted data), Postgres treats Read Uncommitted exactly the same as Read Committed.

Isolation Level Dirty Read Non-Repeatable Read Phantom Read Serialization Anomaly
Read Uncommitted ❌ (Mapped to RC) ✓ ✓ ✓
Read Committed ❌ ✓ ✓ ✓
Repeatable Read ❌ ❌ ❌ (Mostly) ✓ (Write Skew)
Serializable ❌ ❌ ❌ ❌
  • ✓: Possible (The anomaly can happen)
  • ❌: Not Possible (Safe)

2. Interactive Anomaly Simulator

Visualize how different isolation levels handle concurrent transactions.

Concurrency Lab

Transaction A
Transaction B
System Status: Account Balance = $1000

3. The Anomalies Explained

Dirty Read

  • Definition: Reading data written by another transaction that has not yet committed.
  • Risk: If the other transaction rolls back, you’ve acted on data that never “officially” existed.
  • Postgres: Impossible. Postgres implementation of MVCC simply ignores uncommitted xmin values from other transactions.

Non-Repeatable Read

  • Definition: You run the same SELECT twice in one transaction and get different results because someone else committed an UPDATE in between.
  • Risk: Logic that depends on data stability (e.g., calculating a total, then verifying it) breaks.
  • Prevention: Use Repeatable Read. It holds a snapshot from the start of your transaction.

Phantom Read

  • Definition: You run a query like SELECT * FROM users WHERE age > 18 twice. In between, someone inserts a new 20-year-old. The second query returns an extra row (a “phantom”).
  • Risk: Aggregations (SUM, COUNT) become inconsistent.
  • Postgres: Technically, Postgres Repeatable Read prevents Phantoms because it uses Snapshot Isolation. Standard SQL allows Phantoms in Repeatable Read, but Postgres is stricter.

4. Write Skew & Serialization Anomalies

This is the most subtle and dangerous anomaly. It occurs when two transactions read overlapping data but update disjoint data.

The Scenario (Hospital Shift):

  • Rule: At least one doctor must be on call.
  • Current state: Alice and Bob are both on call.
  • Transaction A (Alice): “Is Bob on call? Yes. Okay, I’ll go off duty.”
  • Transaction B (Bob): “Is Alice on call? Yes. Okay, I’ll go off duty.”

Both transactions check the state before either commits. Both see the other person on call. Both proceed to go off duty. Result: No doctors on call.

This is Write Skew. It is prevented only by Serializable isolation.

How Serializable Works (SSI)

Postgres uses SSI (Serializable Snapshot Isolation). It tracks “RW-dependencies” (Read-Write dependencies).

  1. It notes that TX A read a value (Bob’s status) that TX B later changed.
  2. It notes that TX B read a value (Alice’s status) that TX A later changed.
  3. It detects a cycle in the dependency graph and aborts one of the transactions.

[!WARNING] Retry Logic Required If you use Serializable isolation, your application MUST be prepared to catch serialization errors (SQL State 40001) and retry the transaction.

5. Summary

  1. Read Committed: Good for simple lookups. Changes by others become visible immediately after they commit.
  2. Repeatable Read: Guarantees a consistent snapshot. Great for reporting. Vulnerable to Write Skew.
  3. Serializable: The gold standard for correctness. Prevents all anomalies including Write Skew, but requires retry logic and has a performance cost.