Concurrency control is a fundamental DBMS concept that is useful in terms of addressing a multi-user system. When applied in a standard DBMS, concurrency control will help to coordinate simultaneous transactions and also help preserve data integrity to a large extent. Concurrency is all about controlling multi-user access to a database.
To better understand the concurrency controlconcept, you may consider two travelers going to electronic kiosks simultaneously by train ticketsfor the same train to the same destination. Consider if there is only one seat left to be reserved. Without the concept of concurrency control, it is possible that both those who are on to booking may get the ticket for the same seat. However, concurrency control will not let this happen to the database. Both those who attempt to book will be able to access the seating database. Still, the approach of concurrency control will preserve the data accuracy and let one traveler only purchase the available seat.
Concurrency control will also help to illustrate the relevance of addressing the same issue in multi-user databases. It is possible that one may run into problems with inaccurate data, which will result in multiple simultaneoustransactions with overwriting. Let us explore such possibilities in detail as below.
Locking strategies in concurrency control
There are various strategies adopted in concurrency control, which we may explore further.
- Pessimistic Locking: it is a concurrency control strategy that keeps an entity in the database locked for the whole time as it exists in the database’s memory. This will restrict the users from altering any data which is locked. There are two primary types of concurrency locks under pessimistic locking as write lock and read lock.
- Using the write lock, the lock holder may be prevented from reading, updating, and deleting the entity. Using read lock, the other users may be able to read the entity, but no one can delete or update the file except for the lock holder.
- Optimistic lock: this is another strategy used in terms of simultaneous transactions or collisions, which are expected infrequently. In contrast to pessimistic locking, the option of optimistic locking can be used to prevent any collision. It primarily aims to detect any collisions and will help resolve any chance of its occurrence.
- Finally, pessimistic locking will guarantee that the database changes are made safer. This becomes less viable when the number of simultaneous users or the entities in a transaction is increased due to the potential need to wait for the lock to release.
Among these locks, optimistic locking will help alleviate the problem of waiting for the locks to release. Still, users can have the potential to experience some collisions while they are attempting to update the databases. For concurrency control-related challenges, you may contact RemoteDBA.com experts for quick support.
Lock related problems
In the case of using locks, there are two types of problems used to occur. The first among these is a deadlock. It refers to a situation in which two or more simultaneous processes are running, each of which is waiting for another one to release some resource, or sometimes it may be more than two processes waiting for the same. It creates a circular chain. In the case of multiprocessing, deadlock is a very common problem while sharing exclusive resources. Some advanced computing systems, which are supposed to handle situations like real-time trading, are equipped with a hard lock or hardware lock, which will ensure exclusive access to the processes and force serialization.
Another major problem regarding locking is livelock, which occurs in the case of resource starvation. Livelock is also somewhat similar to the deadlock except that the process states constantly change regarding one another and never progressing. Say, for example, if the system keeps on selecting the same transaction to rollback, and the transaction will not ever finish executing. A livelock also comes about while a system decides which transaction to get a lock-in and wait for during a conflict situation. Livelock problem is similar to when many cars arrive at a four-way junction and not sure which to proceed with first. If someone wants to make a move, but at the same time, others creeping into the intersection, being afraid of someone else may come and hit them, then livelock will occur.
Concurrency control with basic time stamping
This concurrency control measure will help eliminate the deadlock scenario. Basic time stamping may not use any locks to handle concurrency, so there is no chance of a deadlock. In this operating mode, a unique timestamp will be assigned to each of the transactions, which shows when the action is started. This will let age to be assigned to the transaction and the order to be maintained. Data items will have read and write timestamps, which are updated each time they are read or written.
There is also a chance of problems arising while a transaction will try to read the data item written by a less aged transaction. It is known as late read, in which case the corresponding data item may have changed since the start time of the initial transaction, and so the solution here is to roll back the timestamp and stick a new one. Another issue is when the transaction tries to write data itemsthat will be read by a later transaction. This is known as the late write. In this case, the data item is read by another transaction from the transaction’s start time, which alerts it. The solution here is the same as in the case of the late read. The timestamp is rolled back to acquire a new one.
Sticking to the basic time stamping method will serialize the transactions in chronological order. In the case of huge databases with a high number of transactions, time-stamping may not be practical. In such a database, there may be the need to dedicate a huge storage space for storing the timestamps itself.
So, here we have reviewed some easy measures for concurrency control. When it comes to enterprise database administration on the cloud, there are many new methods and solutions offered by the vendors you may explore.
