Concurrency control

Concurrency control

It is the process of managing s execution of transactions (such as queries, updates, inserts, deletes and so on) to run simultaneously in a multiprocessing database system without having them interfere with one another.

In this multiprogramming environment where multiple transactions may be executed at the same time so it’s extremely necessary to manage the concurrency of transactions.

We have concurrency control protocols to make sure atomicity, isolation, and serializability of concurrent transactions.

Concurrency control protocols are divided into two categories −

• Lock based protocols
• Time stamp based protocols

Lock-based Protocols

Lock-based protocols use a mechanism by which any transaction cannot read or write information until it acquires an appropriate lock on it. They synchronize the access by concurrent transactions to the database items. It is required in this protocol that each of the data items must be accessed in a reciprocally exclusive manner.

Lock-based protocols managed the order between the conflicting pairs among transactions at the time of execution

There are two common locks which are used and some terminology followed in this protocol.

• Shared Lock (S): It is also known as Read-only lock. It can be shared between the transactions because while holding shared lock the transaction does not have the permission to update data on the data item.

• Exclusive Lock (X): Data item can be both read as well as written. This is Exclusive and cannot be held at the same time on the same data item.

There are four types of lock based protocols

1. Simplistic Lock Protocol
2. Pre-claiming Lock Protocol
3. Two-Phase Locking 2PL
4. Strict Two-Phase Locking

Simplistic Lock Protocol-: This protocols allow transactions to obtain a lock on each object before a ‘write’ operation is performed. Transactions may unlock the data item after completing the ‘write’ operation.

Pre-claiming Lock Protocol-: this 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 finished. If all the locks are not granted, the transaction rolls back and waits until all the locks are granted.

Two-Phase Locking 2PL-: This two phase locking protocol divides the execution phase of a transaction into the three parts. In the first part, when the transaction starts executing, it request permission for the locks it requires. The second part is where the transaction attain 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.

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

Timestamp-based Protocols

The most commonly used is the timestamp based protocol which start working as soon as a transaction is created. This protocol uses either system time or logical counter as a timestamp.

Problems of Concurrency Control

While concurrent transactions are executed in an uncontrolled manner problems can occur. 
The concurrency control has the following three main problems:

• Lost updates.
• Dirty read (or uncommitted data).
• Unrepeatable read (or inconsistent retrievals).

Lost update-: The lost update problem occurs when two transactions T1 and T2 that access the same database and the value of some database item are incorrect and overwritten. 

At time t1, Transactions- X reads value of T.
At time t2, Transactions- Y reads value of T.
At time t3, Transactions- X writes value of T on the basis of the value seen at time t1.
At time t4, Transactions- Y writes value of T on the basis of the value seen at time t2.
So, update of Transactions- X is lost at time t4, because Transactions- Y overwrites it without looking at its current value.
Such type of problem is referred as the Update Lost Problem, as update made by one transaction is lost here.

Dirty read (or uncommitted data)-: A dirty read problem occurs when one transaction updates an item in database and then the transaction fails for some reason.

Inconsistent Retrievals Problem-: The problem is that the transaction might read some data before they are changed and other data after they are changed, this cause Inconsistent Retrievals