Transaction isolation levels — READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, SERIALIZABLE

WHY do isolation levels exist?

The "gold standard" is serializability: the concurrent result must equal some serial execution order. But locking everything to guarantee that is slow. So the SQL standard defines 4 levels, each banning a specific set of read anomalies.

The three classic anomalies (this is the 20% that explains 80%)

The 4 levels and what each BANS

Formula The standard table (memorize this)

Level	Dirty read	Non-repeatable read	Phantom read
READ UNCOMMITTED	✅ possible	✅ possible	✅ possible
READ COMMITTED	❌ prevented	✅ possible	✅ possible
REPEATABLE READ	❌ prevented	❌ prevented	✅ possible*
SERIALIZABLE	❌ prevented	❌ prevented	❌ prevented

*Phantoms are allowed by the SQL standard at REPEATABLE READ, but some engines (e.g. MySQL InnoDB via next-key locks, PostgreSQL via MVCC snapshots) prevent them anyway.

Figure — Transaction isolation levels — READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, SERIALIZABLE

HOW it's implemented (so the table isn't magic)

Worked example Dirty read, step by step

T2: UPDATE accounts SET bal = 0 WHERE id=1;   -- not committed
T1: SELECT bal FROM accounts WHERE id=1;       -- reads 0  (DIRTY)
T2: ROLLBACK;                                   -- bal is actually back to old value
T1: acts on 0 that never existed → BUG

Why this step? At READ UNCOMMITTED, T1's read does not require T2's lock to be released, so it peeks at uncommitted state. Raise to READ COMMITTED → T1 would have blocked or read the committed old value, never 0.

Worked example Non-repeatable read at READ COMMITTED

T1: SELECT bal FROM accounts WHERE id=1;  -- 100
T2: UPDATE accounts SET bal=50 WHERE id=1; COMMIT;
T1: SELECT bal FROM accounts WHERE id=1;  -- 50  (DIFFERENT!)

Why this step? READ COMMITTED takes a fresh snapshot per statement, so T1's second read sees T2's committed change. Fix: REPEATABLE READ → T1 keeps its start-of-transaction snapshot, both reads return 100.

Worked example Phantom read at REPEATABLE READ (standard behavior)

T1: SELECT count(*) FROM users WHERE age>30;  -- 5
T2: INSERT INTO users(age) VALUES (40); COMMIT;
T1: SELECT count(*) FROM users WHERE age>30;  -- 6  (PHANTOM)

Why this step? REPEATABLE READ (per the standard) protects rows you already read, not the predicate's membership. A brand-new row matches the WHERE and appears. Fix: SERIALIZABLE → a range/predicate lock on age>30 blocks T2's insert until T1 finishes.

Forecast-then-Verify drill

Recall Before reading: predict the outputs

Two transactions at READ COMMITTED. T1 reads bal (=100). T2 commits bal=200. T1 reads again. Forecast: second read = ? · Is this an anomaly, and which?

Verify: second read = 200. This is a non-repeatable read — allowed at READ COMMITTED, banned at REPEATABLE READ.

Common mistakes (Steel-man → fix)

Recall Feynman: explain to a 12-year-old

Imagine the database is a shared notebook and people scribble in it at the same time.

READ UNCOMMITTED: you read someone's pencil scribble before they decide to keep it — they might erase it. Risky.
READ COMMITTED: you only read things people have inked in for real. But if you look at the same line twice, someone might have re-inked it in between, so it changes.
REPEATABLE READ: you take a photo of your page at the start and read from the photo, so lines you already looked at never change under you.
SERIALIZABLE: it's as if everyone takes strict turns — the final notebook looks exactly like if people went one by one. No surprises at all, but you wait more.

Flashcards

What are the three classic read anomalies?

Dirty read, non-repeatable read, phantom read.

Which isolation level allows dirty reads?

READ UNCOMMITTED (only).

Difference between non-repeatable read and phantom read?

Non-repeatable = an existing row's value changes between two reads; phantom = the set of rows matching a predicate changes (rows inserted/deleted).

