DBMS Concurrency Control

Concurrent Execution in DBMS

In a multi-user system, multiple users can access and use the same database at one time, which is known as the concurrent execution of the database.
It means that the same database is executed simultaneously on a multi-user system by different users.
While working on the database transactions, there occurs the requirement of using the database by multiple users for performing different operations, and in that case, concurrent execution of the database is performed.
The thing is that the simultaneous execution that is performed should be done in an interleaved manner, and no operation should affect the other executing operations, thus maintaining the consistency of the database.
Thus, on making the concurrent execution of the transaction operations, there occur several challenging problems that need to be solved.

The problem occurs when two different database transactions perform the read/write operations on the same database items in an interleaved manner (i.e., concurrent execution) that makes the values of the items incorrect hence making the database inconsistent.

The dirty read problem occurs when one transaction updates an item of the database, and somehow the transaction fails, and before the data gets rollback, the updated database item is accessed by another transaction.
There comes the Read-Write Conflict between both transactions.

Also known as Inconsistent Retrievals Problem that occurs when in a transaction, two different values are read for the same database item.

Concurrency Control is the working concept that is required for controlling and managing the concurrent execution of database operations and thus avoiding the inconsistencies in the database.
Thus, for maintaining the concurrency of the database, we have the concurrency control protocols.

The concurrency control protocols ensure the atomicity, consistency, isolation, durability and serializability of the concurrent execution of the database transactions.
Therefore, these protocols are categorized as:
Lock Based Concurrency Control Protocol
Time Stamp Concurrency Control Protocol
Validation Based Concurrency Control Protocol

In this type of protocol, any transaction cannot read or write data until it acquires an appropriate lock on it.
There are two types of lock:
Shared lock:
- It is also known as a Read-only lock. In a shared lock, the data item can only read by the transaction.
- It can be shared between the transactions because when the transaction holds a lock, then it can't update the data on the data item.
Exclusive lock:
- In the exclusive lock, the data item can be both reads as well as written by the transaction.
- This lock is exclusive, and in this lock, multiple transactions do not modify the same data simultaneously.

The Timestamp Ordering Protocol is used to order the transactions based on their Timestamps.
The order of transaction is nothing but the ascending order of the transaction creation.
The priority of the older transaction is higher that's why it executes first.
To determine the timestamp of the transaction, this protocol uses system time or logical counter.
The lock-based protocol is used to manage the order between conflicting pairs among transactions at the execution time. But Timestamp based protocols start working as soon as a transaction is created.
Let's assume there are two transactions T1 and T2. Suppose the transaction T1 has entered the system at 007 times and transaction T2 has entered the system at 009 times. T1 has the higher priority, so it executes first as it is entered the system first.
The timestamp ordering protocol also maintains the timestamp of last 'read' and 'write' operation on a data.

Validation phase is also known as optimistic concurrency control technique.
In the validation based protocol, the transaction is executed in the following three phases:
Read phase:
- In this phase, the transaction T is read and executed.
- It is used to read the value of various data items and stores them in temporary local variables.
- It can perform all the write operations on temporary variables without an update to the actual database.
Validation phase:
- In this phase, the temporary variable value will be validated against the actual data to see if it violates the serializability.
Write phase:
- If the validation of the transaction is validated, then the temporary results are written to the database or system otherwise the transaction is rolled back.