Isolation Levels and Locking

1Isolation is a menu of accidents

Isolation levels are named settings that say which of the accidents from the last lesson a session is still allowed to see. They are not extra ACID letters. They are how strictly the engine keeps T2 from seeing T1's in-flight or newly committed work. Stronger levels block more accidents and usually hold locks longer or keep a longer snapshot.

The ANSI SQL menu, weakest to strongest: READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, SERIALIZABLE. Each row of the next concepts adds one ban. We will keep using Tea stock and the tea-sales count so the same two stories show what each level still permits.

Figure. Weakest at the top. Each step down bans one more accident. SERIALIZABLE bans all three.

2READ UNCOMMITTED still allows dirty reads

READ UNCOMMITTED is the weakest setting: T2 may read a value T1 has written but not committed. At the kiosk, T1 is restocking Tea and writes 50. T2, under READ UNCOMMITTED, may see 50 and print a "we have plenty" sign. T1 then aborts; stock is 8. The sign lied. That is the dirty-read row of the menu, allowed on purpose at this level.

Almost no production cashier should sit here. The level exists so you can name the accident, and so engines that skip locking on reads have a standard name for that choice. The next level's whole job is to ban this one case.

Figure. READ UNCOMMITTED lets T2 see T1's unfinished 50. After T1 aborts, that 50 was never real.

The 50 that never committed

1. T1WRITE stock = 50; not committed.
2. T2 at RUREAD stock → 50.
3. T1 abortstock = 8. T2's 50 was never a fact.

3READ COMMITTED bans dirty reads

READ COMMITTED says T2 only sees values whose writer has committed. The unfinished 50 is invisible; T2 waits or sees the old 8. Dirty reads are gone. Non-repeatable reads remain: T2 reads hostel A, T1 commits Asha's move to C, T2 reads again and sees C. Both values were committed at the moment they were read.

This is the default in several engines because "never show me a write that might vanish" is the minimum a cashier needs, and it still lets T1's committed move become visible on T2's next read. If T2 must see the same Asha row twice, we need the next level.

Figure. Two committed answers for the same Asha row. READ COMMITTED allows this; REPEATABLE READ will not.

Same Asha row, two committed answers

1. T2 first readhostel = A (committed).
2. T1 commitsUPDATE student SET hostel = 'C' WHERE student_id = 1; COMMIT.
3. T2 second readhostel = C. Not dirty — both reads saw a committed value.

4REPEATABLE READ freezes seen rows

REPEATABLE READ says a row T2 already read will not change under T2's feet. Asha stays in hostel A for the rest of T2, even if T1 commits a move to C — T2's second read still sees A (or T1 waits). Dirty and non-repeatable reads are gone.

Phantoms remain. T2 counts tea sales and gets 2. T1 commits sale 104 (Meera buys another tea). T2 counts again and gets 3. No old row changed; a new row appeared in the search. That new matching row is the phantom REPEATABLE READ still allows. SERIALIZABLE is the setting that also freezes the search.

Figure. Same WHERE, one extra tea row the second time. REPEATABLE READ still allows that phantom.

Count teas twice

1. T2 count 1WHERE item_id = 11 → 2 rows (101, 103).
2. T1 commitsINSERT sale 104, Meera, Tea, qty 1.
3. T2 count 23 rows. The new 104 is a phantom.

5SERIALIZABLE bans the phantom too

SERIALIZABLE is the strongest ANSI level: the result must match some serial order of the transactions. Dirty, non-repeatable and phantom reads are all banned. T2's tea count stays 2 until T2 finishes, or T1's insert waits. Asha's hostel stays A. Uncommitted 50 is invisible.

It is not free. The engine holds range locks or keeps a snapshot that includes "there is no third tea sale". That is why cashiers doing a stock-take use it, and why a busy lunch rush might sit at READ COMMITTED and accept that a second count can grow.

Figure. Under SERIALIZABLE the tea search is frozen. Sale 104 waits, or T2 keeps seeing two rows.

6Two-phase locking

Locks are one way an engine implements those levels. Two-phase locking (2PL) has a growing phase — acquire locks, never release — then a shrinking phase — release locks, never acquire. That discipline produces conflict-serializable schedules. It does not, by itself, prevent deadlock: two cashiers can each hold Tea and wait for Samosa.

Strict 2PL holds exclusive locks until commit (or abort). That extra hold stops T2 reading a write T1 might still abort — it is how you get recoverability and a clean READ COMMITTED / stronger story, at the cost of T2 waiting until T1 finishes. Growing then shrinking is the shape; hold-until-commit is the stricter variant.

Figure. Two phases, one direction. Crossing back — unlocking then locking again — is not 2PL.

2PL on Tea stock

1. GrowT1 locks stock, writes 50. No unlocks yet.
2. Commit / shrinkT1 commits, then releases. Strict 2PL releases only now.
3. T2May lock and read 8 or 50 only after that release — never the in-flight 50.