Concurrency Control in DBMS
Subject: Computer Science Fundamentals · Language: DBMS · Level: beginner · 10 min read
Learn concurrency control in DBMS with transaction problems, schedules, locks, deadlock, timestamp ordering, and isolation levels in a beginner-friendly way.
Concurrency means multiple transactions are running at the same time. In a real database system, many users may read, update, book, pay, search, or modify records together. Concurrency control is the DBMS mechanism that keeps these simultaneous operations safe. Concurrency is useful because it improves performance. If only one transaction could run at a time, a busy system would become very slow. But if transactions run together without control, they may create incorrect data.
Concurrency control tries to get both benefits: many transactions can run at the same time, but the final database result should remain correct.
Why Concurrency Control Is Needed
Imagine two users trying to book the last available seat. If both transactions read that one seat is available and both update it as booked, the same seat may be sold twice. This is a concurrency problem. Another example is wallet balance. If two payments update the same wallet balance at the same time, one update may overwrite the other. The final balance may become wrong even though both payments looked successful.
Concurrency control prevents these situations by controlling the order, visibility, and timing of transaction operations.
Simple Concurrency Example
This example shows two transactions updating the same account balance.
```text
Initial balance = 1000
Transaction T1 reads balance = 1000
Transaction T2 reads balance = 1000
T1 adds 500 and writes 1500
T2 subtracts 200 and writes 800 Final balance becomes 800, but the correct balance should be 1300.
``` The problem happens because T2 used the old value 1000 instead of the value after T1's update. One transaction's update is lost. The DBMS must make sure such conflicting operations are handled in a safe order.
Important Terms
Before learning concurrency problems, students should understand a few basic terms.
1. Transaction: A unit of database work.
2. Schedule: The order in which operations of multiple transactions are executed.
3. Serial schedule: A schedule where one transaction finishes before the next starts.
4. Concurrent schedule: A schedule where operations of multiple transactions are interleaved.
5. Conflict: A situation where operations from different transactions access the same data item and at least one operation is a write.
A serial schedule is easy to keep correct, but it may be slow. A concurrent schedule is faster, but it needs control to remain correct.
Schedule Example
A schedule shows how operations of transactions are arranged.
```text
Serial schedule:
T1: Read A, Write A, Commit
T2: Read A, Write A, Commit
Concurrent schedule:
T1: Read A
T2: Read A
T1: Write A
T2: Write A
``` The concurrent schedule may be faster, but it can be unsafe if both transactions update the same data without proper control. The goal of concurrency control is to allow useful concurrency while avoiding wrong results.
Common Concurrency Problems
The main concurrency problems are listed below.
1. Lost update problem: One transaction's update is overwritten by another transaction.
2. Dirty read problem: A transaction reads data written by another uncommitted transaction.
3. Non-repeatable read problem: A transaction reads the same row twice and gets different values.
4. Phantom read problem: A transaction repeats a query and sees new rows inserted by another transaction.
5. Incorrect summary problem: A transaction calculates a summary while another transaction is changing the same data.
These problems are important for interviews because they explain why databases need locks, isolation levels, and concurrency control protocols.
Lost Update Problem
A lost update happens when two transactions read the same value, update it separately, and the later write overwrites the earlier write. For example, if T1 adds 500 and T2 subtracts 200 from the same balance, both should affect the final answer. If the final value reflects only T2's change, T1's update is lost. Locks or proper isolation can prevent this by making one transaction wait while another transaction updates the same data item.
Dirty Read Problem
A dirty read happens when one transaction reads data that was changed by another transaction but not committed yet. Suppose T1 updates a student's marks from 70 to 90 but has not committed. T2 reads 90 and prepares a report. If T1 later rolls back, the real marks return to 70, but T2 already used the wrong value.
Dirty reads are dangerous because uncommitted data may disappear. Isolation levels help control whether such reads are allowed.
Non-Repeatable Read
A non-repeatable read happens when a transaction reads the same row twice and gets different values because another committed transaction changed that row between the two reads. For example, T1 reads product price as 500. T2 updates the price to 550 and commits. When T1 reads the same product again, it sees 550.
This may be acceptable in some systems, but it is unsafe in reports or calculations that require a stable view of data.
Phantom Read
A phantom read happens when a transaction repeats a query and finds extra rows or missing rows because another transaction inserted or deleted records. For example, T1 counts students in CSE and gets 40. T2 inserts a new CSE student and commits. When T1 repeats the same count, it gets 41.
The new row is called a phantom row because it appears between two executions of the same query.
Locks in DBMS
A lock is a control placed on a data item so that transactions do not access it in unsafe ways. Locks are one of the most common ways to manage concurrency. When a transaction wants to read or write data, the DBMS may first check whether the data is locked. If another transaction has an incompatible lock, the transaction may need to wait.
Locks help protect data, but they can also reduce concurrency if used too strictly. The DBMS must balance safety and performance.
Types of Locks
The common lock types are listed below.
1. Shared lock: Allows multiple transactions to read the same data item.
2. Exclusive lock: Allows one transaction to write a data item and blocks other conflicting access.
3. Binary lock: A simple locked or unlocked state.
4. Intention lock: Used in multi-level locking to show that lower-level items may be locked.
5. Update lock: Used by some systems to reduce deadlock during read-before-write operations.
For beginners, shared and exclusive locks are most important. Shared means read access can be shared, while exclusive means write access must be protected.
Shared Lock and Exclusive Lock
A shared lock is used for reading. Many transactions can hold shared locks on the same data because reading does not change the value. An exclusive lock is used for writing. If one transaction is updating a row, other transactions should not update the same row at the same time. They may also be blocked from reading depending on the isolation rules.
This simple rule protects the database from lost updates and unsafe reads.
Two-Phase Locking
Two-phase locking, also called 2PL, is a locking protocol that helps produce correct schedules.
1. Growing phase: The transaction can acquire locks but cannot release any lock.
2. Shrinking phase: The transaction can release locks but cannot acquire new locks.
The idea is simple: first collect the locks needed for work, then release them after the work moves toward completion. This structure helps maintain serializability. 2PL can still lead to deadlock, so DBMS systems may need deadlock handling along with locking.
Deadlock
A deadlock happens when two or more transactions wait for each other forever. Each transaction holds a resource that another transaction needs.
```text
T1 holds lock on A and waits for B
T2 holds lock on B and waits for A
Both transactions wait forever unless the DBMS handles the deadlock.
``` Deadlocks can happen in systems that use locks. The DBMS must detect or prevent them so transactions do not remain stuck.
Deadlock Handling
Deadlock can be handled in different ways.
1. Deadlock prevention: Rules are used so deadlock cannot occur.
2. Deadlock detection: The DBMS detects deadlock after it happens and aborts one transaction.
3. Deadlock avoidance: The DBMS checks whether granting a lock may lead to unsafe state.
4. Timeout: A transaction waiting too long may be rolled back.
In practice, many systems detect deadlock and abort one transaction so the others can continue. The aborted transaction may be retried later.
Timestamp Ordering
Timestamp ordering is another concurrency control method. Each transaction receives a timestamp when it starts. Older transactions get smaller timestamps, and newer transactions get larger timestamps. The DBMS uses timestamps to decide the valid order of conflicting operations. If an operation would break the timestamp order, it may be rejected or rolled back.
Timestamp ordering avoids some locking problems, but it may cause transactions to restart if conflicts occur.
Isolation Levels
Isolation levels define how much one transaction is separated from other transactions. Higher isolation gives stronger correctness but may reduce concurrency.
1. Read Uncommitted: Allows dirty reads and gives the lowest isolation.
2. Read Committed: Prevents dirty reads but may allow non-repeatable reads.
3. Repeatable Read: Prevents dirty and non-repeatable reads, but may allow phantom reads in some systems.
4. Serializable: Strongest isolation; transactions behave as if they ran one by one.
Serializable is safest, but it can be slower because it limits concurrency more strongly. Real systems choose isolation level based on correctness needs and performance needs.
Concurrency Control Methods Summary
This table summarizes important concurrency control methods.
| Method | Main Idea | Useful For |
| --- | --- | --- |
| Locking | Controls read/write access using locks | Preventing unsafe simultaneous updates |
| Two-Phase Locking | Uses growing and shrinking lock phases | Maintaining serializable schedules |
| Timestamp Ordering | Orders operations using transaction timestamps | Managing conflicts without normal locks |
| Isolation Levels | Controls visibility between transactions | Balancing correctness and performance |
| Deadlock Handling | Detects, prevents, or resolves waiting cycles | Keeping transactions from getting stuck |
A student should not memorize these as isolated terms. They all solve the same bigger problem: multiple transactions need to work together without damaging data.
Important Points to Remember
Concurrency control allows many transactions to run at the same time while keeping the database correct. Common concurrency problems include lost update, dirty read, non-repeatable read, phantom read, and incorrect summary. These problems happen when transactions interfere with each other. Locks, two-phase locking, timestamp ordering, isolation levels, and deadlock handling are used to control concurrent transactions safely.