Concurrency Control in DBMS is one of the most important concepts in database management systems. It ensures that multiple users can access and modify a database simultaneously without causing data inconsistency, corruption, or transaction conflicts.
In a multi-user database environment, several transactions may execute at the same time. Concurrency control provides mechanisms that coordinate these transactions while maintaining database consistency and improving overall system performance.
Table of Contents
Complete Advance AI Topics: Click Here
SQL Tutorial: Click Here
What is Concurrent Execution in DBMS?
Concurrent execution occurs when multiple transactions are executed at the same time or their operations are interleaved during execution.
For example, consider an airline reservation system where thousands of users may book or cancel tickets simultaneously. Each user’s operation is handled through a transaction. If these transactions access the same data without proper control, they may interfere with each other and produce incorrect results.
A simple way to avoid such problems would be to execute transactions sequentially, one after another. Although this approach can maintain consistency, it significantly reduces system performance.
DBMS therefore supports concurrent execution by taking advantage of CPU and I/O operations. While one transaction waits for an I/O operation, another transaction can use the CPU.
Advantages of Concurrent Execution
- Improved Throughput: More transactions can be completed within a given period.
- Better CPU Utilization: The CPU can execute another transaction while one transaction waits for I/O.
- Reduced Waiting Time: Short transactions do not necessarily have to wait for long transactions to finish completely.
- Better Resource Utilization: CPU, memory, storage, and I/O resources can be used more efficiently.
Problems with Concurrent Execution
Although concurrent execution improves performance, uncontrolled concurrency can produce several problems. The most common problems occur when transactions perform READ and WRITE operations on the same data item at nearly the same time.
1. Lost Update Problem
The Lost Update Problem occurs when two transactions read the same data item and both attempt to update it. One transaction’s update may overwrite the update made by the other transaction.
Example
Suppose the balance of account A is $300.
- Transaction TX reads the balance as $300 and plans to deduct $50.
- Transaction TY also reads the balance as $300 and plans to add $100.
- TX calculates the new balance as $250.
- TY calculates the new balance as $400.
- TX writes $250.
- TY then writes $400.
The update made by TX is overwritten by TY. Therefore, the $50 deduction is lost.
Solution: Locking mechanisms such as exclusive locks can be used to prevent conflicting updates.
2. Dirty Read Problem
A Dirty Read occurs when one transaction reads data that has been modified by another transaction but has not yet been committed.
Example
- Transaction TX changes account A from $300 to $350.
- TX writes the new value but has not committed yet.
- Transaction TY reads the balance as $350.
- TX encounters an error and rolls back.
- The balance is restored to $300.
Transaction TY has already used the temporary value of $350. Since this value was never committed, the read performed by TY was a dirty read.
3. Unrepeatable Read Problem
An Unrepeatable Read occurs when a transaction reads the same data item more than once and receives different values because another transaction modified and committed that data between the reads.
Example
- Transaction TX reads account A and gets $300.
- Transaction TY updates the account balance to $400 and commits.
- TX reads account A again.
- This time, TX gets $400.
The same transaction received two different values for the same data item. This is known as an unrepeatable read.
4. Phantom Read Problem
A Phantom Read occurs when a transaction executes the same query twice but obtains a different set of rows because another transaction inserted, deleted, or modified rows that satisfy the query condition.
Example
Suppose transaction TX executes the following query:
SELECT * FROM employees WHERE salary > 50000;The query initially returns 10 employees.
Before TX executes the query again, transaction TY inserts a new employee whose salary is greater than $50,000 and commits.
When TX executes the same query again, it now returns 11 employees. The additional row is called a phantom row.
Why is Concurrency Control Needed?
Concurrency control is required to ensure that multiple transactions can execute simultaneously without compromising the correctness of the database.
Without proper concurrency control, databases may suffer from:
- Data Inconsistency: Different transactions may produce conflicting results.
- Data Integrity Problems: Concurrent updates can violate database rules and constraints.
- Incorrect Results: Transactions may read temporary or outdated values.
- Transaction Conflicts: Multiple transactions may interfere with each other’s operations.
- System Reliability Issues: Applications such as banking and e-commerce may produce incorrect results.
The primary goal of concurrency control is to maintain database consistency and isolation while allowing transactions to execute efficiently.
Challenges in Concurrent Transactions
1. Data Consistency
Concurrent transactions can cause problems such as lost updates, dirty reads, unrepeatable reads, and phantom reads.
2. Data Integrity
Transactions must follow database constraints even when several users modify related data simultaneously.
3. Isolation Levels and Performance
Database systems provide different isolation levels. Lower isolation levels generally provide better performance but may allow certain concurrency anomalies, while higher isolation levels provide stronger consistency at the cost of potentially reduced concurrency.
Common SQL isolation levels include:
- Read Uncommitted
- Read Committed
- Repeatable Read
- Serializable
4. Deadlocks
A deadlock occurs when two or more transactions wait for resources held by one another, causing them to remain blocked indefinitely unless the DBMS intervenes.
DBMSs can use techniques such as deadlock detection, prevention, avoidance, and transaction rollback to handle deadlocks.
5. Resource Contention
When many transactions compete for the same database resources, such as locks, memory, CPU, or disk operations, overall system performance can decrease.
Concurrency Control Mechanisms
DBMS uses several mechanisms to control concurrent transactions and maintain consistency.
1. Lock-Based Concurrency Control
Locks prevent transactions from accessing data in conflicting ways at the same time.
Shared Lock
A Shared Lock (S-Lock) allows a transaction to read a data item. Multiple transactions can generally hold shared locks on the same item, but a conflicting exclusive lock cannot be granted while shared locks remain.
Exclusive Lock
An Exclusive Lock (X-Lock) is required when a transaction wants to modify a data item. It prevents other transactions from obtaining conflicting locks on that item.
2. Two-Phase Locking (2PL)
Two-Phase Locking (2PL) is a locking protocol used to ensure conflict serializability.
It consists of two phases:
- Growing Phase: The transaction can acquire locks but cannot release them.
- Shrinking Phase: The transaction can release locks but cannot acquire new locks.
By following these rules, transactions can be ordered in a way that is equivalent to a serial execution with respect to conflicting operations.
3. Strict Two-Phase Locking
Strict 2PL is a stronger form of two-phase locking in which exclusive locks are held until the transaction commits or rolls back.
This helps prevent other transactions from reading or overwriting uncommitted data and simplifies recovery by reducing the possibility of cascading rollbacks.
Concurrency Control Protocols
Concurrency control protocols define rules that determine how transactions can access and modify database resources.
1. Lock-Based Protocol
Lock-based protocols use shared and exclusive locks to control access to database items. Two-phase locking is a widely used approach for achieving serializability.
2. Timestamp-Based Protocol
In Timestamp-Based Concurrency Control, every transaction receives a unique timestamp. The DBMS uses these timestamps to determine the correct order in which conflicting operations should occur.
This approach can prevent certain conflicts without relying on traditional locking mechanisms.
3. Validation-Based Protocol
Validation-Based Concurrency Control, also known as optimistic concurrency control, allows transactions to execute without acquiring locks for every operation. Before committing, the transaction is validated to determine whether its execution conflicts with other transactions.
If a conflict is detected, the transaction may be rolled back and restarted.
Concurrency Control and ACID Properties
Concurrency control plays an important role in maintaining the Isolation property of transactions and contributes to overall database correctness.
The four ACID properties are:
- Atomicity: A transaction is completed entirely or not performed at all.
- Consistency: A transaction moves the database from one valid state to another valid state.
- Isolation: Concurrent transactions should not interfere in ways that produce incorrect results.
- Durability: Once a transaction is committed, its changes remain stored even after failures.
Concurrency Control vs. Serial Execution
| Feature | Serial Execution | Concurrent Execution with Control |
|---|---|---|
| Performance | Generally lower | Generally higher |
| Resource Utilization | Less efficient | More efficient |
| Transaction Processing | One transaction at a time | Multiple transactions can overlap |
| Risk of Conflicts | Very low | Managed through concurrency control |
| Scalability | Limited | Better for multi-user systems |
Real-World Applications of Concurrency Control
Concurrency control is essential in applications where multiple users access the same data simultaneously.
- Online Banking: Preventing conflicting account updates.
- E-Commerce: Managing simultaneous purchases of limited-stock products.
- Airline Reservation Systems: Preventing multiple users from booking the same seat.
- Hospital Management Systems: Managing simultaneous access to patient and appointment records.
- Inventory Management: Keeping stock quantities accurate when multiple orders are processed.
YT:- DecodeIT
Conclusion
Concurrency Control in DBMS is essential for maintaining database consistency, integrity, and reliability in multi-user environments. It allows multiple transactions to execute concurrently while controlling conflicts between their operations.
Problems such as lost updates, dirty reads, unrepeatable reads, and phantom reads demonstrate why concurrency control is necessary.
Techniques such as locking, Two-Phase Locking, timestamp-based protocols, and validation-based protocols help DBMSs achieve safe and efficient transaction processing.
A well-designed concurrency control mechanism provides the right balance between data consistency, transaction isolation, and system performance, making it a fundamental component of modern database systems.
Keywords
DBMS Concurrency Control, Concurrency Control in DBMS, Concurrent Execution in DBMS, Lost Update Problem, Dirty Read, Unrepeatable Read, Phantom Read, Two Phase Locking, Strict 2PL, Lock Based Concurrency Control, Timestamp Based Concurrency Control, Validation Based Concurrency Control, Transaction Management, Database Transactions, SQL Isolation Levels, DBMS Transactions
