Concurrency Control: Two-Phase Locking (2PL)
Scenario
Two warehouse workers both want to update the same row of inventory. Without any coordination, both read quantity = 10, both subtract 3, and both write 7 — losing one subtraction entirely.
What rule can every transaction follow independently — without central coordination — that guarantees the schedule is always serializable?
Mental model
2PL is like a library checkout rule: you must gather all the books you need before you start reading, and return them all after you finish — you can never borrow a new book once you've returned one.
In practice: a transaction can acquire locks freely (growing phase), but the moment it releases its first lock, it can never acquire another (shrinking phase). This single rule provably prevents the cycles in the precedence graph that cause non-serializability — because releasing a lock signals 'I'm done with this data', so no one can interleave a conflicting operation after that point.
Explanation
Two-Phase Locking (2PL) is the most widely used concurrency control protocol. It imposes one simple rule: every transaction goes through exactly two phases. In the growing phase it may only acquire locks and may never release one. In the shrinking phase it may only release locks and may never acquire a new one. The point of transition — the moment the first lock is released — is called the lock point.
Why does this rule guarantee conflict-serializability? Because the lock point establishes a total order on transactions. If T1 releases a lock that T2 then acquires, T1's lock point comes before T2's, so T1 must appear before T2 in the equivalent serial order. This means the precedence graph built from all conflicting operations is forced to be acyclic — exactly the condition for conflict-serializability.
Two lock modes exist. A shared lock (S-lock) is granted to a transaction that wants to read a data item; multiple transactions can hold S-locks on the same item simultaneously. An exclusive lock (X-lock) is granted to a transaction that wants to write; it conflicts with both S-locks and other X-locks. A transaction must upgrade from S to X before writing — and this upgrade can cause deadlocks if two transactions both hold S-locks and both try to upgrade.
Basic 2PL prevents non-serializable interleavings but does NOT prevent cascading rollbacks. If T1 releases a lock, T2 reads that data, and then T1 aborts, T2 has read dirty data and must also abort — and any transaction that read from T2 must abort too. Strict 2PL (S2PL) fixes this: a transaction holds ALL its locks until it commits or aborts, releasing them all at once. This prevents dirty reads but increases blocking. Rigorous 2PL (R2PL) is stricter still: all locks (shared and exclusive) are held until commit. Most real databases (PostgreSQL, MySQL InnoDB) implement Strict or Rigorous 2PL combined with MVCC.
The key weakness of 2PL is deadlock. Two transactions can each hold a lock the other needs, forming a cycle. Databases detect deadlocks by periodically looking for cycles in the waits-for graph (a graph where T1 → T2 means T1 is waiting for a lock held by T2), then aborting one victim transaction to break the cycle. Alternatively, deadlocks can be prevented by lock ordering (impose a global order on data items and acquire locks in that order) or timeout-based policies.
Key points
- Growing phase: A transaction may acquire locks freely but may not release any. All reads and writes happen under locks acquired in this phase.
- Shrinking phase: Once a transaction releases its first lock, it may never acquire another. The lock point (first release) defines the serial order.
- Shared lock (S-lock): For reads. Multiple transactions can hold S-locks on the same item simultaneously. Compatible with other S-locks; incompatible with X-locks.
- Exclusive lock (X-lock): For writes. Only one transaction can hold an X-lock on an item at a time. Incompatible with both S and X locks from other transactions.
- Strict 2PL (S2PL): Holds all locks until commit/abort — prevents dirty reads and cascading rollbacks. The variant used by most production databases.
- Deadlock handling: Detected via cycle in the waits-for graph; resolved by aborting one victim. Prevented by global lock ordering or timeout.
Common mistakes
- Thinking 2PL prevents deadlocks: Basic 2PL does NOT prevent deadlocks — it actually makes them more likely by holding locks longer. Deadlock prevention requires additional mechanisms (lock ordering, timeout). 2PL prevents non-serializability, not deadlock.
- Confusing 2PL phases with two-phase commit (2PC): These are completely different protocols. 2PL is a concurrency control mechanism within one database (growing/shrinking lock phases). 2PC is a distributed commit protocol across multiple nodes. The '2-phase' in both is a coincidence.
- Assuming basic 2PL is what databases use: Most production databases use Strict 2PL or Rigorous 2PL (combined with MVCC). Basic 2PL allows cascading rollbacks, which is unacceptable. If you say '2PL' in an interview, specify the variant.
Glossary
- Two-Phase Locking
- A rule that makes a database transaction first gather all the locks it needs (growing phase) before it starts releasing any of them (shrinking phase) to safely handle simultaneous operations.
- growing phase
- The first stage of a transaction where it can only ask for new locks on data and is not allowed to let go of any locks it already has.
- shrinking phase
- The second stage of a transaction where it can only release the locks it holds and is strictly forbidden from asking for any new ones.
Recall questions
- State the 2PL rule in one sentence.
- Why does 2PL guarantee conflict-serializability?
- What is the difference between basic 2PL and Strict 2PL?
- How does a database detect and resolve a deadlock in 2PL?
Questions & answers
T1 holds S-lock on A and wants X-lock on A. T2 holds S-lock on A and wants X-lock on A. What happens?
Both try to upgrade their S-lock to X-lock. Neither can succeed because the other holds an S-lock blocking the upgrade. This is a lock upgrade deadlock — the DB must abort one of them.
Approach: Name the specific deadlock type (upgrade deadlock). Explain that S-to-X upgrade requires exclusive access, which is blocked by the peer's S-lock. Show awareness of the deadlock detection mechanism.
Why do databases combine 2PL with MVCC instead of using 2PL alone?
Pure 2PL blocks reads behind write locks, reducing throughput. MVCC lets readers access a consistent snapshot without acquiring locks on written rows. Writers still use 2PL for write-write conflict prevention. The combination gives high read concurrency with correct write ordering.
Approach: Contrast the bottleneck of pure 2PL (readers block writers and vice versa) with MVCC's snapshot model. Mention PostgreSQL or MySQL InnoDB as real examples.
Continue learning
Previous: Schedules & Serializability