What are the four SQL isolation levels and what anomalies does each prevent?

How to think about it

The senior-level signal here isn’t naming the four levels — it’s explaining which anomaly each one prevents and knowing when SERIALIZABLE is the right call in production. Start by grounding each level in a concrete failure scenario.

Isolation levels let you trade correctness for concurrency. Most applications run at READ COMMITTED (PostgreSQL default) and seldom need to go higher — understanding when they do is the real test.

Anomalies defined — picture the failure

Build the intuition before memorising the table. Each anomaly is a real bug that can happen without the right level:

Anomaly	What goes wrong
Dirty read	You read uncommitted data that is later rolled back — your report is based on data that never officially existed
Non-repeatable read	You read the same row twice in one transaction and get different values — another transaction committed a change between your two reads
Phantom read	You re-run a range query and get different rows — another transaction inserted or deleted matching rows between your two reads
Lost update	Two transactions read-modify-write the same row; one overwrites the other’s change silently

Practical examples — walk through each scenario

These are the scenarios to sketch on a whiteboard. Each one shows exactly why the lower level is insufficient:

-- Dirty read (READ UNCOMMITTED)
-- Session A: BEGIN; UPDATE orders SET status='shipped' WHERE id=1;  -- not committed yet
-- Session B: SELECT status FROM orders WHERE id=1;  -- sees 'shipped'
-- Session A: ROLLBACK;
-- Session B read data that never existed.

-- Non-repeatable read (READ COMMITTED)
-- Session A: BEGIN;
--   SELECT amount FROM orders WHERE id=1;   -- returns 100
-- Session B: UPDATE orders SET amount=200 WHERE id=1; COMMIT;
-- Session A:
--   SELECT amount FROM orders WHERE id=1;   -- returns 200 — different!

-- Phantom read (REPEATABLE READ at standard SQL level)
-- Session A: BEGIN;
--   SELECT COUNT(*) FROM orders WHERE status='pending';  -- returns 5
-- Session B: INSERT INTO orders (status) VALUES ('pending'); COMMIT;
-- Session A:
--   SELECT COUNT(*) FROM orders WHERE status='pending';  -- returns 6 — phantom row!

-- Setting isolation level in PostgreSQL
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
  -- All reads are consistent as of the transaction start
  -- Any conflict will cause a serialization failure (error 40001) -> retry
COMMIT;

When to use SERIALIZABLE

Use it when correctness is non-negotiable and the operation cannot tolerate any interleaving — classic example: seat reservation or inventory decrement where two concurrent transactions must not both believe a seat is available.

-- Serializable handles this correctly
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
  SELECT qty FROM inventory WHERE sku = 'X' FOR UPDATE;  -- locks the row
  UPDATE inventory SET qty = qty - 1 WHERE sku = 'X' AND qty > 0;
COMMIT;

Performance trade-offs

Higher isolation levels extend the time rows or key ranges are locked (or snapshot data is retained in MVCC). SERIALIZABLE under heavy write concurrency can cause high abort rates — design retry logic before choosing it. Most OLTP workloads run comfortably at READ COMMITTED with explicit SELECT ... FOR UPDATE where needed.

The key insight to land in an interview: picking isolation level is a correctness vs throughput dial. You don’t default to SERIALIZABLE for everything — you pick the lowest level that prevents the specific anomaly your use case can’t tolerate.