How 2PL achieves serializability by splitting every transaction into a lock-growing phase and a lock-shrinking phase. Strict 2PL in practice, how deadlock detection works with real examples, and exactly when MVCC is the better choice.
32 min read2026-04-05harddatabasestransactionsconcurrencylockingserializability
A bank's account table has one row: account_id=1, balance=1000. Two transactions start concurrently.
Transaction A reads the balance: 1000. Transaction A calculates a new balance: 1000 - 800 = 200. Transaction B reads the balance: 1000. Transaction B calculates a new balance: 1000 - 800 = 200. Transaction A writes 200. Transaction B writes 200. Both transactions commit successfully. The account now has a balance of 200, but two withdrawals of 800 each were supposed to leave it at -600. The bank lost $600.
This is the lost update anomaly. It happens because Transaction B read the account's pre-A-write state and wrote its calculation on top of A's result. The final balance reflects only one withdrawal instead of two.
A second classic anomaly: the phantom read. Transaction A runs SELECT COUNT(*) FROM orders WHERE user_id = 42; it gets 5. Transaction B inserts a new order for user_id 42 and commits. Transaction A reruns the same query in the same transaction and gets 6. The phantom row appeared mid-transaction. If Transaction A's logic was "if user has less than 6 orders, grant discount," the discount decision is now based on inconsistent state read within a single transaction.
Both anomalies happen because concurrent transactions can observe each other's intermediate state. The correct guarantee is serializability: for any concurrent execution of N transactions, the result is equivalent to running those transactions in some serial order, one at a time. Two-phase locking is the classical protocol for enforcing it.
Two-phase locking (2PL) is a transaction isolation protocol that achieves serializability by enforcing one rule: once a transaction releases any lock, it may not acquire new locks.
This splits every transaction's lifetime into exactly two phases. In the growing phase, the transaction acquires shared locks for reads and exclusive locks for writes. It does not release anything. In the shrinking phase, the transaction releases locks. Once shrinking starts, no new locks can be added.
Think of it like checking books out of a library before a study session. You gather every book you need (growing phase) before sitting down to study. Once you start returning books (shrinking phase), you cannot grab new ones. This ensures that if two students are using the same set of books, their sessions cannot interleave in a way that corrupts their notes. One student's full set of books is available to them in a consistent snapshot throughout their session.
The phrase "two-phase" refers to the two lifecycle phases, not to the two-phase commit (2PC) distributed protocol. They solve different problems and share only the name.
Continue Reading with Premium
Unlock this article and every other in-depth system design guide on the platform with NotesFromSDE Premium.
Every row, page, or table that a transaction reads or writes must be locked before access. The lock manager maintains a global lock table mapping each resource to its current lock state and the queue of waiting transactions.
Without 2PL, both transactions read 1000 simultaneously and both write 200, losing one withdrawal. With 2PL, T2 waits until T1 commits, then reads the post-T1 balance of 200 and correctly produces -600.
The lock table structure looks like this in memory:
lock_table: map<ResourceID, LockEntry>
struct LockEntry:
lock_mode: UNLOCKED | SHARED | EXCLUSIVE
owners: list<TransactionID> // transactions currently holding the lock
waiters: queue<WaitRequest> // transactions waiting for the lock
struct WaitRequest:
transaction_id: TransactionID
requested_mode: SHARED | EXCLUSIVE
condition_variable: CV // woken when lock is granted
The lock point is the moment the transaction holds its maximum set of locks. In Strict 2PL (used by PostgreSQL, InnoDB, SQL Server), all locks are held until commit or rollback. No locks are released piecemeal during the transaction. This is a stronger variant than basic 2PL and prevents the "dirty read on partial rollback" anomaly that basic 2PL allows.
Not all locks conflict with each other. Shared locks exist because multiple readers can safely operate simultaneously. The lock manager consults a compatibility matrix before granting each request.
Lock Compatibility Matrix
(Row = held lock, Column = requested lock)
| SHARED (S) | EXCLUSIVE (X)
---------|------------|---------------
SHARED | COMPATIBLE | CONFLICT
EXCLUSIVE| CONFLICT | CONFLICT
Rules:
- Multiple transactions can hold S locks on the same row simultaneously
- An X lock can only be granted when no other transaction holds any lock on the row
- Upgrading S to X requires waiting for all other S holders to release
Real systems add intention locks for multi-granularity locking (row-level vs table-level locking decisions without scanning every row). The full matrix in PostgreSQL and InnoDB includes:
Lock mode
Abbreviation
Used for
Shared
S
Plain reads in serializable isolation
Exclusive
X
Writes (INSERT, UPDATE, DELETE)
Intent Shared
IS
Signal: "I will place S locks on rows below"
Intent Exclusive
IX
Signal: "I will place X locks on rows below"
Shared Intent Exclusive
SIX
Table locked S overall, some rows X
The critical production insight: PostgreSQL uses S locks only at the SERIALIZABLE isolation level (via predicate locking / SSI). At REPEATABLE READ and READ COMMITTED, it uses MVCC for reads and only acquires X locks for writes. This means SELECT statements in a BEGIN; ... COMMIT; block do NOT hold a shared lock by default. To lock a row for reading, you must use SELECT ... FOR SHARE or SELECT ... FOR UPDATE explicitly.
Transaction T1:
acquire lock(A) --+
read A | Growing phase
acquire lock(B) |
write B --+
(lock point: maximum locks held)
--+
release lock(A) | Shrinking phase
release lock(B) | (in Strict 2PL: happens at commit, all at once)
commit --+
2PL introduces deadlocks as an unavoidable consequence of holding locks for the duration of a transaction. If Transaction A holds a lock on row X and wants row Y, while Transaction B holds row Y and wants row X, neither can proceed. This is a deadlock cycle.
The wait-for graph is the canonical representation. Each node is a transaction. An edge from T1 to T2 means "T1 is waiting for a lock that T2 holds." A cycle in the wait-for graph is a deadlock.
Databases detect deadlocks by periodically scanning the wait-for graph for cycles (PostgreSQL: every ~1 second, triggered after a configurable deadlock_timeout, default 1 second). When a cycle is found, one transaction is selected as the victim, rolled back, and the cycle is broken. The other transaction can then proceed.
// Deadlock detection algorithm (simplified)
function detect_deadlocks():
graph = build_wait_for_graph(lock_table)
cycles = find_cycles(graph) // DFS with cycle detection
for cycle in cycles:
victim = select_victim(cycle) // lowest cost to abort
abort_transaction(victim)
release_all_locks(victim)
notify_waiters(victim)
The victim selection heuristic matters in practice. PostgreSQL aborts the transaction that has done the least work (fewest locks acquired). This minimizes wasted work. Applications must be written to handle deadlock errors (SQLSTATE 40P01 in PostgreSQL) and retry the entire transaction from the beginning.
Deadlock prevention (the lock ordering approach): The most reliable prevention strategy is to always acquire locks in a fixed global order. If every transaction always locks orders before shipments, the T1/T2 cycle cannot form. This is the single most important operational tip for teams experiencing frequent deadlocks: audit your transactions and enforce a canonical lock acquisition order.
-- Deadlock-prone: T1 locks user then account, T2 locks account then user-- Fix: always lock in alphabetical order: accounts before usersBEGIN; SELECT * FROM accounts WHERE account_id = 42 FOR UPDATE; -- lock first SELECT * FROM users WHERE user_id = 7 FOR UPDATE; -- lock second -- ... proceed with writesCOMMIT;
Deadlock prevention (wait-die / wound-wait): Distributed databases that cannot afford the cost of scanning a global wait-for graph use timestamp-based protocols:
Wait-die: older transactions wait; younger transactions are aborted when they would form a cycle. "Old wait, young die."