Which level prevents non-repeatable reads but (per the standard) still allows phantoms?

REPEATABLE READ.

Order the 4 levels weakest → strongest.

READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, SERIALIZABLE.

What does SERIALIZABLE actually guarantee?

The concurrent result is equivalent to SOME serial (one-at-a-time) execution order — not literal serial execution.

In MVCC, what's the difference between READ COMMITTED and REPEATABLE READ snapshots?

READ COMMITTED takes a fresh snapshot per statement; REPEATABLE READ takes one snapshot at transaction start and reuses it.

How are phantoms prevented under locking?

Range/predicate (next-key) locks block inserts into a queried range.

Default isolation level in PostgreSQL vs MySQL InnoDB?

PostgreSQL = READ COMMITTED; MySQL InnoDB = REPEATABLE READ.

Why not just use SERIALIZABLE everywhere?

More blocking, deadlocks, and serialization-failure retries → lower throughput; use the lowest level that prevents anomalies your app cares about.

Connections

ACID properties — the I in ACID is exactly this isolation.
MVCC (Multi-Version Concurrency Control) — snapshot mechanism behind READ COMMITTED / REPEATABLE READ.
Two-Phase Locking (2PL) — lock-based path to serializability.
Serializable Snapshot Isolation (SSI) — how PostgreSQL does SERIALIZABLE optimistically.
Deadlocks — side effect of holding locks at higher levels.
Write skew anomaly — anomaly that snapshot isolation allows but SERIALIZABLE prevents.

Concept Map

Hinglish (regional understanding)

Intuition Hinglish mein samjho

Dekho, database mein ek saath bahut saari transactions chal rahi hoti hain. Agar woh same rows ko touch karein bina kisi control ke, toh galat results aa sakte hain. Isolation levels ek dial hai jahan tum decide karte ho ki kitni "weirdness" allow karni hai concurrency badhane ke liye. Teen famous problems hain: dirty read (kisi ki uncommitted scribble padh li, jo baad mein rollback ho gayi), non-repeatable read (ek hi row do baar padhi, value badal gayi), aur phantom read (ek WHERE query do baar chalayi, naye rows aa gaye ya gayab ho gaye).

Char levels hain, weakest se strongest: READ UNCOMMITTED → READ COMMITTED → REPEATABLE READ → SERIALIZABLE. Har step neeche jaate hi ek anomaly band ho jaati hai. READ UNCOMMITTED sab kuch allow karta hai. READ COMMITTED sirf committed data dikhata hai (dirty read gone). REPEATABLE READ ek baar ka snapshot pakad leta hai, toh jo rows tumne padhi woh nahi badlengi (non-repeatable read gone). SERIALIZABLE poori tarah safe — result aisa hoga jaise transactions ek-ek karke chali ho (phantom bhi gone).

Yaad rakhne ka mnemonic: U-C-R-S order, aur D-N-P (Dirty, Non-repeatable, Phantom) ek-ek karke peel off hoti hain. Ek important baat: SERIALIZABLE ka matlab yeh nahi ki transactions literally line mein chalti hain — bas final result kisi serial order ke barabar hota hai, andar concurrency rehti hai (PostgreSQL toh SSI use karke conflicting transactions ko abort kar deta hai, jise tumhe retry karna padta hai).

Practical tip: hamesha SERIALIZABLE mat lagao — woh slow karta hai, deadlocks aur retries badhata hai. App ko jo anomaly actually nuksan karti hai, bas usse rokne wala lowest level choose karo. PostgreSQL default READ COMMITTED hai, MySQL InnoDB default REPEATABLE READ — yeh interview mein puchte hain!

Transaction isolation levels — READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, SERIALIZABLE

WHY do isolation levels exist?

The three classic anomalies (this is the 20% that explains 80%)

The 4 levels and what each BANS

HOW it's implemented (so the table isn't magic)

Forecast-then-Verify drill

Common mistakes (Steel-man → fix)

Flashcards

Connections

Concept Map

Hinglish (regional understanding)

Go deeper — visual, from zero

Test yourself — Databases

Connections