What Your Transaction Is Allowed to See of Mine

Read committed, repeatable read, and serializable — the three isolation levels, demonstrated with real two-session runs that produce a non-repeatable read, a phantom, and write skew, plus the serialization failure that stops it. Run against PostgreSQL 18.

Last chapter established that every transaction reads against a snapshot. This one asks the follow-up: how fixed is that snapshot, and what can leak through it? That’s the question isolation levels answer. An isolation level is a contract about which of another transaction’s effects you’re allowed to see mid-flight. Loosen it and you get more concurrency but more anomalies; tighten it and the anomalies vanish but transactions start colliding. The SQL standard names the anomalies; Postgres gives you three practical levels to trade against them.

Every anomaly below was produced live, two real psql sessions interleaved with pg_sleep, against PostgreSQL 18. Nothing here is a thought experiment — the outputs are pasted as they came back, including the abort at the end.

The default is looser than you think

Postgres defaults to READ COMMITTED, and its rule is simple: each statement sees all data committed before that statement began. Not before the transaction began — before the statement. So within one transaction, two identical queries can return different answers if someone commits in between. That’s called a non-repeatable read, and it’s allowed at this level. Here it is. Session A reads a balance, waits, reads again; session B commits a change in the gap.

# Session A (background): READ COMMITTED, two reads
BEGIN ISOLATION LEVEL READ COMMITTED;
SELECT 'A first read', bal FROM acct WHERE id=1;   -- t=0
SELECT pg_sleep(3);
SELECT 'A second read', bal FROM acct WHERE id=1;  -- t=3
COMMIT;

# Session B (t≈1): commit a change between A's two reads
UPDATE acct SET bal = bal + 100 WHERE id=1;
 A first read  | 100
 A second read | 200

A read 100, then 200, inside a single transaction, without changing anything itself. Each statement took a fresh snapshot, so the second one saw B’s committed update. For most work this is fine and it’s why it’s the default — it maximizes concurrency, and each statement is at least consistent with a real committed state. But if your transaction reads a value, makes a decision, and reads it again expecting stability, READ COMMITTED will not give you that.

Freezing the snapshot: REPEATABLE READ

REPEATABLE READ takes one snapshot at the first statement and holds it for the entire transaction. Every read sees the database as of that instant, no matter what commits later. Run the exact same interleaving, only change A’s isolation level:

# Session A (background): REPEATABLE READ this time
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT 'A first read', bal FROM acct WHERE id=1;
SELECT pg_sleep(3);
SELECT 'A second read', bal FROM acct WHERE id=1;
COMMIT;

# Session B (t≈1): same update, same timing
UPDATE acct SET bal = bal + 100 WHERE id=1;
 A first read  | 100
 A second read | 100

Same timing, same committed update by B — and now A reads 100 both times. The non-repeatable read is gone. A’s snapshot froze at BEGIN, and B’s later commit is simply invisible to it for its whole life. (B still succeeded instantly, exactly as in the MVCC chapter; its change is real, A just can’t see it yet.) One knob, and a whole class of anomaly disappears.

MySQL’s InnoDB defaults to REPEATABLE READ, so a developer moving from MySQL to Postgres inherits the looser default without noticing. Worth knowing which floor you’re standing on.

Phantoms: when new rows appear

A non-repeatable read is about a row changing under you. Its cousin is the phantom read. You run a range query; someone inserts a new row that matches your range; you run the query again, and a row appears that wasn’t there before. Under READ COMMITTED it happens:

# Session A (background): count a range twice, READ COMMITTED
BEGIN ISOLATION LEVEL READ COMMITTED;
SELECT count(*), sum(amount) FROM ledger WHERE amount >= 0;   -- t=0
SELECT pg_sleep(3);
SELECT count(*), sum(amount) FROM ledger WHERE amount >= 0;   -- t=3
COMMIT;

# Session B (t≈1): insert a row inside the range
INSERT INTO ledger (amount) VALUES (999);
 A count #1 |     3 |    60
 A count #2 |     4 |  1059

Three rows became four, and the sum jumped, because B’s new row materialized inside A’s range mid-transaction. Switch A to REPEATABLE READ and rerun with a fresh insert:

 A count #1 |     4 |  1059
 A count #2 |     4 |  1059

