4.4.19
Ek shared library book imagine karo. Bahut saare log ek hi book ko ek saath padh sakte hain (koi nuksaan nahi). Lekin usse edit karne ke liye, tumhe book bilkul akele chahiye — jab tum likhoge, koi aur na padh sakta hai na likh sakta hai. Database lock bilkul yahi courtesy rule hai, jo engine enforce karta hai taaki do transactions ek hi data ko corrupt na kar dein. Intent locks waise hain jaise poore shelf par ek sticky note lagana — "Main is shelf se ek book lene wala hoon" — taaki baaki logon ko pata ho ki puri shelf ko wholesale lock na karein.
Definition Problem: concurrent accessJab do transactions ek hi data ko ek saath touch karte hain, toh anomalies aati hain:
Lost update — dono ne x = 10 x + 1 12 11 Dirty read — tune wo data padha jo doosre transaction ne likha tha lekin commit nahi kiya.Non-repeatable read — tune x
Ek lock ek aisa mechanism hai jo ek transaction ko kisi data item ka temporary, controlled access deta hai, aur doosron ka conflicting access tab tak block karta hai jab tak lock release na ho.
KYA lock karte hain: rows, pages, tables (granularity).
KAISE correctness enforce karte hain: ek transaction ko read/write se pehle sahi lock acquire karna hoga, aur use hold karna hoga (usually commit tak, Two-Phase Locking ke under).
Definition Shared (S) aur Exclusive (X)
Shared lock (S) — "Main read karna chahta hoon." Multiple transactions ek hi item par simultaneously S hold kar sakte hain.Exclusive lock (X) — "Main write karna chahta hoon." Sirf EK transaction ise hold kar sakta hai, aur koi bhi S lock saath exist nahi kar sakta.
Intuition Do modes KYUN, ek kyun nahi?Reading data change nahi karta, isliye bahut saare readers safe hain saath mein. Writing data change karta hai, isliye writer ko isolation chahiye. Agar hum sab kuch ke liye ek lock use karte, toh readers ek doosre ko unnecessarily block karte — performance kill ho jaati. S/X mein split karne se readers parallel run kar sakte hain jabki writers serialized rehte hain.
Socho: kya do requests bina kisi anomaly ke saath exist kar sakti hain?
Existing →
S X
S ✅ compatible
❌ conflict
X ❌ conflict
❌ conflict
KYUN har cell:
S–S ✅: do readers ek hi unchanged value dekhte hain → safe.
S–X ❌: writer wo data change kar dega jis par reader depend kar raha hai → unsafe.
X–S ❌: reader ek uncommitted/changing value dekhega → unsafe.
X–X ❌: do writers = lost update → unsafe.
"Share is fair, Exclusive is selfish." S sirf S se haath milata hai; X sabko mana kar deta hai (khud ko bhi).
Intuition Intent locks KYUN exist karte hainMaano Transaction A ek table ki ek row ko X se lock karta hai. Ab Transaction B poori table ko X se lock karna chahta hai (jaise ALTER TABLE). B ko kaise pata chalega ki ek row busy hai? Bina help ke, B ko har row scan karni padegi locks check karne ke liye — bahut slow hoga. Intent locks yahi solve karte hain: row lock karne se pehle, A table level par ek lightweight flag lagata hai — "Andar kuch lock karne wala hoon." Ab B sirf table-level flag check karta hai — O ( n ) O ( 1 )
Definition Intent lock modesEk coarse object (table) par rakhe jaate hain, yeh signal dene ke liye ki neeche finer lock (row) held hai:
IS (Intent Shared) — "Mere paas/hoga S lock kuch neeche ka."IX (Intent Exclusive) — "Mere paas/hoga X lock kuch neeche ka."SIX (Shared + Intent Exclusive) — "Main poore node par S hold karta hoon AUR kuch children ke liye IX bhi." (Sab kuch padho, kuch likho.)
KAISE row locking kaam karta hai (rule): row par S lene ke liye → pehle table par IS lo. Row par X lene ke liye → pehle table par IX lo.
IS
IX
S
SIX
X
IS ✅
✅
✅
✅
❌
IX ✅
✅
❌
❌
❌
S ✅
❌
✅
❌
❌
SIX ✅
❌
❌
❌
❌
X ❌
❌
❌
❌
❌
Do intent locks (IS, IX) ek doosre se almost hamesha compatible hote hain — yeh sirf fine-grained activity announce karte hain, poore node ko lock nahi karte. IX–IX ✅ kyunki do transactions alag-alag rows likh sakte hain. Lekin IX–S ❌: ek full-table reader (S) us kisi ke saath coexist nahi kar sakta jo koi row likh raha ho (IX), kyunki woh writer ek aisi row change kar sakta hai jo reader dekh raha hai.
Worked example Example 1 — Do readers, koi conflict nahiT1: SELECT * FROM accounts WHERE id=5 → row 5 par S request karta hai.
T2: SELECT * FROM accounts WHERE id=5 → row 5 par S request karta hai.
Result: dono granted (S–S ✅). Yeh concurrently run karte hain.
Yeh step kyun? Koi bhi data modify nahi karta, isliye read share karna safe hai.
Worked example Example 2 — Reader writer ko block karta haiT1 row 5 par S hold karta hai (reading).
T2: UPDATE accounts SET bal=0 WHERE id=5 → row 5 par X chahiye.
Result: T2 wait karta hai jab tak T1 release na kare (S–X ❌).
Yeh step kyun? T2 ko likhne dena wo data change kar deta jis par T1 abhi bhi depend kar raha hai → non-repeatable read.
Worked example Example 3 — Intent locks in actionT1: UPDATE accounts SET bal=0 WHERE id=5.
Steps: (1) table accounts par IX acquire karo; (2) row 5 par X acquire karo.
T2: LOCK TABLE accounts IN SHARE MODE → poori table par S chahiye.
Result: T2 block ho jaata hai. Kyun? Table-level check: existing IX vs requested S → ❌ matrix mein. T2 ko turant pata chal jaata hai ki table par "kuch likha ja raha hai" bina rows scan kiye.
Yeh step kyun? IX flag ne O ( n )
Worked example Example 4 — IX–IX coexistenceT1 row 5 update karta hai; T2 row 99 update karta hai. Dono table par IX acquire karte hain (✅), phir alag-alag rows par X (koi conflict nahi — alag items hain). Dono parallel mein proceed karte hain.
Yeh step kyun? Intent locks activity announce karte hain lekin table ko monopolize nahi karte, isliye disjoint writers saath exist karte hain.
Common mistake "Intent lock table ko lock karta hai, isliye IX–IX conflict karna chahiye."Kyun sahi lagta hai: IX mein word "exclusive" bolata hai sirf ek allowed .
Fix: IX ek X lock nahi hai. Yeh sirf announce karta hai ki koi X neeche exist karta hai. Do transactions alag-alag rows likhne ke liye dono ko table par IX chahiye — aur yeh theek hai. Actual conflict (X–X) row level par hota hai, sirf agar woh ek hi row hit karein.
Common mistake "Shared locks kabhi kisi ko block nahi karte."Kyun sahi lagta hai: S–S compatible hai, isliye S harmless lagta hai.
Fix: S X ko block karta hai. S hold karne wala reader kisi bhi writer ko stall kar deta hai jo woh item chahta ho (S–X ❌). Zyada der tak hold kiye gaye S locks writer starvation ki ek real wajah hain.
Common mistake "Tum table ko touch kiye bina directly row X lock le sakte ho."Kyun sahi lagta hai: tum sirf ek row ki parwah karte ho.
Fix: multi-granularity protocol ke under tumhe pehle har ancestor (table) par intent lock acquire karna hoga — warna announcement system toot jaata hai aur coarse lockers tumhe detect nahi kar sakte.
Common mistake SIX ko "S aur X ek saath" samajhna.Kyun sahi lagta hai: naam mein S aur X dono listed hain.
Fix: SIX = poore node par S + children ke liye IX (intent) . Matlab "Main poori table padh raha hoon lekin kuch rows likhne ka plan hai" — scan-then-update ke liye common.
Recall Feynman: ek 12-saal ke bacche ko samjhaoEk coloring book socho jo sab share karte hain. Agar kuch bachche sirf ek page dekhna chahte hain, toh sab saath crowd kar sakte hain — yeh hai shared lock . Lekin agar ek bacchi page color karna chahti hai, toh woh use le jaati hai taaki koi aur na dekhe na color kare — yeh hai exclusive lock . Ab book bahut badi hai, isliye page pakadne se pehle, tum cover par ek flag lagaate ho — "Hey, Main andar ek page use kar raha hoon!" — yeh hai intent lock . Yeh flag ek teacher ko, jo poori book chahta ho, turant pata deta hai ki yeh busy hai, bina har page palte.
Recall Active recall — answers cover karo
Kaun se do base lock modes exist karte hain, aur kaun se pairs compatible hain?
IX–IX compatible kyun hai lekin IX–S nahi?
Intent locks kaunsi problem solve karte hain, Big-O terms mein?
SIX ka matlab kya hai?
Shared (S) lock kya permit karta hai? Concurrent reads — multiple transactions ek hi item par S hold kar sakte hain; kisi bhi X ko block karta hai.
Exclusive (X) lock kya permit karta hai? Ek single writer; us item par koi doosra S ya X nahi.
S aur X mein kaun se lock-mode pairs compatible hain? Sirf S–S. S–X, X–S, X–X sab conflict karte hain.
Intent Shared (IS) lock kya hai? Ek coarse-level flag jo announce karta hai ki transaction kisi finer-grained child par S lock hold karta/karega.
Intent Exclusive (IX) lock kya hai? Ek coarse-level flag jo announce karta hai ki transaction kisi child par X lock hold karta/karega.
IX aur IX compatible kyun hain? Yeh sirf neeche writes announce karte hain; do transactions alag-alag rows likh sakte hain, isliye coarse node par koi real conflict nahi.
IX, S ke saath incompatible kyun hai? S poore node ko reading ke liye lock karta hai; IX matlab koi child likh raha ho sakta hai, jo reader ka data change kar sakta hai.
Intent locks kaunsi problem solve karte hain? Yeh sab child rows (O ( n ) O ( 1 )
SIX ka matlab kya hai? Poore node par Shared lock PLUS kuch children ke liye Intent-Exclusive: sab kuch padho, kuch rows likho.
Row par X lock lene se pehle pehle kya acquire karna hoga? Uske parent (table) par IX (intent exclusive) lock, multi-granularity protocol ke anusaar.
Teen anomalies batao jo locking prevent karta hai. Lost update, dirty read, non-repeatable read.
Two-Phase Locking — locks kab acquire/release hote hain serializability guarantee karne ke liye.ACID Properties — Isolation mostly locking ke through implement hoti hai.Isolation Levels — Read Committed / Repeatable Read / Serializable lock duration tune karte hain.Deadlocks — conflicting order mein held locks cycles cause karte hain; detection & victims.MVCC — read-locks ka alternative: readers snapshots dekhte hain S locks lene ki jagah.Lock Granularity — row vs page vs table tradeoffs jo intent locks ko motivate karte hain.
avoids scanning all rows O n
Multi-granularity locking
