Levels of Locking in DBMS
There are several levels of locks that can control the access to the elements stored at various levels in the Database. There can be multiple levels of the locks. These locks make the transaction process quite efficient and allow concurrent access between various users. Using various locks, we can lock the information at the particular level or database object that has requested the lock without disturbing the entire Database.
These levels of the lock are:
- The row of any table or Database
- A page from the Database
- An entire table of the Database.
- On the complete Database
The level here represents the scope of the lock and the proportion of the Database that is affected by it. Implementation of a lock in the rows, page or table can be done directly by the user, but implementing any lock on the entire Database can only be done by using SQL commands or utilities.
Let us discuss each level of lock individually:
Row
This is the most basic level where the user can implement a lock on the information stored. Implementing a lock on any row provides the user with complete control of the row. But the locks can only be acquired level-wise, which means to implement the lock on the row, the user needs to make sure that the page on which the row is present is an intent lock.
Any transactions that aim to use the lock on the Database row should hold intent locks of the table and page in which the row is present. Additionally, it should have shared or exclusive locks of the Database too. The user can implement the row lock only when the page size of the Database is greater than 2KB. We cannot implement row locking when working with servers that do not support fast commits or when the Distributed Multi-Cache Management (DMCM) is turned on or VMS Cluster is being installed.
It is important to understand the term Multi-version Concurrency Control to implement the row locks without using the intent lock on the data pages.
Multi-version Concurrency Control (MVCC).
It is a technique used in various database management systems to implement locks on the data. It allows implementing the lock without the requirement of intent locking the page. It is not possible to lock the row of the table in a read-only state, nor is it to exclusively lock the row while updating the contents of the row. The reader cannot implement a lock to block the writer, and similarly, the writer cannot block the reader either. If we perform the transaction on the Database using the MVCC, we can implement a lock on the row at MVCC that will hold intent table locks and exclusive row locks on the modified table.
Page
As the name suggests, it will lock the content displayed on the entire Database page. For any transaction to implement the lock on the page, it requires holding the intent lock of the tables of that page and shared or exclusive page locks.
Table
This lock is used to implement a lock on an entire relationship in the Database. Any transaction that requires the implementation of the lock at the table level. It must hold shared or exclusive table locks.
Database
It is not an actual lock level as locking the entire Database does not make any real sense in the real world. But when connecting an application to the database management system using the CONNECT system. This is done by the application to prevent concurrent updates by other applications on the Database while executing the query on the Database.
Purpose of implementing Locks In DBMS
As discussed above, the database management system allows multiple users to modify the data stored; implementing locks on the data ensures the ACID properties are maintained throughout. Each transaction performed must ensure they fulfil all the ACID requirements.
The ACID properties include:
- Atomicity: It makes sure that any transaction that is started must also be terminated successfully, or it should not start at all.
- Consistency: Any change made should be written in the memory. Before any other transactions take place.
- Isolation: One transaction should not interfere with another transaction. Each transaction should be independent of the other.
- Durability: The committed results should then be modified in the database too for future use.
Lock Modes
The lock implemented at various levels can be applied, allowing different actions on the locked items. There are six modes of lock:
- Exclusive Lock: These locks are represented by X. They are implemented when the user performs data manipulation. This lock can only be implemented on a row of the table or the database page. It prevents access to these resources; no more than one transaction can be performed to an item holding this lock.
- Share Lock: It is represented by S; it allows all the transactions to read the values from the resource. The user is introduced as a reader to the resource. It prevents the users from implementing any data definition command on the resource. All action that tries to modify the value of the resource is blocked.
- Update Lock: It is represented by U and is similar to an exclusive lock. This is more flexible than the exclusive lock. A resource must have a shared lock to upgrade it to update the lock. It can also be implemented on the page or row. This lock is implemented to prevent the system from going into a deadlock condition. The resource can switch between update and exclusive lock when the resource attempt to modify the data.
- Intent Lock: It is represented by I; it notifies other transactions about the transaction's intention to gain a lock. This lock ensures that all the locks are acquired sequentially. The aim is to ensure that the data modification is performed. All locks must follow the lock hierarchy. The intent lock can be classified into three types:
- Intent Shared: It is represented by IS. A page or row can acquire this lock. It is acquired on a resource if the transaction aims to acquire a shared lock. Once an IS lock is intended, no other transaction can acquire the shared lock on it.
- Intent Exclusive: This lock is sent by the transaction when it wants to acquire an exclusive lock enabling the transaction to modify the value of the resource.
- Intent Update: This lock can only be implemented on the page of the database; it increases the flexibility of the exclusive lock whenever an update operation is performed.
- Schema Lock: It is acquired if the transaction wants to perform any operation on the table's schema. Usually, we can perform two operations on the schema. These are modifying the schema or ensuring the stability of the schema. Depending upon these functions, there are two modes of schema lock.
- Schema modification: This lock is acquired when the transaction attempts to perform a data definition lock on the schema.
- Schema stability: It is acquired when the transactions attempt to perform schema dependent.
- Bulk Update Lock: This lock is acquired when the modification is performed on the table. This allows the user to add a large amount of data to the database.
Placement of Locks
Database Management System allows users to access various elements of the database. The user can request the lock at the following resources:
Resources | Definition |
Row ID | Allows locking an entire row or tuple in the table. |
Table | The whole relationship can be locked at once. |
Key | The index of the table can be locked in the table. |
Page | It constitutes 8KB of data on the current page. |
Extent | It locks about 64KB of data which is equivalent to 8 pages of the database. The pages should be consecutive. |
Database | The database administrator can only implement this lock, it ensures that the database is read-only and no manipulation can be performed on it. |
File | The lock is implemented on the file. |
Application | This locks the entire resources that are being used by a particular application. |
Metadata | This lock is different from others as it is implemented on the metadata and not on the database object. |
Object | This lock can be used on any element that is entered as a database object. |
Locking Hierarchy
To acquire a lock on any resources the locks are required to be acquired in a sequential top to bottom order. The hierarchy of locking is as follows:
Similarly, the various lock modes can only be acquired if the lock at the lower lock is compatible with the upper lock. If not, the lock at the lower level will not be executed. The compatibility can be studied using the following matrix: