4.4.19 · D5Databases
Question bank — Locking — shared, exclusive, intent locks
Before we start, one reminder of the vocabulary this page assumes — every term was built in the parent note Locking — shared, exclusive, intent locks (index 4.4.19):
Recall The five modes in one breath
- S (Shared) — "I want to read." Many S can coexist.
- X (Exclusive) — "I want to write." Alone, blocks everyone.
- IS (Intent Shared) — coarse flag: "there's an S somewhere below me."
- IX (Intent Exclusive) — coarse flag: "there's an X somewhere below me."
- SIX — "S on this whole node and IX for a few children."
The one picture this whole page leans on
Almost every answer below cites "the matrix" or "the tree." Rather than make you rebuild them from memory, here they are — keep them in view while you drill.
The compatibility matrix
The single question every cell answers: if the column mode is already held, can the row mode be granted right now? A ✅ means "coexist, no anomaly"; a ❌ means "the requester must wait."
The granularity tree
Multi-granularity locking is easier to see than to describe. A database is a nested container: the table holds pages, each page holds rows. To lock a fine object you first drop an intent flag on every coarser container above it — so a transaction wanting the whole table only has to glance at the table node, never walk down to the leaves.
True or false — justify
Two readers on the same row can run at the same time.
True. Reading does not change data, so multiple S locks on one item coexist (S–S ✅) — many people reading the same library book harms nobody.
A shared lock never blocks any other transaction.
False. S is compatible with S, but S blocks X (S–X ❌). A long-held reader will stall any writer wanting that same item — a classic writer-starvation source.
If T1 holds IX on a table, no other transaction can touch that table.
False. IX only announces a write below; another transaction can also take IX (IX–IX ✅) and write a different row. IX blocks table-wide S and X, not other intent locks.
Two transactions each writing a different row must conflict at some point.
False. They take IX on the table (compatible) and X on different rows (different items never conflict). They run fully in parallel.
X is incompatible with itself.
True. Two writers on the same item = lost update, so X–X ❌. X is the only mode that refuses even a copy of itself.
An S lock and an IS lock on the same table can coexist.
True. A full-table reader (S) and someone announcing a read below (IS) are both non-mutating, so S–IS ✅ in the matrix above.
SIX means holding both an S lock and an X lock on the same node.
False. SIX = S on the whole node + IX (intent) for children. It means "read the entire table, plan to write a few rows" — no X is held on the node itself.
You may acquire an X lock on a row without first touching the table.
False. Under the multi-granularity protocol you must first get IX on every ancestor (the table). Skipping it breaks the announcement system so coarse lockers can't detect you.
Intent locks make correctness stricter (allow fewer things).
False. Intent locks don't change which fine-grained conflicts exist; they make detection faster — an table check instead of an row scan. Correctness is identical, performance is better.
If two transactions both want to lock the whole table with S, they conflict.
False. S–S ✅ even at table granularity. Two full-table readers coexist; the conflict only appears if one of them wants X.
Spot the error
"T1 holds S on row 5; T2 requests X on row 5 and is granted immediately."
Error. S–X ❌, so T2 must wait until T1 releases. Granting X now would let T2 change data T1 still depends on (non-repeatable read).
"T1 holds IX on table; T2 requests S on the whole table and both proceed."
Error. IX–S ❌ (see the matrix). A full-table reader cannot coexist with a writer somewhere below, because that writer might change a row the reader sees. T2 blocks.
"Because IX contains the word 'exclusive', only one transaction can hold IX on a table."
Error. IX is not an X lock; it is a lightweight announcement. Multiple IX holders coexist (IX–IX ✅). The real exclusivity happens at the row's X lock, and only on the same row.
"To read row 5, T1 takes IX on the table then S on the row."
Error. To read a row you first take IS (not IX) on the table, then S on the row. IS is for read-below, IX is for write-below.
"S–SIX is compatible because both involve sharing."
Error. S–SIX ❌. SIX includes an IX component (a plan to write children), which conflicts with a full-node reader. Only IS survives alongside SIX.
"Two IX locks conflict, but that's fine because the rows differ."
Error. The premise is wrong: IX–IX is compatible (✅), which is exactly why different-row writers work. Nothing conflicts at the table level here.
"If no S locks exist on a row, an X request is always granted instantly."
Error. X also conflicts with an existing X. An X request waits if either any S or any X is held on that item.
Why questions
Why do we split locks into S and X instead of one universal lock?
Because reads are safe together but writes need isolation. One universal lock would force readers to block each other needlessly, destroying read parallelism; S/X lets readers run concurrently while still serializing writers.
Why is IX–IX compatible but X–X not?
IX only announces that some X exists below the node — the two announcements don't clash. The real conflict is deferred to the row level, where X–X only fires if both writers hit the same row.
Why do intent locks exist at all — what would break without them?
Without them, a transaction wanting the whole table would have to scan every row checking for locks — work. The intent flag lets it do one table-level matrix lookup instead (see the tree figure).
Why must you acquire intent locks on every ancestor, not just the immediate parent?
A coarse locker at any level (table, page) must be able to detect activity below by checking its own level. Skipping an ancestor leaves a blind spot where a wholesale lock could be granted over live fine-grained locks.
Why does a reader holding S block a writer, given S "doesn't change anything"?
The reader is depending on the value staying stable. Letting the writer proceed would change data mid-read, causing a non-repeatable read — so S must block X even though S itself mutates nothing.
Why is SIX useful — why not just take X on the whole table?
SIX lets you read the entire table while only exclusively locking the few rows you'll write, so other transactions can still take IS and read the untouched rows. A full-table X would block all of them.
Why is the compatibility matrix symmetric for real conflicts but asymmetric to read?
The underlying "can these coexist?" relation is symmetric (if A blocks B, B blocks A). The matrix lists rows as requested and columns as existing mostly for lookup convenience — the conflict truth is the same either way.
Edge cases
What lock does a transaction that reads the whole table but writes nothing take?
A plain S lock on the table — no intent needed, since there is no finer-grained write to announce.
What happens to X–X on different rows of the same table?
No conflict. Each holds IX on the table (compatible) and X on a distinct row; distinct items never conflict, so both proceed.
Can a transaction upgrade an S lock on a row to X while another transaction holds S on the same row?
No — the upgrade needs X, which conflicts with the other transaction's S (S–X ❌). The upgrade waits, and mutual waiting like this is a common cause of Deadlocks.
What is the coarsest lock compatible with an existing IS on a table?
Everything except X. IS is compatible with IS, IX, S, and SIX — it announces only a read below, so it clashes only with a full-node X.
If a transaction holds SIX on a table, what may another transaction still take on that table?
Only IS. SIX permits IS (read-below) alongside it, but blocks IX, S, SIX, and X — because SIX already reads the whole node and plans writes below.
What lock state exists on a table when zero transactions are active?
None — the table is unlocked, so any request (S, X, IS, IX, SIX) is granted immediately. Every conflict rule concerns existing locks; with none held, nothing blocks.
Is holding an S lock on a table the same as holding IS plus S on every row?
No. A single table-S is one lock covering the whole table; per-row S locks would be locks plus the IS announcement — the coarse S exists precisely to avoid that cost.
See also: Lock Granularity, Two-Phase Locking, Isolation Levels, MVCC, ACID Properties.