Concurrency Control Protocol

Need of Concurrency Control Protocol

  • To maintain the consistency of shared data access during concurrent execution.
  • Attempting to have maximum possible concurrency without inconsistency.

Mechanisms To Control the Concurrency:

There are two mechanisms by which we can control concurrency according to our needs.

  1. The first mechanism is that in which the user is responsible to write the consistent concurrent transactions and they are called Lock Based Protocols.
  2. And the second mechanism is that in which system itself tries to detect possible inconsistency during concurrent execution and either the inconsistency recovered or avoided. They are called Time Stamping Protocols.

Classification of concurrency control protocol

What is locking?

  • The concurrency problems can be solved by means of concurrency control technique called locking.
  • A LOCK variable ( equivalent to semaphores used in OS) is associated with each data item which is used to identify the status of the data item ( whether the data is in use or not).
  • When a transaction T intends to access the data item, it must first examines the associated LOCK.
  • If no other transaction holds the LOCK, the scheduler lock the data item for T.
  • If another transaction T1 wants to access same data item then the transaction T1 has to wait until T releases the lock.
  • Thus, at a time only one transaction can access the data item.
Using locks, we ensure Serializability and Recoverability but sometimes they may lead to Deadlock.

Deadlock occurs when each transaction T in  set of two or more transactions is waiting for some item that is locked by some other transaction T' in the set.

Transaction Responsibility : Legal Transaction

  1. To request lock before use of data item
  2. To release lock after use of data item.
Example :
L(A)   // Locks the data item A
R(A)   // Read data item A
W(A)   // Write data item A
U(A)   // Unlock data item A 
L(B)   // Locks the data item B

Locking Procedure - Concurrency control protocol

Timestamp ordering

A different approach that guarantees serializability involves using transaction timestamps in order transaction execution for an equivalent serial schedule.

Timestamp ordering is an alternative to locking in which the problem of concurrency control can be handled by assigning ordered timestamps to transactions.

In this Scheme :

Transaction timestamp TS (T) is a unique identifier which tells the order in which the transaction are started. Every transaction is assigned a timestamp by Database Administrator (DBA) such that

  • Timestamp is unique.
  • Time stamps are in increasing order.

Hence, if transaction T1 starts before transaction T2, then TS(T1) <TS(T2).

A timestamp based scheduler orders conflicting operations according to their timestamp values.
Thus if Pi(X) and Qi(X) are two conflicting operations on data item X requested by transaction Ti and Tj,

Pi(X) will be scheduled before Qi(X) if and only if TS(Ti) < TS(Tj).

Previous Home Next
Implementation of Transaction in SQL Binary Locks


Incoming search terms:

  • edugrabs DBMS concrrency
  • concurrency control mechanism in dbms
  • edu grabs concurrency control ques

Leave a Reply