Multiple Granularity Locking in DBMS

DBMS (Database Management System) is used for effective management of data. It enables multiple users to access the same data without compromising its consistency and integrity. A Challenge in DBMS is locking, which is used to prevent multiple users from accessing the same data simultaneously. In this article, we will discuss a detailed discussion on multiple granularity locking.

Multiple Granularity Locking

Multiple granularity locking is a locking mechanism that provides different levels of locks for different database objects. It allows for different locks at different levels of granularity. This mechanism allows multiple transactions to lock different levels of granularity, ensuring that conflicts are minimized, and concurrency is maximized.

To illustrate, let's consider a tree structure that has four levels of nodes. The top level represents the entire database, and below it are nodes of type "area", which represent specific areas of the database. Each area has child nodes called "files", and each file represents a specific subset of data within that area. Importantly, no file can span more than one area.

Finally, each file has child nodes called "records", which represent individual units of data within the file. Like files, each record is a child node of its corresponding file and cannot be present in more than one file. Therefore, the tree can be divided into the following levels, starting from the top −

• Database

• Area

• File

• Record

Multiple granularity locking uses two types of locks

Shared Lock

It allows multiple transactions to read the same data simultaneously. It is used to prevent other transactions from modifying the data while a transaction is reading it.

Exclusive Lock

It prevents any other transaction from accessing the data. It is used to prevent other transactions from reading or modifying the data while a transaction is writing to it.

Different Types of Intention Mode Locks in Multiple Granularity

Intention mode locks are a type of lock used in multiple granularity locking that allows multiple transactions to acquire locks on the same resource, but with different levels of access.

There are three types of intention mode locks in multiple granularity locking −

Intent Shared (IS) Locks

This lock is used when a transaction needs to read a resource but does not intend to modify it. It indicates that the transaction wants to acquire a Shared lock on a resource.

Intent Exclusive (IX) Locks

This lock is used when a transaction needs to modify a resource but does not intend to share it. It indicates that the transaction wants to acquire an Exclusive lock on a resource.

Shared with Intent Exclusive (SIX) Locks

This lock is used when a transaction intends to acquire both Shared and Exclusive locks on a resource. It indicates that the transaction wants to acquire an Exclusive lock on a resource after acquiring Shared locks on other resources.

These intention mode locks are used to optimize the locking mechanism in a database by allowing transactions to acquire locks on multiple resources in a coordinated manner. They help prevent deadlocks and improve concurrency in a database system.

The compatibility metrics for these lock modes are described below −

Compatibility Matrix

	IS	IX	S	SIX	X
IS	YES	YES	YES	YES	NO
IX	YES	YES	NO	NO	NO
S	YES	NO	YES	NO	NO
SIX	YES	NO	NO	NO	NO
X	NO	NO	NO	NO	NO

Intention lock modes are utilized in the multiple-granularity locking protocol to ensure serializability. According to this protocol, when a transaction (T) attempts to lock a node, it must adhere to the following guidelines

• Transaction T must follow the lock-compatibility matrix.

• Transaction T must initially lock the root of the tree in any mode.

• Transaction T may only lock a node in S or IS mode if it has already locked the node's parent in either IS or IX mode.

• Transaction T may only lock a node in IX, SIX, or X mode if it has already locked the parent of the node in either SIX or IX modes.

• Transaction T may only lock a node if it has not yet unlocked any nodes (i.e., it is two- phase).

• Transaction T may only unlock a node if it is not currently holding any locks on its child nodes.

In the multiple granularity protocol, locks must be acquired in a top-down (root-to-leaf) manner, while they must be released in a bottom-up (leaf-to-root) manner. This ensures that transactions can access shared resources efficiently while avoiding deadlocks.

Advantages of Multiple Granularity Locking

Multiple granularity locking has several advantages over other locking mechanisms, including −

Increased Concurrency

Multiple granularity locking allows for multiple transactions to access different levels of granularity concurrently, thereby increasing concurrency.

Reduced Locking Overhead

Multiple granularity locking allows for locks to be set at different levels of granularity, reducing the locking overhead.

Improved Performance

Multiple granularity locking provides finer-grained control over locks, improving performance by reducing the number of conflicts and deadlocks.

Granularity Levels

Multiple granularity locking provides different levels of granularity, which include −

Table-level Locking

Table-level locking locks an entire table, preventing other transactions from accessing the table while a transaction is using it.

Page-level Locking

Page-level locking locks a specific page within a table, preventing other transactions from accessing the page while a transaction is using it.

Row-level Locking

Row-level locking locks a specific row within a table, preventing other transactions from accessing the row while a transaction is using it.

Deadlocks in Multiple Granularity Locking

A deadlock occurs when two or more transactions are waiting for each other to release their locks. Deadlocks can be detected using a timeout mechanism. Deadlock can be resolved by rolling back one or more transactions involved in the deadlock. Deadlocks can also be prevented by using strict locking protocols.

Implementation of Multiple Granularity Locking

Multiple granularity locking can be implemented in DBMS by defining different levels of granularity for the database objects and using locking protocols that support multiple granularity locking. The implementation of multiple granularity locking can be challenging due to the complexity of the locking protocols.

Use Cases of Multiple Granularity Locking

Multiple granularity locking is used when there are high levels of concurrency. Database objects have different levels of granularity. Some use cases of multiple granularity locking include −

Banking Systems

Banking systems require high levels of concurrency. Database objects have different levels of granularity, making multiple granularity locking useful.

Inventory Management Systems

Inventory management systems require high levels of concurrency. Database objects have different levels of granularity, making multiple granularity locking useful.

E-commerce Systems

E-commerce systems require high levels of concurrency. Database objects have different levels of granularity, making multiple granularity locking useful.

Conclusion

Multiple granularity locking is a locking mechanism used in DBMS. It provides different levels of locks for different database objects. It increases concurrency, reduces locking overhead, and improves performance by reducing conflicts and deadlocks. It allows transactions to lock different levels of granularity. Deadlocks can be detected, resolved, and prevented using strict locking protocols. Multiple granularity locking used in banking systems, inventory management systems, and e-commerce systems.