Multiple Granularity Locking

Multiple Granularity Locking

• In database management system there are data at various levels depending upon the data sizes.

• For example: A database contains a table and a table contains rows and each row contains some data value. Thus different levels of data can be database, tables, the records.

This can be represented in the form of tree. For example.

• Each node can be locked individually. When a transaction locks a node, in either shared or exclusive mode, the transaction also implicitly locks all the descendants of that node in the same lock mode.

•  For example - if transaction T1 gets an explicit lock on table tbl in exclusive mode, then it has an implicit lock in exclusive mode on all the records belonging to that table. It does not need to lock the individual records r1 and r2.

• A new mode of lock is introduced along with exclusive and shared locks. This is called intention mode lock. Thus In addition to S and X lock modes, there are three wo additional lock modes with multiple granularity:

   • Intention-Shared (IS): Explicit locking at a lower level of the tree but only with shared locks.

   • Intention-Exclusive (IX): Explicit locking at a lower level with exclusive or shared locks.

   • Shared and Intention-Exclusive (SIX): The sub-tree rooted by that node is locked explicitly in shared mode and explicit locking is being done at a lower level with exclusive mode locks.

• The compatibility matrix for these lock modes are described below:

• With help of intention lock modes on the transactions the multiple-granularity locking protocol ensures serializability.

• The protocol follows following rules -

1) Transaction T_i must follow the lock-compatibility matrix.

2) Transaction T_i must lock the root of the tree first and it can lock it in any mode.

3) Transaction T_i can lock a node in S or IS mode only if T_i currently has the parent of the node locked in either IX or IS mode.

4) Transaction T_i can lock a node in X, SIX, or IX mode only if T_i currently has the parent of the node locked in either IX or SIX mode.

5) Transaction T_i, can lock a node only if T_i, has not previously unlocked any node. That means T_i, is two phase.c

6) Transaction T_i, can unlock a node only if T_i, currently has none of the children of the node locked.

Advantages of multiple granularity protocol

1) It enhances concurrency.

2) It ensures serializability.

3) It keeps track of what to lock and how to lock.       

4) This type of locking can be hierarchically represented.

5) It makes it easy to decide which data item to be locked and which data item need to be unlocked.