Why Concurrency Control Is Needed

In a multiuser database system, many transactions run at the same time.

This simultaneous execution is called concurrent execution.

Although concurrency improves performance and response time, it can also create data inconsistencies if transactions interfere with each other.

To understand these issues, consider a simplified airline reservation system.

Database Example

Each flight has a record that stores the number of reserved seats.

Let:

• X = reserved seats for Flight 1

• Y = reserved seats for Flight 2

---

Example Transactions

Transaction T1 – Transfer Seats

This transaction transfers N seat reservations from flight X to flight Y.

Steps:

read_item(X)
X := X − N
write_item(X)

read_item(Y)
Y := Y + N
write_item(Y)

Meaning:

• Remove N seats from flight X

• Add N seats to flight Y

---

Transaction T2 – Reserve Seats

This transaction adds M reservations to flight X.

Steps:

read_item(X)
X := X + M
write_item(X)

Meaning:

• Add M seats to flight X

---

When T1 and T2 run concurrently, several problems may occur.

---

1. Lost Update Problem

Concept

The lost update problem occurs when two transactions update the same data item, and one update overwrites the other.

How It Happens

Suppose:

• Initial value X = 80 seats

• T1 transfers 5 seats (N = 5)

• T2 reserves 4 seats (M = 4)

Correct result should be:

X = 80 − 5 + 4 = 79

But if operations interleave incorrectly:

1. T1 reads X = 80

2. T2 reads X = 80

3. T1 updates X → 75

4. T2 updates X → 84

Final value becomes:

X = 84 (incorrect)

The update made by T1 was lost, because T2 overwrote it.

Key Idea

One transaction’s update disappears, causing incorrect data.

---

2. Temporary Update Problem (Dirty Read)

Concept

A dirty read occurs when a transaction reads data that was updated by another transaction that has not yet committed.

If the first transaction fails, the second transaction has read invalid data.

Example Scenario

1. T1 updates X

2. T2 reads the updated value of X

3. T1 fails

4. System rolls back X to its original value

But T2 already used the incorrect temporary value.

Why It’s Called Dirty Read

The data read by T2 is called dirty data because:

• It came from an uncommitted transaction

• It may never become permanent

---

3. Incorrect Summary Problem

Concept

This problem occurs when a transaction calculates an aggregate value (such as SUM or COUNT) while other transactions are updating the same data.

The result becomes inconsistent.

Example

Transaction T3 calculates:

Total reservations = X + Y

Meanwhile T1 transfers N seats from X to Y.

If the operations interleave like this:

1. T1 subtracts N from X

2. T3 reads X

3. T3 reads Y

4. T1 adds N to Y

Result:

• T3 reads X after subtraction

• T3 reads Y before addition

So the calculated total becomes:

Total = (X − N) + Y

Correct total should be:

Total = (X − N) + (Y + N)

So the result is off by N.

Key Idea

The summary becomes incorrect because updates occur during calculation.

---

4. Unrepeatable Read Problem

Concept

This problem occurs when a transaction reads the same data item twice but gets different values because another transaction modified the data between the reads.

Example

Transaction T checks seat availability:

1. Read seats for flight X → 80

2. Another transaction updates X → 85

3. Transaction T reads X again → 85

Now the same query produced two different results.

Real-world Example

A customer:

1. Checks available seats

2. Takes time to decide

3. Confirms booking

But during that time another user booked seats, so the value changed.

---

Summary of Concurrency Problems

Problem	Description
Lost Update	One transaction overwrites another transaction's update
Dirty Read	A transaction reads uncommitted data
Incorrect Summary	Aggregate calculation reads partially updated data
Unrepeatable Read	Same item read twice gives different values

---

Why Concurrency Control Is Important

Concurrency control ensures that:

• Transactions do not interfere with each other

• Database remains consistent

• Results are the same as if transactions ran one after another (serial execution)

Without concurrency control, multiuser database systems would produce incorrect data.