Notestransactions🌱 seedling
Two-Phase Locking
Also: 2PL · Strict Two-Phase Locking · S2PL · Predicate Lock · Index-Range Lock · Next-Key Lock · Pessimistic Concurrency Control
Readers take shared locks, writers take exclusive locks, and every lock is held until the transaction commits or aborts. Readers block writers and writers block readers, which forbids the read-write conflicts behind write skew.
Why it exists
For decades it was the way to get Serializability. Row locks alone stop dirty writes; extending them to reads, held to the end, stops anyone changing the premise of a transaction's decision while it runs.
How it works
- Shared (S) lock to read: many holders allowed. Exclusive (X) lock to write: no other holder. A reader upgrading to write must wait for other readers.
- Growing phase acquires locks; shrinking phase releases them. Strict 2PL holds exclusive locks until commit; strong strict 2PL, what databases actually use, holds every lock until commit or abort.
- Phantom Reads: a predicate lock on the search condition blocks inserts that would match. Real engines approximate it with index-range (next-key) locks, or a table lock if no index fits.
- Lock cycles cause Deadlocks, which the database detects and breaks by aborting a victim.
Trade-offs
| Gains | Costs |
|---|---|
| serializable, including phantoms | readers and writers block each other |
| wastes little work under high contention | queues and unstable tail latency |
| no validation step at commit | deadlocks; long reads block writers |
In practice
- MySQL InnoDB
SERIALIZABLEconverts plainSELECTs toSELECT … FOR SHARE(with autocommit off) and uses next-key and gap locks for ranges. - SQL Server
SERIALIZABLEuses key-range locks. Spanner uses 2PL with wound-wait for read-write transactions.
Pitfalls
- A long-running report under 2PL blocks every writer to the rows it read.
- Missing indexes turn range locks into table locks.
Connections
- Part of
- Serializability
- Contains
- Deadlock
- Contrasts with
- Actual Serial ExecutionMVCCOptimistic Concurrency ControlSerializable Snapshot Isolation
- Related
- Phantom Read
Recall
These cards are also in your review deck, scheduled with spaced repetition.