Introduction
Concurrency is the ability of two or more transactions to use the same data simultaneously. Concurrency control in Database Management System (DBMS) is a procedure of managing simultaneous transactions without conflict.

Accessing the data simultaneously is relatively easy if all the users only read the data from the database because there is no way they can interfere.
What is concurrency control in DBMS?
Concurrency control in Database Management Systems (DBMS) is the process of managing simultaneous access to shared data by multiple transactions to ensure data consistency and integrity. It prevents conflicts such as lost updates, uncommitted data, and inconsistent reads that may occur when multiple transactions access and modify the same data concurrently. Concurrency control mechanisms include locking, timestamping, and optimistic concurrency control techniques to maintain data consistency while allowing for concurrent access and updates from multiple users or transactions.
Transaction
A transaction is the execution of a program that accesses or modifies the contents of a database. The primary operations of transactions are
Read (A): Read operations R(A) or Read(A) reads the value A from the database and stores it in a buffer in the main memory.
Write(A): Write operations W(A) or Write(A) writes the value A back to the database from the buffer that presents in the main memory.
Transaction states/ operations
The system keeps track of the state of the Transaction to prevent the system from entering into an inconsistent state, For example, in case of a failure while execution, because of software or hardware issues, we can continue from the state before failure with the help of operations such as commit and rollback.
Following are the transaction states/operations.
1. BEGIN: denotes the beginning of execution of the Transaction.
2. READ or WRITE: These specify read or write operations on the database items executed as part of a transaction.
3. END: After read-write operations are done, Transaction enters the end state. It is determined whether changes done during execution can be committed or aborted due to violation of concurrency control or to roll back to the previous stage.
4. Commit: it safely stores the changes done during transaction execution and makes them permanent. It marks the successful completion of the Transaction.
5. Rollback: if at any point in transaction operations are not executed successfully, then all changes done are reverted.
Transaction Properties
A DBMS(Database Management System) is considered an RDBMS (Relational Database management system) if it follows the given ACID transactional properties.
- A: Atomicity
- C: Consistency
- I: Isolation
- D: Durability
The SQL Server takes care of the Atomicity, Consistency, and Durability of the system. Meanwhile, the user has to care about the isolation property of the Transaction.
Atomicity
A transaction should be atomic. A transaction should either be performed as a whole or not performed at all, which means, Transaction does not partly happen. It does not leave the work incomplete.
Consistency
The database state should not be left inconsistent at the end of a transaction. It must be consistent.
Isolation
A transaction should not allow other transactions to make changes until it’s committed. This property, when applied strictly, solves the temporary update problem and makes cascading rollbacks unnecessary.
Durability
Once the database has committed a transaction, the changes are saved. These changes should be permanent and available to all other transactions later.
Concurrency
As explained in the introduction, Concurrency occurs when two or more users try to access the same data or information. DBMS concurrency is a problem because when multiple users access the data simultaneously, this can lead to inconsistent results. For example, when two users are updating the same data simultaneously, and some other users try to access that data, this will lead to invalid behavior or inconsistent results.
Recommended Topic, B+ Tree in DBMS
Concurrency Problems
Since the two main operations in a database transaction are Read and Write operations. This problem mainly arises when one user is writing and the other is reading or when both the users try to write the same data simultaneously. Following are some common concurrency problems:
- Dirty Read Problem ( W-R conflict )
- Lost Update Problem ( W-W conflict )
- Non-repeatable Read Problem ( W-R conflict )
Dirty Read Problem
This problem occurs when one Transaction updates an item of the database, and somehow that Transaction fails. Before the data gets a rollback, another transaction can access that updated database item. This situation will cause the Write-Read conflict between both transactions.
For example:
Consider the two transactions Tx and Ty in the following diagram performing read or write operations on A. Let say the given balance in account A is 650 rupees.
Time |
Tx |
Ty |
---|---|---|
t1 |
R(A) |
— |
t2 |
A=A+250 |
— |
t3 |
W(A) |
— |
t4 |
— |
R(A) |
t5 |
SERVER DOWN ROLLBACK |
— |
- At t1 time, transaction Tx will read the value of account A 650 rupees.
- At t2 time, transaction Tx adds 250 rupees to account A, which becomes 800.
- At t3 time, transaction Tx writes the updated value in account A, which is 800.
- At t4 time, transaction Tx rollbacks due to server problem, and A’s value changes back to 650 rupees(as initially).
- But the value for account A remains 800 for transaction Ty (because at time t4, Ty Reads the value of A , i.e., 800 ), which is the dirty read. Therefore it is known as the Dirty Read Problem.
Lost Update Problem
This problem occurs when two different transactions perform the read or write operations on the same database items in an interleaved manner (concurrent execution ), making the values inconsistent, resulting in invalid behavior.
For example:
Consider the two transactions Tx and Ty in the following diagram performing read or write operations on A. Let say the given balance in account A is 650 rupees.
Time |
Tx |
Ty |
---|---|---|
t1 |
R(A) |
— |
t2 |
A=A-100 |
|
t3 |
— |
R(A) |
t4 |
— |
A=A+100 |
t5 |
— |
— |
t6 |
W(A) |
— |
t7 |
W(A) |
- At t1 time, transaction Tx will read the value of account A that is 650 rupees.
- At t2 time, transaction Tx deducts 100 rupees from account A, which becomes 550 rupees(only deducted and not updated yet).
- At t3 time, transaction Ty read the value of A in account A, which is 650 only because Tx didn’t update the value yet.
- At t4 time, transaction Ty adds 100 to account A, which becomes 750 (only added but not updated yet).
- At t6 time, transaction tx writes the value of account A which becomes 550, as Ty hasn’t updated the value yet.
- At t7 time, transaction ty writes the value of account A, which will become 750 as it was added at time t4. It means the value written by Tx at time t6 is lost. Hence data becomes incorrect, and database sets to inconsistent. This is known as the Lost Update problem.
Non-repeatable Read Problem
It is also known as Inconsistent Retrievals Problem. This problem occurs when in a transaction, two different values of the same item are read from the database.
For example:
Consider the two transactions Tx and Ty in the following diagram performing read or write operations on A. Let say the given balance in account A is 650 rupees.
Time |
Tx |
Ty |
---|---|---|
t1 |
R(A) |
— |
t2 |
— |
R(A) |
t3 |
— |
A=A+250 |
t4 |
— |
W(A) |
t5 |
R(A) |
— |
- At t1 time, transaction Tx will read the value from account A, that is, 650 rupees.
- A t2 time, transaction Ty will read the value from account A, that is, 650 rupees.
- At t3 time, transaction Ty adds 250 to account A, which will become 900 rupees( only added, not updated yet).
- At t4 time, transaction Ty writes the updated value of A that is 900.
- Later at t5 time, transaction Tx reads the value of account A, which is 900.
Within the same Transaction Tx, it reads two different values of (which are 650 at time t1 and 900 at time t5). It is a non-repeatable read and therefore known as a Non-repeatable read problem.
Thus to maintain consistency in the database and avoid such problems, concurrency control is introduced to manage this issue.
Concurrency Control
It is required for controlling and managing the concurrent execution of operations in the database and thus avoiding the inconsistencies caused by them in the database. Thus for maintaining this, we have concurrency control protocols.
Concurrency Control Protocols
These protocols always ensure the ACID properties(atomicity, consistency, isolation, durability ) and serializability of the concurrent execution of the transactions. These protocols are divided into three parts that are
- Lock Based Concurrency Control Protocol
- Time Stamp Concurrency Control Protocol
- Validation Based Concurrency Control Protocol
We will learn these topics separately in their further blogs.
You can also read about the Log based recovery.