Isolation Levels Deep Dive

Overview

Isolation is the contract between the engine and your code: what concurrent transactions may see, and when they may commit. The wrong level silently corrupts business rules (double-book a seat, drain inventory). The right-but-too-strong level burns throughput in aborts and lock waits.

ANSI-SQL labels are a shared vocabulary, not a wire protocol. PostgreSQL REPEATABLE READ is Snapshot Isolation. MySQL InnoDB REPEATABLE READ is next-key locking. Write skew is not even on the ANSI phenomena list. Senior answers name the engine, then the anomaly, then the fix.

By the end of this lesson you should be able to:

• Walk dirty / non-repeatable / phantom / write skew on a whiteboard
• Translate ANSI names into Postgres and InnoDB behavior
• Choose RC vs RR vs SERIALIZABLE for a given invariant
• Set the level per transaction and retry serialization failures
• Know when compensating transactions beat a global SERIALIZABLE tax

Why the weakest honest level wins

Always-SERIALIZABLE is easy to say and expensive to run. The winning path is the weakest isolation that still preserves the invariant, with locks or constraints only where SI has a hole.

1. 1

   simple CRUD → fresh statement snapshot

   Default READ COMMITTED everywhere

   Cheap. Dirty reads are gone. Two SELECTs in one txn can disagree. Fine for a product catalog; wrong for “sum of these rows stays ≥ 0”.

2. 2

   name the anomaly → pick RC, SI, SSI, or a lock

   Winner: weakest level that preserves the invariant

   Same-row debit: Postgres RR (SI) or an atomic UPDATE … WHERE qty ≥ n. Multi-row “at least one”: SSI retry, FOR UPDATE on the read set, or a constraint — not a global SERIALIZABLE default.

3. ?

   SERIALIZABLE as a scalpel, not a religion

   Postgres SERIALIZABLE is SSI: non-blocking reads plus commit-time rw-checks. InnoDB SERIALIZABLE turns SELECTs into locking reads. High abort or gap-lock cost means you picked too strong, or the key set is too hot — drop to locks on a small set. Details: SSI vs SI and SELECT FOR UPDATE.

ANSI-SQL isolation levels

Four names, in increasing strictness as the spec tells the story:

That table is the ANSI claim. It is not a proof of true serializability, and engines do not implement the cells the same way.

READ UNCOMMITTED

A transaction may read another transaction’s uncommitted writes. If the writer rolls back, the reader observed a value that never existed. Almost never acceptable on OLTP. Postgres does not really give you this: requesting it behaves like READ COMMITTED. InnoDB treats it as READ COMMITTED as well.

READ COMMITTED

Every value you read was committed at the moment of that statement. No dirty reads. Each statement may take a new snapshot, so two SELECTs of the same row in one transaction can disagree. Phantoms and write skew are both in play.

REPEATABLE READ (ANSI)

Rows you already read must not change for the rest of the transaction. Phantoms — new rows matching a predicate — may still appear. This is the weak ANSI definition. Postgres RR is stronger (SI). InnoDB RR is stronger in a different direction (gap locks).

SERIALIZABLE (ANSI)

The outcome should be equivalent to some serial order. ANSI operationalizes this as “no dirty, non-repeatable, or phantom reads.” Berenson et al. showed that blocking those three phenomena is not the same as conflict serializability. Write skew slips through the gap. Postgres closes it with SSI; InnoDB approximates with locking reads.

The three ANSI phenomena

Dirty read. T1 reads a row T2 has modified but not committed. T2 rolls back. T1 acted on a ghost.

Non-repeatable read. T1 reads a row. T2 commits an update (or delete) of that same row. T1 reads it again and sees a different value. Same primary key, different committed version.

Phantom read. T1 runs SELECT … WHERE a predicate. T2 inserts or deletes rows that match the predicate and commits. T1 re-runs the same query and the set changed. No existing row mutated; the membership changed.

