Locking — shared, exclusive, intent locks: Worked examples

This page is the exhaustive drill. The parent note taught the rules; here we hunt down every case class a lock-compatibility question can throw at you and work each one to the ground. If you can answer every cell below, no exam question on locking can surprise you.

Before we start, one reminder of the alphabet we already earned, so you never meet a symbol unexplained:

Recall The lock modes used on this page (one-line reminder each)

S — shared, "I want to read." Many S together are fine.
X — exclusive, "I want to write." Nobody else on that item.
IS — intent-shared, a flag on the table saying "I hold an S on some row below."
IX — intent-exclusive, a flag saying "I hold an X on some row below."
SIX — S on the whole node plus IX for a few children ("read all, write some").
U (update lock) — a special lock one transaction takes when it reads a row intending to soon write it. Only one holder allowed (like X), but it still lets other readers keep their S until the writer actually upgrades. It exists purely to prevent the deadlock — we build that deadlock and then apply U in Ex 11.

The scenario matrix

Every locking question is really one of these case classes. We will hit each one at least once.

Case class	What varies	Example that covers it
Base compatible	S vs S on same item	Ex 1
Base conflict	S vs X on same item	Ex 2
Two writers, same item	X vs X on same item	Ex 3
Two writers, different items	IX + IX, disjoint rows	Ex 4
Intent vs full lock	IX (existing) vs S (requested)	Ex 5
SIX combination	scan-then-update	Ex 6
Intent-shared vs whole-table X	IS (existing) vs X (requested)	Ex 7
Degenerate / zero input	empty table, no rows to lock	Ex 8
Phantom / range edge case	insert of a future matching row	Ex 9
Limiting / starvation	many S vs one waiting X	Ex 10
Real-world word problem	bank transfer, two accounts	Ex 11
Exam twist + U lock	lock upgrade S → X, deadlock & the U fix	Ex 12

The compatibility matrix is our single source of truth. Read a cell as "Existing (row) vs Requested (column)":

Figure — Locking — shared, exclusive, intent locks

Keep this picture in view — every example below is just pointing at one square of it.

The worked examples

Worked example Ex 1 — Base compatible (S vs S)

Statement. T1 runs SELECT bal FROM accounts WHERE id=5. While it runs, T2 runs the same SELECT on row 5. Can both proceed?

Forecast: guess yes/no before reading on. (Both are only looking…)

T1 requests S on row 5. No lock exists → granted. Why this step? A reader must announce "I depend on this value" so no writer sneaks in.
T2 requests S on row 5. Existing = S, Requested = S → look at the matrix cell (S,S) = ✅. Why this step? Two readers see the same unchanged value; nothing can go wrong.
Both run concurrently, each seeing bal unchanged. Why this step? Because (S,S)=✅ means neither request blocks, so the engine lets both threads execute at once — that concurrency is the whole point of having a shared mode.

Verify: Neither writes, so bal never changes → both reads are consistent. Cell (S,S)=compatible confirms it. ✔

Worked example Ex 2 — Base conflict (S vs X)

Statement. T1 holds S on row 5 (still reading). T2 runs UPDATE accounts SET bal=0 WHERE id=5, needing X on row 5. What happens to T2?

Forecast: granted immediately, or wait? (A writer wants a row a reader is watching…)

T2 requests X on row 5. Existing = S, Requested = X → matrix cell (S,X) = ❌. Why this step? If T2 wrote now, T1's second read would see a different value — a non-repeatable read.
T2 blocks (enters the wait queue for row 5). Why this step? Blocking is how the engine enforces the ❌ — it doesn't reject, it delays.
When T1 commits and releases S, T2's X is granted. Why this step? Once the last conflicting S is gone the cell (S,X) no longer applies — there is nothing left to conflict with — so the engine may safely hand T2 its X and let the write proceed.

Verify: During T1's hold, bal stays at its read value; only after release does it become 0. No reader ever sees a half-written value. ✔

Worked example Ex 3 — Two writers, same item (X vs X)

Statement. Both T1 and T2 read x = 10, then both try UPDATE ... SET x = x + 1 on the same row.

Forecast: what is the final value of x — 11 or 12?

