DBMS Multiple Granularity

Let's start by understanding the meaning of granularity.

What is Granularity?

Granularity refers to the size of the data item that can be locked in a database.

What is Multiple Granularity?

Multiple Granularity is a locking technique in which the database is hierarchically divided into different levels of data items that can be locked. It helps improve concurrency and reduces lock management overhead.

The Multiple Granularity Protocol keeps track of what should be locked and how it should be locked. It also makes it easier to decide whether a data item should be locked or unlocked. This hierarchy is usually represented in the form of a tree.

Example of Multiple Granularity

Consider a tree which has four levels of nodes.

• The first level or higher level shows the entire database.
• The second level represents a node of type area. The higher level database consists of exactly these areas.
• The area consists of children nodes which are known as files. No file can be present in more than one area.
• Finally, each file contains child nodes known as records. The file has exactly those records that are its child nodes. No records represent in more than one file.
• Hence, the levels of the tree starting from the top level are as follows:
  1. Database
  2. Area
  3. File
  4. Record

In this example, the highest level shows the entire database. The levels below are file, record, and fields.

There are three additional lock modes with multiple granularity:

Intention Mode Lock

Intention-shared (IS): It contains explicit locking at a lower level of the tree but only with shared locks.

Intention-Exclusive (IX): It contains explicit locking at a lower level with exclusive or shared locks.

Shared & Intention-Exclusive (SIX): In this lock, the node is locked in shared mode, and some node is locked in exclusive mode by the same transaction.

Compatibility Matrix with Intention Lock Modes: The below table describes the compatibility matrix for these lock modes:

It uses the intention lock modes to ensure serializability. It requires that if a transaction attempts to lock a node, then that node must follow these protocols:

• Transaction T1 should follow the lock-compatibility matrix.
• Transaction T1 firstly locks the root of the tree. It can lock it in any mode.
• If T1 currently has the parent of the node locked in either IX or IS mode, then the transaction T1 will lock a node in S or IS mode only.
• If T1 currently has the parent of the node locked in either IX or SIX modes, then the transaction T1 will lock a node in X, SIX, or IX mode only.
• If T1 has not previously unlocked any node only, then the Transaction T1 can lock a node.
• If T1 currently has none of the children of the node-locked only, then Transaction T1 will unlock a node.

Observe that in multiple-granularity, the locks are acquired in top-down order, and locks must be released in bottom-up order.

• If transaction T1 reads record R_a9 in file F_a, then transaction T1 needs to lock the database, area A₁ and file F_a in IX mode. Finally, it needs to lock R_a2 in S mode.
• If transaction T2 modifies record R_a9 in file F_a, then it can do so after locking the database, area A₁ and file F_a in IX mode. Finally, it needs to lock the R_a9 in X mode.
• If transaction T3 reads all the records in file F_a, then transaction T3 needs to lock the database, and area A in IS mode. At last, it needs to lock F_a in S mode.
• If transaction T4 reads the entire database, then T4 needs to lock the database in S mode.