Concurrency Problems In Database

  • In a non-serial schedule, when multiple transactions are executed concurrently but in an uncontrolled manner, 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 there is a BANK database and multiple users need to perform the transactions without any restriction, then the following concurrency problems can happen.

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 a dirty read.

  • A dirty reading is not always problematic.
  • It is problematic only when the uncommitted transaction fails later on. Because as the failure occurs, a rollback occurs.

Example

  • Transaction T2 reads the dirty value of data “A” written by the uncommitted transaction T1.
  • T1 fails in a later one and rolls back.
  • 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 even when it has not updated its value.

Example

Here,

  • 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).

Conclusion

T2 wonders how the value of data variable “A” got changed from 15 to 25 even when it has not updated its value. T2 understands 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.

Example

Here,

  • 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.

Example

Here,

  • T1 reads the value of A (= 20, say).
  • T1 updates the value to A (= 30, say) in the buffer.
  • T2 blindly writes A = 50 (write without reading) in the buffer.
  • T2 commits.
  • When T1 commits, it writes A = 50 in the database. 

Conclusion

  • T1 performs the overwriting 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 the 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 types

  • Lock-based concurrency control protocol
  • Time-stamp concurrency control protocol
  • Validation-based concurrency control protocol