Concurrency Control

 The concurrency control concept comes under the Transaction in the database management system (DBMS). It is a procedure in DBMS which helps us control two simultaneous processes to execute without conflicts among every other, these conflicts occur in multi-user systems.

Concurrency explains executing multiple transactions at a time. It is required to increase time efficiency. If many transactions try to access the same data, then irregularity arises. Concurrency control is required to maintain consistent data.

For example, if we take ATMs and do not use concurrency, multiple persons cannot draw money at a time in different places. Hence, we need concurrency.

Advantages

The advantages of concurrency control are: –

• Waiting time will be decreased.
• Response time will gradually decrease.
• It increases Resource Utilization.
• System accuracy & Efficiency is increased.

Control Concurrency

The simultaneous execution of transactions over shared databases can create several data integrity and consistency problems.

For example, if too many people are logging in to the ATMs, serial updates and synchronization in the bank servers should manifest whenever the transaction is done, if not it gives wrong information and wrong data in the database.

Main problems with using Concurrency

The problems which arise while using concurrency are as follows −

• Updates will be lost – If a single transaction does some changes, and another transaction removes that change. One transaction empties the updates of another transaction.

For example:

Consider the below diagram in which two transactions TX and TY, are performed on the same account A where the balance of account A is $300.

Figure 1 – DBMS Concurrency

• At time t1, transaction TX reads the value of account A, i.e., $300 (only read).
• At time t2, transaction TX deducts $50 from account A which becomes $250 (only deducted and not updated/written).
• Alternately, at time t3, transaction TY reads the value of account A that will be $300 only because TX didn’t update the value yet.
• At time t4, transaction TY adds $100 to account A which becomes $400 (only added but not updated/written).
• At time t6, transaction TX writes the value of account A that will be updated as $250 only, as TY didn’t update the value yet.
• Similarly, at time t7, transaction TY writes the values of account A, so it will write as done at time t4 which will be $400. Then the value written by TX is lost, i.e., $250 is lost.

As a result, data becomes incorrect, and database sets to inconsistent.

•  Dirty read problem – The variable which updated in one transaction, at the same time another transaction has started and deleted the value of the variable there the variable is not getting updated or committed that has been done on the first transaction this gives us false values or the previous values of the variables this is a major problem.

For example:

Consider two transactions TX and TY in the below diagram executing read/write operations on account A where the available balance in account A is $300:

Figure 2 – Concurrency Server Problem

At time t1, transaction TX reads the value of account A, i.e., $300.

At time t2, transaction TX adds $50 to account A which becomes $350.

At time t3, transaction TX writes the newly refurbished value in account A, i.e., $350.

Then at time t4, transaction TY reads account A which will be read as $350.

Then at time t5, transaction TX rollbacks because of a server problem, and the value changes back to $300 (as initially).

But the value for account A remains $350 for transaction TY as committed, which is the dirty read and therefore known as the Dirty Read Problem.

Inconsistent retrievals − One transaction is updating multiple different variables, another transaction is in the process to update those variables, and the problem that occurs is the inconsistency of the same variable in various instances.

Concurrency control techniques

The concurrency control techniques are as follows –

Locking

Lock guarantees exclusive use of data items to a current transaction. It first gains the data items by acquiring a lock, after completion of the transaction it releases the lock.

Types of Locks

The variety of locks is as follows: –

• Shared Lock [Transaction can read only the data item values]
• Exclusive Lock [Used for both reading and writing data item values]

Time Stamping

The timestamp is a unique identifier created by DBMS that indicates the relative starting at the time of a transaction. Whatever transaction we are doing stores the starting time of the transaction and denotes a specific time.

This can be created using a system clock or logical counter. This can be implemented whenever a transaction is started. Here, the logical counter in addition after a new timestamp has been assigned.

Optimistic

It is based on the belief that conflict is rare, and it is more efficient to allow transactions to proceed without implementing delays to ensure serializability.

Summary

You are now familiar with concepts of transaction management and concurrency. The concurrency control management of database employs various techniques that ensures that database access smooth and efficient. The concurrency control also makes sure that the database is always in a consistent state.

https://notesformsc.org/concurrency-control/