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 other program. 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 concurrent execution of transactions are 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 are given as follows :

  • Exclusive Lock
  • Shared Lock
  • 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.

    Shared Lock :

    Transactions which want to read a data item only and do not modify it, it 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 technique are exist :

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

    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 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 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.