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-
1/9
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.
2/9
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-
3/9
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:------
4/9
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.
5/9
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
6/9
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.
7/9
