Concurrency control Protocols

To ensure that many transactions may access and edit data concurrently without causing data inconsistencies or compromising data integrity, database systems must include concurrency control protocols. Data consistency and system performance are intended to be balanced via concurrency control methods.

The many kinds of concurrency control protocols and how they operate will be covered in this article.

Lock-Based Concurrency Control

In database systems, lock-based concurrency control is a common strategy. To prevent other transactions from simultaneously accessing the same data, this protocol requires transactions to request locks on data objects. Locks come in shared and exclusive varieties.

Nevertheless, only one transaction may possess an exclusive lock, which enables the transaction to edit the data item. A shared lock permits several transactions to read the same data item. By ensuring that transactions do not clash with one another, this protocol avoids inconsistent data.

Lock-based concurrency control techniques come in two varieties −

• Two-Phase Locking (2PL) − A transaction in this protocol gets all the necessary locks before making any modifications and only releases them after the transaction is finished. By doing this, it is made sure that the present transaction and any others are not at odds.

• Strict Two-Phase Locking (S2PL) − Similar to 2PL, S2PL mandates that a transaction keep all of its locks until it commits or aborts

Pessimistic and optimistic protocols can be used to further divide lock-based concurrency control algorithms. Pessimistic protocols utilize locks to stop disputes because they anticipate that they will happen. Positive protocols allow transactions to continue without locking data items because they believe conflicts are uncommon.

Timestamp-Based Concurrency Control

Each transaction that enters the system is given a distinct timestamp via the timestamp-based concurrency control protocol. The protocol utilises these timestamps to select which transaction should be permitted to run and uses them to establish the order in which transactions are handled.

There are two different kinds of concurrency control techniques based on timestamps −

• Timestamp Ordering (TO) − The system processes transactions according to timestamp order while using this protocol. A transaction with a lower timestamp must wait if it tries to access a data item that has already been locked by a transaction with a higher timestamp.

• Thomas' Write Rule (TWR) − TWR is a TO protocol optimization. It permits the execution of a transaction if and only if the timestamp of the transaction is higher than the most recent timestamp of any transaction that wrote the data item.

Multi-Version Concurrency Control

The Multi-Version Concurrency Control (MVCC) protocol makes numerous copies of each piece of data so that various transactions can access it concurrently. Each transaction sees a copy of the data item that matches the timestamp of that transaction.

Web applications and other systems requiring high concurrency employ the MVCC protocol. Two variants of the MVCC protocol exist −

• Snapshot Isolation (SI) − Each transaction in this protocol reads a copy of the database as it was at the time of the transaction's initiation. The current transaction can continue to read the original data item even if another transaction updates it, maintaining data consistency.

• Serializable Snapshot Isolation (SSI) − As an addition to SI, SSI guarantees serializability while still enabling high concurrency. Transactions can run concurrently under the SSI protocol, and each transaction sees a database snapshot that is consistent with its timestamp.

Conclusion

In order to guarantee data consistency and avoid data inconsistencies, concurrency control techniques are essential. Since they are straightforward and efficient, lock-based protocols are frequently used, while systems that demand high concurrency utilise timestamp-based and MVCC protocols.

The requirements, workload, and performance objectives of the database system determine which concurrency control protocol should be used. Database administrators and developers must comprehend the various concurrency control protocols and how they operate in order to construct high-performance and reliable database systems.