Concurrent Execution of Transaction in DBMS

In an uncontrolled manner, when multiple transactions in DBMS run concurrently, problems such as inconsistencies, uncommitted updates, and lost data can occur. These issues, known as concurrency problems, can disrupt the accuracy and reliability of the database.

Example: In a multi-user environment, multiple users can access the same database simultaneously. If there is a BANK database and multiple users need to perform transactions without any restrictions, the following concurrency problems may occur.

Types of Concurrency Problems

Below, we discuss the four major types of concurrency problems that can arise in a DBMS:

1. Dirty Read Problem

A dirty read occurs when a transaction reads data that has been written by another transaction, but that transaction has not yet been committed. In simpler terms, a dirty read happens when one transaction reads uncommitted data from another transaction.

This problem becomes significant only when the uncommitted transaction later fails and is rolled back. As a result, the data that was read by the other transaction is no longer valid, causing inconsistency in the database.

Example:

  • Transaction T2 reads a value (say, data “A”) that was written by an uncommitted transaction T1.
  • Later, T1 fails and rolls back, meaning the changes it made are undone.
  • Since T2 read data that no longer exists, the database becomes inconsistent.

2. Unrepeatable Read Problem (Inconsistent Retrievals)

The unrepeatable read problem occurs when a transaction reads the same data multiple times, but during its execution, another transaction modifies the data. This leads to inconsistent results because the data read by the first transaction changes unexpectedly during its course.

Example:

  • Transaction T1 reads the value of data “A” (say, 15).
  • Transaction T2 also reads the same value (15) of data “A”.
  • Transaction T1 updates the value of A from 15 to 25 in the buffer.
  • Transaction T2 reads the value of A again but finds it as 25 instead of 15.

This leads to confusion as T2 wonders why the value of data A changed from 15 to 25, even though no updates were performed during its transaction.

3. Phantom Read Problem

A phantom read occurs when a transaction reads a set of data, but when it tries to read the same set of data later in the transaction, the data has changed. Specifically, a row that existed previously may not be present in the database anymore, resulting in a “phantom” row that seems to disappear.

Example:

  • Transaction T1 reads data variable “A.”
  • Transaction T2 also reads data variable “A.”
  • Transaction T1 deletes data variable “A.”
  • Transaction T2 attempts to read the data again but does not find it.

This leads to the phantom read problem, where T2 is confused about why the data is no longer available, as it appears to have been deleted during the execution of a different transaction.

4. Lost Update Problem (Write-Write Conflict)

The lost update problem happens when two or more transactions attempt to update the same data item concurrently, leading to one or more updates being overwritten. This issue is also referred to as a write-write conflict, and it causes one transaction’s changes to be lost when another transaction writes over them.

Example:

  • Transaction T1 reads the value of data “A” (say, 20).
  • Transaction T1 updates the value of A from 20 to 30.
  • Meanwhile, Transaction T2 writes a new value (50) to A without reading it.
  • Transaction T1 commits its changes, overwriting the value written by T2, thus causing T2’s update to be lost.

This results in an inconsistency because the update from one of the transactions is completely lost.

How to Avoid Concurrency Problems

To avoid concurrency problems, Concurrency Control Protocols are essential. These protocols manage the concurrent execution of transactions to ensure that the DBMS maintains consistency, atomicity, isolation, and serializability of transactions. By implementing these protocols, we can prevent concurrency problems like dirty reads, unrepeatable reads, phantom reads, and lost updates.

Types of Concurrency Control Protocols

1. Lock-Based Concurrency Control Protocol

In a lock-based protocol, transactions acquire locks on data items before accessing or modifying them. The types of locks include:

  • Shared locks: Allow read-only access to data.

  • Exclusive locks: Allow both read and write access to data.
    This protocol ensures that conflicting operations (such as reads and writes) are not allowed simultaneously, thus avoiding concurrency problems.

2. Timestamp-Based Concurrency Control Protocol

The timestamp-based protocol assigns a unique timestamp to each transaction when it starts. The protocol ensures that transactions are executed in the order of their timestamps. This approach prevents conflicts by ensuring that transactions are processed in a serializable order, maintaining the consistency of the database.

3.Validation-Based Concurrency Control Protocol

In a validation-based protocol, transactions are executed in three phases:

  • Read phase: The transaction reads data without making any changes.

  • Write phase: The transaction writes changes to the database.

  • Validation phase: The system checks if the transaction’s actions conflict with other concurrent transactions. If there is no conflict, the transaction is committed; otherwise, it is rolled back.

Conclusion

Concurrency control is a vital component of DBMS that ensures multiple transactions can be executed simultaneously without causing inconsistencies or data corruption. By using the appropriate concurrency control protocol, database systems can avoid the four major concurrency problems: dirty reads, unrepeatable reads, phantom reads, and lost updates. Ensuring the correct isolation level and using protocols such as lock-based, timestamp-based, or validation-based methods helps maintain data integrity in a multi-user environment.