Two-phase locking
Two-phase locking is a transaction concurrency-control protocol in which a transaction acquires no new locks after releasing its first lock.
In the basic shared/exclusive locking model, shared locks permit concurrent readers; exclusive locks conflict with other readers and writers. With suitable locks covering every conflicting access, the two-phase discipline produces conflict-serializable histories. [1]
Basic 2PL can release locks before commit and permit cascading aborts. A common strict variant retains exclusive locks through commit or abort; rigorous 2PL retains shared locks too. Upgrades belong to the growing phase, downgrades to the shrinking phase. [2]
Names vary: Bernstein, Hadzilacos and Goodman call the all-locks-until-completion variant Strict 2PL. Specify the retention rule instead of relying on the adjective alone. [1]
For predicate-based queries, lock coverage must include changes to predicate membership, including possible inserts, not merely rows returned by a query. An empty result can still carry a dependency. [3]
PostgreSQL's Serializable SIReadLock tracks dependencies without blocking writers; it is not a blocking 2PL read lock. [4]
Two-phase commit (2PC) coordinates a transaction's commit decision across participants, not its growing and shrinking lock phases. PostgreSQL PREPARE TRANSACTION serves an external transaction manager; the prepared transaction retains its locks until resolved. [5]
Lomet's key-range locking separates a key from the range it bounds. In the paper's unique-key model, combining them with ordinary multigranularity modes can block a logically nonconflicting insertion before a singleton-read key. Composite range/key modes preserve that distinction while using one named resource. Fewer lock resources need not mean the same permitted concurrency. [6]
Relations
Limitations
- 2PL does not prevent deadlock; waiting transactions may need to be aborted and retried. [1]
- A locking protocol alone does not specify crash durability or the distributed commit procedure. [1, 5]
Citations
- Bernstein, Hadzilacos and Goodman. Concurrency Control and Recovery in Database Systems (1987), chapter 3, sections 3.2-3.5 and 3.7.
- Silberschatz, Korth and Sudarshan. Database System Concepts, fifth edition: chapter 16 author slides, 16.8-16.11.
- Berenson et al. A Critique of ANSI SQL Isolation Levels. SIGMOD 1995; MSR-TR-95-51, section 2.3.
- PostgreSQL 18: Transaction Isolation, section 13.2.3.
- PostgreSQL 18: PREPARE TRANSACTION, purpose and prepared-state lock retention.
- Lomet. Key Range Locking Strategies for Improved Concurrency. VLDB 1993, pp. 655-664, sections 3.2 and 4.1.
Further reading
Sources & caveats
- Primary-source synthesis; no database engine or failure experiment was run.