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.
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.
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.
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.
- 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.
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:
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 –
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]
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.
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.
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.