T1 acquires X on the row, computes $10 + 1 = 11$ , holds X until commit. Why this step? X on X is ❌ (matrix cell (X,X)), so only one writer proceeds at a time.
T2's X request blocks until T1 commits and releases. Why this step? This serialization is exactly what prevents the lost update.
T2 now re-reads the committed value $11$ , computes $11 + 1 = 12$ , writes. Why this step? Because T2 was blocked until after T1 committed, it reads T1's fresh $11$ (not the stale $10$ ) — so its increment lands on top instead of overwriting, giving the correct $12$ .

Verify: Final x = 12. Had X–X been (wrongly) compatible, both would compute $10 + 1 = 11$ → lost update. The lock gives the correct $12$ . ✔

Worked example Ex 4 — Two writers, different items (IX + IX)

Statement. T1 updates row 5; T2 updates row 99 of the same table. Do they block each other at the table level?

Forecast: the word "exclusive" is in both IX names — does that force a conflict?

T1 acquires IX on table accounts, then X on row 5. Why this step? Rule: to X a row you must first IX its table (the announcement system).
T2 acquires IX on accounts. Existing = IX, Requested = IX → matrix cell (IX,IX) = ✅. Why this step? IX only announces "some X below," not "the whole table." Two announcements coexist.
T2 acquires X on row 99. Different item from row 5 → no row-level conflict. Why this step? An X lock attaches to one specific item; rows 5 and 99 are different items, so the (X,X) conflict never even arises — there is no shared item to fight over.

Verify: They touch disjoint rows, so no data overlaps → parallel execution is safe. Cell (IX,IX)=✅ confirms the table level; distinct rows confirm the fine level. ✔

Worked example Ex 5 — Intent vs full lock (IX vs S)

Statement. T1 holds IX on accounts (it is writing some row). T2 runs LOCK TABLE accounts IN SHARE MODE, needing S on the whole table. Granted or blocked?

Forecast: T2 only wants to read — surely reads are always allowed?

T2 requests S on the table. Existing = IX, Requested = S → matrix cell (IX,S) = ❌. Why this step? A full-table reader must be sure no row changes. But IX says a row is being written.
T2 blocks on the table lock. Why this step? Blocking, not rejecting, is how ❌ is enforced — T2 will be woken when the conflict clears.
When T1 commits (releasing IX and its row X), T2's S is granted. Why this step? Once IX is gone the table has no writers announced below it, so a whole-table read is now safe — the cell that blocked T2 no longer has an existing lock to conflict with.

Verify: The IX flag turned an $O (n)$ "scan every row for locks" into a single matrix cell check ( $O (1)$ ). Correctness preserved, cost minimized. ✔

Worked example Ex 6 — SIX combination (scan-then-update)

Statement. T1 runs a report that reads every row of accounts but also updates a handful (e.g. flag inactive accounts). Which table-level lock does it take, and can T2 read one row via S?

Forecast: does T1 need plain S, plain X, or something in between?

T1 takes SIX on the table. SIX = S on the whole node (to read all) + IX (to write a few rows). Why this step? Plain S forbids its own writes; plain X blocks all other readers needlessly. SIX is the exact fit.
T2 requests IS on the table (it wants S on one row → must first IS). Existing = SIX, Requested = IS → matrix cell (SIX,IS) = ✅. Why this step? SIX only announces writes below; a reader announcing a read below is compatible.
T2 requests S on a specific row. If T1 hasn't X-locked that row, T2 proceeds; if it has, T2 waits at the row level. Why this step? SIX guarantees safety only at the table level; the actual per-row safety is decided by whether that exact row carries T1's X — so we must drop down and re-check at row granularity.

Verify: SIX vs plain S — check the row: SIX vs S is ❌ (T2 cannot lock the whole table S while T1 reads-and-plans-writes), but SIX vs IS is ✅. So per-row readers are welcome, full-table readers are not — exactly the intended behaviour. ✔

Worked example Ex 7 — Intent-shared vs whole-table X (IS vs X)

Statement. T1 runs SELECT bal FROM accounts WHERE id=5 — internally it takes IS on the table, then S on row 5. While T1 holds these, T2 runs LOCK TABLE accounts IN EXCLUSIVE MODE (e.g. an ALTER TABLE), needing X on the whole table. Granted or blocked?

Forecast: IS is "only an intent" and X is "the whole table" — will the tiny intent flag really stop a full-table X?

