Concurrency Control in DBMS

Introduction to Concurrency Control in DBMS

Concurrency control in DBMS is an important concept that is related to the transactions and data consistency of the database management systems. Concurrency control refers to the process of managing independent operations of the database that are simultaneous and considered as a transaction in DBMS. Concurrency Control works on the principle of maintaining the transactions state that can be a complete transaction or an incomplete transaction. In the case of the complete transaction, all the associated database operations need to be completed with specified rules and sequences, whereas an incomplete transaction occurs in case all the database operations are not completed due to some technical, power failure, or network connectivity issue.

How does Concurrency Control work in DBMS?

The concurrency control is the process to maintain the data where there are multiple resources or users are accessing the data element and performing the database operations. There are several enterprise systems such as banking, ticket booking, and traffic light systems that use a shared database as part of the data store associated with concurrent transactions. There is a chance of conflict for these transactions and resulting data inconsistency.

We will discuss the protocols and the problems related to concurrency control in DBMS.

Concurrency Control Protocols

Concurrency control protocols are the techniques used to maintain data consistency, atomicity, and serializability. Following are some of the  concurrency control protocols

1. Lock based

The lock-based protocol is the technique of applying lock condition on the data element, which helps in restricting another resource to perform read and write operation until the lock is active. There are mainly two types of lock such as shared or read-only lock and exclusive lock.

2. Validation based

The validation based protocol is also known as an optimistic concurrency control technique. It involves the read phase, validation phase, and writes phase for concurrency control.

3. Timestamp based

The timestamp-based protocol uses system time or logical count as a timestamp to serialize the execution of concurrent transactions that helps to maintain the order of the transactions.

4. Two-phase protocol

The two-phase protocol (2PL) is a locking mechanism that ensures serializability by using two distinct phases of lock condition. It uses the expanding phase and shrinking phase to acquire and release the lock condition to maintain concurrency control.

Concurrency Control Problems

There are multiple problems that can arise in concurrent transaction scenarios. Some of the common problems are:

1. Dirty Read

Dirty read or temporary update problems happen while there is an incomplete transaction. In this scenario, the data element or item got updated by one transaction and filed before completing it. And another transaction tries to access the data element before it is modified or rolled back to its last value.

Explanation: As shown in the table the transaction T1 reads a data item X as Reading (X) operation and performs some arithmetic operation on the Value X  using a numeric value n with Write (X-n) operation. While the write operation in action, it got interrupted and not yet reverted to the databases, The Transaction T2 tries to read the Value X through reading (X) operation that represents the value as X-n. This results in a dirty read problem.

2. Unrepeatable Read

Unrepeatable Read is the scenario where two or more read operations read the same variable as different values and that value is modified by a different transaction by writing operations.

Explanation: The table shows two transactions T1 and T2. The T1 reads the Variable X and performs an arithmetic operation as X-n with numeric value n, At the Same time T2 reads the value X and captures the initial value of X. Next T1 performs a Write(X) operation and modified the value of X in the database. Next T2 reads the X values again and this time it finds a different value of X due to the T1. This results in an unrepeatable read problem.

3. Phantom Read

Phantom read problem refers to the scenario where the Transaction reads a variable once and when it tries to read the variable again it gets an error showing the variable does not exist, as the variable is deleted by another transaction.

Explanation: The Table shows T1 reads Variable X, simultaneously T2 reads X. The T1 Deletes X with Delete(X) operation, without T2 acknowledgment. While, T2 tried to read the variable X again, it not able to find the variable. This results in the phantom read problem.

4. Lost updates

Lost updates are the concurrency problem scenario where modification to the variable done by a transaction is lost due to write operation by another transaction.

Explanation: The Table shows the T1 reads the variable X and modifies the values by adding a numerical value n in the operation X=X+n statement. However, T2 performs X=X+n1 statement that overwrites the T1 arithmetic operation. This results in a lost update problem for the T1 transaction.

5. Incorrect Summary

An incorrect Summary problem in concurrency control scenario appears while a transaction applies an aggregate function to some of the variables while another transaction tries to update the variable.

Explanation: The tables show Transaction T1 reads the variable X and uses the Value of X to generate the aggregate value of Sum=Sum+X, whereas T2 reads the value of X, Modifies it by X=X+n statement and writes it to the database using Write(X) operation. It results in an incorrect summary problem in T1.

Conclusion

Concurrency Control in DBMS is a very useful technique to maintain mutually exclusive transactions for database operations. It manages the requests and streamlines the operations where multiple systems or processes try accessing the same database resource. It helps in data integrity across systems and avoids the occurrence of transaction conflicts.

Recommended Articles

This is a guide to Concurrency Control in DBMS. Here we discuss an introduction to Concurrency Control in DBMS along with how does it work, protocols, and problems. You can also go through our other related articles to learn more –

1. Introduction To DBMS
2. MongoDB Alternatives
3. Operational Database
4. Lock Based Protocols in DBMS