Isolation Levels & Anomalies

While full Isolation (Serializable) is ideal, it is also very slow. SQL standards define four Isolation Levels that allow developers to trade off between performance and data accuracy.

---

Learning Objectives

After completing this chapter, you will be able to:

• Explain the three concurrency anomalies (Dirty Read, Non-Repeatable Read, Phantom Read).
• Describe all four SQL isolation levels.
• Identify which anomalies each level prevents.
• Understand Snapshot Isolation.
• Choose the right isolation level for a given scenario.
• Answer interview questions on isolation levels.

---

Concurrency Anomalies

These are the “glitches” that happen when isolation is low.

1. Dirty Read

Reading data written by another transaction that has not yet committed.

T1: Write(A) = 100
                    T2: Read(A) → 100 (DIRTY!)
T1: ROLLBACK       (T2 read data that no longer exists)

Problem: T2 read data that was never committed — it doesn’t exist anymore.

2. Non-Repeatable Read

Reading the same row twice and getting different values because another transaction updated and committed it in between.

T1: Read(A) → 100
                    T2: Write(A) = 200, COMMIT
T1: Read(A) → 200  (Different value! Non-Repeatable)

Problem: T1 gets inconsistent results within the same transaction.

3. Phantom Read

Running the same range query twice and getting different sets of rows because another transaction inserted new matching rows.

T1: SELECT * FROM Orders WHERE > 1000 → 5 rows
                    T2: INSERT INTO Orders(1500), COMMIT
T1: SELECT * FROM Orders WHERE > 1000 → 6 rows  (Phantom!)

Problem: New rows appear (phantoms) within the same transaction.

---

SQL Isolation Levels

Level	Dirty Read	Non-Repeatable Read	Phantom Read
Read Uncommitted	Possible	Possible	Possible
Read Committed	Prevented	Possible	Possible
Repeatable Read	Prevented	Prevented	Possible
Serializable	Prevented	Prevented	Prevented

---

Detail on Each Level

1. Read Uncommitted

The lowest isolation level. A transaction can see uncommitted changes of other transactions.

Anomalies	Allowed
Dirty Read	Yes
Non-Repeatable Read	Yes
Phantom Read	Yes

Implementations: Almost no database actually uses this. It’s theoretically possible but dangerous.

2. Read Committed (Default in most databases)

A transaction sees only committed data. Dirty reads are prevented.

Anomalies	Allowed
Dirty Read	No
Non-Repeatable Read	Yes
Phantom Read	Yes

How it works: Each statement sees a snapshot of committed data at the start of that statement.

Default in: PostgreSQL, Oracle, SQL Server.

3. Repeatable Read

The same row read twice gives the same result. Prevents Non-Repeatable Reads by holding read locks until the transaction ends.

Anomalies	Allowed
Dirty Read	No
Non-Repeatable Read	No
Phantom Read	Yes

How it works: Read locks are held until COMMIT/ROLLBACK. But new rows (phantoms) can still appear.

Default in: MySQL (InnoDB).

4. Serializable

The highest isolation level. Transactions execute as if they were serial (one after another). All anomalies prevented.

Anomalies	Allowed
Dirty Read	No
Non-Repeatable Read	No
Phantom Read	No

How it works: Uses Index Range Locking (Gap Locking) — locks not just the rows found, but the gaps where new rows could be inserted.

---

The Trade-Off

Lower Isolation                     Higher Isolation
    │                                    │
    ▼                                    ▼
Faster                              Slower
Fewer locks                         More locks
Risk of anomalies                   No anomalies
    │                                    │
    ▼                                    ▼
Read Uncommitted ... Serializable

Rule of thumb: Always choose the lowest isolation level that your business logic can safely tolerate. Most applications use Read Committed.

---

Snapshot Isolation

A modern technique used by PostgreSQL (in Repeatable Read mode), Oracle, and SQL Server.

How it works:

• Each transaction sees a snapshot of the database as of when it started
• Changes made by other transactions are invisible until COMMIT
• No locks needed for reads → high concurrency

Results:

• Reads never block writes
• Writes never block reads
• Similar to Repeatable Read but handles some phantom cases more efficiently
• Does NOT guarantee Serializable (anomalies like Write Skew can occur)

---

Practical Guidance

Use Case	Recommended Level
Reporting (read-only)	Read Committed or Repeatable Read
Banking transactions	Serializable
E-commerce checkout	Repeatable Read or Serializable
Blog/CMS (most reads)	Read Committed
Analytics dashboard	Read Uncommitted (if approximate OK)

---

Interview Deep Dive

---

Key Takeaways

• Dirty Read: Reading uncommitted data.
• Non-Repeatable Read: Same row, different value on re-read.
• Phantom Read: Same query, different set of rows on re-read.
• Read Uncommitted: All anomalies possible (rarely used).
• Read Committed: Dirty reads prevented — default in most databases.
• Repeatable Read: Non-repeatable reads prevented (MySQL default).
• Serializable: All anomalies prevented — slowest.
• Higher isolation = more locks = lower performance.
• Snapshot Isolation: Optimistic approach — no read locks, high concurrency.
• Choose the lowest level that your business logic tolerates.