T1 holds IS on the table (announcing "I hold an S on some row below") and S on row 5. Why this step? Under the protocol a row read needs IS on its ancestor first — that is the flag T2 will collide with.
T2 requests X on the whole table. Existing = IS, Requested = X → matrix cell (IS,X) = ❌ (the only ❌ in the IS row). Why this step? A whole-table X means "I will change/restructure everything." But IS says a reader is depending on some row right now — restructuring under it would corrupt that read.
T2 blocks until T1 commits and releases IS + the row S; only then is X granted. Why this step? X demands that nothing — not even a lightweight IS intent — coexists on the table; so the engine must wait for the very last intent flag to vanish before it can safely hand over the whole table.

Verify: IS is compatible with everyone except X (IS row = ✅,✅,✅,✅,❌). This example exercises that single ❌ cell — the intent-shared vs exclusive interaction — proving a "harmless" intent flag still blocks a full-table writer. ✔

Worked example Ex 8 — Degenerate / zero input (empty table)

Statement. accounts currently has zero rows. T1 runs UPDATE accounts SET bal=0 WHERE id=5. What locks are actually taken?

Forecast: with no rows, is any lock needed at all?

T1 acquires IX on the table — before it knows whether row 5 exists. Why this step? The protocol requires the intent lock on the ancestor first; the table exists even if empty.
The row scan finds no row 5 → no X row lock is placed (there is nothing to lock). Why this step? Row locks attach to rows; a missing row has nothing to attach to.
T1's UPDATE affects 0 rows and commits, releasing the IX. Why this step? Because only the table IX was ever acquired, commit has just that one lock to release — confirming the degenerate case still fully exercises the intent protocol even though no data changed.

Verify: Locks taken = 1 (table IX), 0 (rows). The degenerate case still exercises the IX protocol — this is why "grab a row X directly without the intent lock" is a mistake even when zero rows match. ✔

Worked example Ex 9 — Phantom / range edge case (a

future matching row) Statement. Row 5 does not exist yet. Under serializable isolation, T1 runs SELECT * FROM accounts WHERE id=5 (0 rows), then plans to re-run it later. Meanwhile T2 runs INSERT INTO accounts VALUES (5, 200). If nothing stopped T2, T1's second read would suddenly find a row that wasn't there — a phantom. What lock prevents it?

Forecast: an ordinary row X on row 5 can't exist (the row is absent) — so what does the engine lock instead?

T1 takes IS on the table, then a range/key lock on the predicate id=5 (the gap where row 5 would live), not a row lock. Why this step? You cannot lock a row that isn't there, so serializable isolation locks the key range — the empty slot — so it stays empty.
T2's INSERT of id=5 requests an X on that same key/gap. Existing = T1's range S-lock, Requested = X on the gap → the (S,X) conflict applies to the range → ❌. Why this step? Inserting into a range another transaction has read would create the phantom; the range lock turns "future row" into a lockable object.
T2 blocks until T1 commits; T1's two reads both return 0 rows → repeatable. Why this step? Holding the range lock to commit is what makes the count of matching rows stable, closing the phantom hole that plain row locks leave open.

Verify: T1 reads 0 rows both times (count stable at 0). Without the range lock T2 inserts and T1's second count becomes 1 → a phantom. The range (S,X) conflict = ❌ prevents it. At weaker levels (READ COMMITTED) no range lock is taken and phantoms are allowed. ✔

Worked example Ex 10 — Limiting case: writer starvation

Statement. A steady stream of readers each takes S on row 5, overlapping so at least one S is always held. A single writer T2 waits for X. When does T2 run?

Forecast: will T2 ever get its turn?

Each new S request meets existing S → (S,S)=✅ → granted immediately. Why this step? Naïvely, compatible requests skip the queue — so they keep jumping ahead of T2.
T2's X sees an S present → (S,X)=❌ → keeps waiting. In the limit of never-empty S holders, T2 waits forever — starvation. Why this step? This is the pathological boundary of "S never blocks anyone" — it does block writers, indefinitely.
Fix: fair schedulers make new S requests queue behind a waiting X, so the S-holders drain and T2 eventually runs. Why this step? Forcing new S to wait behind the pending X breaks the infinite supply of overlapping readers — the current S-holders finish with none arriving to replace them, so the (S,X) conflict finally clears and T2 proceeds in bounded time.

