Concurrency Control in DBMS

Concurrency Control

Concurrency control is a process in DBMS where programs are executed simultaneously or at the same time without conflicting with each other programs. In multi-programming/multi-user computer systems, resources are shared among multiple users or programs. The database becomes a shared resource, some DBMS packages allow many users and application programs to access data concurrently. There are some techniques used for controlling the concurrent execution of transactions given below:

Locking

A data item can be locked by a transaction to prevent this data item from being accessed and modified by any other transaction. The part of the database system which is responsible for locking or unlocking the data items that are called Lock Manager.
There are mainly two types of Locks that are given as follows:

1. Exclusive Lock
2. Shared Lock

Exclusive Lock:

In this locking, It provides exclusive use of a data item to one transaction. Transactions which want to read as well as modify a data item must make an exclusive lock on that data item called write lock or update lock.

Shared Lock:

Transactions that want to read a data item only and do not modify, can make a shared lock on the data item that is called read lock. Any number of transactions can make the shared lock and read the data item but they can’t modify the data item unless the shared lock a released by all the transactions that are holding locks on that data item.

Concepts of Two-Phase Locking

The concept of two-phase locking emphasizes that all locks on required data items are first acquired before any of the locks are released. It has two phases namely a growing phase and a contracting phase. In the growing phase, the number of locks increases from zero to maximum for a transaction.

In the contracting phase, the number of locks held decreases from maximum to zero. The transaction must first acquire locks on all the required data items. It can’t unlock a data item unless it has locked all the data items it needs for the execution of a transaction. There are 4 types of Two-Phase Locking techniques are exist:

Basic Two-Phase Locking:

It is the basic concept of the Two-Phase Locking technique. It provides all locks on required data items are first acquired before any of the locks are released.

Conservative Two-Phase Locking:

In the Conservative Two-Phase Locking technique, a transaction locks all the required data items before a transaction is executed. If any data item needs can’t be locked, the transaction does not lock any item instead it waits until all the items required are available for locking which is called a Deadlock-free mechanism.

Strict Two-Phase Locking:

In the Strict Two-Phase Locking technique, a transaction doesn’t release any of its exclusive locks until it commits or aborts. No other transaction can read or write a data item commits or aborts.

Rigorous Two-Phase Locking:

In the Rigorous Two-Phase Locking technique, a transaction doesn’t release any of its locks (exclusive or shared) until it commits or aborts. So, Rigorous Two-Phase Locking is more strict than strict two-phase locking.