Isolation Levels and Anomalies — DBMS

Summary#

Isolation level is the contract the DBMS offers about what intermediate state concurrent transactions can observe in each other. The SQL standard names four levels — READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, SERIALIZABLE — each progressively stronger and progressively more expensive. Real engines also ship SNAPSHOT (an MVCC-flavored REPEATABLE READ). Each level is defined by which anomalies it prevents: dirty read, non-repeatable read, phantom read, and write skew. The standard’s level names are portable; the implementation cost and the exact guarantees vary by engine.

The pragmatic takeaways most teams converge on: Postgres defaults to READ COMMITTED, MySQL InnoDB defaults to REPEATABLE READ (implemented via MVCC, so phantoms are also prevented), SQL Server defaults to READ COMMITTED with optional SNAPSHOT. Use the default unless you can name the anomaly you’re protecting against; promote to SERIALIZABLE for transactions that read-then-write based on what they read (the write-skew pattern); and never rely on isolation level alone for correctness when the contention is “this row must reflect the latest value” — that’s what SELECT ... FOR UPDATE is for.

Why it matters#

Isolation level is the lever between “correct under all interleavings” and “fast under most realistic workloads”. Pick too weak and you ship subtle bugs that only surface under load; pick too strong and the database burns CPU and aborts transactions under contention. Most production systems get this wrong in one of two directions: developers assume the default is SERIALIZABLE when it’s READ COMMITTED, or they crank to SERIALIZABLE everywhere and pay 10x the latency.

In interviews, this comes up as the obligatory follow-up to ACID. The answers that signal real experience: name the four anomalies in plain English; know which level your favourite engine defaults to and why; recognise write skew when shown the doctors-on-shift example; understand the difference between snapshot isolation and serializability (snapshot prevents the first three anomalies but not write skew — Postgres’s SERIALIZABLE layers SSI on top to detect write-skew cycles).

How it works#

The four anomalies#

Dirty read. Transaction T1 reads a row written by T2 before T2 commits. If T2 rolls back, T1 has based decisions on a value that never existed.

