Concurrency Control in DBMS

When more than one transactions are running simultaneously there are chances of a conflict to occur which can leave database to an inconsistent state. To handle these conflicts we need concurrency control in DBMS, which allows transactions to run simultaneously but handles them in such a way so that the integrity of data remains intact.

Concurrent Execution in DBMS

In a multi-user environment, multiple users are allowed to access data simultaneously. These users can send requests at the same time, which means the DBMS is serving multiple requests from different users simultaneously. This is called concurrent execution in DBMS.
In concurrent execution, there are several operations performed on the data items in database concurrently. There can be multiple read requests, write requests or combination of both. Serving a read request while another user is making change in the database can cause several issues that we discussed in the next section of this article.
The goal of the concurrent execution is to allow multiple transactions to execute simultaneously in such a way, that each executing transaction doesn’t affect the other transaction in any way.

Issues with the concurrent execution

Let’s take an example to understand what are the issues that can arise when transactions are executing concurrently.

Conflict Example

You and your brother have a joint bank account, from which you both can withdraw money. Now let’s say you both go to different branches of the same bank at the same time and try to withdraw 5000 INR, your joint account has only 6000 balance. Now if we don’t have concurrency control in place you both can get 5000 INR at the same time but once both the transactions finish the account balance would be -4000 which is not possible and leaves the database in inconsistent state.
We need something that controls the transactions in such a way that allows the transaction to run concurrently but maintaining the consistency of data to avoid such issues.

Problems with the concurrent execution

There are two main operations in the database, read and write. If we somehow manage these two operations on a data item in such a way, that the database always end up being in a consistent state, then we can say that this is a perfect concurrency control scenario.

Problem 1: W-W Conflict – Lost Update Problem

This conflict occurs when two transactions perform a write operation on a same data item in database in such a way that the database ends up in an inconsistent state. This problem is also known as Write- Write conflict or W-W conflict or lost update problem.

Let’s take an example to understand this problem:

In this example, at time t1, the transaction T1 reads the value of data item A. Let’s say the value of A is 1000.
At time t2, transaction T1 adds 100 to the data item A, the value of A becomes 1100. This is value is yet to be updated in database as no write operation has been performed yet.
At time t3, transaction T2 read the value of data item A, the value of A in database is still 1000 so transaction T2 reads the A value as 1000.
AT time t4, transaction T2 adds 200 to data item A, in transaction T2 the value of A becomes 1200 but this value is yet to be updated in database.
At time t5, transaction T1 writes the value of A in database, since the value of A is 1100 according to transaction T1, it updates the value of A in database to 1100.
At time t7, transaction T2 writes the value of A in database, since the value of A is 1200 in T2, it updates the value of A to 1200 in database.

Lost Update: Initial value of A was 1000, if T1 is adding 100 and T2 is adding 200, the value of A in database should be 1300 at the end of execution of both of these transactions. However as you can see the value of A is 1200 in this case. This is because the update made by transaction T1 is lost.

Problem 2: W-R Conflict – Dirty Read Problem

This conflict occurs when a transaction make changes to a data item in the database and the transaction fails after making the change. However before the failed transaction is rolled back, another transaction reads the value updated by failed transaction. This is called Dirty Read in DBMS or W-R (Write – Read) Conflict.

Let’s take an example to understand this conflict:

At time t1, transaction T1 reads the value of A, let’s say the value of A is 1000. T1 read A as 1000.
At time t2, T1 deducts 500 from A, the value of A becomes 500.
At time t3, T1 make the changes in database by writing the value of A. The database value of A gets updated from 1000 to 500.
At time t4, another transaction T2 reads the value of A which is 500 now in database.
At time t5, T2 adds 100 and value of A is now 600 but in database it is still 500
At time t6, Transaction T1 fails and T2 writes the value of A in database, value of A gets updated to 600 in database.
At time t7, transaction T1 is rolled back because it is failed in previous step.

Dirty Read: Since T1 failed and rolled back, the changes made by T1 should be reverted. T2 should have read original value of A which is 1000 and at the end of T2 the value of A in database should be 1100. However as we have seen above, the value of A in database is 600. This is because T2 read an updated value by failed transaction. This is called dirty read and it left the database in an inconsistent state.

Problem 3: W-R Conflict – Non-Repeatable Read Problem

This conflict occurs when a transaction reads the different values for same data-item. This is also known as inconsistent retrieval or Non-repeatable read problem.

Let’s take an example to understand this:

At time t1, the transaction T1 reads the value of A as 1000.
At time t6, the same transaction T1 reads reads the different value of A as 500.
This is because between time t1 and t6, an another transaction made the changes in the database and updated the value of A from 1000 to 500.
This is an issue as the transaction reads two different values for same data item, thus it is called non-repeatable read problem. This leaves the database in an inconsistent state.