Concurrency in DBMS

Concurrency control concept comes under the Transaction in database management system (DBMS). It is a procedure in DBMS which helps us for the management of two simultaneous processes to execute without conflicts between each other, these conflicts occur in multi user systems.

Concurrency can simply be said to be executing multiple transactions at a time. It is required to increase time efficiency. If many transactions try to access the same data, then inconsistency arises. Concurrency control required to maintain consistency data.

For example, if we take ATM machines and do not use concurrency, multiple persons cannot draw money at a time in different places. This is where we need concurrency.

The advantages of concurrency control are as follows −

• Waiting time will be decreased.
• Response time will decrease.
• Resource utilization will increase.
• System performance & Efficiency is increased.

But simultaneous execution of transactions over shared databases can create several data integrity and consistency 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.

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.

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.