Locks Stack & the Lock Manager
Concept. SQL, database locks, OS mutexes, and hardware atomics coordinate concurrent work. Four layers cooperate to deliver that: your SQL, the database's lock manager, the OS's mutexes, and the hardware's atomic instructions.
Intuition. When Mickey and Minnie both like the same song at the same instant, both transactions hit the same Likes row. Four layers cooperate so both likes count without one overwriting the other: the database's lock manager serializes access to the row, the OS schedules the threads that hold those locks, and the hardware guarantees the increment instruction is atomic.
Every BEGIN TRANSACTION runs through four layers:
Figure: The concurrency full stack: one COMMIT, four layers down to one instructionA four-layer call stack. Your Code runs COMMIT and sees one transaction. It delegates down to the Database, whose lock manager acquires an exclusive lock on the row. The lock manager delegates down to the Operating System, which takes a mutex on its thread. The OS delegates down to the Hardware, which executes one compare-and-swap, the single indivisible instruction every layer reduces to. The guarantee then returns back up the right side: swapped atomically, mutex held, row locked, transaction isolated. Color key: violet is the call delegating down; grey is the layers and the guarantee returning up.
Figure 1. One COMMIT, four layers down to a single instruction. The lock manager decides what to lock, the OS mutex decides how, and the hardware's compare-and-swap is the one indivisible step everything reduces to. The guarantee returns up the right side: swapped atomically, mutex held, row locked, transaction isolated.
Each layer builds on the one below to answer a single question: how do thousands of transactions run at once without overwriting each other?
Why OS vs Database Locks?
Why can't databases just reuse the OS's locks? Because the two solve different problems:
Figure: OS lock vs database lock manager: same word, different layerA two-column comparison. The left column, grey, is the OS lock: a mutex or semaphore that is binary locked or unlocked, has no idea what data it guards, coordinates threads, and leaves deadlock for you to detect by hand. The right column, violet, is the database lock manager: it locks at table, page, or row granularity, supports intent locks that announce a plan, resolves deadlock automatically, and scales to millions of transactions. Color key: grey is the OS lock, a mutex or semaphore; violet is the database lock manager.
Figure 2. The same word, two layers on one machine. The OS lock (grey) is one bit of state guarding a memory address it knows nothing about. The database lock manager (violet) locks at table, page, or row granularity, lets a transaction announce intent, and resolves deadlock for millions of transactions at once. The OS primitive cannot do any of that, so the database builds its own lock layer on top.
The lock manager
That lock layer the database builds is the lock manager: the component every transaction asks for its locks. It runs a concurrency-control algorithm, the strategy for who waits and who runs, and a lock is the tool it uses. It keeps a lock table, the running record of who holds each row and who is queued behind them.
Figure: The lock manager: one component, two goals, three jobsThree transactions each send a lock request to one central component, the lock manager, which keeps a lock table of who holds each row and who is waiting. Its mission is two goals: keep every schedule correct (equal to some serial order) and fast (overlap the idle waits). It does three jobs. Job one, shown here in violet, is to grant a free row or queue you behind the holder. Jobs two and three are greyed as coming up: schedule many transactions, and break deadlocks. Color key: violet is the job this page covers, grey is a job still ahead, green marks the correctness goal.
Figure 3. Every transaction asks one component, the lock manager, for its locks, and it keeps the lock table of who holds each row and who waits. It owes two goals: correct, equal to some serial order, and fast, overlapping the idle waits. Its first job is the one shown, granting a free row or queuing you behind the holder; scheduling many transactions and breaking deadlocks (greyed) each get their own page later.
Next: one lock, start to finish, on DB Locks Lifecycle.