The locking in a database can be done at 4 levels, which start with the database at the highest level and down via table and page to the row at the lowest level.
- Database Level
- Table Level
- Page-Level
- Row Level
Before we discuss the levels of locking, we should know about the types of locks or lock mode. So, there are 5 locks types in the locking and these are discussed below :
-
Exclusive (X) Lock-
This method of locking differentiates the locks based on their usage. This also ensures that the data or information of a page will be reserved exclusively for those transactions that used the exclusive lock. These locks are applied on those resources only where a WRITE operation is performed. These locks can be applied to a page only if there is no other shared or exclusive type of lock is applied already.
-
Shared (S) Lock –
This method of locking is applied only to the read operations. If this lock is applied to any row or a page, then it will reserve that row or page for the read operation. We can apply more than one lock on the same row or the same page, but it should not apply to any other type of lock.
-
Intent exclusive (IX) Lock –
This method of locking explicit locking at a lower level with exclusive or shared locks. This means that if a transaction has used this type of lock, then it must be a case of modifying the lower level of resources by imposing exclusive lock separately.
-
Intent shared (IS) Lock –
This method of locking is explicit locking at a lower level of the tree but only with shared locks. This means that if a transaction has used this type of lock, then it must be a case of reading the lower level of resources by imposing shared lock separately.
-
Shared intent exclusive (SIX) Lock –
This method of locking states that the transaction is used to read the resources at a lower level. Here in SIX, we impose the shared lock on all the resources that are available at the lower level. The subtree rooted by that node is locked explicitly in shared mode and explicit locking is done at a lower level with exclusive mode locks. In this method, only one SIX can be acquired on a relation at a time and if there are any other transactions for updating any change, then it will block those transactions.
-
Update (U) Lock –
This method of locking can be imposed on a record that already consists of a shared lock and if it has a shared lock already, then the update lock will impose another shared lock on the target row or page of relation. This is the same as an exclusive lock and also in some ways flexible. Here in this lock, after checking that the transaction holds the update lock for modifying the data, then the update lock will be modified into an exclusive lock.
Drawbacks of locking:
- May not free from recoverability.
- Not free from deadlock.
- Not free from starvation.
- Not free from cascading rollback.
Level of Hierarchy in Locking
Now, let’s discuss the levels of locking one by one.
1. Database Level :
At the database level, the full or complete database is locked. Now, If let’s say there are two relations in a database. If say, R1 and R2, where R1 uses tables, then R2 cannot use them. You will always find a shared lock on this level that is used whenever a transaction is connected to a database. On this level, we use shared locking to prevent dropping of the database or restoring a database backup over the database in use. Let’s see an example. When we use a SELECT statement to read some data, a shared lock will be applied on the database level, an intent shared lock will be applied on the table and on the page level, and a shared lock on the row itself.
Applications of Database level –
- This type of locking is suitable for a group of processes.
- This type of locking is very slow, so it is not used for the online version of multi-user DBMS.
2. Table Level :
At the table level, the full table or relation is locked. Now, If let’s say there are two relations in a database say, R1 and R2, where R1 uses tables, then R2 cannot use it. But, two transactions can access the same database only if they are accessing different relations. A transaction using a level of the table will hold shared and/or exclusive table locks. At the table level, there are 5 different types of locks. i.e, Exclusive (X), Shared (S), Intent exclusive (IX), Intent shared (IS), and Shared with intent exclusive (SIX) and these locks have already been discussed above.
Applications of Table level –
- This type of locking level is not suitable for multi-user database management systems.
- It is also primarily used in preventing a relation from being dropped in a DML operation.
3. Page Level :
The Page-level always consists of fixed size i.e, power of 2 or 2i type. A table can span several pages and a page can contain several tuples of one or more relations. At the page level, an intent shared lock (IS) will be imposed. This lock is capable of locking a table, shared, or exclusive page. An intent exclusive lock (IX) or intent update lock (IU) will be imposed if there’s a case of DML statements (i.e. insert, update, delete).
Applications of Page level –
- This type of locking level is suitable for multi-user database management systems.
- They are comparatively fast but also there are various conflicts.
4. Row Level :
This level of locking is less restrictive as compared to other levels of locking. At the row level, if a concurrent transaction is accessing different rows in the same relationship, even if the rows are located on the same page, then this can be accepted by database systems. Here at this level, DBMS allows concurrent transactions to access the rows of the same relation even in the case of where the rows are on the same page. At this level, we can apply 3 different types of locks, i.e, Exclusive, Shared, Update and these locks have already been discussed above and also, particular rows are locked in a query on this level.
Applications of Row level –
- It is very costly compared to other levels of locking.
- It’s also very restrictive.