In a real‑world database, data is organized in a hierarchical structure:

• Database → Tables → Pages → Records (rows)

If a concurrency control system used only one level of locking (for example, only table‑level locks), it would be either too restrictive (blocking many users) or too loose (not protecting enough).

Multiple‑granularity locking solves this by allowing transactions to acquire locks at different levels (database, table, page, record) and coordinating these locks so that consistency is maintained.

What Is Multiple‑Granularity Locking?

Multiple‑granularity locking is a lock‑based protocol that lets a transaction lock coarse‑grained items (like a whole table) or fine‑grained items (like a single row), depending on what it needs.

For example:

• Coarse‑granularity:

  • Lock an entire table (good when scanning or updating many rows).

• Fine‑granularity:

  • Lock a single row or page (good when only one record is modified).

Using multiple granularities improves performance and concurrency without losing correctness.

How It Uses Intent Locks

To manage locks at different levels, DBMS often uses intent locks in addition to ordinary shared (S) and exclusive (X) locks.

Common intent‑lock types:

• Intention‑Shared (IS)

  • Says: “I intend to acquire shared locks on some children of this node.”

• Intention‑Exclusive (IX)

  • Says: “I intend to acquire exclusive locks on some children of this node.”

• Shared‑Intention‑Exclusive (SIX)

  • Says: “I want a shared lock on this node and exclusive locks on some children.”

These intent locks are placed on ancestor nodes (for example, table level) to signal what the transaction will do at lower levels (pages or rows).

Example Hierarchy

Imagine a simple hierarchy:

• Database

  • Table T

    • Page P1

      • Record R1, R2

If a transaction wants to write record R1, it might:

• Acquire an IX lock on Table T.

• Acquire an X lock on Page P1.

• Acquire an X lock on Record R1.

This pattern allows the DBMS to know, at each level, what kind of access is planned below, and to block conflicting transactions appropriately.

Why Multiple‑Granularity Locking Matters?

• Better concurrency:
  Fine‑granularity locking allows many transactions to work on different rows or pages without blocking each other.

• Better performance:
  Coarse‑granularity locking reduces lock overhead when a transaction accesses many records in the same table or page.

• Safe coordination:
  Intent locks at higher levels prevent incompatible operations (for example, another transaction taking an exclusive lock on the whole table while a fine‑grained write is happening below).

For beginners, the key idea is:

• Multiple‑granularity locking lets the DBMS lock at different levels (table, page, record) and uses intent locks to coordinate them, so that the system can be both efficient and safe.

Summary

Multiple‑granularity locking in DBMS is a concurrency‑control technique that allows transactions to acquire shared or exclusive locks at different levels of the data hierarchy (such as table, page, or record). It uses intent locks on parent nodes to signal locking intentions at child nodes, enabling the DBMS to balance fine‑grained concurrency and coarse‑grained efficiency while still ensuring correctness and consistency.