Problems & Strategies in Concurrency Control

Concurrency control is essential in database management systems to prevent problems that can arise from multiple transactions attempting to access and modify data simultaneously. Here are some common problems and the concurrency control protocols used to address them:

Problems Due to Lack of Concurrency Control

1. Dirty Read Problem:

   • Issue: Occurs when one transaction reads data that has been modified but not yet committed by another transaction. If the second transaction rolls back, the first transaction's read becomes invalid.

   • Solution: Concurrency control protocols like timestamp-based or two-phase locking prevent dirty reads by ensuring that transactions can only access committed data.

2. Lost Update Problem:

   • Issue: When two or more transactions attempt to modify the same data concurrently, one transaction's update may be overwritten by another, resulting in data inconsistency.

   • Solution: Lock-based protocols, such as two-phase locking or strict two-phase locking, prevent lost updates by granting exclusive locks for write operations and allowing only one transaction to modify data at a time.

Concurrency Control Protocols

1. Lock-Based Protocol:

   • Description: Transactions acquire locks (shared or exclusive) before accessing data. They release locks when finished.

   • Advantages: Ensures data consistency and prevents conflicts between transactions. Provides clear control over data access.

   • Disadvantages: Can lead to deadlocks if not managed properly. May reduce concurrency, as transactions may have to wait for locks.

2. Timestamp-Based Protocol:

   • Description: Each transaction is assigned a timestamp indicating its order of execution. Conflicts are resolved based on timestamps.

   • Advantages: Efficient and prevents dirty reads and lost updates. Transactions can execute concurrently based on their timestamps.

   • Disadvantages: May require a centralized timestamp authority. Timestamp assignment can be complex.

3. Two-Phase Locking Protocol:

   • Description: Transactions follow a two-phase process, acquiring locks in the growing phase and releasing them in the shrinking phase. No new locks can be acquired after releasing.

   • Advantages: Ensures serializability and prevents conflicts. Provides a clear order of lock acquisition and release.

   • Disadvantages: May lead to reduced concurrency, as transactions may hold locks for extended periods.

4. Strict Two-Phase Locking Protocol:

   • Description: Similar to two-phase locking, but transactions can release locks only when they commit, not during the execution phase.

   • Advantages: Guarantees strict serializability and prevents cascading rollbacks.

   • Disadvantages: May lead to longer lock-holding periods, potentially reducing concurrency.

In summary, concurrency control protocols, such as lock-based and timestamp-based approaches, help prevent problems like dirty reads and lost updates in a multi-user database environment. Each protocol has its advantages and disadvantages, and the choice of protocol depends on the specific requirements of the application and the desired trade-offs between concurrency and data integrity.