DB Locks Lifecycle

Concept. A lock is a claim a transaction puts on a row before it reads or writes. 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.

One lock through its lifecycle: T1 requests the lock on row A, the lock manager grants it, T1 reads then writes A while holding the lock, and T1 releases it. A second transaction T2 requests the same lock, waits the whole time T1 holds it, and gets it only after T1 releases.

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.

Two lock modes on seat A15. A shared lock (S) is for reading: T1, T2, and T3 all take S and read the status at once, because reads do not conflict. An exclusive lock (X) is for writing: T1 takes X and writes the status to sold, while T2 and T3 are blocked.

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.

A two-by-two lock compatibility matrix. Rows are the lock already held, columns the lock requested. Shared then shared is compatible, because two reads do not conflict. The other three combinations, shared then exclusive, exclusive then shared, and exclusive then exclusive, make the requester wait.

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.