Locking Techniques in DBMS

Locking Techniques in DBMS are generally used for concurrency control in Transaction Management.
Locking technique is a concurrency control mechanism.
Problems based on locking techniques are always asked in GATE(CS/IT) and UGC NET exam.

Today we will learn about various locking techniques in concurrency control.

Frequently asked Questions

Some frequently asked questions on locking techniques are After reading this locking techniques in concurrency control tutorial students will be able to answer the following questions

What is Concurrency Control in DBMS?
What is Lock ?
What is difference between Shared lock and Exclusive lock?
What do mean by compatibility of lock?
Explain Two phase locking technique.
Write the difference between growing phase and shrinking phase?
What is Timestamp ?
Explain Timestamp Locking Protocol .
Explain validation based locking Technique.

Let’s start with introduction of Lock.

What is Lock ?

Lock is a variable associated with a Data Item.
Lock in DBMS is used to access a Database Item in Synchronize manner.
While one transaction is executing and accessing the database, to control concurrent access to data, locking is used.
Whenever transactions are executing simultaneously, locking will decide that which transaction can access the data from the database.
To prevent incorrect results, a Lock may deny access to other transactions.

Types of Lock in DBMS

There are two types of locks used in DBMS under locking techniques in DBMS for Concurrency Control. These types of Lock are as follow –

What is Shared Lock ?

If a transaction has a Shared Lock on its data item, it can read the item but can’t update it.

What is Exclusive Lock ?

If a transaction has Exclusive Lock on a data item, it can both read and update the data item.

What is Locking Compatibility ?

Locking Compatibility is an important issues in Transaction Processing. Locking compatibility helps in decision of approval of a locking request on a data item.
When one Transaction holds a lock on a Data Item and another Transaction requests a lock on the same Data Item. When the two lock modes are compatible, the request for a second lock on the Data Item can be granted.
If the lock mode of the requested lock is not compatible with the lock that is already held, the lock request cannot be granted. Instead, the request must wait until the first Transaction releases its lock, and all other existing incompatible locks are released.
Suppose one transaction has requested a lock mode on a data item. The request may be to get unlocked, may be Shared Lock or may be Exclusive Lock on a data item.
Current state of data item shows the initial state of the data item, whether it is unlocked, shared lock or exclusive lock.

Detailed Explanation of Locking Compatibility is Explained in Two Cases.

Case 1 – Transaction Request Unlocked

If a transaction requests unlock then there may be following possibilities

If a transaction requests unlocked state on a data item and if the initial state of data item is unlocked then there is no need to carry off the request.
If the data item is initially shared lock or exclusive lock then it can be unlocked.

Case 2 – Transaction Requests a Shared Lock

If its initial state is unlocked state then it can be requested for shared lock, but can’t be requested for exclusive lock or exclusive lock is not allowed.
If a transaction requests shared lock on a data item and if the initial state of the item is shared lock then it can be requested for shared lock by other transactions.
If a data item is initially in exclusive lock state and a shared lock is requested on that item, by default the data item is not allowed to be on shared lock. But if the request is allowed then it is called upgrade of lock.

Case 3- Transaction Requests a Exclusive Lock

Again if a transaction requests exclusive lock on a data item and it’s initially in unlocked state, it can be requested for exclusive lock state.
But if its initial state is shared or exclusive then it can’t be requested for exclusive lock.

Types of Locking Techniques in DBMS

Generally Three types of Locking Techniques or locking methods in DBMS re used in Transaction Processing System. These Locking Techniques for concurrency control in DBMS are as follow –

Two Phase Locking Protocol.
Time Stamp Ordering Locking Techniques.
Validation Based Locking Technique.

Each of these Locking method in DBMS is explained One by One here in this Tutorial.

What is Two Phase Locking (2PL) Protocol ?

Two Phase Locking Protocol is widely used locking technique in DBMS.

The problems caused in concurrency control can be solved by this 2PL technique.

According to the rules of this protocol, every transaction is divided into two phases-

First, the Growing Phase, where it can acquire all the locks but can’t release any lock or no unlocking will be executed.
Lastly, the Shrinking Phase, where it can release all locks or unlock but can’t acquire any new lock.

Because there are two phases, it is called two phase locking.

This protocol must have to follow some rules.

These are-

A transaction can acquire a lock on an item only before operating on the item. The lock may be read or to write depending on the access needed.
Once a lock is released by a transaction, it can not acquire any new lock.

Time Stamping Concurrency Protocol

Before understanding the timestamp concurrency technique under locking techniques in DBMS let’s first know about time stamp.

What is Timestamp?

To identify each and every transaction uniquely, one time stamp is attached to each transaction.
This timestamp can be the system clock or may be a simple logical counter too.
In this way, whenever multiple transactions are operating concurrently we can find out which old transaction is and which new transaction is.
Hence, a timestamp is defined as a unique identifier created by Database Management System that includes the relative starting point of a transaction.

Time Stamping Ordering Concurrency Control Protocol

Time Stamp Ordering is a concurrency control protocol, that specifies an order for executing transaction in such a way that the transactions with smaller timestamps and the older timestamps should get priority in execution if there is an event of conflict.

Timestamps are not only used for transactions but also for data items.

Each data item will have read-timestamp and write-timestamp which will give the timestamp of any read and write operation that is executed on the data item in that transaction.

Validation based Concurrency Control Protocol

Validation based Concurrency Control Protocol is also known as Optimistic Concurrency Control Protocol.

In validation based concurrency control protocol we will consider that while executing transactions the interference in execution of two transactions is rare or the conflict will be rare.

This validation based concurrency control protocol is based on this assumption and it is also considered that it is more efficient to allow transactions to proceed without imposing delays to ensure Serializability.

Validation concurrency protocol is followed by three phases, depending on whether the transaction is read-only or a write-only transaction.

The three phases in concepts of locking techniques in DBMS are given as follows-

Read Phase

Read all necessary values on local variable and carry out updates on them and not on the database itself.

Validation Phase

Serializability and interference will be checked in this phase.

Write Phase

In this phase the updated transactions are successfully validated. The updates that are made in the local copy are also updated in the database.

To pass the validation test one of the following must be true.

1) A transaction S with earlier time stamps must finish before transaction T started.

2) If transaction T starts before S finishes then there are two possible cases-

The earlier transaction completes the write phase before it completes its validation phase.

Conclusion and Summary

I hope this specialized locking techniques in DBMS for concurrency control based tutorial will be helpful for computer science students in understanding the concepts of concurrency control and various locking protocol.

If you have any query then feel free to ask in comment section.