DBMS Concurrency Control

DBMS Concurrency Control

Concurrency control is one of the most crucial aspects of database management systems (DBMS). It ensures that when multiple users access and manipulate a database at the same time, the operations remain consistent, reliable, and free from conflicts.

But before diving deep into concurrency control, let us first understand what concurrent execution means.

Machine Learning Tutorial:–Click Here
Data Science Tutorial:-Click Here
Complete Advance AI topics:- CLICK HERE
Deep Learning Tutorial:- Click Here

Concurrent Execution in DBMS

In a multi-user environment, multiple users may access the same database simultaneously. This is known as concurrent execution.

For example, consider an airline reservation system where thousands of users are booking or canceling tickets at the same time. Each user’s interaction with the database is executed through a transaction. Since these transactions often run concurrently without the users knowing about each other, they may interfere and cause inconsistencies if not managed properly.

A naïve approach to avoid these issues would be to run transactions sequentially (one after another). While this ensures correctness, it drastically reduces system performance. Fortunately, DBMS supports concurrent execution by leveraging CPU and I/O overlap—similar to multiprogramming.

Benefits of concurrent execution include:

• Improved throughput: More transactions complete in less time.
• Better CPU utilization: While one transaction is waiting for I/O, another can use the processor.
• Reduced waiting times for short transactions, since they don’t have to wait for longer ones.

Problems with Concurrent Execution

Concurrency brings efficiency, but it also introduces several challenges. The two primary operations in transactions are READ and WRITE, and when performed in an uncontrolled manner, they can lead to inconsistencies.

1. Lost Update Problem (W–W Conflict)

Occurs when two transactions update the same data item simultaneously, and one update overwrites the other.

Example:

• Transaction TX deducts $50 from account A (balance $300 → $250).
• Before TX updates the value, Transaction TY reads the balance as $300 and adds $100 ($300 → $400).
• TX then writes $250, followed by TY writing $400.
• The deduction by TX is lost, leaving the database inconsistent.

Solution: Ensure that only one transaction modifies a data item at a time, often using locks.

2. Dirty Read Problem (W–R Conflict)

Occurs when a transaction reads uncommitted data from another transaction that later fails or rolls back.

Example:

• TX updates account A from $300 → $350 and writes the value.
• TY reads this $350 before TX commits.
• TX later rolls back, restoring the value to $300.
• But TY has already used the incorrect value ($350).

This leads to incorrect computations, called a dirty read.

3. Unrepeatable Read Problem (W–R Conflict)

Occurs when the same transaction reads the same item twice and gets different results due to another transaction’s update in between.

Example:

• TX reads account A = $300.
• TY updates the same account to $400 and commits.
• TX reads again and gets $400.

This inconsistency within a single transaction is called an unrepeatable read.

Why is Concurrency Control Needed?

Without concurrency control, databases risk becoming unreliable. Problems such as lost updates, dirty reads, and unrepeatable reads compromise:

• Data consistency: Ensuring transactions leave the database in a valid state.
• Data integrity: Preventing corruption from concurrent access.
• System reliability: Allowing multiple users to work without interfering with each other.

The goal of concurrency control is to balance data consistency with system performance so that databases remain both accurate and efficient.

Challenges in Concurrent Transactions

Some key difficulties include:

1. Data Consistency Issues
   • Lost updates
   • Temporary inconsistencies during transaction execution
2. Data Integrity Issues
   • Non-repeatable reads
   • Phantom reads (when new rows appear or disappear between queries)
3. Isolation Levels and Performance
   • Low isolation levels (e.g., Read Uncommitted) → Faster but risk dirty reads.
   • High isolation levels (e.g., Serializable) → Safer but slower.
4. Deadlocks
   • When two or more transactions wait indefinitely for each other’s locked resources.
   • Solutions include deadlock detection, prevention, or resolution strategies.
5. Resource Contention
   • Too many locks or limited hardware can reduce throughput.

Concurrency Control Mechanisms

The DBMS employs various techniques to manage concurrent transactions safely.

• Locks:
  • Shared Lock: Allows reading but not writing.
  • Exclusive Lock: Allows both reading and writing but blocks others.
• Two-Phase Locking (2PL): Ensures transactions acquire all locks before releasing any, preventing conflicts.
• Strict 2PL: A stricter form that avoids cascading rollbacks.

Concurrency Control Protocols

Concurrency control protocols enforce ACID properties (Atomicity, Consistency, Isolation, Durability) and ensure serializable transaction execution. Common protocols include:

1. Lock-Based Concurrency Control
   • Uses shared and exclusive locks, plus 2PL rules.
2. Timestamp-Based Concurrency Control
   • Orders transactions based on timestamps to avoid conflicts.
3. Validation-Based Concurrency Control
   • Validates transactions at commit time to ensure they don’t interfere.

Complete Python Course with Advance topics:-Click Here
SQL Tutorial :–Click Here
Download New Real Time Projects :–Click here

Final Thoughts

Concurrency control in DBMS is not just a theoretical concept—it is the backbone of modern applications where multiple users interact with the same system simultaneously, such as online banking, e-commerce, or airline reservations.

By understanding the problems of concurrent execution and applying control protocols, databases achieve the right balance between consistency and performance.

In the next section on UpdateGadh, we will dive deeper into individual concurrency control protocols to see how they function in real-world scenarios.

dbms concurrency control pdf
dbms concurrency control example
types of concurrency control in dbms
lock-based concurrency control in dbms
optimistic concurrency control in dbms
concurrency in dbms
multiversion concurrency control in dbms
dbms concurrency control tutorial