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.