Imagine a shared library book . Many people can read the same book at once (no harm). But to edit it, you need the book entirely to yourself — nobody else can read or write while you scribble. A database lock is exactly this courtesy rule, enforced by the engine so two transactions don't corrupt the same data. Intent locks are like putting a sticky note on the whole shelf saying "I'm about to grab a book on this shelf" — so others know not to lock the shelf wholesale.
Definition The problem: concurrent accessWhen two transactions touch the same data at the same time , you get anomalies:
Lost update — both read x = 10 x=10 x = 10 x + 1 x+1 x + 1 11 11 11 12 12 12 Dirty read — you read data another transaction wrote but hasn't committed.Non-repeatable read — you read x x x
A lock is a mechanism that grants a transaction temporary, controlled access to a data item, blocking conflicting access by others until released.
WHAT we lock: rows, pages, tables (granularity).
HOW we enforce correctness: a transaction must acquire the right lock before it reads/writes, and hold it (usually until commit, under Two-Phase Locking ).
Definition Shared (S) and Exclusive (X)
Shared lock (S) — "I want to read ." Multiple transactions can hold S on the same item simultaneously.Exclusive lock (X) — "I want to write ." Only ONE transaction can hold it, and no S locks may coexist.
Intuition WHY two modes and not one?Reading doesn't change data, so many readers are safe together . Writing changes data, so a writer needs isolation . If we used one lock for everything, readers would needlessly block each other — killing performance. Splitting into S/X lets readers run in parallel while still serializing writers.
Ask: can two requests coexist without an anomaly?
Existing →
S X
S ✅ compatible
❌ conflict
X ❌ conflict
❌ conflict
WHY each cell:
S–S ✅: two readers see the same unchanged value → safe.
S–X ❌: a writer would change data a reader is depending on → unsafe.
X–S ❌: reader would see an uncommitted/changing value → unsafe.
X–X ❌: two writers = lost update → unsafe.
"Share is fair, Exclusive is selfish." S shakes hands with S only; X refuses everyone (including itself).
Intuition WHY intent locks existSuppose Transaction A locks one row of a table with X. Now Transaction B wants to lock the whole table with X (e.g. ALTER TABLE). How does B know a single row is busy? Without help, B would have to scan every row checking for locks — horribly slow. Intent locks solve this: before locking a row, A first places a lightweight flag at the table level announcing "I intend to lock something inside." Now B just checks the table-level flag — O ( 1 ) O(1) O ( 1 ) O ( n ) O(n) O ( n )
HOW locking a row works (rule): to get S on a row → first get IS on the table. To get X on a row → first get IX on the table.
IS
IX
S
SIX
X
IS ✅
✅
✅
✅
❌
IX ✅
✅
❌
❌
❌
S ✅
❌
✅
❌
❌
SIX ✅
❌
❌
❌
❌
X ❌
❌
❌
❌
❌
Intuition Reading the matrixTwo intent locks (IS, IX) are almost always compatible with each other — they only announce fine-grained activity, they don't lock the whole node. IX–IX ✅ because two transactions can each be writing different rows. But IX–S ❌: a full-table reader (S) cannot coexist with someone writing some row (IX), because that writer might change a row the reader sees.
Worked example Example 1 — Two readers, no conflictT1: SELECT * FROM accounts WHERE id=5 → requests S on row 5.
T2: SELECT * FROM accounts WHERE id=5 → requests S on row 5.
Result: both granted (S–S ✅). They run concurrently.
Why this step? Neither modifies data, so sharing the read is safe.
Worked example Example 2 — Reader blocks writerT1 holds S on row 5 (reading).
T2: UPDATE accounts SET bal=0 WHERE id=5 → needs X on row 5.
Result: T2 waits until T1 releases (S–X ❌).
Why this step? Letting T2 write would change data T1 is still depending on → non-repeatable read.
Worked example Example 3 — Intent locks in actionT1: UPDATE accounts SET bal=0 WHERE id=5.
Steps: (1) acquire IX on table accounts; (2) acquire X on row 5.
T2: LOCK TABLE accounts IN SHARE MODE → needs S on the whole table.
Result: T2 blocks. Why? Table-level check: existing IX vs requested S → ❌ in the matrix. T2 instantly knows the table is "being written somewhere" without scanning rows.
Why this step? The IX flag turned an O ( n ) O(n) O ( n )
Worked example Example 4 — IX–IX coexistenceT1 updates row 5; T2 updates row 99. Both acquire IX on the table (✅), then X on different rows (no conflict — different items). Both proceed in parallel.
Why this step? Intent locks announce activity but don't monopolize the table, so disjoint writers coexist.
Common mistake "An intent lock locks the table, so IX–IX should conflict."Why it feels right: the word "exclusive" in IX screams only one allowed .
The fix: IX is not an X lock. It only announces that some X exists below . Two transactions writing different rows both need IX on the table — and that's fine. The actual conflict (X–X) happens at the row level, only if they hit the same row.
Common mistake "Shared locks never block anyone."Why it feels right: S–S is compatible, so S seems harmless.
The fix: S blocks X . A reader holding S will stall any writer wanting that item (S–X ❌). Long-held S locks are a real source of writer starvation.
Common mistake "You can grab a row X lock directly without touching the table."Why it feels right: you only care about one row.
The fix: under multi-granularity protocol you must first acquire the intent lock on every ancestor (table) — otherwise the announcement system breaks and coarse lockers can't detect you.
Common mistake Confusing SIX with "S and X at once."Why it feels right: the name lists S and X.
The fix: SIX = S on the whole node + IX (intent) for children . It means "I'm reading the entire table but plan to write a few rows" — common for scan-then-update.
Recall Feynman: explain it to a 12-year-oldThink of a coloring book everyone shares. If a few kids just want to look at a page, they can all crowd around — that's a shared lock . But if one kid wants to color a page, she takes it away so no one else can look or color — that's an exclusive lock . Now the book is huge, so before grabbing a page, you stick a flag on the cover saying "Hey, I'm using a page inside!" — that's an intent lock . The flag lets a teacher who wants the whole book instantly know it's busy, without flipping through every page.
Recall Active recall — cover the answers
What two base lock modes exist, and which pairs are compatible?
Why is IX–IX compatible but IX–S not?
What problem do intent locks solve, in Big-O terms?
What does SIX mean?
What does a Shared (S) lock permit? Concurrent reads — multiple transactions may hold S on the same item; blocks any X.
What does an Exclusive (X) lock permit? A single writer; no other S or X on that item.
Which lock-mode pairs are compatible among S and X? Only S–S. S–X, X–S, X–X all conflict.
What is an Intent Shared (IS) lock? A coarse-level flag announcing the transaction holds/will hold an S lock on some finer-grained child.
What is an Intent Exclusive (IX) lock? A coarse-level flag announcing the transaction holds/will hold an X lock on some child.
Why are IX and IX compatible? They only announce writes below; two transactions can write different rows, so no real conflict at the coarse node.
Why is IX incompatible with S? S locks the whole node for reading; an IX means someone may be writing a child, which could change data the reader sees.
What problem do intent locks solve? They avoid scanning all child rows (
O ( n ) O(n) O ( n ) ) to detect a conflict; a coarse-level check is
O ( 1 ) O(1) O ( 1 ) .
What does SIX mean? Shared lock on the whole node PLUS Intent-Exclusive for some children: read everything, write a few rows.
Before getting an X lock on a row, what must you acquire first? An IX (intent exclusive) lock on its parent (table), per the multi-granularity protocol.
Name three anomalies locking prevents. Lost update, dirty read, non-repeatable read.
Two-Phase Locking — when locks are acquired/released to guarantee serializability.ACID Properties — Isolation is largely implemented via locking.Isolation Levels — Read Committed / Repeatable Read / Serializable tune lock duration.Deadlocks — locks held in conflicting order cause cycles; detection & victims.MVCC — alternative to read-locks: readers see snapshots instead of taking S locks.Lock Granularity — row vs page vs table tradeoffs that motivate intent locks.
avoids scanning all rows O n
Multi-granularity locking
Intuition Hinglish mein samjhoDekho, database me jab do transactions ek hi data ko ek saath touch karte hain, to gadbad ho sakti hai — jaise dono ne balance padha, dono ne badhaya, par ek update kho gaya (lost update). Isko rokne ke liye lock lagate hain. Do main type hote hain: Shared (S) matlab "main sirf padhna chahta hoon" — bahut saare log ek saath padh sakte hain, no problem. Exclusive (X) matlab "main likhna chahta hoon" — ab pura item akele chahiye, koi aur padh ya likh nahi sakta. Yaad rakho: S–S compatible hai, baaki sab (S–X, X–X) conflict hai. Simple rule: padhna share karo, likhna selfish hai.
Ab problem ye hai ki agar koi transaction ek row pe X lock laga de, aur dusra transaction pure table ko lock karna chahe, to use kaise pata chale ki andar koi row busy hai? Agar har row scan kare to bahut slow ho jayega (O ( n ) O(n) O ( n ) intent lock aaye. Row pe lock lagane se pehle, transaction table pe ek chhota sa flag laga deta hai — IS (andar S lagega) ya IX (andar X lagega). Ab table-lock chahne wala bas us flag ko check karta hai, O ( 1 ) O(1) O ( 1 )
Sabse important confusion: IX–IX compatible hai! Logon ko lagta hai "Exclusive" word hai to conflict hoga, par nahi — IX sirf announce karta hai ki "main neeche kuch likhunga," asli X lock to row level pe lagta hai. Do transactions alag-alag rows likh rahe hain to dono IX le sakte hain, koi dikkat nahi. SIX ka matlab hai poora table padh raha hoon + kuch rows likhne ka iraada — scan karke kuch update karne wale kaam me kaam aata hai.
Ye sab important kyun hai? Kyunki real databases me hazaaron transactions ek saath chalte hain. Sahi locking se aapko isolation (ACID ka I) milta hai bina performance maare. Agar locks galat samjhe to deadlock, slow queries, ya data corruption — sab ho sakta hai. Interview me bhi compatibility matrix bahut poocha jaata hai!
