What is Two-Phase Locking?
Two-Phase Locking (2PL) is a concurrency control protocol that guarantees conflict serializability — meaning concurrent transactions produce a result equivalent to some serial execution. The protocol divides every transaction's lifetime into two distinct phases:
The Growing Phase
During the growing phase, a transaction may acquire locks but must never release any lock. Every time the transaction needs to read a row, it requests a shared (S) lock. Every time it needs to write, it requests an exclusive (X) lock. If the requested lock conflicts with a lock held by another transaction, the requester blocks (waits) until the conflicting lock is released.
The Shrinking Phase
Once a transaction releases its first lock, it enters the shrinking phase. From this point forward, it may release locks but must never acquire new ones. This simple rule is what guarantees serializability: it prevents cycles in the serialization graph.
Lock Types and Compatibility
The two basic lock types are:
- Shared (S): for reading. Multiple transactions can hold S locks on the same resource simultaneously.
- Exclusive (X): for writing. Only one transaction can hold an X lock, and it conflicts with all other locks.
The lock compatibility matrix:
| S held | X held | |
|---|---|---|
| S requested | Compatible | Conflict |
| X requested | Conflict | Conflict |
Strict 2PL
Basic 2PL has a problem: if transaction T1 releases a lock during the shrinking phase, another transaction T2 might read the modified data and commit. If T1 later aborts, T2 has committed based on data that was rolled back — a cascading rollback. Strict 2PL solves this by holding all write (X) locks until the transaction commits or aborts. This is what most real databases implement.
Lock Manager Implementation
The lock manager maintains a hash table mapping each resource (row, page, or table) to a lock queue. Each entry in the queue records the transaction ID, the lock mode (S or X), and whether the lock is granted or waiting. When a lock is released, the manager walks the queue and grants compatible waiting requests.
Lock Escalation
When a single transaction holds thousands of individual row locks on the same table, the lock manager's memory overhead becomes significant. Lock escalation automatically promotes many fine-grained row locks to a single coarse-grained table lock. SQL Server, for example, escalates when a transaction exceeds ~5000 row locks on a single table. This trades concurrency for reduced overhead.
Real-Life: Bank Transfer with 2PL
Consider two concurrent transactions on a banking database:
- T1: transfer $100 from Account A to Account B
- T2: compute the total balance across all accounts (audit query)
Without 2PL, T2 might read Account A after T1 debited it but read Account B before T1 credited it, seeing a total that is $100 short — a phantom inconsistency.
With Strict 2PL:
- T1 acquires X-lock on Account A, debits $100.
- T1 acquires X-lock on Account B, credits $100.
- T2 tries to acquire S-lock on Account A — blocked (T1 holds X-lock).
- T1 commits, releasing both X-locks.
- T2 now acquires S-locks on A and B, sees the consistent post-transfer state.
Result: T2 always sees a consistent total. The schedule is serializable (equivalent to T1 then T2).
Real databases using Strict 2PL:
- MySQL InnoDB: uses Strict 2PL for its REPEATABLE READ and SERIALIZABLE levels
- SQL Server: default locking behavior is Strict 2PL
- PostgreSQL: uses 2PL for explicit SELECT FOR UPDATE / FOR SHARE locks