T1: SELECT balance FROM accounts WHERE id = 1;  -- reads 1100 (T2's uncommitted write)
T2: UPDATE accounts SET balance = 1100 WHERE id = 1;
T2: ROLLBACK;   -- balance is back to 1000, but T1 acted on 1100

Non-repeatable read. T1 reads the same row twice in the same transaction and gets different values because T2 committed an UPDATE in between.

T1: SELECT balance FROM accounts WHERE id = 1;  -- 1000
T2: UPDATE accounts SET balance = 1100 WHERE id = 1; COMMIT;
T1: SELECT balance FROM accounts WHERE id = 1;  -- 1100, different

Phantom read. T1 runs the same range query twice and the second result includes rows that didn’t exist before because T2 inserted matching rows.

T1: SELECT COUNT(*) FROM orders WHERE total > 100;  -- 42
T2: INSERT INTO orders ... ; COMMIT;
T1: SELECT COUNT(*) FROM orders WHERE total > 100;  -- 43, phantom appeared

Write skew. Two transactions each read a set of rows, each decides their write is consistent with that set, but together they violate a multi-row constraint.

-- Both doctors check: is anyone else on call?
T1: SELECT count(*) FROM oncall WHERE shift = today;  -- 2 (both on call)
T2: SELECT count(*) FROM oncall WHERE shift = today;  -- 2
T1: UPDATE oncall SET shift = NULL WHERE name = 'alice';  -- alice goes off
T2: UPDATE oncall SET shift = NULL WHERE name = 'bob';    -- bob goes off
-- Both commit. Nobody is on call.

The standard hierarchy#

Level	Dirty read	Non-repeatable	Phantom	Write skew
READ UNCOMMITTED	possible	possible	possible	possible
READ COMMITTED	prevented	possible	possible	possible
REPEATABLE READ	prevented	prevented	possible*	possible
SNAPSHOT (MVCC)	prevented	prevented	prevented	possible
SERIALIZABLE	prevented	prevented	prevented	prevented

*MySQL InnoDB’s REPEATABLE READ uses MVCC and does prevent phantoms in most cases; the standard’s lock-based RR does not.

How engines implement it#

Lock-based (SQL Server default, older systems). Two-phase locking: shared locks on reads, exclusive locks on writes, held to end of transaction. Stronger isolation means longer-held locks and more contention.

Snapshot / MVCC-based (Postgres, MySQL InnoDB, Oracle). Each transaction reads from a consistent snapshot — a logical “version” of the database as of when the snapshot was taken. Writers create new row versions; readers see versions consistent with their snapshot. Readers never block writers and writers never block readers. The cost is keeping old versions around until no snapshot needs them (the vacuum problem).

Serializable Snapshot Isolation (Postgres SERIALIZABLE). MVCC + a runtime dependency tracker that detects read-write cycles and aborts one transaction in any cycle, producing serializable behavior without 2PL’s locking overhead. The most elegant published implementation.

Setting the level#

-- For a single transaction:
BEGIN;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
-- ... statements ...
COMMIT;

-- For a session (Postgres):
SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL REPEATABLE READ;

Promoting a transaction to SERIALIZABLE means your application code must handle the serialization failure error — the engine will sometimes abort a transaction it detects in a dependency cycle, and you have to retry.

Variants and trade-offs#

READ COMMITTED (Postgres default) — readers see only committed data; each statement gets a fresh snapshot. Allows non-repeatable reads and phantoms inside one transaction. Cheap, no aborts under contention, good for typical OLTP. Wrong choice when one transaction reads-then-writes based on what it read.

SERIALIZABLE — every transaction behaves as if it ran alone in some serial order. Prevents all four anomalies. Costs: longer-held locks (in lock-based engines) or serialization failures (in SSI engines like Postgres). Use for money transfers, inventory decrements, and any read-modify-write where correctness matters more than throughput.

Engine-by-engine defaults and behaviors:

Postgres: default READ COMMITTED. REPEATABLE READ is true snapshot isolation. SERIALIZABLE is SSI — no extra locks, but transactions may abort with 40001 and need retry.
MySQL InnoDB: default REPEATABLE READ, implemented via MVCC + gap locks; prevents phantoms in practice. SERIALIZABLE adds shared locks on every read.
SQL Server: default READ COMMITTED with locking. READ_COMMITTED_SNAPSHOT database option switches to MVCC-style read-committed. SNAPSHOT and SERIALIZABLE are separate options.
Oracle: default READ COMMITTED, statement-level read consistency via MVCC. SERIALIZABLE is snapshot isolation (so write skew is possible despite the name — a famous historical quirk).

The pragmatic recipe most teams converge on

Default to your engine’s default isolation level for the bulk of transactions — it’s tuned for typical OLTP and avoids most aborts. Promote specific transactions to SERIALIZABLE when they read-then-write based on what they read (transfers, decrements, “first one wins” patterns). For hot rows where you need pessimistic exclusion, use SELECT ... FOR UPDATE at the default isolation level — that’s cheaper than serializing the whole transaction. Wrap every SERIALIZABLE transaction in a retry loop because aborts are part of the contract. And measure: serialization-failure rate is a real metric, not a corner case.

When this is asked in interviews#

Every database round. Common sequence:

“Name the SQL isolation levels.” READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, SERIALIZABLE.
“What anomaly does each prevent?” Dirty / non-repeatable / phantom / write skew, in the order the table above shows.
“What’s the default in Postgres / MySQL?” READ COMMITTED / REPEATABLE READ.
“Give an example of write skew.” The doctors-on-shift or two-account-overdraft example.
“How does Postgres implement SERIALIZABLE without 2PL?” SSI: tracks read-write dependencies; aborts one transaction in any cycle.
“You designed a transfer between accounts at READ COMMITTED. What can go wrong?” Two concurrent transfers can both read enough balance, both write, both leave the account negative. Either escalate to SERIALIZABLE or use SELECT ... FOR UPDATE.

System-design loops use this when discussing payments, inventory, or any read-modify-write workflow — the candidate should reach for SERIALIZABLE or row locks, not just “wrap it in a transaction”.