Isolation Levels

Pradeep P
3 days ago
3 min read

Series: Modern System Design · Layer 4 — Data

Layer 4 · Post 3 of 11

← Previous: Transactions → Next: Optimistic vs Pessimistic Locking

Layer 4 — Data · Post 42 of 88

Isolation levels control how much one transaction can see of another still in flight. Higher isolation means fewer surprises and more waiting.

What you'll learn

Dirty reads, non-repeatable reads, and phantom reads — and which level allows which
What Postgres and MySQL actually default to (not SERIALIZABLE)
How to pick a level in an interview instead of reciting the SQL standard

The idea in one minute

Isolation is the I in ACID, and it is a dial, not a boolean.

Two transactions run at once. How much of T2's work can T1 see before T2 commits? How stable is T1's view if it reads the same row twice?

The SQL standard names four levels, from leaky to strict:

Read Uncommitted: you can see uncommitted writes (dirty reads). Almost nobody wants this for money.
Read Committed: you only see committed data. The same SELECT twice can return two answers if someone committed in between.
Repeatable Read: your snapshot of existing rows stays stable. New rows that match your WHERE (phantoms) may still appear, depending on the engine.
Serializable: the result looks as if transactions ran one at a time. The database may abort you and ask you to retry.

T1: SELECT qty FROM inventory WHERE sku = 'A' --> 5 T2: UPDATE inventory SET qty = 0 WHERE sku = 'A'; COMMIT T1: SELECT qty ... again Read Committed: 0 Repeatable Read / Serializable: still 5 (until T1 commits)

Why it matters

ACID without a named isolation level is incomplete. "We use Postgres, so we are safe" is false: Postgres defaults to Read Committed. MySQL InnoDB defaults to Repeatable Read. Those are different products.

Interviewers love a lost-update or write-skew story. Isolation is how you explain them. Locking (next post) is how you close some of them without jumping straight to SERIALIZABLE.

How it works

Engines implement isolation with locks, MVCC snapshots, or both.

Postgres MVCC: each transaction sees a snapshot. Read Committed takes a new snapshot per statement. Repeatable Read and Serializable keep one snapshot for the whole transaction. Serializable adds SSI (serializable snapshot isolation): it watches for dangerous read/write patterns and aborts one transaction rather than locking the world.

MySQL Repeatable Read plus gap locks is why people say InnoDB is "stricter" on range scans than the standard's Repeatable Read.

Anomalies you should be able to name:

Dirty read: see uncommitted data. Forbidden from Read Committed up.
Non-repeatable read: same row, two values. Allowed under Read Committed.
Phantom: a range grows or shrinks. The usual reason people reach for Serializable or explicit locks.
Write skew: two transactions read overlapping state, each writes a different row, both commit, invariant broken. Classic Serializable / constraint / lock problem.

A simple example

Two doctors on call. Rule: at least one must remain. Each transaction reads on_call = true for both, each sets themselves off call, each commits. Read Committed and Repeatable Read often allow this. Serializable (or a constraint, or SELECT FOR UPDATE on the pair) does not.

Common mistakes

SERIALIZABLE on everything. Abort rate and retries become the product. Use it for the few invariants that need it.

Ignoring retries. Serializable and optimistic schemes fail transactions on purpose. Your app must retry; a 500 to the user is not isolation.

Assuming Repeatable Read means "no phantoms" on every engine. Check Postgres vs InnoDB. Cite the engine.

How this shows up in real systems

Postgres: READ COMMITTED default; SERIALIZABLE via SSI; SET TRANSACTION ISOLATION LEVEL.
MySQL InnoDB: Repeatable Read default; gap locks; SELECT ... FOR UPDATE.
SQL Server: more lock-heavy historically; snapshot isolation is opt-in.
DynamoDB: item-level; not these SQL names. Do not pretend they map 1:1.