Multiple Granularity Locking | Database Management System (DBMS) - Computer Science Engineering (CSE) PDF Download

Multiple Granularity Locking in DBMS

In the various Concurrency Control schemes have used different methods and every individual Data item as the unit on which synchronization is performed. A certain drawback of this technique is if a transaction Ti needs to access the entire database, and a locking protocol is used, then Ti must lock each item in the database. It is less efficient, it would be more simpler if Ti could use a single lock to lock the entire database. But, if it consider the second proposal, this should not in fact overlook the certain flaw in the proposed method. Suppose another transaction just needs to access a few data items from a database, so locking the entire database seems to be unnecessary moreover it may cost us loss of Concurrency, which was our primary goal in the first place. To bargain between Efficiency and Concurrency. Use Granularity.
Let’s start by understanding what is meant by Granularity.

Granularity: It is the size of data item allowed to lock. Now Multiple Granularity means hierarchically breaking up the database into blocks which can be locked and can be track what need to lock and in what fashion. Such a hierarchy can be represented graphically as a tree.
For example, consider the tree, which consists of four levels of nodes. The highest level represents the entire database. Below it are nodes of type area; the database consists of exactly these areas. Area has children nodes which are called files. Every area has those files that are its child nodes. No file can span more than one area.
Finally, each file has child nodes called records. As before, the file consists of exactly those records that are its child nodes, and no record can be present in more than one file. Hence, the levels starting from the top level are:

  • database
  • area
  • file
  • record

Multi Granularity tree HierarchyMulti Granularity tree Hierarchy

Consider the above diagram for the example given, each node in the tree can be locked individually. As in the 2-phase locking protocol, it shall use shared and exclusive lock modes. 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 Ti gets an explicit lock on file Fc in exclusive mode, then it has an implicit lock in exclusive mode on all the records belonging to that file. It does not need to lock the individual records of Fc explicitly. this is the main difference between Tree Based Locking and Hierarchical locking for multiple granularity.
Now, with locks on files and records made simple, how does the system determine if the root node can be locked? One possibility is for it to search the entire tree but the solution nullifies the whole purpose of the multiple-granularity locking scheme. A more efficient way to gain this knowledge is to introduce a new lock mode, called Intention lock mode.

Intention Mode Lock
In addition to S and X lock modes, there are three 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 & 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:

Multi Granularity tree HierarchyMulti Granularity tree Hierarchy

The multiple-granularity locking protocol uses the intention lock modes to ensure serializability. It requires that a transaction Ti that attempts to lock a node must follow these protocols:

  1. Transaction Ti must follow the lock-compatibility matrix.
  2. Transaction Ti must lock the root of the tree first, and it can lock it in any mode.
  3. Transaction Ti can lock a node in S or IS mode only if Ti currently has the parent of the node locked in either IX or IS mode.
  4. Transaction Ti can lock a node in X, SIX, or IX mode only if Ti currently has the parent of the node locked in either IX or SIX mode.
  5. Transaction Ti can lock a node only if Ti has not previously unlocked any node (i.e., Ti is two phase).
  6. Transaction Ti can unlock a node only if Ti currently has none of the children of the node locked.

Observe that the multiple-granularity protocol requires that locks be acquired in top-down (root-to-leaf) order, whereas locks must be released in bottom-up (leaf to-root) order.

As an illustration of the protocol, consider the tree given above and the transactions:

  • Say transaction T1 reads record Ra2 in file Fa. Then, T2 needs to lock the database, area A1, and Fa in IS mode (and in that order), and finally to lock Ra2 in S mode.
  • Say transaction T2 modifies record Ra9 in file Fa . Then, T2 needs to lock the database, area A1, and file Fa (and in that order) in IX mode, and at last to lock Ra9 in X mode.
  • Say transaction T3 reads all the records in file Fa. Then, T3 needs to lock the database and area A1 (and in that order) in IS mode, and at last to lock Fa in S mode.
  • Say transaction T4 reads the entire database. It can do so after locking the database in S mode.

Note that transactions T1, T3 and T4 can access the database concurrently. Transaction T2 can execute concurrently with T1, but not with either T3 or T4.
This protocol enhances concurrency and reduces lock overhead. Deadlock are still possible in the multiple-granularity protocol, as it is in the two-phase locking protocol. These can be eliminated by using certain deadlock elimination techniques.

The document Multiple Granularity Locking | Database Management System (DBMS) - Computer Science Engineering (CSE) is a part of the Computer Science Engineering (CSE) Course Database Management System (DBMS).
All you need of Computer Science Engineering (CSE) at this link: Computer Science Engineering (CSE)
62 videos|66 docs|35 tests

Top Courses for Computer Science Engineering (CSE)

FAQs on Multiple Granularity Locking - Database Management System (DBMS) - Computer Science Engineering (CSE)

1. What is multiple granularity locking in DBMS?
Ans. Multiple granularity locking in DBMS is a technique that allows locks to be placed at different levels of granularity, such as table-level or page-level, to improve concurrency and avoid conflicts in a database system.
2. Why is multiple granularity locking important in a database management system?
Ans. Multiple granularity locking is important in a database management system because it allows for more fine-grained control over locking, enabling multiple transactions to access different parts of the database concurrently without causing unnecessary delays or conflicts.
3. How does multiple granularity locking improve concurrency in a DBMS?
Ans. Multiple granularity locking improves concurrency in a DBMS by allowing transactions to lock only the specific portion of the database they need, rather than locking the entire database or a large portion of it. This enables other transactions to access different parts of the database concurrently, enhancing overall system performance.
4. What are the different levels of granularity used in multiple granularity locking?
Ans. The different levels of granularity used in multiple granularity locking include table-level, page-level, and record-level locking. Table-level locking locks the entire table, page-level locking locks a specific database page, and record-level locking locks individual records within a page.
5. How does multiple granularity locking help avoid conflicts in a DBMS?
Ans. Multiple granularity locking helps avoid conflicts in a DBMS by allowing transactions to lock only the specific portion of the database they need, reducing the chances of two transactions needing conflicting locks. This improves concurrency and reduces the likelihood of deadlock situations, where transactions are unable to proceed due to conflicting lock requests.
62 videos|66 docs|35 tests
Download as PDF
Explore Courses for Computer Science Engineering (CSE) exam

Top Courses for Computer Science Engineering (CSE)

Signup for Free!
Signup to see your scores go up within 7 days! Learn & Practice with 1000+ FREE Notes, Videos & Tests.
10M+ students study on EduRev
Related Searches

Semester Notes

,

video lectures

,

Viva Questions

,

mock tests for examination

,

Sample Paper

,

study material

,

Objective type Questions

,

MCQs

,

Extra Questions

,

pdf

,

practice quizzes

,

Multiple Granularity Locking | Database Management System (DBMS) - Computer Science Engineering (CSE)

,

Important questions

,

Multiple Granularity Locking | Database Management System (DBMS) - Computer Science Engineering (CSE)

,

shortcuts and tricks

,

ppt

,

Exam

,

past year papers

,

Summary

,

Free

,

Previous Year Questions with Solutions

,

Multiple Granularity Locking | Database Management System (DBMS) - Computer Science Engineering (CSE)

;