Shared and Exclusive Locks

Locking — Shared & Exclusive

Snapshots (previous lesson) let readers avoid blocking on writers. But when two transactions might write the same data, or when you want strict correctness without MVCC's version-chain machinery, databases fall back on locks. This lesson implements the classic two-mode lock: shared (S) for readers and exclusive (X) for writers.

The compatibility matrix

Locking comes down to one small table — whether a new lock request is compatible with locks already held on the same resource:

In words:

• Any number of transactions can hold a shared lock at once — readers don't conflict with other readers.
• An exclusive lock conflicts with everything, including other exclusive locks and existing shared locks — a writer needs sole ownership.

Commands to implement

LOCK users SHARED         -- acquire a read lock on `users`
LOCK users EXCLUSIVE      -- acquire a write lock on `users`
UNLOCK users              -- release whatever lock this holder had
LOCK STATUS users         -- report SHARED / EXCLUSIVE / UNLOCKED

Model each table's lock state as something like:

• mode is None, "SHARED", or "EXCLUSIVE".
• holders tracks which lock-holder(s) currently hold it (useful once you track multiple simultaneous shared holders, and essential for the upgrade rule below).

The upgrade rule

The trickiest part of this lesson: a shared lock can be upgraded to exclusive if the requester is the only current holder. This mirrors real database semantics — SELECT ... FOR UPDATE style upgrades are safe only when no one else is reading concurrently, because upgrading while others hold shared locks would violate the exclusivity guarantee.

LOCK users SHARED       -- holder A: granted, holders={A}, mode=SHARED
LOCK users EXCLUSIVE    -- holder A again: upgrade allowed, holders={A}, mode=EXCLUSIVE

But:

LOCK users SHARED       -- holder A: granted
LOCK users SHARED       -- holder B: granted, holders={A,B}, mode=SHARED
LOCK users EXCLUSIVE    -- holder A: BLOCKED — B also holds it

To support this you need to know who is asking, not just what is held — otherwise you can't tell "the sole holder wants to upgrade" apart from "a third party wants exclusive access while others hold shared."

Why this matters for the bigger project

Real databases (this is literally what Postgres, MySQL, and SQLite's rollback-journal mode do) use exactly this S/X lock model — often per-row or per-page rather than per-table — combined with a lock manager that queues blocked requests and detects deadlocks (two transactions each waiting on a lock the other holds). This lesson gives you the foundational compatibility and upgrade logic; more sophisticated engines add lock granularity (row/page/table), intent locks, and timeout/deadlock detection on top.

Edge cases to watch

• Releasing a lock you never held, or double-unlocking, should not corrupt the holders set — guard against removing an id that isn't present.
• LOCK STATUS on a table nobody has touched should report UNLOCKED, not error.
• After the last shared holder unlocks, mode must reset to None so a fresh exclusive lock can be granted to a new requester.