Concurrency Control in DBMS

Introduction, Schedules, and Concurrency Problems

In a database system, multiple users may access and modify data at the same time. This simultaneous execution of transactions is called concurrency.

Concurrency improves performance and resource utilization. However, if not properly controlled, it can lead to data inconsistency and incorrect results.

Concurrency Control is the mechanism used by a DBMS to ensure that concurrent transactions execute safely without violating database consistency.

In this article, we will understand:

  • What concurrency control is
  • Why is it needed
  • What is a schedule
  • Types of schedules
  • Common concurrency problems

1. What is Concurrency Control?

Concurrency control is a process in a DBMS that ensures the correct execution of transactions when multiple transactions run simultaneously.

Its main objectives are:

  • Maintain database consistency
  • Preserve isolation property
  • Prevent data conflicts
  • Ensure correct execution equivalent to serial execution

Without concurrency control, transactions may interfere with each other and produce incorrect results.

2. Why is Concurrency Needed?

In real-world systems:

  • Many users access banking systems simultaneously.
  • Multiple customers place orders at the same time.
  • Several employees update records concurrently.

If transactions are executed strictly one after another (serial execution), system performance will be slow.

Therefore, DBMS allows concurrent execution to improve throughput and efficiency.

However, concurrency must be controlled.

3. What is a Schedule?

A schedule defines the order in which operations of multiple transactions are executed.

Each transaction consists of operations such as:

  • Read (R)
  • Write (W)
  • Commit (C)
  • Abort (A)

When transactions execute concurrently, their operations are interleaved. The sequence of these operations is called a schedule.

4. Types of Schedules

 

4.1 Serial Schedule

A schedule is serial if one transaction completes entirely before another begins.

Example:

T1: Read(A)
T1: Write(A)
T1: Commit

T2: Read(B)
T2: Write(B)
T2: Commit

Characteristics:

  • Always consistent
  • No interference
  • Low performance in multi-user systems

Serial schedules are safe but inefficient.

4.2 Non-Serial Schedule

A schedule is non-serial if operations of different transactions are interleaved.

Example:

T1: Read(A)
T2: Read(A)
T1: Write(A)
T2: Write(A)

This improves system performance but may cause inconsistencies if not properly controlled.

4.3 Concurrent Schedule

A concurrent schedule is a non-serial schedule that maintains database correctness.

The main goal of concurrency control is to ensure that concurrent schedules produce results equivalent to some serial schedule.

This concept leads to serializability, which will be discussed in the next article.

5. Concurrency Problems (Anomalies)

When concurrency is not properly controlled, the following problems may occur.

5.1 Lost Update Problem

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

Example:

Initial Balance = 1000

T1:

  • Read Balance = 1000
  • Add 100 → 1100

T2:

  • Read Balance = 1000
  • Add 200 → 1200

If T1 writes 1100 and later T2 writes 1200, the update of T1 is lost.

Final balance becomes 1200 instead of 1300.

5.2 Dirty Read Problem

Occurs when a transaction reads data written by another transaction that has not yet committed.

Example:

T1:

  • Update salary to 50000 (not committed)

T2:

  • Reads salary = 50000

If T1 rolls back, T2 has read an invalid value that never actually existed.

5.3 Non-Repeatable Read

Occurs when a transaction reads the same data item twice and gets different values.

Example:

T1:

  • Read salary = 40000

T2:

  • Update salary to 45000
  • Commit

T1:

  • Reads salary again → 45000

The value changes within the same transaction.

5.4 Inconsistent Read (Incorrect Summary Problem)

Occurs when a transaction reads multiple related data items while another transaction updates some of them.

As a result, the first transaction reads a mixture of old and new values.

Example:

Account A = 5000
Account B = 3000

Total balance should be 8000.

T1 (calculating total):

  • Read Account A = 5000

T2 (transfer 1000 from A to B):

  • Deduct 1000 from A → 4000
  • Add 1000 to B → 4000

T1:

  • Read Account B = 4000

T1 calculates total = 5000 + 4000 = 9000

This total is incorrect because it usedthe old value of A and the new value of B.

This is called an inconsistent read.

5.5 Phantom Read

Occurs when a transaction reads a set of rows that satisfy a condition, and another transaction inserts or deletes rows affecting that condition.

Example:

T1:

  • Select employees where salary > 50000

T2:

  • Insert employee with salary 60000
  • Commit

T1:

  • Executes the same query again
  • Sees an extra row (phantom row)

Summary

  • Concurrency allows multiple transactions to execute simultaneously.
  • A schedule defines the execution order of transaction operations.
  • Serial schedules are safe but inefficient.
  • Non-serial schedules improve performance but may cause anomalies.
  • Common concurrency problems include:
    • Lost update
    • Dirty read
    • Non-repeatable read
    • Inconsistent read
    • Phantom read