Concurrency Control in DBMS

Concurrency control in DBMS plays an important role in transaction management. Concurrency control is an important topic in Database management system subject. Problems based on concurrency control are always asked in GATE(CS/IT) and UGC NET exam.

Today we will explain the concurrency control techniques in DBMS here in this tutorial.

Let’s start with introduction of concurrency control in DBMS

Frequently Asked Questions

After reading this tutorial students will be able to answer the following frequently asked questions-

• What is concurrency in database system ?
• What is purpose of concurrency control ?
• How concurrency control is implemented in DBMS ?
• What are various methods of concurrency controls ?
• What are concurrent transactions in DBMS ?
• What is meant by concurrent execution ?

What is Concurrency Control in DBMS ?

In a certain application multiple transactions will be executed in parallel concurrently. So if there is no concurrency control, then those transaction executions will leave behind the database in some inconsistent state.

“The concurrency control manages simultaneous or concurrent operations that execute in the database without interfering with one another or without conflict.”

That means the concurrency control will ensure that each and every transaction must behave as if the transaction was getting executed not in parallel with others and the transaction’s effect will be permanent and will be durable on the database as if the transaction was executed in isolation.

Concurrency Control Problems

There may arise several types of problems due to concurrency. The three main problems in concurrency control in DBMS. These problems are as follow –

Lost Update Problem in Concurrency Control

While one transaction will make some changes in one of the variables, another transaction may try to cancel that update and if that cancel in the update occurs, there is a problem.  This problem is lost update problem.

Here, update made by a transaction is nullified by the execution of another transaction. One example is shown below-

Uncommitted Dependency Problem in Concurrency Control

If during execution of a transaction, a variable gets updated and another transaction has initiated in parallel concurrently with the former transaction.

The newly initiated transaction has read that updated content in the variable. In the meantime, the previous transaction has Rolled Back or Terminated.

Now the transaction that read the updated content of the variable will have the ‘dirty read’ i.e. the updated value is not current as the transaction has terminated before.

Uncommitted Dependency problem occurs while the transaction is not committed and no permanent change is there in the database whatsoever.

One example is shown below-

Inconsistent Analysis Problem in Concurrency Control

When one transaction is updating multiple different variables and another transaction is in a process to add some instances of those variables.

Now as the previous transaction is updating various variables, in the second transaction those different variables will be available in the different instances of the database, which will cause another problem.

Instance of a database means, the content of the database at that certain instant of time. The problem caused due to this is called inconsistent analysis problem.

One example is shown below-

Conclusion and Summary

In this Concurrency Control in DBMS Tutorial we have explained the overview of concurrency control and different problems occurs in concurrency control.

I hope that after reading this tutorial students will be able to answer the concurrency control problems questions.