Concurrency Control in DBMS

In DBMS when multiple users are allowed to access the same data at the same time it is referred to as Concurrency. The process to do this is called Concurrency Control in DBMS.

Need of Concurrency Control in DBMS

Concurrency is needed for the following reasons:

1. Increase the number of transactions executed per unit time i.e., improve the throughput.
2. Better Resource Utilization as the CPU and Disk operations can be executed side by side.
3. Reduced average waiting time for transactions to complete.

Problems Arising Due to Concurrency

Concurrency is of great advantage because of the above said reasons but when there is multiple concurrent access to a single file in the database, then many problems would arise in a DBMS.

The problems due to concurrency are as follows:

Now let us study these in detail.

LostUpdate Problem (WW Conflict)

This problem arises when data which is being updated by one transaction is overwritten by an update of another transaction.

Consider the following scenario:

Here, in these concurrent transactions, at time t1 transaction 1 reads a value a from the database. At time t2, transaction 2 reads the same value. At time t3 transaction 1 updates the value of a. And without the knowledge of the update done by transaction 1, transaction 2 updates the value of a at time t4.

As a result, the update done by transaction 1 is overridden by the update performed by transaction 2. This loss of data is known as a Loss Update Problem.

Solution to the problem

To overcome this problem, there must be a locking mechanism performed by DBMS such that when one transaction is performing an update operation then the other transactions must not be able to even retrieve the unmodified data until a commit or rollback is performed by the preceding transaction.

Dirty Read or Temporary Update or Uncommitted Dependency Problem (WR Conflict)

This problem arises when a transaction retrieves intermediate results of another transaction before it is committed or rollbacked.

Consider the following scenario:

Here if we observe closely, at time t1 transaction 2 updates the value of a. at time t2 transaction 1 reads the uncommitted value of a. and at time t3 transaction 2 rollbacks its update.

This leads to a problem where transaction 1 reads the wrong value for a and executes according to the wrong data. This will lead to incorrect output.

Solution to the problem

This problem would be solved when one transaction is prevented from reading the values of the database until all transactions those are performing update operations on that database have completed their executions.

Inconsistent Analysis Problem (RW Conflict)

This problem arises when one of the transactions is using aggregate functions on a set of records from the database. Here, a transaction uses some but not all updated values from the database.

Consider the following example:

Let us consider two bank accounts B1 and B2 with Rs 110 and Rs 210 as their balances respectively. Now let us consider two transactions working on these concurrently.

If we take a closer look then the result of transaction 1 must be the sum of the balances of the two accounts. The correct result (considering the updates made as they are committed by transaction 2) should have been Rs 400.

But, at time t1 the read balance for account B1 is Rs 110 and at time t7 the read balance for account B2 is Rs 240. At time t8 the sum would yield a total of Rs 350. Hence, leading to a wrong output.

Solution to the problem

Once the data is read by a transaction, an update operation by another transaction must not be allowed concurrently. This would ensure the consistency of data to a great extent.

Conclusion

Whenever transactions are run concurrently, it is the responsibility of the DBMS to ensure that transactions must be committed successfully and their effect must be recorded in the database permanently. Concurrency Control in DBMS ensures that concurrent execution of transactions must not lead to data inconsistencies.

Anupreet Kalsi