Concurrency Control

Concurrency Control in Database Management System is a procedure of managing simultaneous operations without conflicting with each other. It ensures that Database transactions are performed concurrently and accurately to produce correct results without violating data integrity of the respective Database.


Concurrent access is quite easy if all users are just reading data. There is no way they can interfere with one another. Though for any practical Database, it would have a mix of READ and WRITE operations and hence the concurrency is a challenge.


DBMS Concurrency Control is used to address such conflicts, which mostly occur with a multi-user system. Therefore, Concurrency Control is the most important element for proper functioning of a Database Management System where two or more database transactions are executed simultaneously, which require access to the same data.


Potential problems of Concurrency

Here, are some issues which you will likely to face while using the DBMS Concurrency Control method:

  • Lost Updates occur when multiple transactions select the same row and update the row based on the value selected

  • Uncommitted dependency issues occur when the second transaction selects a row which is updated by another transaction (dirty read)

  • Non-Repeatable Read occurs when a second transaction is trying to access the same row several times and reads different data each time.

  • Incorrect Summary issue occurs when one transaction takes summary over the value of all the instances of a repeated data-item, and second transaction update few instances of that specific data-item. In that situation, the resulting summary does not reflect a correct result.


Why use Concurrency method?

Reasons for using Concurrency control method is DBMS:

  • To apply Isolation through mutual exclusion between conflicting transactions

  • To resolve read-write and write-write conflict issues

  • To preserve database consistency through constantly preserving execution obstructions

  • The system needs to control the interaction among the concurrent transactions. This control is achieved using concurrent-control schemes.

  • Concurrency control helps to ensure serializability

Example

Assume that two people who go to electronic kiosks at the same time to buy a movie ticket for the same movie and the same show time.

However, there is only one seat left in for the movie show in that particular theatre. Without concurrency control in DBMS, it is possible that both moviegoers will end up purchasing a ticket. However, concurrency control method does not allow this to happen. Both moviegoers can still access information written in the movie seating database. But concurrency control only provides a ticket to the buyer who has completed the transaction process first.


Concurrency Control Protocols

Different concurrency control protocols offer different benefits between the amount of concurrency they allow and the amount of overhead that they impose. Following are the Concurrency Control techniques in DBMS:

  • Lock-Based Protocols

  • Two Phase Locking Protocol

  • Timestamp-Based Protocols