Home » DBMS

DBMS | Concurrency Control and Various Methods of Concurrency Control

DBMS | Concurrency Control: In this tutorial, we will learn about concurrency control and its different methods in the database management system. By Prerana Jain Last updated : May 31, 2023

What is Concurrency Control in DBMS?

The concurrency control is the coordination of the simultaneous execution of a transaction in a multiuser database system. The concurrency control can ensure the serializability of the transaction in a multiuser database environment and that is the main objective of concurrency control. There is only One way in which the required data items can be accessed in a mutually exclusive manner. That is while one transaction is accessing a data item another transaction can modify that data item. The most common method used to implement this requirement is to allow a transaction to access a data item only if it is currently holding a lock on that item.

Various Methods of Concurrency Control

1) Binary Locking

A data item can be locked in various modes:

• Shared mode (S): when any transaction Ti has 0 shared mode lock on any data item Q then Ti can only read but cannot write Q.
• Exclusive mode (X): Any transaction Ti can read and write Q only If a transaction Ti has obtained an exclusive mode lock (denoted by X) on any data item Q.
• The transaction makes the request to the concurrency control manager. It can proceed only when the concurring control manager grants the lock to the transaction.

2) Locked based protocol

In locked based protocol basic idea is first to acquire a lock before accessing a data item directly after use should delete that data item. Because of this, we can avoid a clash. Here transaction uses types of locks. Shared lock and exclusive lock.

3) Shared lock

If the transaction has acquired a shared lock on a data item Q than its own perform only read operation but if a transaction has acquired exclusive lock then it can perform read as well as write operation.

4) Two phase locking

In two-phase locking every transaction work in 2 phase in the entire span. Growing phase and shrinking phase. In the growing phase, only a transaction may acquire locks but cannot release only lock while in shrinking phase a transaction can only release lock but cannot any acquire any lock.

Growing and shrinking phase terminology is applicable to the transaction not suitable.

5) Rigorous 2 phase locking

In rigorous 2 phase locking, we remove shrinking phases from the system i.e. a transaction can acquire locks but cannot release any lock because of which dirty read is not possible. This protocol ensures conflict and view serializability recoverability and cascadeless but may suffer from deadlock.

6) Strict 2 phase locking

In strict 2 phase locking is an improvement over rigorous 2 phase locking ensure rigorous two-phase locking where unlocking of a shared lock is allowed in the shrinking phase.

7) Conservative 2 phase locking

In conservation 2 phase locking we remove growing phase from the system and every transaction is first required to acquire all the lock first before performing any read or write. It ensures conflict serializability view serializability and deadlock independence but suffers from recoverability and cascades.

8) Time stamping protocol

In this method, we decide to order before transaction enters into the system here every transaction is given time stamp Ts(Ti) which is actually the value of the system clock when the transaction enters into the system. This time stamp will remain constant until the transaction is in the system.

Write every data item Q use associate two time stamp read time stamp of Q and write time stamp of Q.

Note: If a read operation is allowed than reading timestamp must be updated and if a write operation is allowed than write timestamp must be updated. If an operation is not allowed than transaction must enter into a system with a new timestamp.