The concurrency control technique is a process of managing the simultaneous execution of transactions in a shared database. It preserves the database consistency, enforces the isolation of different transactions, and resolves the conflicts which occur due to the read-write operation of transactions.
The various concurrency control techniques are
- Locking based protocol
- Timestamp based protocol
- Optimistic protocol
- Multiversion protocol
1). Locking based protocol
This protocol uses a lock that guarantees the exclusive use of data of transactions. The lock is used to restrict different transaction which is executing at the same time.
There are 3 phases of this protocol
- Taking the lock control and accessing the data of the transaction
- Complete the transaction execution
- Release lock
There are two types of lock available in this protocol
- Shared lock: It is also represented by ‘lock-S’, and allows only to read the data from the transaction.
- Exclusive lock: It is also represented by ‘lock-X, and allows both the read and write operation to the data from the transaction.
2). Timestamp based protocol
In Timestamp based Protocol, timestamp are the Logical Counter or System Time which is used to serialize the execution of concurrent transactions. This protocol is a unique identifier that indicate the relative stating time of the transaction. It executes all the conflicting transactions in timestamp order. The confliction occurs due to read and write operations from a number of transactions running at the same time. This protocol is generated using a System clock that stores the starting time of any transaction and the Logical counter which keeps incrementing after the assignment of every new timestamp.
The timestamp value of the new transaction is always greater than the timestamp value of the old transaction. And in this method old transaction always gets the priority over the new transactions
There are two types of transaction values
- Read timestamp, which is the largest timestamp of any transaction that is executed the read operation successfully on a transaction.
- Write timestamp, which is the largest timestamp of any transaction that is executed the write operation successfully on a transaction.
For Example
Suppose we have two transactions T1 and T2.
T1 enters in system at Time 5 and T2 enters in System at time 10.
Priority will always given to T1 and then T2
3). Optimistic protocol/Validation Based Protocol
This protocol is also called validation-based protocol which copies the transaction data locally and updates the local copy of data instead of actual data in the database.
It works in three phases
- Read phase: All transaction data are read and stored in a temporary variable which is also called the local variable of that transaction, after which a write operation is performed on the temporary variable.
- Validation phase: In this phase, a test is performed to determine if the changes of the local variable are also possible in the actual database. This phase ensures the violation-free change in the execution of a transaction.
- Write phase: After the validation phase passed successfully then the changes are made in the actual database.
This protocol results in less interference during the execution of transactions and is used where transactions are conflict-free and read-only.
4). Multi-Version protocol
This protocol minimizes the delay for reading operation and maintains different versions of data items. For each writes operation performs, it creates a new version of transaction data so that whenever any transaction performs read operation to read that data then the appropriate created data version is selected by the control manager to make that read operation conflict-free and successful.