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-
- Dirty Read Problem
- Unrepeatable Read Problem
- Lost Update Problem
- 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,
- T1 reads the value of A.
- T1 updates the value of A in the buffer.
- T2 reads the value of A from the buffer.
- T2 writes the updated the value of A.
- T2 commits.
- 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,
- T1 reads the value of X (= 10 say).
- T2 reads the value of X (= 10).
- T1 updates the value of X (from 10 to 15 say) in the buffer.
- 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,
- T1 reads the value of A (= 10 say).
- T2 updates the value to A (= 15 say) in the buffer.
- T2 does blind write A = 25 (write without read) in the buffer.
- T2 commits.
- 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,
- T1 reads X.
- T2 reads X.
- T1 deletes X.
- 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.