concurrency control in DBMS

Concurrency control in DBMS

Introduction of Concurrency Control (Concurrency Meaning- parallelism)

  • Concurrency control is the procedure in DBMS for managing simultaneous( parallel ) operations without conflicting with each other.
  • Concurrency control is used to address such conflicts which mostly occur with a multi-user system. 
  • It helps to make sure that database transactions are performed concurrently without violating the data integrity of respective databases.


Problems with concurrency control

When multiple transactions execute concurrently in an uncontrolled or unrestricted manner, then it might lead to several problems. 

The following are the problems in concurrency control.

1. Dirty Read

  • Reading the data written by an uncommitted transaction is called a dirty read.
  • This leads to the inconsistency of the database.
  • The dirty read occurs in the case when one transaction updates an item of the database, and then the transaction fails for some reason.
  • The updated database item is accessed by another transaction before it is changed back to the original value.

  1. T1 reads the value of A.
  2. T1 updates the value of A in the buffer.
  3. T2 reads the value of A from the buffer.
  4. T2 writes the updated value of A.
  5. T2 commits.
  6. T1 fails in later stages and rolls back.

In this example,
T2 reads the dirty value of A written by the uncommitted transaction T1.
T1 fails in later stages and rollbacks.
Thus, the value that T2 read now stands to be incorrect.
Therefore, the database becomes inconsistent.

NOTE:- Dirty read does not lead to inconsistency always. It becomes problematic only when the uncommitted transaction fails and rollbacks later due to some reason.

A transaction T1 updates a record that is read by T2. If T1 aborts then T2 now has values
which have never formed part of the stable database.

  • At time t2, transaction-Y writes A's value.
  • At time t3, Transaction-X reads A's value.
  • At time t4, Transactions-Y rollbacks. So, it changes A's value back to that of prior to t1.
  • So, Transaction-X now contains a value that has never become part of the stable database.
  • Such a type of problem is known as the Dirty Read Problem, as one transaction reads a dirty value that has not been committed.
  • ===================================================

2. Inconsistent Retrievals Problem

  • Inconsistent Retrievals Problem is also known as unrepeatable read.
  • a transaction gets to read unrepeated i.e. different values of the same
  • variable in its different read operations even when it has not updated its value.
  • When a transaction calculates some summary function over a set of data while the other transactions are updating the data, then the Inconsistent Retrievals Problem occurs.
  • A transaction T1 reads a record and then does some other processing during which the transaction T2 updates the record. Now when the transaction T1 reads the record, then the new value will be inconsistent with the previous value.


  1. T1 reads the value of X (= 10 say).
  2. T2 reads the value of X (= 10).
  3. T1 updates the value of X (from 10 to 15 say) in the buffer.
  4. T2 again reads the value of X (but = 15).

In this example,
T2 gets to read a different value of X in its second reading.
T2 wonders how the value of X got changed because according to it, it is running in isolation.

Suppose two transactions operate on three accounts.

Transaction-X is doing the sum of all balances while transaction-Y is transferring an amount
  • 50 from Account-1 to Account-3.
  • Here, transaction-X produces the result of 550 which is incorrect. If we write this produced result in the database, the database will become an inconsistent state because the actual sum is 600.
  • Here, transaction-X has seen an inconsistent state of the database.


3. Lost update problem

  • When two transactions that access the same database items contain their operations in a way that makes the value of some database item incorrect, then the lost update problem occurs.
  • If two transactions T1 and T2 read a record and then update it, then the effect of updating the first record will be overwritten by the second update.

  1. T1 reads the value of A (= 10 say).
  2. T2 updates the value to A (= 15 say) in the buffer.
  3. T2 does blind write A = 25 (write without reading) in the buffer.
  4. T2 commits.
  5. When T1 commits, it writes A = 25 in the database.

In this example,
T1 writes the overwritten value of X in the database.
Thus, an update from T1 gets lost.

NOTE:- This problem occurs whenever there is a write-write conflict. In the write-write conflict, there are two writes one by each transaction on the same data item without any read in the middle.



Transaction - X

Transaction - Y





Read A




Read -A


Write A




Write- A





  • At time t2, transaction-X reads A's value.
  • At time t3, Transaction-Y reads A's value.
  • At time t4, Transactions-X writes A's value on the basis of the value seen at time t2.
  • At time t5, Transactions-Y writes A's value on the basis of the value seen at time t3.
  • So at time T5, the update of Transaction-X is lost because Transaction y overwrites it without looking at its current value.
  • Such a type of problem is known as the Lost Update Problem as an update made by one transaction is lost here.


4. Phantom Read Problem-

This problem occurs when a transaction reads some variable from the buffer and when it reads the same variable later, it finds that the variable does not exist.


  1. T1 reads X.
  2. T2 reads X.
  3. T1 deletes X.
  4. T2 tries reading X but does not find it.

In this example,
T2 finds that there does not exist any variable X when it tries reading X again.
T2 wonders who deleted the variable X because according to it, it is running in isolation.

Reasons for using the Concurrency control method is DBMS:

  • To apply Isolation through mutual exclusion between conflicting transactions
  • To resolve read-write and write-write conflict issues
  • To preserve database consistency through constantly preserving execution obstructions
  • The system needs to control the interaction among concurrent transactions. This control is achieved using concurrent-control schemes.

Concurrency control helps to ensure serviceability.


Post a Comment