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 SERIALIZABLE converts plain SELECTs to SELECT … FOR SHARE (with autocommit off) and uses next-key and gap locks for ranges.
  • SQL Server SERIALIZABLE uses 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.

Recall

These cards are also in your review deck, scheduled with spaced repetition.