Two-Phase Locking

The Protocol

Two phases, strictly ordered per transaction:

1. Growing phase — acquire locks as needed: SHARED (S) for reads you want repeatable, EXCLUSIVE (X) for writes. S↔S compatible; anything involving X conflicts. In SERIALIZABLE mode (InnoDB, SQL Server) plain SELECTs take S locks; at weaker levels only SELECT ... FOR UPDATE/SHARE and DML lock.
2. Shrinking phase — release everything, but only at commit/abort (this "hold everything to the end" variant is Strong Strict 2PL, what production engines implement). No new locks after first release — that ordering is what guarantees serializability.

Holding write locks to commit is also what gives you no-dirty-reads/writes at Read Committed Isolation; 2PL extends the same idea to reads, closing skew and non-repeatable reads by force of blocking.

Predicate Locks and Next-Key Locking

Row locks don't cover rows that don't exist yet — the phantom hole from Write Skew and Phantom Writes. True predicate locks (lock the condition) are theoretically clean but expensive to evaluate, so engines approximate with index-range locks: InnoDB's next-key lock = record lock + gap lock on the index interval. SELECT ... WHERE age BETWEEN 20 AND 30 FOR UPDATE locks the index gaps covering that range, so a concurrent insert of age=25 blocks until commit. Cost: reduced concurrency on hot ranges and the infamous next-key deadlock patterns on unindexed predicates (which degrade toward table-ish locking — index your predicates).

Deadlocks: Detection and Discipline

Any lock-wait cycle (T1 holds A wants B; T2 holds B wants A) deadlocks permanently without intervention. Engines maintain a wait-for graph and poll for cycles (InnoDB checks on every lock wait when innodb_deadlock_detect is on, plus a 50 s innodb_lock_wait_timeout fallback). The victim — usually the txn with fewest rows modified — is aborted with a retryable error (MySQL 1213, PG 40P01).

Rules to recite: acquire locks in a consistent global order (e.g. parent→child, ascending PK), keep transactions short (no user think-time inside), touch the fewest rows possible, and index every locked predicate.

Concrete Lock Compatibility

Upgrades (S→X on the same row, the classic read-then-write) are a deadlock magnet: two txns both holding S that both want X deadlock deterministically. Prefer SELECT FOR UPDATE (takes X/IX upfront) for read-modify-write.

Trade-offs

Interview One-Liners

• Q: 2PL in one sentence? A: Take shared/exclusive locks while running, release nothing until commit — any conflicting txn waits, which forces an equivalent serial order.
• Q: Why must locks be held to commit, not released early? A: Early release lets another txn see your uncommitted state or overwrite it before you commit — reintroducing dirty reads/writes and breaking the serial order.
• Q: How does 2PL stop phantoms? A: Range/gap (next-key) locks on the index interval cover not-yet-existing rows, blocking concurrent inserts into the locked predicate range.
• Q: What causes most production deadlocks? A: Inconsistent lock acquisition order across code paths plus long transactions — fix by ordering locks globally and shortening txns.
• Q: S→X upgrade deadlock — what and how to avoid? A: Two txns holding shared locks both requesting exclusive deadlock; avoid with SELECT FOR UPDATE taking the exclusive lock upfront.
• Q: 2PL vs SSI in one line? A: 2PL prevents conflicts by blocking (latency, deadlocks); SSI detects dangerous patterns and aborts (retries, no blocking) — pessimistic vs optimistic serializability.

Related

• Read Committed Isolation
• Write Skew and Phantom Writes
• Serial Execution and Achieving ACID
• Serializable Snapshot Isolation
• B-Tree Indexes
• MySQL vs PostgreSQL