Multiple Granularity in DBMS

Multiple Granularity Kya Hota Hai?

DBMS mein Multiple Granularity ek technique hai jo concurrency (yaani ek saath multiple transactions kaam karein) ko efficiently handle karne ke liye use hoti hai.

Granularity ka matlab hota hai data ke size ka level – jaise pura database, ek table, ek page, ya ek row (record).

Is technique mein hum data ko alag-alag levels pe lock kar sakte hain, jisse system ko flexibility milti hai aur performance bhi better hoti hai.

Granularity Hierarchy – Levels of Locking

Sochiye aapka data ek hierarchy mein arranged hai:

Aap ismein kisi bhi level pe lock laga sakte ho – isse kehte hain Multiple Granularity Locking.

Multiple Granularity Ki Need Kyu Padti Hai?

Agar hum poore database ko lock kar lein → simple but bahut restrictive (baaki users ruk jaayenge).
Agar hum sirf ek row ko lock karein → zyada flexible but zyada locks lagane padte hain → expensive.
Multiple Granularity humein choice deta hai ki kis level pe lock lagana best hoga.

Simple Example

Maan lo Students table hai:

Agar aap sirf ek student ka record update kar rahe ho → Row-level lock better hai.
Agar aap poora table delete kar rahe ho → Table-level lock better hoga.

Lock Types in Multiple Granularity

DBMS mein is system ko manage karne ke liye special locks use hote hain:

Lock Type	Meaning
IS (Intention Shared)	Neeche ke level pe Shared lock lagane ka plan hai
IX (Intention Exclusive)	Neeche ke level pe Exclusive lock lagane ka plan hai
S (Shared)	Read-only lock – sirf padh sakte ho
SIX (Shared + Intention Exclusive)	Is level pe read + neeche write
X (Exclusive)	Read + Write dono allowed
NL (No Lock)	Koi lock nahi hai

Kaise Kaam Karta Hai?

Maan lo ek transaction T1 hai:

T1 ko kuch rows read karni hain → woh IS lock table pe lagata hai
Fir woh S lock individual rows pe lagata hai

Agar doosra transaction T2 usi table ke rows ko update karna chahta hai:

Woh IX lock table pe lagata hai
Aur X lock rows pe

Isse system pehle hi samajh jaata hai ki kya conflict ho sakta hai, aur accordingly action leta hai.

Lock Compatibility Table

	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

Compatible = ek saath lag sakta hai
Conflict = wait ya abort

Diagram – Granularity with Lock Example

Is case mein transaction sirf ek row read kar raha hai:

Har level pe IS lock
Aur final row pe S lock

Advantages (Fayde)

Fayda	Description
Efficiency	Har baar poore table ya database ko lock nahi karna padta
Less overhead	Sirf zaroori part ko lock karte ho
More concurrency	Baaki users bhi alag part pe kaam kar sakte hain
Structured control	Hierarchy follow karte hue locking hoti hai

Disadvantages (Nuksaan)

Nuksaan	Description
Complexity zyada	Locking rules samajhna thoda difficult hai
Extra intention locks	Har level pe additional locks lagane padte hain

Summary Table

Term	Meaning
Granularity	Data ka size jise lock kar rahe hain
IS/IX	Intention batate hain ki neeche kya lock lagega
S/X	Actual read/write locks
SIX	Mix of read + write intention
Hierarchy	Database → Table → Page → Row → Field

Conclusion

Multiple Granularity ek smart way hai data locking ka.
Ismein aap data ke different levels pe lock laga sakte ho.
Intention Locks help karte hain conflicts ko detect karne mein.
Ye system concurrency ko improve karta hai aur unnecessary locks se bachaata hai.

The Hingineer