🧠 Understanding Read Phenomena in Databases: Dirty, Non-Repeatable, and Phantom Reads
When working with databases, especially in systems with concurrent transactions, we expect consistency. But without proper control, transactions can interfere with each other, leading to unexpected and sometimes disastrous results.
This blog dives into three common read anomalies, collectively known as read phenomena, that arise from weak isolation: Dirty Reads, Non-Repeatable Reads, and Phantom Reads.
🔒 Isolation and ACID
Before we explore the anomalies, let’s recall that databases aim to follow ACID properties — Atomicity, Consistency, Isolation, and Durability. Isolation ensures that one transaction doesn’t see the intermediate state of another.
However, depending on the chosen isolation level, some of these guarantees may weaken to allow better performance. And that’s when read anomalies sneak in.
📚 What Are Read Phenomena?
Let’s explore each of the three read anomalies with examples and visual prompts.
1. 🧼 Dirty Read
A dirty read happens when Transaction T2 reads data modified by T1, which hasn't been committed yet.
If T1 rolls back, T2 would have based its logic on a value that never existed.
Real-life analogy: You peek at someone’s exam answer sheet while they're still writing. Then they erase it. What you saw was never final.
💥 Example:
| Time | Transaction T1 | Transaction T2 |
|---|---|---|
| 10:00 AM | UPDATE balance = 1200 | |
| 10:01 AM | SELECT balance → returns 1200 ✅ | |
| 10:02 AM | ROLLBACK |
🔴 Issue: T2 read a temporary, uncommitted value.
2. 🔁 Non-Repeatable Read
A non-repeatable read occurs when a transaction reads the same row twice and gets different results, due to another transaction modifying and committing between those reads.
Real-life analogy: You check your bank balance. A second later, your salary gets credited. You check again — the number changed.
💥 Example:
| Time | Transaction T1 | Transaction T2 |
|---|---|---|
| 2:00 PM | SELECT price FROM product → ₹500 | |
| 2:01 PM | UPDATE product SET price = ₹600 ✅ | |
| 2:02 PM | SELECT price FROM product → ₹600 ❗ |
🔴 Issue: T1 sees inconsistent data across reads.
3. 👻 Phantom Read
A phantom read happens when a transaction runs a query twice, and the set of rows returned is different, due to inserts or deletes by another transaction.
Real-life analogy: You count how many guests are in a room. You turn around, someone enters. You count again — now there are more people.
💥 Example:
| Time | Transaction T1 | Transaction T2 |
|---|---|---|
| 3:00 PM | SELECT * FROM orders WHERE amount > 1000 → 5 rows | |
| 3:01 PM | INSERT INTO orders (amount = 1500) ✅ | |
| 3:02 PM | SELECT * FROM orders WHERE amount > 1000 → 6 rows❗ |
🔴 Issue: Same query returns different row sets.
🛡️ Isolation Levels & What They Prevent
| Isolation Level | Dirty Read | Non-Repeatable Read | Phantom Read |
|---|---|---|---|
| Read Uncommitted | ✅ Yes | ✅ Yes | ✅ Yes |
| Read Committed | ❌ No | ✅ Yes | ✅ Yes |
| Repeatable Read | ❌ No | ❌ No | ✅ Yes |
| Serializable | ❌ No | ❌ No | ❌ No |
✅ = Allowed (anomaly can occur)
❌ = Prevented
🧪 Want to Try It?
You can simulate these in two SQL shells with this pattern:
-- Transaction 1
BEGIN;
UPDATE accounts SET balance = 1200 WHERE id = 1;
-- Transaction 2
SELECT balance FROM accounts WHERE id = 1; -- Dirty read?
-- Transaction 1
ROLLBACK;
Change isolation levels using:
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
🎯 Conclusion
Understanding these read phenomena helps us decide the right isolation level for our application. Choose higher isolation for critical financial or transactional systems, and lower isolation when performance matters more than absolute consistency.
Top comments (0)