Multiple Granularity

Let's start by understanding the meaning of granularity.

Granularity: It is the size of data item allowed to lock.

Multiple Granularity:

• It can be defined as hierarchically breaking up the database into blocks which can be locked.
• The Multiple Granularity protocol enhances concurrency and reduces lock overhead.
• It maintains the track of what to lock and how to lock.
• It makes easy to decide either to lock a data item or to unlock a data item. This type of hierarchy can be graphically represented as a tree.

For example: 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.