Two-Phase Locking
Subject: Computer and Technology noun /ˌtuː.feɪz ˈlɑː.kɪŋ/ 1 definition
- database-systems
- concurrency-control
- transaction-processing
- data-consistency
-
Definition (noun)
Entry #312 · UpdatedTwo-Phase Locking (2PL) is a pessimistic concurrency control protocol used in database management systems to guarantee conflict serializability among concurrent transactions. It divides each transaction's execution into two strictly ordered stages: a growing phase, during which the transaction may acquire shared (read) or exclusive (write) locks without releasing any, and a shrinking phase, during which it may release locks but cannot acquire new ones. By requiring every lock acquisition to precede the first lock release, each transaction establishes a lock point that fixes an equivalent serial execution order for conflicting reads and writes. While this rule ensures conflict serializability, basic 2PL alone does not prevent deadlocks or cascading aborts if locks are released before commit, motivating stricter variants in production engines.
Common Forms
Basic Two-Phase Locking: Releases shared and exclusive locks during the shrinking phase as soon as a transaction finishes accessing an item, ensuring conflict serializability while remaining susceptible to cascading aborts and deadlocks.
Strict Two-Phase Locking: Holds all exclusive (write) locks until transaction commit or abort while allowing early release of shared (read) locks, guaranteeing strict schedules free of dirty reads, dirty writes, and cascading aborts.
Strong Strict (Rigorous) Two-Phase Locking: Retains both shared and exclusive locks until
COMMITorROLLBACK, simplifying lock management and ensuring transaction commit order aligns with serialization order in pessimistic database engines.Conservative (Static) Two-Phase Locking: Pre-acquires all required locks atomically before a transaction starts executing—or waits without holding partial locks if any item is unavailable—eliminating deadlocks when the full read and write set is known upfront.
Example
In a MySQL 8.0 InnoDB database running under
SERIALIZABLEisolation, Transaction A readsaccountsrowid = 101(SELECT balance FROM accounts WHERE id = 101 FOR SHARE) and executesUPDATEstatements to move$250toid = 204, acquiring and upgrading row locks throughout its growing phase without releasing any. A concurrent Transaction B queryingid = 101blocks until Transaction A runsCOMMITand releases all locks together in its shrinking phase, preventing dirty or non-serializable reads of the mid-transfer balance.