Database Concurrency Control
What is Concurrency control?
- Concurrency control manages the transactions simultaneously without letting them interfere with each another.
- The main objective of concurrency control is to allow many users perform different operations at the same time.
- Using more than one transaction concurrently improves the performance of system.
- If we are not able to perform the operations concurrently, then there can be serious problems such as loss of data integrity and consistency.
- Concurrency control increases the throughput because of handling multiple transactions simultaneously.
- It reduces waiting time of transaction.
Consider two transactions T1 and T2.
Deposits Rs. 1000 to both accounts X and Y.
Doubles the balance of accounts X and Y.
X ← X + 1000
Y ← Y + 1000
The above two transactions can be executed concurrently as below
The above concurrent schedule executes in the following manner:
X ← X * 2
Y ← Y * 2
Part of transaction (T1) is executed, which updates X to Rs. 2000.
The processor switches to transaction (T2). T2 is executed and updates X to Rs. 4000. Then the processor switches to transaction (T1) and remaining part of T2 which updates Y to Rs. 2000 is executed.
At the end of remaining part of T2 which reads Y as Rs. 2000, updates it to Rs. 4000 by multiplying value of Y.
This concurrent schedule maintains the consistency of database as,
X + Y = 2000 + 2000 = 4000, remains unchanged.
Therefore, the above schedule can be converted to equivalent serial schedule and hence it is a consistent schedule.