Concurrency control techniques are mechanisms used to ensure that database transactions are executed in a way that maintains the consistency, integrity, and isolation of the data while allowing multiple transactions to occur simultaneously.
Two common techniques for concurrency control are:
- Two-Phase Locking (2PL)
- Timestamp Ordering
1.) Two-Phase Locking (2PL)
Two-Phase Locking (2PL) is a concurrency control protocol that ensures serializability (the highest level of isolation) by dividing a transaction’s execution into two phases:
- The Growing Phase: In this phase, a transaction can acquire any number of locks (read or write locks) but cannot release any locks. It keeps acquiring locks on the data it needs to access but does not release any until it has completed its actions.
- The Shrinking Phase: Once a transaction releases its first lock, it enters the shrinking phase. In this phase, it can release locks but cannot acquire any new locks. After entering this phase, the transaction can no longer request new locks, ensuring that no further conflicts can occur.
2.) Timestamp Ordering
Timestamp Ordering is another concurrency control method that ensures transaction serializability based on the timestamps assigned to each transaction.
- Each transaction is given a unique timestamp, and this timestamp determines the order in which the transaction operations should be executed.
How Timestamp Ordering Works:
- Timestamp Assignment: Each transaction is assigned a timestamp when it starts. Transactions with earlier timestamps are given higher priority.
- Transaction Operations: During the execution of a transaction, the DBMS compares the timestamp of the transaction with the timestamps of other transactions involved in data access to ensure that operations occur in the correct order.
- Conflict Prevention: If a transaction tries to access data in a way that would violate serializability (i.e., it attempts to read or write data in a manner that contradicts the timestamp order), the system will either roll back the transaction or delay it until the conflict is resolved.
