DB Locks Lifecycle
Concept. A transaction requests a lock, uses the protected row, and releases the lock. It moves through one fixed cycle: request, get, read or write while holding it, release. A lock comes in two modes, shared (S) for reads and exclusive (X) for writes, and their compatibility decides who proceeds and who waits.
Intuition. Mickey wants to write the Likes row. He requests an exclusive lock, the manager grants it, he writes, then releases. While he holds it, Minnie's request for the same row waits. Two readers, though, can hold a shared lock at once, because reads never conflict.
You met the lock manager and its lock table on the last page. Now zoom into one lock: what it looks like start to finish, and the two modes it comes in.
One lock, start to finish
A single lock is not instant. A transaction requests it, the manager grants it, the transaction reads or writes the row while holding it, then releases it. Anyone else who wants that row waits the whole time it is held.
Figure: The lock lifecycle, in the schedule notationOne transaction's lock cycle on a time axis, drawn in the same notation the lock schedules ahead use. A green flag is a request (Req), a red flag is the grant (Get), a light-blue box is the read, a light-orange box is the write, and a purple flag pointing forward is the release (Unl). T1 requests seat A at time 1, gets it at 2, reads then writes while holding it, and unlocks at 8. T2 requests the same lock at time 2, waits the whole time T1 holds it, and gets it only after T1 unlocks.
Figure 1. One lock through its cycle, in the notation the schedules ahead use: a green flag is the request (Req), a red flag the grant (Get), a blue box the read, an orange box the write, and a purple flag the release (Unl). T1 takes row A, reads then writes while holding it, and releases. T2 wants the same row: it requests, waits the whole hold, and gets it only once T1 releases.
Two kinds of lock: shared and exclusive
Reading does not change a row; writing does. The manager exploits that with two lock modes: a shared (S) lock for reads and an exclusive (X) lock for writes. Many readers can hold S on the same row at once; a writer needs X alone.
Figure: Shared and exclusive locksTwo lock modes on seat A15. On the left, a shared lock for reading: T1, T2, and T3 each take a shared lock and all read the status as available at the same time, because reads do not conflict. On the right, an exclusive lock for writing: T1 takes an exclusive lock and writes the status to sold, while T2 and T3 are blocked and must wait.
Figure 2. A shared lock (S) is for reading: several transactions can hold S on one row and read it together, because reads do not conflict. An exclusive lock (X) is for writing: only one transaction holds X, and everyone else waits.
Figure: Lock compatibility matrix: shared and exclusive locksA two-by-two matrix. Rows are the lock already held, columns the lock now requested. Holding a shared lock and requesting a shared lock is compatible, because two reads do not conflict. Every other combination, shared then exclusive, exclusive then shared, exclusive then exclusive, is incompatible, so the requester waits. A write excludes everyone.
Figure 3. The one compatible pairing is shared-with-shared; every cell touching an exclusive lock forces a wait. This is the conflict rule from the Correctness page made operational: two operations collide only when they touch the same row and at least one writes.
The manager grants one lock at a time, correctly. But a transaction holds many locks across its life, and handing out each one safely on its own still lets the transaction interleave into a result no serial run could produce. The discipline that closes that gap is Two-Phase Locking.