Some database engines take intent locks — modes named IS, IX and SIX — on a table before locking individual rows inside it. What problem do intent locks solve, and how does SIX differ from IX?

## The problem intent locks exist to solve Suppose transaction A holds an exclusive lock on one row deep inside a 50-million-row table. Transaction B now wants to lock the whole table exclusively — say, to run `ALTER TABLE`. How does B find out it must wait? With only S and X modes on rows, B would have to walk the entire lock manager looking for any lock whose resource belongs to that table. That is a linear scan on a hot, latched data structure, executed on every coarse lock request. It does not scale. **Multiple-granularity locking (MGL)** solves it with a rule: *before you may lock a node in the hierarchy, you must first lock all of its ancestors in a compatible intent mode.* The table entry then carries a summary of everything happening below it, and a table-level request becomes a single O(1) compatibility check. ## The intent modes - **IS — intent shared.** "I hold, or am about to hold, shared locks on some descendants." Taken on the table before an S row lock. - **IX — intent exclusive.** "I hold, or am about to hold, exclusive locks on some descendants." Taken on the table before an X row lock. - **SIX — shared with intent exclusive.** The composite: a real S lock on the whole object *plus* IX beneath it. Semantically "I am reading everything and writing some of it." Some engines also define IX-variants such as UIX or a separate schema-stability mode; those are refinements, not different ideas. ## The compatibility matrix ``` IS IX S SIX X IS yes yes yes yes no IX yes yes no no no S yes no yes no no SIX yes no no no no X no no no no no ``` Read it as "held mode (row) against requested mode (column)". Two facts carry the whole design: 1. **Intent modes are mutually compatible.** IS/IX/IX all coexist on the same table. They must — otherwise two transactions updating different rows of the same table would serialise, destroying the entire point of row-level locking. The intent lock is an *announcement*, not a reservation; the actual conflict is resolved at the row level. 2. **Intent conflicts with the real coarse modes.** A table-level S is incompatible with IX, because someone is writing rows underneath and a whole-table reader must not see them change. A table-level X is incompatible with everything, because it claims the entire subtree. ## Where SIX earns its place Consider a transaction that scans an entire table and updates a handful of rows it finds: ```sql UPDATE accounts SET fee = fee * 1.05 WHERE status = 'delinquent'; ``` Without SIX the transaction would need either a table-level X — far too strong, blocking innocent readers — or S plus IX, which the matrix forbids as a pair on the same object. SIX packages exactly the right combination: the whole table is read-stable for this transaction, other readers holding IS may proceed, and other *writers* (who need IX, S, SIX or X) are all blocked. That asymmetry is the interview point. SIX is strictly stronger than IX and strictly weaker than X: it admits readers, excludes writers. ## Lock conversion and the hierarchy Modes are not fixed for a transaction's lifetime. A transaction that starts with IS on a table and then decides to write must **convert** its table lock to IX (or SIX if it already holds table-level S). Conversion requests queue like any other and can participate in deadlocks — two transactions each holding IS and each requesting SIX will wait on each other until the detector abandons one. The hierarchy also extends downward. In an engine with page locks the chain is table → page → row, and a row X lock requires IX on the page *and* IX on the table. Acquisition goes top-down; release goes bottom-up, which is what keeps the summary at each level truthful at all times. ## Which engines expose this MGL with explicit IS/IX/SIX is the classic System R design and is visible in SQL Server and DB2. MySQL/InnoDB implements intent locks too: `IS` and `IX` are genuine table-level modes there, and `SELECT ... FOR UPDATE` takes IX on the table before X on the rows. PostgreSQL takes the same idea with different naming — `ROW SHARE` and `ROW EXCLUSIVE` are its intent modes, and they are precisely what makes `ACCESS EXCLUSIVE` (taken by DDL) a one-check conflict test. ## What interviewers listen for The efficiency argument first: intent locks turn an O(rows) conflict search into O(1). Then the counter-intuitive compatibility fact — IX is compatible with IX, because the announcement is not the conflict. And finally SIX as the read-all/write-some composite, with the correct claim that it blocks writers while still admitting IS readers.