Table of Contents
Concurrency Control in DBMS 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 the data integrity of the respective Database.
Problems with Concurrent Execution Transaction
Let’s see what are the problem arise with Concurrent Execution Transaction.
- Lost Update Problems (W – W Conflict)
The problem occurs when two different database transactions perform the read/write operations on the same database items in an interleaved manner (i.e., concurrent execution) that makes the values of the items incorrect hence making the database inconsistent.
- Dirty Read Problems (W-R Conflict)
The dirty read problem occurs when one transaction updates an item of the database, and somehow the transaction fails, and before the data gets rollback, the updated database item is accessed by another transaction. There comes the Read-Write Conflict between both transactions.
- Unrepeatable Read Problem (W-R Conflict)
Also known as Inconsistent Retrievals Problem that occurs when in a transaction, two different values are read for the same database item.
Thus, for maintaining the concurrency of the database, we have the concurrency control protocols.
What is the purpose of concurrency control?
Concurrency controls prevent data integrity problems, which can arise when two update processes access the same data item at the same time. Access controls restrict updating of the database to authorized users, and controls such as passwords prevent the inadvertent or unauthorized disclosure of data from the database.
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. Therefore, these protocols are categorized as:
- Lock-Based Protocols
- Two-Phase Locking Protocol
- Timestamp-Based Protocols
- Validation-Based Protocols
Characteristics of Good Concurrency Protocol
An ideal concurrency control DBMS mechanism has the following objectives:
- Must be resilient to site and communication failures.
- It allows the parallel execution of transactions to achieve maximum concurrency.
- Its storage mechanisms and computational methods should be modest to minimize overhead.
- It must enforce some constraints on the structure of atomic actions of transactions.
