Concurrency control is needed to ensure that multiple transactions running simultaneously do not interfere with each other in a way that leads to inconsistencies or data corruption.
In a multi-user environment, if two or more transactions access the same data at the same time, it may lead to:
- Lost Updates
- Dirty Reads
- Non-repeatable Reads
- Uncommitted Data Access
Goals of Concurrency Control
- Ensure correctness of database transactions
- Maintain isolation (the ‘I’ in ACID)
- Avoid conflicts and anomalies
- Allow parallelism without compromising integrity
Example Without Concurrency Control
Let’s say two users, T1 and T2, are transferring money at the same time.
Initial Balance in Account A = ₹1000
Transaction T1 (Withdraw ₹100):
READ A → 1000 A = A - 100 → 900 WRITE A
Transaction T2 (Deposit ₹200):
READ A → 1000 A = A + 200 → 1200 WRITE A
If T1 and T2 run concurrently:
- T1 reads A = 1000
- T2 reads A = 1000
- T1 writes A = 900
- T2 writes A = 1200
Final balance becomes ₹1200, but it should have been:
1000 - 100 + 200 = ₹1100
This is a lost update anomaly.
How Concurrency Control Helps
If locking or serial schedules are used:
- T1 would lock Account A
- T2 would wait until T1 finishes
- T1 writes 900
- T2 reads 900, adds 200, writes 1100
Now the final balance is correct.
Techniques for Concurrency Control
| Technique | Description |
|---|---|
| Lock-based | Transactions lock data items before accessing them |
| Timestamp ordering | Uses timestamps to schedule transactions |
| Optimistic control | Checks conflicts only at commit time |
| Multiversion control | Keeps multiple versions of data for reading |
Conclusion
Concurrency control ensures that:
- Transactions do not interfere with each other
- Database remains consistent
- Users see accurate results
It is essential for correct behavior in multi-user environments.