Concurrency Control in DBMS

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

Locking : A data item can be locked by a transaction in order 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 is called Lock Manager .
There are mainly two types of Locks are given as follows :

  • Exclusive Lock
  • Shared Lock
  • 1. Exclusive Lock : In this locking, It provides exclusive use of a data item to one transaction. Transaction which want to read as well as modify a data item must make exclusive lock on that data item that called write lock or update lock.

    2. Shared Lock : Transactions which want to read a data item only and do not modify it, it can make shared lock on the data item that is called read lock. Any number of transactions can make 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 growing phase, the number of locks increase from zero to maximum for a transaction.
    In contracting phase, the number of locks held decrease 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 transaction.
    There are 4 types of Two Phase Locking technique are exist :

  • Basic Two Phase Locking
  • Conservative Two Phase Locking
  • Strict Two Phase Locking
  • Rigorous Two Phase Locking

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

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

    3. Strict Two Phase Locking : In Strict Two Phase Locking technique, a transaction don’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.

    4. Rigorous Two Phase Locking : In Rigorous Two Phase Locking technique, a transaction don’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.