Distributed System: Concurrency Problems in Relational Database
Article Outline :
Concurrency Problem
Concurrency problems are common problems faced while maintaining any database management systems. Concurrency problems occur when multiple transactions are being executed on the same database in unrestricted problems.
Types of concurrency problems in DBMS are as follows:
Lost Update Problem:
What Are the Different Ways to Prevent Lost Updates?
As we can see from the above example, the lost update problem occurs in DBMS due to concurrent transactions being performed on a DB and a lack of control over these transactions.
But, a lost update problem can prove fatal in the case of critical databases like banking databases. And we need to control and prevent lost update concurrency problems. In this section, we will see numerous ways to prevent lost update problem and ensure that update from any transaction is not lost.
Increase Transaction Isolation Level
Increasing the isolation level of transactions on a database is one of the ways to prevent lost update problems. Isolation is I in ACID properties. This method ensures that the isolation level of transactions on a database is increased to ‘Repeatable Read’ so that database can perform efficient checks in conjunction. The method to set the isolation level to ‘Repeatable Read’ is different in multiple database systems.
Optimistic Locking
Optimistic locking is the most common method used to prevent lost update problems. This method allows any update of a record to happen only when the value of that record has not changed after its last read. Optimistic Locking checks if the current value of the record is the same as it was when previously read then the update is not allowed, and the read-modify-write cycle has to be repeated. Optimistic Locking is also called Conditional Update or Compare-And-Update.
Pessimistic Locking
Pessimistic locking is also a way to prevent lost update problems in DBMS. In this approach, the DB objects that are going to be updated are explicitly locked using the ‘PESSIMISTICWRITE’ mode. After locking the object read-modify-write operations are performed on the objects and then the object is released. During these operations, if another transaction tries to read the same object it has to wait until the read-modify-write cycle of the first transaction is completed.
Atomic write operations
One of the many ways to prevent lost update problems is Atomic write operations. Atomic write operations read records at a memory location and write a new value into it simultaneously. Atomic write operations remove the need to implement read-modify-write cycles repeatedly. In this method a record is read and written at the same time, hence the lost update problem is avoided.
Recommended by LinkedIn
Dirty Read Problem
The dirty read problem in DBMS occurs when a transaction reads the data that has been updated by another transaction that is still uncommitted. It arises due to multiple uncommitted transactions executing simultaneously.
Example: Consider two transactions A and B performing read/write operations on a data DT in the database DB. The current value of DT is 1000: The following table shows the read/write operations in A and B transactions.
Transaction A reads the value of data DT as 1000 and modifies it to 1500 which gets stored in the temporary buffer. The transaction B reads the data DT as 1500 and commits it and the value of DT permanently gets changed to 1500 in the database DB. Then some server errors occur in transaction A and it wants to get rollback to its initial value, i.e., 1000 and then the dirty read problem occurs.
Unrepeatable Read Problem
The unrepeatable read problem occurs when two or more different values of the same data are read during the read operations in the same transaction.
Example: Consider two transactions A and B performing read/write operations on a data DT in the database DB. The current value of DT is 1000: The following table shows the read/write operations in A and B transactions.
Transaction A and B initially read the value of DT as 1000. Transaction A modifies the value of DT from 1000 to 1500 and then again transaction B reads the value and finds it to be 1500. Transaction B finds two different values of DT in its two different read operations.
Phantom Read Problem
In the phantom read problem, data is read through two different read operations in the same transaction. In the first read operation, a value of the data is obtained but in the second operation, an error is obtained saying the data does not exist.
Example: Consider two transactions A and B performing read/write operations on a data DT in the database DB. The current value of DT is 1000: The following table shows the read/write operations in A and B transactions.
Transaction B initially reads the value of DT as 1000. Transaction A deletes the data DT from the database DB and then again transaction B reads the value and finds an error saying the data DT does not exist in the database DB.
Incorrect Summary Problem
The Incorrect summary problem occurs when there is an incorrect sum of the two data. This happens when a transaction tries to sum two data using an aggregate function and the value of any one of the data get changed by another transaction.
Example: Consider two transactions A and B performing read/write operations on two data DT1 and DT2 in the database DB. The current value of DT1 is 1000 and DT2 is 2000: The following table shows the read/write operations in A and B transactions.
Transaction A reads the value of DT1 as 1000. It uses an aggregate function SUM which calculates the sum of two data DT1 and DT2 in variable add but in between the value of DT2 get changed from 2000 to 2500 by transaction B. Variable add uses the modified value of DT2 and gives the resultant sum as 3500 instead of 3000.