Exercises — Locking — shared, exclusive, intent locks

Granted. Two readers see the same unchanged value — S–S is the only compatible pair among the base modes. Why? Reading changes nothing, so parallel reads are safe.

• (a) → S (Shared)
• (b) → X (Exclusive)
• (c) → IX (Intent Exclusive — announces an X below)
• (d) → SIX (S on the node + IX for children)

True. The entire X row and X column are ❌. A writer needs total isolation, so no S, no X, no intent lock may coexist with it on the same item.

1. IX on table accounts — "I intend to write a row inside."
2. X on row 5 — the actual write lock.

Why the order? You must announce intent on every ancestor before locking the child; otherwise a coarse locker cannot detect you.

1. IS on table accounts.
2. S on row 5.

Read needs the intent-shared announcement, then the shared row lock.

T2 waits. Decision cell: existing IX vs requested S → ❌. T2 never scans rows; the table-level IX flag alone tells it the table is "written somewhere below." That is the whole point of intent locks: an $O(1)$ lookup instead of an $O(n)$ scan.

Yes, concurrent.

• Table level: T1 gets IX, T2 gets IX → IX–IX is ✅ (both merely announce writes below).
• Row level: T1 locks row 5 with X, T2 locks row 99 with X — different items, so the X–X rule never triggers (that rule is per-item).

The X–X conflict only fires when two writers hit the same row.

• IX–S ❌: a full-table reader (S) can't coexist with someone writing some row (IX) — the write could alter a row the reader sees.
• IX–IX ✅: both only announce writes below; disjoint rows never collide.
• SIX–IX ❌: SIX already holds S on the whole node; a new writer's intent (IX) conflicts with that node-wide read.
• X–IS ❌: the whole item is exclusively locked; no intent to even read a child is allowed.

Anomaly: lost update. T2's write overwrites T1's, so one increment vanishes. Prevention: T1 should take an X lock on $x$ before writing (indeed, hold it from its first read under Two-Phase Locking). Then T2's read (S) at t2 would wait — S–X conflict — until T1 commits, forcing $x=12$.

This is the classic SIX pattern — read everything, write a few.

1. SIX on table accounts = S over the whole table (for the scan) + IX (intent to write children).
2. X on row 5, X on row 99 (the two updates).

One coarse lock (SIX) simultaneously guards the full read and announces the pending writes. See Lock Granularity.

• T2 wants S: existing SIX vs requested S → ❌. Waits. (SIX's IX component conflicts with a table-wide reader.)
• T2 wants IS: existing SIX vs requested IS → ✅. Granted — IS is compatible with everything except X. T2 may then read individual rows that aren't X-locked.

Writer starvation. Because S–S is compatible, readers keep overlapping so the S lock on row 5 is never fully released; the X request (which needs zero S locks) waits forever. Fixes: a lock queue with fairness (once X is queued, block new S requests behind it), or switch to MVCC where readers don't take row locks at all — see Isolation Levels.

Deadlock. T1 waits on T2's row 99; T2 waits on T1's row 5 — a cycle. See Deadlocks. IX–IX being ✅ only settled the table level; the fatal conflict is X–X at the row level, on the two specific rows they cross-request. The engine breaks it by aborting one transaction (a victim).

A writer holds X on every item it touches until commit. Any reader needs S on that item, but S–X is ❌, so the reader cannot observe the item until the writer commits (or aborts, undoing the change). Therefore no transaction ever reads uncommitted data → no dirty reads.

• Without intent locks: the table-lock request must check each of the $n$ rows for an existing X → $O(n)$ scan.
• With intent locks: the writer left an IX flag on the table; the S request checks that single table-level entry → $O(1)$.

Intent locks convert an $O(n)$ scan into an $O(1)$ matrix lookup — the entire reason the mechanism exists.