There are three concurrency control techniques which are as follows:

1. Locking method

   Database systems equipped with lock-based protocols use a mechanism by which any transaction cannot read or write data until it acquires an appropriate lock on it. Locks are of two kinds −

   • Binary Locks − A lock on a data item can be in two states; it is either locked or unlocked.

   • Shared/exclusive − This type of locking mechanism differentiates the locks based on their uses. If a lock is acquired on a data item to perform a write operation, it is an exclusive lock. Allowing more than one transaction to write on the same data item would lead the database into an inconsistent state. Read locks are shared because no data value is being changed.

   There are four types of lock protocols available −

   i. Simplistic Lock Protocol

   Simplistic lock-based protocols allow transactions to obtain a lock on every object before a 'write' operation is performed. Transactions may unlock the data item after completing the ‘write’ operation.

   ii. Pre-claiming Lock Protocol

   Pre-claiming protocols evaluate their operations and create a list of data items on which they need locks. Before initiating an execution, the transaction requests the system for all the locks it needs beforehand. If all the locks are granted, the transaction executes and releases all the locks when all its operations are over. If all the locks are not granted, the transaction rolls back and waits until all the locks are granted.

iii. Two-Phase Locking 2PL

This locking protocol divides the execution phase of a transaction into three parts. In the first part, when the transaction starts executing, it seeks permission for the locks it requires. The second part is where the transaction acquires all the locks. As soon as the transaction releases its first lock, the third phase starts. In this phase, the transaction cannot demand any new locks; it only releases the acquired locks.

Two-phase locking has two phases, one is growing, where all the locks are being acquired by the transaction; and the second phase is shrinking, where the locks held by the transaction are being released.To claim an exclusive (write) lock, a transaction must first acquire a shared (read) lock and then upgrade it to an exclusive lock.

iv. Strict Two-Phase Locking

The first phase of Strict-2PL is same as 2PL. After acquiring all the locks in the first phase, the transaction continues to execute normally. But in contrast to 2PL, Strict-2PL does not release a lock after using it. Strict-2PL holds all the locks until the commit point and releases all the locks at a time.

Strict-2PL does not have cascading abort as 2PL does.

1. Timestamp method i. The most commonly used concurrency protocol is the timestamp based protocol. This protocol uses either system time or logical counter as a timestamp.

   ii. Lock-based protocols manage the order between the conflicting pairs among transactions at the time of execution, whereas timestamp-based protocols start working as soon as a transaction is created.

   iii. Every transaction has a timestamp associated with it, and the ordering is determined by the age of the transaction. A transaction created at 0002 clock time would be older than all other transactions that come after it. For example, any transaction 'y' entering the system at 0004 is two seconds younger and the priority would be given to the older one.

   iv. In addition, every data item is given the latest read and write-timestamp. This lets the system know when the last ‘read and write’ operation was performed on the data item.

2. Optimistic method

   i. In optimistic concurrency control techniques, it is assumed that the transactions do not directly update the data items in the database until they finish their execution.

   ii. Instead, each transaction maintains local copies of the data items it requires and updates them during execution.

   iii. All the data items in the database are updated at the end of the transaction execution. In this technique, each transaction Ti proceeds through three phases, namely, read phase, validation phase, and write phase, depending on whether it is a read-only or an update transaction. The execution of transaction Ti begins with read phase, and the three timestamp values are associated with it during its lifetime. The three phases are explained here.

3. Read phase: At the start of this phase, transaction Ti is associated with a timestamp Start (Ti). Ti reads the values of data items from the database and these values are then stored in the temporary local copies of the data items kept in the workspace of Ti. All modifications are performed on these temporary local copies of the data items without updating the actual data items of the database.

4. Validation phase: At the start of this phase, transaction Ti is associated with a timestamp Validation (Ti). The system performs a validation test when Ti decides to commit. This validation test is performed to determine whether the modifications made to the temporary local copies can be copied to the database. In addition, it determines whether there is a possibility of Ti to conflict with any other concurrently executing transaction. In case any conflict exists, Ti is rolled back, its workspace is cleared and Ti is restarted.

5. Write phase: In this phase, the system copies the modifications made by Ti in its workspace to the database only if it succeeds in the validation phase. At the end of this phase, Ti is associated with a timestamp Finish (Ti).