Transaction isolation levels

Isolation is the most misunderstood letter in ACID. The SQL standard's definitions are vague, vendor implementations differ, and most app developers ship Read Committed and pray. Here is what actually happens.

The anomaly taxonomy

ANSI SQL defines four anomalies. Berenson 1995 extended this with more. The ones that matter in practice:

• P0 Dirty write: T1 overwrites T2's uncommitted data. Nobody allows this.
• P1 Dirty read: T1 reads T2's uncommitted data. Prevented by Read Committed.
• P2 Non-repeatable read: T1 reads x, T2 writes x and commits, T1 reads x and gets different value. Prevented by Repeatable Read.
• P3 Phantom: T1 reads rows matching predicate P, T2 inserts a new row matching P, T1 re-reads and sees the new row. Prevented by Serializable (and by Postgres Repeatable Read).
• A5A Read skew: T1 reads x, T2 updates x and y atomically, T1 reads y. Sees inconsistent pair.
• A5B Write skew: T1 reads , T2 reads , T1 writes x, T2 writes y. Each based decision on stale view of the other's write. Prevented only by Serializable.

Postgres's implementation map

Read Committed

Each statement gets GetSnapshotData(). Within a transaction, statement 1 and statement 2 see different snapshots. If statement 2 is an UPDATE that targets a row another transaction modified after statement 1 started, Postgres re-reads the latest committed version and re-applies the WHERE clause to that version. This is EvalPlanQual.

The implication: in Read Committed, UPDATE accounts SET balance = balance - 100 WHERE id = 1 is safe even if another transaction also updated row 1. Postgres serializes them at the row level.

But two separate SELECT ... UPDATE statements are not safe:

BEGIN;
SELECT balance FROM accounts WHERE id = 1;  -- returns 500
-- application logic: if balance >= 100, then:
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
COMMIT;

Between the SELECT and the UPDATE, another transaction can drain the balance. Classic lost update.

Repeatable Read (Snapshot Isolation)

Postgres's Repeatable Read takes the snapshot at the first data-reading statement and reuses it for the whole transaction. All reads are consistent.

For writes, if you try to update a row that has been updated by a transaction not in your snapshot, you get:

ERROR: could not serialize access due to concurrent update

You must retry the whole transaction. Your application code must handle this.

Repeatable Read is Snapshot Isolation. It is stronger than ANSI Repeatable Read because it prevents phantom reads (the new row's xmin is outside your snapshot, so you do not see it). It is weaker than true Serializable because of write skew.

Serializable (SSI)

SSI was added in Postgres 9.1 based on Cahill 2008. It tracks read-write dependencies between concurrent transactions. If T1 reads x and T2 writes x, that is a rw-antidependency from T1 to T2. If you have a cycle T1 -> T2 -> T1 in the dependency graph, the schedule is not serializable, and Postgres aborts one transaction.

The implementation uses predicate locks (SIREAD locks) on tuples and index pages. These are not real locks; they do not block. They are just markers that the SSI machinery uses to detect cycles.

You will see:

ERROR: could not serialize access due to read/write dependencies among transactions

Retry the transaction. With low contention, abort rate is under 1 percent. With high contention on shared rows, it can spike.

When to use each

The write skew example in code

The doctors-on-call problem:

-- Setup
CREATE TABLE doctors (id int, on_call bool);
INSERT INTO doctors VALUES (1, true), (2, true);
 
-- T1 wants to go off call
BEGIN ISOLATION LEVEL READ COMMITTED;
SELECT count(*) FROM doctors WHERE on_call = true;  -- 2
-- "OK, safe to go off call"
UPDATE doctors SET on_call = false WHERE id = 1;
COMMIT;
 
-- T2, concurrently
BEGIN ISOLATION LEVEL READ COMMITTED;
SELECT count(*) FROM doctors WHERE on_call = true;  -- 2 (snapshot before T1 commit)
UPDATE doctors SET on_call = false WHERE id = 2;
COMMIT;
 
-- Now zero doctors on call. Bug.

With SERIALIZABLE, T2 would be aborted on commit, and the application would retry T2's logic with the updated snapshot showing only 1 doctor on call.

Alternatives to Serializable

• SELECT ... FOR UPDATE: take a row lock. Works for single-row invariants. Does not work for predicates over rows that do not yet exist (cannot lock a phantom).
• SELECT ... FOR UPDATE OF table NOWAIT: fail fast if locked. Use to avoid deadlocks.
• Materializing the invariant: add a summary_table row that all transactions update. Now the conflict is on one row and Read Committed plus FOR UPDATE works.
• Advisory locks: pg_advisory_xact_lock(hash). Application-defined lock space. Good for "one transaction at a time per user" patterns.

Performance characteristics

Retry rates depend on contention:

• Low contention: under 0.1 percent aborts
• Medium contention: 1-5 percent aborts
• High contention on hot rows: 20-50 percent aborts, often worse than FOR UPDATE

Common pitfalls

• Not handling serialization failures. Your code must retry on SQLSTATE 40001 (serialization_failure) and 40P01 (deadlock_detected). Most ORMs do not retry by default.
• Mixing isolation levels in one connection. Each transaction can have its own level via SET TRANSACTION ISOLATION LEVEL before any query. Reusing connections from a pool means the level resets.
• Using REPEATABLE READ and expecting SERIALIZABLE. Write skew bites silently.
• Long Serializable transactions. Memory for predicate locks grows. Keep transactions short.

The senior take

Default to Read Committed for simple writes. Use Serializable when you have any invariant that spans multiple rows or predicates over future rows. Always implement a retry loop. The 10 percent throughput cost of SSI is cheaper than the 3am debugging session for a write skew bug that drained $40,000 from a balance.