What is in Concurrency Control?

Concurrency ****Control in databases refers to the methods and techniques used to ensure that multiple transactions can occur simultaneously without leading to data inconsistencies, corruption, or loss.

A database handles more than one transaction at the same time, and some transactions may read and update the same data. This can lead to issues like dirty reads and write-write conflicts. To manage this, databases use techniques known as concurrency control techniques.

1. Lock-Based Concurrency Control
2. Timestamp-Based Concurrency Control
3. Multiversion Concurrency Control (MVCC)

Lock Based Concurrency Control

In this technique, transactions use a locking mechanism to update or modify data in the database. This method helps the database prevent conflicts and maintain consistency

We basically use two types of locks for any database resources.

• Shared Locks (S): Allow multiple transactions to read (or acquire a shared lock on) the same resource but prevent any transaction from modifying it.
• Exclusive Locks (X): Allow a single transaction to both read and modify a resource. When a transaction holds an exclusive lock, no other transaction can acquire any type of lock (shared or exclusive) on that resource.

A shared lock is used when a transaction reads some data. During this time, the transaction acquires a shared lock, and more than one transaction can acquire a shared lock on the same database resource. However, when a transaction wants to update data, it acquires an exclusive lock. This lock is used when a transaction modifies or updates data. When one transaction acquires an exclusive lock, other transactions cannot acquire either a shared or an exclusive lock on that resource.

We use two type of method into lock base concurrency control

• Two-Phase Locking (2PL): A protocol where transactions acquire all the necessary locks before releasing any of them. It consists of two phases:
  • Growing Phase: The transaction acquires locks but does not release any.
  • Shrinking Phase: The transaction releases locks and cannot acquire any new locks.
• Strict Two-Phase Locking (Strict 2PL): A stricter form of 2PL where transactions must hold all their locks until they commit or abort.

We have to consider trade-offs between concurrency and locking. In lock-based concurrency control, a transaction may lock an entire page or table of the resource. While this approach reduces the number of locks the database has to manage, it also results in lower concurrency. This type of lock is called Coarse-Grained Locking. Alternatively, we can lock only the specific row that the transaction is used. Although this approach requires the database to manage more locks, it allows for higher concurrency, and is referred to as Fine-Grained Locking. Depending on our use case, we should choose the appropriate technique.