Concurrency Problems In Database
- In Non-serial schedule, when multiple transactions execute concurrently but in an uncontrolled manner, then several problems can happen.
- These problems are known as concurrency problems.
Note: Uncontrolled manner means the Execution of conflict pairs without any restriction.
Example: In a multi user environment system, multiple users can access the same database at a time. If the there is an BANK database and multiple users needs to perform the transactions without any restriction, then there may be the following concurrency problems can happens.
Types of Concurrency Problems
There are four major concurrency problems
1. Dirty Read Problem
Reading the data of one instruction which is written by an uncommitted second transaction is called as dirty read.
- Dirty read is not problematic always.
- It is problematic in only case when the uncommitted transaction is fail later on. Because as the failure occur, roll back occurs.
- Transaction T2 reads the dirty value of data “A” written by the uncommitted transaction T1.
- T1 fails in later one and roll backs.
- So, the value that transaction T2 reads now does not exist anywhere.
- Thus, database inconsistency occurs.
2. Unrepeatable Read Problem
This problem is also known as Inconsistent Retrievals Problem. It occurs when there are different read values of the same variable occurs even when it has not updated its value.
- T1 reads the value of A (= 15 say).
- T2 reads the value of A (= 15).
- T1 updates the value of A (from 15 to 25 say) in the buffer.
- T2 again reads the value of A (but = 25).
T2 wonders how the value of data variable “A” got changed from 15 to 25 even when it has not updated its value. T2 understand that, it is running in isolation.
3. Phantom Read Problem
It occurs when a transaction reads some data-variable from the database-buffer and when it reads the same data-variable later, it finds that the data-[variable does not exist.
- T1 reads data variable “A”.
- T2 reads data variable “A”.
- T1 deletes data variable “A”.
- T2 tries reading data variable “A” but does not find it.
Conclusion: T2 wonders who deleted the data variable “A”. T2 understand that, it is running in isolation.
4. Lost Update Problem
It occurs when multiple transactions execute simultaneously and updates from one or more transactions get lost. It is due to over-written. So, it is also called write-write conflict.
- T1 reads the value of A (= 20 say).
- T1 updates the value to A (= 30 say) in the buffer.
- T2 does blind write A = 50 (write without read) in the buffer.
- T2 commits.
- When T1 commits, it writes A = 50 in the database.
- T1 perform the over-written of A value in the database.
- So, W(A) from T1 gets lost.
Avoiding Concurrency Problems
Concurrency Control Protocols are used to prevent the occurrence of above problems.
Concurrency control protocols ensure consistency , atomicity, isolation, and serializability of concurrent transactions in a database. The concurrency control protocol can be any of the following type
- Lock based concurrency control protocol
- Time-stamp concurrency control protocol
- Validation based concurrency control protocol