What are Anomalies in Transactions?

When multiple transactions run at the same time (concurrency), if the database isn’t careful, weird problems (called anomalies) can happen.

👉 Anomalies are unwanted behaviors like:

• Reading data that isn’t finished.
• Seeing inconsistent results.
• Losing updates.

Main Anomalies (Problems)

Anomaly	What Happens
Dirty Read	Reading data from another transaction that hasn’t been committed yet.
Non-Repeatable Read	Reading the same row twice and getting different values because someone else changed it.
Phantom Read	New rows appear (or disappear) if you re-run the same query during a transaction.
Lost Update	Two transactions overwrite each other’s updates without knowing.

Let’s Explain Each One

---

1. Dirty Read 🧼📖

You read data that another transaction has changed but not yet committed.

✅ Example:

• Transaction A updates balance to $500 but hasn’t committed yet.
• Transaction B reads the balance — sees $500.
• Later, A rolls back — but B already saw incorrect data!

❌ Very dangerous — you see data that might not actually happen.

2. Non-Repeatable Read 🔄📖

You read the same row twice but get different results.

✅ Example:

• Transaction A reads account balance: $500.
• Meanwhile, Transaction B updates it to $600 and commits.
• Transaction A reads again: now it’s $600.

❌ Bad for consistency — you lose stability during your transaction.

3. Phantom Read 👻📖

New rows appear (or disappear) when you rerun a query during the same transaction.

✅ Example:

• Transaction A reads: SELECT * FROM Orders WHERE status = 'pending'; — 5 rows.
• Transaction B inserts a new “pending” order and commits.
• Transaction A reads again — now 6 rows.

❌ Bad for batch operations — data set keeps changing.

4. Lost Update 🔥📝

Two transactions update the same data — but the second one overwrites the first without knowing.

✅ Example:

• Transaction A reads balance $500, adds $50, plans to update $550.
• Transaction B reads the same $500, adds $100, plans to update $600.
• A writes $550, then B writes $600 — A’s update is lost.

❌ Real problem for concurrent updates — leads to data loss.

How to Fix These Anomalies? 🛡️

✅ We fix them by using:
Transaction Isolation Levels

Isolation Level = how much protection a transaction has from other transactions.

---

SQL Standard Isolation Levels

Isolation Level	Prevents	Allows
READ UNCOMMITTED (lowest)	Nothing	Dirty reads, non-repeatable reads, phantom reads
READ COMMITTED (default in many DBs)	Dirty reads	Non-repeatable reads, phantom reads
REPEATABLE READ	Dirty reads, non-repeatable reads	Phantom reads (depends on DB)
SERIALIZABLE (highest)	Dirty reads, non-repeatable reads, phantom reads	Nothing — highest isolation

Quick Summary

Level	Dirty Read	Non-Repeatable Read	Phantom Read
Read Uncommitted	❌ Not prevented	❌ Not prevented	❌ Not prevented
Read Committed	✅ Prevented	❌ Not prevented	❌ Not prevented
Repeatable Read	✅ Prevented	✅ Prevented	❌ (depends)
Serializable	✅ Prevented	✅ Prevented	✅ Prevented

Real-World Meaning

• Read Uncommitted — super fast, super dangerous. (Almost never use.)
• Read Committed — most common; safer for many apps (prevents dirty reads).
• Repeatable Read — safer for apps that read data multiple times.
• Serializable — safest, but slowest — like transactions run one after another.

---

Why Do We Need Isolation Levels?

• Performance vs Safety:
  High isolation (like Serializable) is safe but slower — more locking, blocking.
• Balance:
  Choose an isolation level depending on:
  • How important data consistency is.
  • How much concurrency (parallel transactions) you expect.

✅ For banks — Serializable (money is serious!).
✅ For social media — Read Committed (comments aren’t life-critical).

In Short

Anomalies = concurrency problems.

Isolation Levels = how much the database protects you from these problems.

---

Real-life Analogy

Dirty Read: Reading someone’s unfinished draft — they might throw it away!

Non-Repeatable Read: You look at a price tag, go back, and it changed.

Phantom Read: You count people in a room, but more people come in before you finish.

Lost Update: You and your friend edit the same Google Doc at the same time — overwriting each other.