Problems With Concurrent Execution of Transactions in SQL

 

The Lost Update Problem

When database transactions are executed, they are typically sequential and very dutifully update the data as expected. On occasion, transactions happen nearly simultaneously, which can lead to something called a lost update. A lost update may cause data within the database to be incorrect, which can lead to problems with normal operations, such as fulfilling customer orders.


A lost update occurs when two different transactions are trying to update the same column on the same row within a database at the same time.


Typically, one transaction updates a particular column in a particular row, while another that began very shortly afterward did not see this update before updating the same value itself.

The Temporary Update (or Dirty Read) Problem

One of the most common problems that occur while running concurrent transactions is the Dirty Read problem. A dirty read occurs when one transaction is permitted to read data that is being modified by another transaction which is running concurrently but which has not yet committed itself.

 This problem occurs when one transaction updates a database item and then the transaction fails for some reason. Meanwhile, the updated item is accessed (read) by another transaction before it is changed back to its original value.

The Incorrect Summary Problem

This problem is caused when one of the transactions is executing an aggregate operation on several data items, and other transactions are updating one or more of those data items. This causes a inconsistent database state.

If one transaction is calculating an aggregate summary function on a number of database items while other transactions are updating some of these items, the aggregate function may calculate some values before they are updated and others after they are updated.

The Unrepeatable Read Problem

Unrepeatable read occurs when a transaction calculate some aggregate (summary) function over a set of data while other transaction are updating the data

In, databases Read-Write Conflict, also known as unrepeatable reads, is a computational anomaly associated with interleaved execution of transactions.