Tausif Tech Hub

 

Multigranularity Locking is a database concurrency control mechanism that allows transactions to lock data at various levels of granularity (e.g., database, table, page, row) to enhance concurrency and performance while maintaining consistency.

In traditional locking, transactions lock data at a single level (e.g., row-level or table-level). However, this may either reduce concurrency (if locks are too coarse) or increase overhead (if locks are too fine-grained). Multigranularity locking addresses this by allowing locks at multiple levels in a hierarchical structure.

Lock Hierarchy Example:

• Database
  └── Table
  └── Page
  └── Row

To manage this, multigranularity locking introduces Intention Locks, which indicate a transaction’s intent to acquire locks at lower levels:

• IS (Intention Shared): Intention to acquire shared locks below.

• IX (Intention Exclusive): Intention to acquire exclusive locks below.

• S (Shared): Read access.

• X (Exclusive): Read and write access.

• SIX (Shared and Intention Exclusive): Shared lock on a node and intention to acquire exclusive locks below.

Benefits:

• Supports high concurrency.

• Reduces locking overhead.

• Prevents conflicts between coarse and fine-grained locks.

Limitations:

• Slightly complex to implement.

• Lock compatibility checks are more involved.

In summary, multigranularity locking balances concurrency and resource usage by allowing locks at various levels with coordination through intention locks.