Tag: Concurrency Control Problems in DBMS

Concurrency Problems | DBMS

Transactions in DBMS-

 

Before you go through this article, make sure that you have gone through the previous article on Transactions in DBMS.

 

We have discussed-

  • A transaction is a set of logically related operations.
  • A transaction goes through different states throughout its life cycle.
  • ACID Properties are followed by each transaction to ensure the consistency of database.

 

In this article, we will discuss about concurrency problems of transactions.

 

Concurrency Problems in DBMS-

 

  • When multiple transactions execute concurrently in an uncontrolled or unrestricted manner, then it might lead to several problems.
  • Such problems are called as concurrency problems.

 

The concurrency problems are-

 

 

  1. Dirty Read Problem
  2. Unrepeatable Read Problem
  3. Lost Update Problem
  4. Phantom Read Problem

 

1. Dirty Read Problem-

 

Reading the data written by an uncommitted transaction is called as dirty read.

 

This read is called as dirty read because-

  • There is always a chance that the uncommitted transaction might roll back later.
  • Thus, uncommitted transaction might make other transactions read a value that does not even exist.
  • This leads to inconsistency of the database.

 

NOTE-

 

  • Dirty read does not lead to inconsistency always.
  • It becomes problematic only when the uncommitted transaction fails and roll backs later due to some reason.

 

Example-

 

 

Here,

  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 the 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 roll backs.
  • Thus, the value that T2 read now stands to be incorrect.
  • Therefore, database becomes inconsistent.

 

2. Unrepeatable Read Problem-

 

This problem occurs when 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.

 

Example-

 

 

Here,

  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.

 

3. Lost Update Problem-

 

This problem occurs when multiple transactions execute concurrently and updates from one or more transactions get lost.

 

Example-

 

 

Here,

  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 read) in the buffer.
  4. T2 commits.
  5. When T1 commits, it writes A = 25 in the database.

 

In this example,

  • T1 writes the over written value of X in the database.
  • Thus, update from T1 gets lost.

 

NOTE-

 

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

 

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.

 

Example-

 

 

Here,

  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.

 

Avoiding Concurrency Problems-

 

  • To ensure consistency of the database, it is very important to prevent the occurrence of above problems.
  • Concurrency Control Protocols help to prevent the occurrence of above problems and maintain the consistency of the database.

 

Next Article- Schedules in DBMS

 

Get more notes and other study material of Database Management System (DBMS).

Watch video lectures by visiting our YouTube channel LearnVidFun.