Write skew is none of those three. Two transactions read overlapping data, write disjoint rows, and the pair violates a multi-row invariant. ANSI never named it. Full contrast: Phantoms vs write skew. Versions and snapshots: MVCC hub.

Write skew sits outside ANSI

Classic shape (doctors on call): invariant “at least one of Alice or Bob stays on.” Both transactions snapshot two-on, each turns a different doctor off, both commit. No dirty read, no non-repeatable read of the same row, no phantom insert. SI is happy. The invariant is dead.

ANSI SERIALIZABLE-as-phenomena-prevention does not automatically catch this. Engines need extra machinery: Postgres SSI tracks rw-conflicts and aborts one txn; InnoDB gap / next-key locks can block some (not all) skew-shaped inserts; a CHECK / guard row / SELECT … FOR UPDATE makes the invariant physical.

Engine implementations

PostgreSQL

Postgres RC takes a fresh snapshot per statement. RR takes one snapshot and reuses it — that is Snapshot Isolation, stronger than weak ANSI RR. SERIALIZABLE is SSI, not 2PL: reads stay non-blocking until commit-time checks. Details: SSI vs Snapshot Isolation.

MySQL InnoDB

Do not assume “REPEATABLE READ” is portable. Quote the engine in the interview.

flowchart TB
  app["Application"]
  db["Database"]
  app -->|BEGIN| db
  app -->|SET TRANSACTION| db
  app -->|SELECT balance| db
  db -->|committed row| app
  db -->|same version or| app
  app -->|COMMIT or| db

When to choose each level

READ UNCOMMITTED. Almost never on a primary. If you want dirty-cheap scans, use a replica and accept lag, not RU on the writer.

READ COMMITTED. Default for most OLTP where a second SELECT seeing a newer committed price is fine. Combine with atomic UPDATE … WHERE predicates or app version columns when a single row must not be lost-updated.

REPEATABLE READ. Postgres: multi-statement SI — reports, same-row lost-update protection, still not multi-row invariants. InnoDB: default; know your gap locks on WHERE id > 100.

SERIALIZABLE / SSI. Invariants that span rows or predicates: seat maps, “at least one on call,” multi-account “sum ≥ 0.” Expect aborts (Postgres) or lock waits (InnoDB). Keep transactions short.

Distributed systems often run weaker isolation at each node and compensate in the app (idempotency, sagas). That is a choice, not an accident — name the anomaly you accepted.

Practical patterns

Per-transaction SET. Issue isolation after BEGIN / before the first query. Changing the level mid-transaction is undefined. Do not mix levels in one session’s in-flight txn.

BEGIN;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
-- first statement takes the SI snapshot (Postgres)
SELECT balance FROM accounts WHERE id = 42;
COMMIT;

Optimistic concurrency on RC. UPDATE t SET …, version = version + 1 WHERE id = $1 AND version = $2. Zero rows means lost update — retry. This does not close write skew.

Compensating transactions. If you must stay on RC for throughput, make rare anomalies idempotent to undo: reverse a double-book with a unique seat constraint plus a refund path, rather than pretending isolation will save you.

Bulk load. SERIALIZABLE on a million-row insert serializes work you do not need. COPY / lower isolation, then validate.

In-memory isolation (run this)

No network, no driver. A tiny heap plus per-statement vs per-txn snapshots. I/O contract: isolation name in, visible values and whether both writers commit out.

Press Run. Snippets must be self-contained — no network, files, or native modules.

Interview Q&A

Pitfalls

Go Deeper

• PostgreSQL — Transaction Isolation
• PostgreSQL — Serializable
• MySQL 8.0 — InnoDB isolation
• Cahill, Röhm, Fekete — Serializable Snapshot Isolation
• Wikipedia — Isolation (database systems)

Cluster: MVCC hub · SSI vs SI · FOR UPDATE · VACUUM · Phantoms vs write skew

One-line takeaway: isolation is an engine-specific contract — ANSI names hide SI vs gap locks, and write skew sits outside the phenomena list until SSI, locks, or constraints close it.