Locking — shared, exclusive, intent locks: Visual walkthrough

We build only what we need, when we need it. Let's define our characters first.

Step 1 — The raw danger: two hands on one box

WHAT. Two transactions, one data item $x$ . No locks yet. We watch what breaks.

WHY. Before inventing locks, we must see the disease they cure. If we can't name the harm, we can't judge which lock pairs are safe.

PICTURE. Below, T1 and T2 both read $x = 10$ , both compute $x + 1$ , both write. The final value is $11$ , not $12$ . One update vanished — this is the lost update.

Figure — Locking — shared, exclusive, intent locks

$both read x = 10 ⟶ both compute x + 1 ⟶ both write x = 11 \neq = correct 12$

Reading the terms: the two "read" arrows overlap in time — that overlap is the whole problem. Because they read before either wrote, both saw the stale $10$ . A lock's job is to forbid the dangerous overlap while permitting the safe one. Which overlaps are safe? That's the rest of the page.

Step 2 — Split the intent: reading vs writing

WHAT. We give a transaction a way to declare what it will do: only read, or actually write. Read-intent gets a Shared (S) tag; write-intent gets an Exclusive (X) tag.

WHY. Notice from Step 1 that the damage happened because a write overlapped another access. Two readers never damage anything — nobody's pen touches the paper. So one single lock for everything would be wasteful: it would make readers wait on readers for no reason. We split into two modes so the engine can allow the harmless case and stop only the harmful one.

PICTURE. The same box now wears one of two tags. Many mint S tags can hang together; a single coral X tag hangs alone.

Step 3 — Compatibility is symmetric — prove it once, use it forever

WHAT. Before filling any grid, we settle a structural fact: if lock mode $A$ held by T1 is compatible with mode $B$ requested by T2, then $B$ held first is compatible with $A$ requested next. In symbols, compatibility is symmetric: $compat (A, B) = compat (B, A)$ .

WHY. This halves our work: every grid below is a mirror across its diagonal, so we only ever reason about one triangle. More importantly, it must be proved, not assumed — otherwise the S–X vs X–S cells could secretly disagree.

PICTURE. Two transactions, two moments. On the left T1 grabs $A$ then T2 asks for $B$ ; on the right the order flips. The engine's only question — "would both holding at once create a Step-1 overlap?" — depends on the pair of modes co-existing, not on who arrived first. Same pair ⇒ same verdict.

A held, B requested compat (A, B) = depends only on the set {A, B} "can A and B coexist?" = B held, A requested compat (B, A)

The middle term is the key: coexistence is a property of the unordered pair ${A, B}$ . Order cannot change whether two tags hang on the same box safely. So from now on we fill only the upper triangle and mirror it.

Step 4 — Derive the base 2×2 grid, cell by cell

WHAT. For each pair of requests on the same box, ask the one governing question: "does letting both coexist create a Step-1-style anomaly?" If no → ✅ compatible. If yes → ❌ conflict. By Step 3 we need only decide ${S, S}$ , ${S, X}$ , ${X, X}$ — the mirror fills the rest.

WHY. We don't memorise the grid; we generate it from that single question.

PICTURE. The three unordered pairs, three verdicts, then mirrored into a full 2×2.

S X S ✓ \times X \times \times

Cell by cell — the symbols mean:

${S, S}$ ✅ : two readers see the same frozen value. No pen, no harm.
${S, X}$ ❌ : a reader is trusting the value; a writer would yank it mid-read → non-repeatable read. By symmetry (Step 3), X–S is the same ❌ — a reader would glimpse an uncommitted change → dirty read.
${X, X}$ ❌ : exactly Step 1's lost update.

Step 5 — A new problem: the whole shelf

WHAT. Zoom out. A table is a box containing thousands of row-boxes. T1 locks one row with X. Now T2 wants to lock the entire table with X (think ALTER TABLE).

WHY. The Step-4 grid answers questions about the same box. But T2's box (the table) is not the same box as T1's (a row) — it's the parent. Our grid is silent here. We need a bridge between coarse and fine.

PICTURE. T2, to be safe, would have to open the table and inspect every single row for a hidden lock tag — an $O (n)$ scan across $n$ rows. Slow and ugly.

$naive check cost = O (n) where n = number of rows$

The term $n$ is the row count; the cost grows linearly with it. For a million-row table that's a million checks per lock request. Unacceptable. Step 6 fixes it.

Step 6 — The fix: plant a flag before you descend

WHAT. New rule: before locking a row, a transaction plants a small intent tag on the table above it.

Going to take S on a row? First plant IS (Intent Shared) on the table.
Going to take X on a row? First plant IX (Intent Exclusive) on the table.
Reading the whole table but planning to write a few rows? Plant SIX = an S over the whole node plus an IX flag for the children you'll write.

WHY. Now T2 doesn't scan rows. It reads the one table-level tag: "is anyone announcing activity below?" — an $O (1)$ lookup. The intent tag is a summary at the top of what's happening at the bottom.

PICTURE. T1's path: plant IX on accounts (top), then hang X on row 5 (bottom). T2 arrives at the table door and reads the IX flag instantly.

"a writer is somewhere below" IX on table ⟶ the actual write X on row 5 check cost = O (1)

Step 7 — Derive every intent-cell from one rule

WHAT. Now we fill the whole 5×5. The trick: translate each mode into what it claims about the node itself vs what it merely announces below, then re-ask the Step-1 question. By Step 3 we fill one triangle and mirror.

WHY. Two claims conflict only if their whole-node parts clash. A pure flag (IS, IX) makes no whole-node claim — it only points down — so it fights almost nothing. The strong whole-node claims live in S, SIX, and X.

PICTURE. Read each mode as a pair (node-part, below-part):