Verify: Without fairness, T2's wait time $\to \infty$ as reader arrival rate stays $\geq$ release rate. With FIFO fairness, T2 waits at most until current S-holders release → finite. This is why long-held S locks are dangerous. ✔

Worked example Ex 11 — Real-world word problem: bank transfer

Statement. Transfer $100 from account A (row 1, bal=500) to account B (row 2, bal=300). Two statements: UPDATE ... bal = bal-100 WHERE id=1 then ... bal = bal+100 WHERE id=2, one transaction T. What locks, and what are the final balances?

Forecast: predict A's and B's final balances and total.

T acquires IX on the table (it will write rows → intent first). Why this step? Both writes are rows; the ancestor announcement is mandatory.
T acquires X on row 1, sets $500 - 100 = 400$ . Holds X to commit (under Two-Phase Locking). Why this step? X serializes writers on A; nobody reads A's half-done value.
T acquires X on row 2, sets $300 + 100 = 400$ . Both X held until commit. Why this step? Holding both to commit guarantees atomicity — either both changes land or neither.
Commit releases IX + both X. Why this step? Releasing only at commit (never mid-transaction) is what keeps the transfer atomic and isolated — no other transaction can observe A debited before B is credited, so the $100 never appears to vanish.

Verify: A: $500 - 100 = 400$ . B: $300 + 100 = 400$ . Total before $= 800$ , after $= 400 + 400 = 800$ → conserved. Correct transfer. ✔

Worked example Ex 12 — Exam twist: S→X upgrade, deadlock, and the U lock

Statement. T1 and T2 both first read row 5 (each takes S). Then each tries to update row 5 (each needs to upgrade its S → X). What happens, and how does the U (update) lock fix it?

Forecast: smooth upgrade, or trouble?

T1 holds S, T2 holds S on row 5 → (S,S)=✅, both fine so far. Why this step? Reads share happily.
T1 requests upgrade S→X. But T2 still holds S → (S,X)=❌ → T1 waits for T2 to drop its S. Why this step? An upgrade to X needs all other S gone.
T2 requests upgrade S→X. T1 still holds S → T2 waits for T1. Now each waits for the other → a deadlock. Why this step? Both hold what the other needs and neither will release first — a cycle.
The U-lock fix (see figure). A transaction that reads a row it intends to soon write takes U instead of plain S. U is compatible with existing S (other pure readers keep reading) but U is not compatible with another U — so only one would-be upgrader can exist at a time. Why this step? If only one transaction can hold U, only one candidate-writer exists; the second upgrader blocks at the U request, before it ever holds anything the first one is waiting for. With no second holder, there is no second edge to close the wait-for cycle.
Sequence with U: T1 takes U on row 5, reads; T2 requests U → (U,U)=❌ → T2 blocks immediately. T1 upgrades U→X (no other S/U present), writes, commits, releases. Then T2 gets U, upgrades, writes. Why this step? Serializing the writers up front converts a deadlock-prone race into an orderly queue — no cycle can form.

Verify: Without U: wait-for graph has edges T1→T2 and T2→T1 — a directed cycle = deadlock (the detector must abort a victim). With U: only T1→(nothing) while T2 waits on the U request → the graph is a single edge T2→T1, acyclic → no deadlock. Removing one edge breaks the cycle, exactly as claimed. (An MVCC engine dodges it differently: readers take no S at all.) ✔

Which cell decides Ex 3, and the wrong answer?

Cell (X,X)=❌; ignoring it gives the lost update, final x=11 instead of 12.

Which is the only conflict in the IS row (Ex 7)?

IS vs X — an intent-shared reader below blocks a whole-table exclusive lock; IS is compatible with IS, IX, S, and SIX.

How many locks on the empty table in Ex 8, and why?

One — the table IX; the protocol demands the intent lock on the ancestor before scanning, even with zero rows.

What is locked in Ex 9 when the row is absent, and what does it stop?

A key-range / gap lock on the predicate id=5; it blocks T2's INSERT and so prevents a phantom row appearing between T1's two reads.

What turns harmless S into starvation?

S–X is ❌, so a continuous overlap of readers keeps blocking a waiting writer forever unless the scheduler is fair.

What proves Ex 12 is a deadlock, and how does U fix it?

A directed cycle in the wait-for graph (T1 waits T2, T2 waits T1); U is mutually exclusive with another U, so only one writer-candidate exists, the second edge never forms, and the cycle can't close.