Code360 powered by Coding Ninjas X Code360 powered by Coding Ninjas X
Table of contents
What is Granularity?
What is Multiple Granularity in DBMS?
What are the Different Types of Intention Mode Locks in Multiple Granularity?
Frequently Asked Questions
Under what circumstances is Multiple Granularity used?
What is multiple levels of granularity?
What are the lock modes used in Multiple Granularity?
Which lock granularity has the highest overhead?
Last Updated: Mar 27, 2024

Multiple Granularity in DBMS

DBMS - Database management systems
Free guided path
12 chapters
93+ problems
Earn badges and level up


Whenever a transaction T is needed to access the database, a locking protocol locks the entire database. Now, if another transaction S happens simultaneously that requires accessing the data from the same database, it will not perform the transaction due to the locking of the entire database.

Locking the entire database is unnecessary and costs us the loss of concurrency. The concept of multiple granularity in DBMS talks about resolving this issue. Now let’s first understand granularity.

Multiple Granularity in DBMS

This article will help you understand Multiple Granularity in DBMS in depth.

So let us begin to understand this topic in depth.

What is Granularity?

Granularity in database management refers to the level of detail at which data is stored in a database. It specifies whether the data is fine- or coarse-grained. A fine granularity database stores data very detailedly, whereas a coarse granularity database stores data at a high-level overview. The application determines granularity needs as well as the size and complexity of the database. Selecting the appropriate granularity is critical for creating an efficient and productive database.

Get the tech career you deserve, faster!
Connect with our expert counsellors to understand how to hack your way to success
User rating 4.7/5
1:1 doubt support
95% placement record
Akash Pal
Senior Software Engineer
326% Hike After Job Bootcamp
Himanshu Gusain
Programmer Analyst
32 LPA After Job Bootcamp
After Job

What is Multiple Granularity in DBMS?

Multiple Granularity in DBMS refers to the ability of a database system to support various levels of data abstraction, allowing different users to view data at different levels of detail. It means hierarchically breaking up the database into blocks that can be locked. Multiple granularity increases concurrency and reduces the lock overhead. It makes it easy to decide which data should be locked and which is not. A tree can represent the hierarchy of multiple granularity.

For example: Consider a tree that has four levels of nodes.

  • The root level or the top-level represents the entire database.
  • The second level represents a node of the type area. The database consists of precisely these areas.
  • The area has children nodes known as files. Any file cannot be present in more than one area.
  • Each file has child nodes known as records. The file precisely consists of those records that are its child nodes. No records can be present in more than one file. Therefore, levels starting from the highest level are
  1. Database
  2. Area
  3. File 
  4. Record
What is Multiple Granularity in DBMS?


  • Consider the above diagram, each node in the tree can be locked individually. According to the 2-phase locking protocol, it will use shared and exclusive locks.
  • When a transaction locks a node, it is either in shared or exclusive mode. The transaction implicitly locks descendants of that node in the same lock mode.
  • Locks on files and records are made simple, but the lock on the root is still not determined.
  • One way to know about a lock on the root is to search the entire tree. But it nullifies the whole concept of multiple granularity locking schemes. 
  • A more efficient way to achieve this is by using a new type of lock called intention mode lock.

What are the Different Types of Intention Mode Locks in Multiple Granularity?

In addition to shared and exclusive lock modes, other three lock modes are available.

  1. Intention-shared (IS): In the context of multiple granularity locks, the "Intention-shared (IS)" mode indicates an intention to acquire a shared lock at a higher level. It signals the desire to read or access shared resources in the higher-level structure while allowing other transactions to hold shared locks at lower levels.
  2. Intention-Exclusive (IX): explicit lock with exclusive or shared locks at a lower level.
  3. Shared & Intention-Exclusive (SIX): In this lock, the node is locked in shared mode, and some nodes are locked in exclusive mode by the same transaction.
      S     X     IS     IX   IX
S Yes No Yes No No
X No No No No No
IS Yes No Yes Yes No
SIX No No Yes No No
IX No No Yes No Yes

 Intention lock modes are used in multiple-granularity locking protocol uses to ensure serializability. In this protocol, if a transaction T that attempts to lock a node need to follow these protocols: 

  1. Transaction Ti must follow the lock-compatibility matrix.
  2. At first, Transaction Ti must lock the tree’s root in any mode.
  3. Transaction Ti will lock a node in S or IS mode only if Ti has locked that node’s parent in either IS or IX mode.
  4. Transaction Ti will lock a node in IX, SIX, or X mode only if Ti currently has the parent of the locked node in either SIX or IX modes.
  5. Transaction Ti will lock a node only if Ti has not unlocked any node before (i.e., Ti is two-phase).
  6. Transaction Ti will unlock a node only if Ti has not locked its children nodes currently.

Multiple granularity protocol requires that locks are made in a top-down (root-to-leaf) manner, whereas locks must be released in a bottom-up (leaf to-root) manner. 

Consider the given tree in the figure and the transactions:

  • Assume transaction T1 reads record Ra2 from file Fa. Then, T2 will lock the database, area A1, and Fa in IS mode (and in that order), and finally, it will lock Ra2 in S mode.
  • Assume transaction T2 modifies record Ra9 from file Fa. Then, T2 will lock the database, area A1, and file Fa (in that order) in IX mode and lock Ra9 in X mode.
  • Assume transaction T3 reads all the records from file Fa. Then, T3 will lock the database and area A1 (and in that order) in IS mode and lock Fa in S mode.
  • Assume transaction T4 reads the entire database. It can be done after locking the database in S mode.

Transactions T1, T3, and T4 can access the database together. Transaction T2 can execute concurrently with T1 but not with T3 or T4. 

Deadlock is also possible in the multiple-granularity protocol, as it is in the two-phase locking protocol. These can be eliminated by using specific deadlock elimination techniques.

Frequently Asked Questions

Under what circumstances is Multiple Granularity used?

Multiple granularity is used to cater to different data analysis needs, optimize performance, and provide varying levels of detail in reporting, resource allocation, forecasting, and data privacy requirements.

What is multiple levels of granularity?

Multiple levels of granularity refer to the ability of a system or structure to operate and provide information at various levels of detail or abstraction, catering to different perspectives or needs.

What are the lock modes used in Multiple Granularity?

In multiple granularity scenarios, the lock modes used are Shared Lock (S-lock) for reading and Exclusive Lock (X-lock) for writing to maintain data consistency during concurrent access.

Which lock granularity has the highest overhead?

Fine-grained lock granularity (e.g., row-level locking) typically has the highest overhead due to managing and tracking a more significant number of individual locks.

Check this out :  Entity in DBMS


In this article, we learned about granularity and multiple granularity in dbms. We have also known how multiple granularity locking affects concurrency performances.

We discussed new locking modes to address multiple granularity locking. 

Visit here to learn more about different topics related to database and management systems. Ninjas don’t stop here. Check out the Top 100 SQL Problems to master frequently asked questions in big companies and land your dream job. You can also consider our Database Management Course to give your career an edge over others.

Previous article
Multiple Granularity Locking in DBMS
Next article
Thomas Write Rule in DBMS
Guided path
DBMS - Database management systems
12 chapters
93+ Problems
Earn badges and level up
Live masterclass