Granularity means the size of data items that are allowed to lock or the level and information type that a lock protects.
Multiple granularities is a database locking technique that allows various data items of different sizes and sets locks on them. And defines the hierarchy of data granularity where small granularities are nested within the larger granularity.
There are three types of lock granularity
- Course granularity: It is used to lock tables, files, databases, and records. It reduces the transaction number and decreases the throughput to increase the response time of the transaction. But this also causes the concurrency to get reduced.
- Fine granularity: It locks the records and fields to provide high concurrency and throughput. But it leads to very high overhead and low response time.
- Intermediate granularity: It locks equal and fixed-size pages that contain a few rows and columns of one or more tables of the database.
Multiple granularity breaks the database into a number of blocks that can be locked to increase the concurrency and decrease the lock overhead. It also makes it easy to decide which segment or part of data to lock or which one to unlock. The hierarchy of data of various sizes is represented using a tree where the node represents the database the next level of the node represents the files and then the next level shows the records of that database.
For example

The additional locks are used to achieve multiple granularity and these locks are named intention locks which guide the path from the root node to the desired node for a transaction.
There are 3 intention locks types
- Intention shared (IS) lock: this lock indicates that one or more shared locks can request on some nodes.
- Intention exclusive (IX) locks: this lock indicates that one or more exclusive locks can request on some nodes.
- Shared intention exclusive (SIX): this lock indicates that the shared lock locked the current node but still one or more exclusive locks can request on some nodes.
Similar Reads
-
Aggregate function in SQL with Examples
In a Database Management System (DBMS), an aggregate function is a special tool that performs calculations on a set of… -
SQL and Parts of SQL language
SQL is a popular programming language, which is used to manage the relational databases and to perform various operations on… -
Views in Relational Algebra
In SQL, virtual table are considered as views. Like Tables, views also contain rows and columns. We can select the… -
Syntax of following SQL commands: sysdate, to date(), dual table ,to_number, substr() and initcap()
1. SYSDATE Purpose: SYSDATE is a function that returns the current date and time of the database server. Syntax SELECT… -
What is Super key, Candidate key and Primary key?
1. Super Key A super key is an attribute or a set of attributes within a table that uniquely identifies… -
What is Cursor? Difference between Implicit and Explicit Cursor
A Cursor is essentially a temporary work area created in the system memory that comes into play during the execution… -
What is Relational Algebra?
Relational algebra is a formal language used to query and manipulate data stored in relational database systems. It provides a… -
What do you mean by Relational Model? Explain with Example
The relational model is a type of model, which represents how the data are stored in a relational database. In… -
Functions of DBA in DBMS
The role of a Database Administrator (DBA) is to manage and maintain the performance, security, and integrity of a database.… -
What is Data Abstraction and Data Redundancy in DBMS
In the world of data management, two terms often come up: data abstraction and data redundancy. Both play crucial roles…