Multiple Granularity Locking in DBMS

Multiple granularity locking provides different levels of locks for different database objects from the entire database down to individual records. This maximizes concurrency while maintaining consistency.

Granularity Hierarchy

Lock Types

• Shared (S) Multiple transactions can read simultaneously.
• Exclusive (X) Only one transaction can read/write.
• Intent Shared (IS) Intends to acquire S locks on descendants.
• Intent Exclusive (IX) Intends to acquire X locks on descendants.
• Shared + Intent Exclusive (SIX) S lock on current node + IX on descendants.

Lock Compatibility Matrix

Protocol Rules

• Lock the root first in any mode.
• Lock a node in S or IS only if parent is locked in IS or IX.
• Lock a node in X, IX, or SIX only if parent is locked in IX or SIX.
• Follow two-phase locking no new locks after any unlock.
• Unlock a node only if no child nodes are currently locked.
• Acquire locks top-down, release locks bottom-up.

Conclusion

Multiple granularity locking lets transactions lock at the appropriate level (database, table, or record) using intention locks to signal locking intent to lower levels. This maximizes concurrency while maintaining consistency, and is widely used in banking, e-commerce, and inventory systems.