Locking & Two-Phase Locking
Press Next → or use ← → arrow keys
The Story — The Library Reserve Room
The DBMS subsystem that keeps parallel transactions from violating isolation. The most widely used mechanism is lock-based concurrency control.
Two Lock Types & When They Clash
| Tj requests ↓ · Ti holds → | S-lock | X-lock |
|---|---|---|
| S-lock | ✓ GRANTED | ✗ WAIT |
| X-lock | ✗ WAIT | ✗ WAIT |
"Readers share, writers are exclusive." Only S–S is compatible; every other pair forces the requester to wait.
Why "Unlock When Done" Isn't Enough
The naive rule — lock before access, unlock when finished — breaks if you release too early.
| t | T1 | T2 |
|---|---|---|
| t1 | lock-X(A) | |
| t2 | R(A)=100 | |
| t3 | W(A)=150 | |
| t4 | unlock(A) | |
| t5 | lock-X(A) | |
| t6 | R(A)=150 | |
| t7 | W(A)=200 | |
| t9 | ABORT! |
T1 unlocked at t4 before committing. T2 read the uncommitted 150 and built on it. When T1 aborts, A must revert to 100 — so T2 must abort too. Early release broke isolation.
A disciplined lock lifecycle — Two-Phase Locking — that forbids acquiring any new lock once you've started releasing.
Two-Phase Locking — The Hill
Growing: acquire locks, release none — the set only grows. Lock point: the instant the last lock is acquired (the peak). Shrinking: release locks, acquire none — the set only shrinks. Once you release any lock, you may never acquire another.
Why 2PL Works
If every transaction in a schedule follows 2PL, the schedule is conflict serializable. And the equivalent serial order is simply the order of the transactions' lock points.
It guarantees serializability — but still allows cascading aborts and deadlocks. That's why stricter variants exist.
Does This Transaction Follow 2PL?
Growing: the three locks. Lock point: after lock-S(C). Shrinking: the three unlocks. No lock acquired after the first unlock.
unlock(A) starts shrinking — but then lock-S(C) acquires a new lock. A lock was taken after a release.
Scan left to right: the moment you see the first unlock, there must be no lock after it. One late lock breaks 2PL.
Four Variants of 2PL
| Variant | Conflict serializable | Cascadeless | Strict | Deadlock-free |
|---|---|---|---|---|
| Basic 2PL | ✓ | ✗ | ✗ | ✗ |
| Strict 2PL | ✓ | ✓ | ✗ | ✗ |
| Rigorous 2PL | ✓ | ✓ | ✓ | ✗ |
| Conservative 2PL | ✓ | ✓ | ✓ | ✓ |
When Does Each Variant Release Locks?
The bar shows how long locks are held. Basic releases as soon as it can; Strict holds X-locks to commit; Rigorous holds everything to commit; Conservative grabs everything up front. Longer bars = safer recovery, less concurrency.
Strict 2PL in Action
T1 transfers ₹500 from A to B; T2 reads A + B — under Strict 2PL.
| t | T1 | T2 | Locks held |
|---|---|---|---|
| t1 | lock-X(A) | T1: X(A) | |
| t2–3 | R(A)=1000 · W(A)=500 | ||
| t4 | lock-X(B) | T1: X(A), X(B) | |
| t5 | lock-S(A) — WAIT | T2 blocked by T1's X(A) | |
| t7–8 | R(B)=2000 · W(B)=2500 | waiting… | |
| t9 | Commit → unlock all | T1 releases X(A), X(B) | |
| t10–11 | lock-S(A) GRANTED · R(A)=500 | T2: S(A) | |
| t12–13 | lock-S(B) · R(B)=2500 | T2: S(A), S(B) | |
| t14 | Commit → unlock all | clean |
T1 holds its X-locks until commit (t9), so T2's read at t5 must wait. T2 then reads only committed values: 500 + 2500 = 3000, the original total. No cascading abort, no lost update.
The Dark Side — Deadlock
| t | T1 | T2 |
|---|---|---|
| t1 | lock-X(A) | |
| t2 | lock-X(B) | |
| t3 | lock-X(B) — WAIT | |
| t4 | lock-X(A) — WAIT |
A cycle in the wait-for graph means deadlock. The DBMS breaks it by aborting a victim (the cheapest transaction).
Four Ways to Handle Deadlock
Wait-Die: older requests younger's lock → older waits; younger requests older's → younger dies. Wound-Wait: older requests younger's → older wounds (kills younger); younger requests older's → younger waits. In both, the younger aborts and retries with its original timestamp.
Academic vs Industry Lenses
PostgreSQL, MySQL/InnoDB, SQL Server combine both: reads use MVCC snapshots (no read locks, so readers never block writers), while writes take row-level X-locks held until commit — i.e. Strict 2PL.
Rapid-Fire Concept Check
| Question | Answer |
|---|---|
| The two lock types | Shared (S) and Exclusive (X) |
| The only compatible pair | S–S (all others block) |
| What 2PL guarantees | Conflict serializability |
| The two phases | Growing (acquire) then Shrinking (release) |
| The lock point | Instant the last lock is acquired |
| What Strict 2PL adds | Hold X-locks to commit → cascadeless |
| What Rigorous 2PL adds | Hold ALL locks to commit → strict |
| Which variant is deadlock-free? | Conservative 2PL |
| Cycle in wait-for graph means | Deadlock |
| Most common engine approach today | Strict 2PL + MVCC hybrid |
Golden Rules of Locking & 2PL
Grow, Peak, Shrink
Locks enforce isolation: readers share, writers are exclusive. Two-Phase Locking disciplines when they're released — grow, hit the lock point, then only shrink — which guarantees conflict serializability. Strict and Rigorous variants hold write (or all) locks to commit for clean recovery; the price is deadlocks, which the DBMS detects and breaks by aborting a victim.
Once a transaction gives up its first lock, it can never take another — that single rule is what makes 2PL serializable. Real engines use Strict 2PL for writes and MVCC for reads.
🔒 End of tutorial · Press ← to review, or click Restart