In DBMS, concurrency control is the process of simultaneous operations handling without conflicting one another. It is quite easy to do concurrent access if the users are reading the data from the DB. In this case, there is no such possibility that one transaction may interfere with another one. However, in the case of a practical database management scenario, it is a fine mix of reading and writes transactions, and concurrency becomes a real challenge.
However, effective modes of concurrency control are used to handle the conflict scenarios in a multi-user DBMS. Running it efficiently will ensure that all types of data transactions are concurrently run without damaging data integrity. In successful database administration, concurrency control is considered to be a crucial element to ensure the proper functioning of the system in which two or more database transactions need access to the same data and execute the requests simultaneously.
Let us now have an overview of some common issues faced in Concurrency Control:
- Lost Updates may occur while more than one transaction may select the same row in the table to update it based on the selected value.
- Dependency issues may occur while a second transaction may select the row, which is updated by another transaction, known as dirty read.
- Non-Repeatable Read can occur if a transaction tries to access the row multiple times, and each time returns different data.
- Incorrect Summary may occur if one transaction retakes the summary the value of other instances of the repeated data-item, and a second transaction updates a few other cases of that same dataitem. So, the result may not be correct.
Usage of Concurrency method
Considering the above challenges in concurrency control, usage of Concurrency Method may provide the below benefits:
- Help to apply Isolation through the mutual exclusion among conflicting simultaneous transactions.
- Resolve the write-read or read-write conflicts in transactions.
- Preserve database consistency among any obstructions in execution.
- Helps to ensure the serializability
Concurrency method is meant to control the interactionsamong concurrent transactions. It is achieved at best by using the concurrent-control schemes.
Protocols for concurrency control
Various protocols in concurrency control offer numerous benefits between the degree of concurrency each allows and the overhead it imposes. Some top concurrency control protocols followed are:
- Lock based
- Two Phase
- Timestamp Based
As described at RemoteDBA let us dig into each of these protocols in more detail:
Lock-based concurrency control protocols
Lock is a data variable that is associated with the given data item. Lock signifies the concurrent transactions to be performed. Locks can synchronize database access through concurrent transactions. The lock requests are ideally made through concurrency-control manager, and transactions will execute only when the request for a lock is approved. There are a few subsidiary locking protocols as below:
- Binary lock: The binary locks on given data items for locked or unlocked states.
- Shared lock: This mechanism helps to separate locks based on usage. If the lock is acquired on a given data item to perform a write operation, this is known as an exclusive lock. A shared lock is also known as the Read-only lock. In this, data items can be shared between the transactions. It is because you will not have permission for data updates on the given data item.
- Exclusive lock: In this type of lock, a given data item can be read or written. However, this runs exclusively and cannot be held concurrently on the same set of data items. Exclusive lock or X-lock is requested with lock-x instruction. The transactions may unlock a data item after finishing the ‘write’ operations.
- Simplistic lock: This is a protocol that lets the database transactions put a lock on each object before the operation. These transactions may unlock the data after the writing operation is finished.
- Pre-claiming lock: It helps assess the operations and prepare a list of data items required to initiate the execution. In such a situation, when all these locks are granted, the transaction is executed. All the locks are released after the transaction when operations are finished.
Two-Phase Locking Protocol or (2PL)
2PL refers to the protocol in which the transaction should first acquire a lock after one of the locks are released. This logic divides the execution into 3 parts as below:
- Transaction begins to execute, which asks for permission for the locks.
- Transaction obtains the requested locks, and the transaction releases the first locks.
- In a third phase, the transaction may not demand new locks but can only release the locks acquires.
In 2PL, the protocol will let each transaction either make a lock or unlock in two phases as a growing phase (obtaining locks) and shrinking phase (releasing locks). Divisions and 2PL are:
- Centralized 2PL
- Strict 2PL
- Distributed 2PL
- Primary copy 2PL
This is an algorithm that uses a timestamp for serialized execution of the transactions. It ensures that each conflicting read and write operations are run in a timestamp sequence. This protocol uses the system time by default as the timestamp. In this approach, the older transactions are always given priority.
While lock-based protocols may help to manage the transaction order of conflicting transactions, the timestamp protocol will handle the conflicts at the initiation of the operation itself.
On any protocol being used, an ideal concurrency control DBMS protocol should be some ideal characteristics. The most important thing is that it should be resilient to communication failures. Proper concurrency also should allow the execution of transactions parallelly and also should enforce some restrictions on the atomic action structure of the transactions. The computational methodologies and storage mechanisms of the concurrency protocol should also be of minimum overhead.
As we have seen above, concurrency control is a critical procedure in database management to handle concurrent operations without mutual conflicts. In case of a lack of concurrency, there could be issues like dirty reads, lost updates, incorrect summary, etc. The concurrency control locks could be either exclusive (X) or shared (S). It is one big challenge to the DBA to ensure that a proper concurrency control protocol is there to ensure appropriate transactional behaviors of the given database.