The count holds at four across both reads — B’s newly inserted row is outside A’s frozen snapshot, so the phantom never appears. In Postgres, REPEATABLE READ stops phantom reads too, which is stronger than the SQL standard strictly requires. (The standard permits phantoms at this level; Postgres’s snapshot mechanism happens to prevent them.)

The anomaly REPEATABLE READ can’t stop: write skew

You’d think a frozen snapshot handles everything. It doesn’t. There’s a subtler anomaly called write skew, and it slips right through REPEATABLE READ. The setup: two accounts, each holding 100, and a business rule that their combined balance must never drop below 100. Two transactions run at once. Each reads the combined total, sees 200, concludes it can safely withdraw 100 from its own account, and does. Neither transaction, on its own, breaks the rule. Together they do.

# Session A (background): REPEATABLE READ, withdraw from account 1
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT sum(bal) FROM acct;          -- reads 200
SELECT pg_sleep(2);
UPDATE acct SET bal = bal - 100 WHERE id = 1;
COMMIT;

# Session B (t≈1): REPEATABLE READ, withdraw from account 2
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT sum(bal) FROM acct;          -- also reads 200
UPDATE acct SET bal = bal - 100 WHERE id = 2;
COMMIT;

Both read sum = 200. Both committed. The final state:

 id | bal
----+-----
  1 |   0
  2 |   0

 combined | 0

Combined balance: zero. The invariant is broken, and both transactions committed cleanly with no error. This is why write skew is dangerous — each transaction updated a different row, so there was no direct write conflict for REPEATABLE READ to catch. They each acted correctly on a snapshot that was true when they read it and false by the time they committed. A snapshot protects what you read; it can’t protect an invariant that spans rows nobody else touched.

SERIALIZABLE catches it, by refusing to commit

SERIALIZABLE is the strongest level. Its guarantee: the result of running the transactions concurrently is identical to running them one after another in some order. Postgres implements this with Serializable Snapshot Isolation. It tracks the read/write dependencies between live transactions, and if it detects a cycle that no serial order could produce, it aborts one of them. Run the identical write-skew scenario with both transactions at SERIALIZABLE:

# both sessions: BEGIN ISOLATION LEVEL SERIALIZABLE; ... same reads and writes as above

Session B commits first, fine. Then session A tries its UPDATE:

ERROR:  could not serialize access due to read/write dependencies among transactions
DETAIL:  Reason code: Canceled on identification as a pivot, during write.
HINT:  The transaction might succeed if retried.
 id | bal
----+-----
  1 | 100
  2 |   0

 combined | 100

Postgres detected that A and B had each read what the other was about to change — a dependency cycle with no equivalent serial order — and aborted A rather than let the invariant break. The combined balance stayed at 100. That error carries SQLSTATE 40001, a serialization failure, and the HINT says exactly what to do: retry. This is the contract you accept with SERIALIZABLE. It guarantees correctness, but it can abort a transaction that did nothing locally wrong, and your application must be ready to catch a 40001 and run the transaction again. In practice that’s a small retry loop around the transaction. The database gives you serializable correctness; you give it the willingness to retry.

Which level, when

  • READ COMMITTED (default) — statement-level snapshots, allows non-repeatable and phantom reads. The right choice for most OLTP, where each statement operating on committed data is enough.
  • REPEATABLE READ — one snapshot per transaction, stops non-repeatable and (in Postgres) phantom reads. Reach for it when a transaction reads the same data more than once and needs stability — reports, multi-step reads, exports.
  • SERIALIZABLE — full serializability, stops write skew and everything else, at the cost of possible 40001 aborts you must retry. Use it when an invariant spans multiple rows and correctness is non-negotiable — inventory, ledgers, booking systems.

The one idea to carry: isolation is a dial between concurrency and anomalies, and you set it per transaction. Most of your transactions want the loose, fast default. The few that guard a cross-row invariant want SERIALIZABLE and a retry loop, and now you’ve seen exactly why — a snapshot alone let two well-behaved transactions zero out an account. Isolation decides what one transaction may see; the next chapter is about what happens when two transactions want to change the same row, and one of them has to wait.

Next: When transactions collide: locking and deadlocks — row locks, lock queues, and the deadlock Postgres will break for you.

Comments