mode	claims on the node	announces below
IS	nothing	a reader
IX	nothing	a writer
S	reads everything	—
SIX	reads everything	a writer
X	writes everything	—

Now every cell is one comparison — do the node-parts clash (using the Step-4 rule), or does one side make a whole-node write that the other's flag-below would touch?

IS vs {IS, IX, S, SIX} ✅ : IS claims nothing on the node. It coexists with any partner whose node-claim is at most "read." Even SIX (reads node + writes some rows) is fine with IS, because IS's reader below can dodge SIX's few written rows. IS vs X ❌ : X writes the whole node, so there is no row for IS's reader to safely occupy.
IX vs {IS, IX} ✅ : neither claims the node; two writers-below dodge on different rows (Step 6). IX vs S ❌ , SIX ❌ , X ❌ : each partner makes a whole-node claim (S/SIX read all rows, X writes all) that collides with IX's hidden writer.
S vs {IS, S} ✅ : IS's below-reader and S's node-read are both reads → safe; S–S is Step-4's ✅. S vs IX ❌ , SIX ❌ , X ❌ : each partner writes some or all rows, breaking S's need for every row stable.
SIX vs IS ✅ (reader-below dodges), everything else ❌ : SIX itself reads the whole node and writes some, so it tolerates only a pure reader-below (IS) and conflicts with any writer-below (IX), any whole-node reader (S, SIX), and X.
X vs everything ❌ : X writes the entire node — no other claim, not even a flag, can survive.

IS IX S SIX X IS ✓ ✓ ✓ ✓ \times IX ✓ ✓ \times \times \times S ✓ \times ✓ \times \times SIX ✓ \times \times \times \times X \times \times \times \times \times

Notice the matrix is symmetric across the diagonal — exactly what Step 3 guaranteed, and a free correctness check on our work.

Step 8 — The two representative cells, in motion

WHAT. We animate the two cells that carry the whole intuition: the friendly IX–IX / IS–IX corner (✅) and the hostile IX–S cell (❌).

WHY. Every ✅ in the top-left of the 5×5 is a variation of "flags dodge"; every ❌ in the S/SIX/X region is a variation of "whole-node claim gets touched." Seeing both moving fixes the pattern.

PICTURE — the ✅ corner. Left: T1 flags IX and writes row 5; T2 flags IX and writes row 99 — two flags, different rows. Right: T1 flags IX (writing row 5); T2 flags IS (reading row 12) — a hidden writer and a hidden reader on different rows also dodge.

diff rows IX ∥ IX = ✓ reader below, writer below IS ∥ IX = ✓

Both ✅ for the same reason: neither side claims the node, so their below-activity can occupy different rows.

PICTURE — the ❌ cell. T1 flags IX (writing some row); T2 asks for S on the whole table. S needs every row stable, but IX hides a writer — the mint blanket cannot cover a moving row.

needs every row stable S_{whole table} vs some row is changing IX = \times

This single ❌ explains the entire S-row and SIX-row of the matrix: any whole-node reader loses to any hidden or whole-node writer.

The one-picture summary

Everything above collapses into one idea: compatibility = "can both claims coexist without recreating Step 1's overlap on the same box?" — and, by Step 3, it is symmetric, so we only ever reason about one triangle. Split each mode into (node-part, below-part): flags claim nothing on the node and dodge; S/SIX/X make whole-node claims and clash with any conflicting activity.

Recall Feynman retelling — the whole walkthrough in plain words

We started with a disaster: two people editing the same page both saw the old number, so one edit vanished (Step 1). Only writing is dangerous, so we handed out two tags — a mint "I'm reading" tag anyone can share, and a coral "I'm writing" tag that must be held alone (Step 2). Before filling any grid we proved a shortcut: it doesn't matter who arrived first — two tags on one box are safe or not as a pair — so every grid is a mirror (Step 3). That gave the tiny grid: only reader-with-reader is safe (Step 4). Then the book got huge and scanning every page for a tag was painfully slow (Step 5), so we planted flags on the cover — IS "reader inside," IX "writer inside," and SIX "I'm reading the whole book but will scribble a few pages" (Step 6). To fill the big grid we split every mode into what it claims on the whole book vs what it only whispers about pages inside: pure flags claim nothing and dodge each other; anyone claiming the whole book clashes with any hidden or whole-book writer (Step 7). Two moving pictures nailed it — flags dodging on different pages (✅) and a whole-book reader losing to a hidden writer (❌) (Step 8). Same one question, asked at every zoom level, and always symmetric.

Recall Active recall — cover the answers

The single question every matrix cell answers ::: "Can both claims coexist without recreating the lost-update / dirty-read overlap on the same box?" Why may we fill only one triangle of any compatibility grid ::: Compatibility is symmetric — coexistence depends on the unordered pair of modes, not on arrival order (Step 3). Split of a lock mode that decides the 5×5 ::: (node-part, below-part): flags (IS, IX) claim nothing on the node; S/SIX read the whole node; X writes it; SIX also announces a writer below. Why is IS compatible with S, IX, and SIX but not X ::: IS only announces a reader below, which can dodge writers-below and coexist with node-reads; but X writes the entire node, leaving no safe row. Why IX–IX ✅ but IX–S ❌ ::: Two IX flags may point at different rows and dodge; a full-table S needs every row stable, which a hidden writer breaks.

Parent: 4.4.19 Locking — shared, exclusive, intent locks (Hinglish)
Locks are held to commit time under Two-Phase Locking.
These modes enforce the "I" in ACID Properties and implement Isolation Levels.
Blocking chains can cycle into Deadlocks.
An alternative that avoids read locks entirely: MVCC.
The coarse-vs-fine choice is Lock Granularity.