Dirty Read, Fuzzy Read, and Phantom Read

In database systems, multiple transactions often execute simultaneously. This simultaneous execution is called concurrent transaction processing.

Concurrency improves:

• System performance
• CPU utilization
• Response time

However, concurrency can also create several problems when transactions access the same data at the same time.

Three important concurrency problems are:

• Dirty Read
• Fuzzy Read (Non-Repeatable Read)
• Phantom Read

These problems occur when transactions interfere with each other due to improper isolation.

---

Transaction Isolation

Before discussing these problems, we must understand the concept of Isolation.

Isolation is one of the ACID properties of transactions.

It ensures that:

One transaction should not affect another transaction while both are executing.

In reality, databases allow controlled interaction between transactions to improve performance. This sometimes leads to concurrency anomalies.

---

What is a Dirty Read?

A Dirty Read occurs when one transaction reads data that has been modified by another transaction but not yet committed.

The reading transaction sees temporary or uncommitted data.

---

Example

Suppose two transactions are running:

• T1 → Updates account balance
• T2 → Reads account balance

---

Initial Balance

Balance = ₹5000

---

Execution

T1: -------------------------------- Update Balance = ₹3000 (Not committed yet) T2: -------------------------------- Reads Balance = ₹3000 T1: -------------------------------- ROLLBACK

---

What Happened?

T2 read the value ₹3000 even though T1 later rolled back the transaction.

The value ₹3000 never became permanent.

Therefore:

T2 read invalid or "dirty" data.

---

Why Dirty Reads Are Dangerous

Dirty reads can create serious inconsistencies because:

• Applications may use incorrect data
• Reports may become inaccurate
• Business decisions may be affected

In banking systems, dirty reads can produce incorrect balances and financial errors.

---

Preventing Dirty Reads

Dirty reads are prevented using:

• Proper locking
• Higher isolation levels

The Read Committed isolation level prevents dirty reads.

---

What is a Fuzzy Read (Non-Repeatable Read)?

A Fuzzy Read, also called a Non-Repeatable Read, occurs when a transaction reads the same data item twice and gets different values because another transaction modified the data in between.

The same query produces different results within the same transaction.

---

Fuzzy Read Example

Initial Balance

Balance = ₹5000

---

Execution

T1: -------------------------------- Reads Balance = ₹5000 T2: -------------------------------- Updates Balance = ₹7000 COMMIT T1: -------------------------------- Reads Balance again = ₹7000

---

What Happened?

T1 read the same row twice but got different results.

The value changed because T2 updated and committed the data between the two reads.

---

Why Fuzzy Reads Are Problematic

Fuzzy reads create inconsistency because:

• Repeated calculations may produce different answers
• Reports may become unreliable
• Data analysis may become inconsistent

Applications expecting stable data within a transaction may fail.

---

Preventing Fuzzy Reads

Fuzzy reads are prevented using:

• Repeatable Read isolation level
• Shared locks on data items

---

What is a Phantom Read?

A Phantom Read occurs when a transaction executes the same query twice and finds that the number of rows has changed because another transaction inserted or deleted rows.

Phantom reads affect entire sets of rows rather than individual data items.

---

Phantom Read Example

Suppose:

Employee Table:
Employees with salary > ₹50,000 = 5 rows

---

Execution

T1: -------------------------------- SELECT * FROM Employee WHERE Salary > 50000; Result = 5 rows T2: -------------------------------- INSERT new employee Salary = ₹60000 COMMIT T1: -------------------------------- Runs same query again Result = 6 rows

---

What Happened?

T1 executed the same query twice but got different numbers of rows.

The new row inserted by T2 appeared like a "phantom."

---

Why Phantom Reads Are Important

Phantom reads can affect:

• Statistical reports
• Inventory calculations
• Analytical queries

Applications expecting stable query results may behave incorrectly.

---

Preventing Phantom Reads

Phantom reads are prevented using:

• Serializable isolation level
• Range locking techniques

The Serializable isolation level provides the highest isolation.

---

Comparison of Dirty Read, Fuzzy Read, and Phantom Read

Problem	Description	Affects
Dirty Read	Reading uncommitted data	Single data item
Fuzzy Read	Repeated read gives different value	Single row
Phantom Read	Repeated query returns different rows	Set of rows

---

Visualization of the Three Problems

Dirty Read

T1 writes → T2 reads → T1 aborts

---

Fuzzy Read

T1 reads → T2 updates → T1 reads again

---

Phantom Read

T1 query → T2 inserts/deletes rows → T1 query again

---

Isolation Levels and Concurrency Problems

Isolation Level	Dirty Read	Fuzzy Read	Phantom Read
Read Uncommitted	Possible	Possible	Possible
Read Committed	Prevented	Possible	Possible
Repeatable Read	Prevented	Prevented	Possible
Serializable	Prevented	Prevented	Prevented

---

Real-World Impact

These concurrency problems are extremely important in:

• Banking systems
• E-commerce platforms
• Reservation systems
• Distributed databases

Incorrect transaction isolation can cause:

• Financial inconsistencies
• Incorrect inventory counts
• Duplicate bookings

---

Dirty Read, Fuzzy Read, and Phantom Read are important concurrency anomalies that occur in database systems when transactions execute simultaneously without proper isolation.

These problems affect data consistency in different ways:

• Dirty Read: Reading uncommitted data
• Fuzzy Read: Reading different values for the same row
• Phantom Read: Reading different sets of rows

Database systems prevent these problems using:

• Locks
• Timestamp ordering
• Isolation levels

---

Happy Exploring!