Part 3 of 5 · 1 min read
The two-phase locking rule
A transaction only acquires locks in a growing phase, then only releases locks in a shrinking phase, never mixing the two.
Think of a construction crew. It first spends time acquiring every permit and resource it will need. Only once that acquiring phase is entirely finished does it begin returning permits and resources one by one, never acquiring anything new after it starts returning.
In plain words: two-phase locking requires every transaction to split its lock activity into two distinct phases: a growing phase, during which it may only acquire new locks, never release any, and a shrinking phase, during which it may only release locks, never acquire any new ones. Once a transaction releases even a single lock, it has entered its shrinking phase and may not acquire any further locks for the rest of its execution. This strict separation is what gives the protocol its name.
A worked example. A transaction acquiring locks on rows A, B, and C, in that order, then releasing C, has entered its shrinking phase, and may not acquire a new lock on row D afterward without violating the protocol.
The key lines. No new SQL; two-phase locking is a discipline the database's lock manager enforces internally on every transaction's lock requests.
Two-phase locking
Two-phase locking requires a transaction to only acquire locks during a growing phase and only release locks during a shrinking phase, never acquiring a new lock after releasing any lock.
Example: a construction crew that finishes acquiring every permit before returning any, never acquiring something new once it starts returning permits, mirrors two-phase locking.