██FR█████ █INTELL███████████

Database Isolation Levels


This content originally appeared on DEV Community and was authored by Fatima Alam

💡 Database Isolation Levels

Imagine you’re driving through a busy intersection.
Cars = transactions
Intersection = database
Traffic lights = isolation levels

If there are no rules, everyone just drives whenever they want → chaos.
That’s why databases use Isolation Levels: rules that control how transactions move so data doesn’t crash into each other.

🚦 The Traffic Light Analogy

Think of a busy intersection.
Cars = transactions
Intersection = database
Traffic lights = isolation levels

🚗 Read Uncommitted → No traffic lights. Everyone just rushes in. Fast, but lots of accidents (dirty reads).

🚦 Read Committed → Red/green lights exist, but they only control entry. Cars can still switch lanes suddenly (non-repeatable reads).

🛑 Repeatable Read → Not only red/green, but lanes are locked until you exit the junction. No car can push you out — but surprise, new cars might still show up in an empty lane (phantoms).

👮 Serializable → A strict traffic cop makes cars go one batch at a time. Zero accidents, but slow traffic.

Understanding isolation levels is key to writing correct and concurrent database applications.
This guide explains all levels with clear tables, anomalies, and transaction flows so that learning is fast and practical.

Isolation Level Key Points Concurrency Isolation Strength
Read Uncommitted Can read uncommitted (dirty) data. Dirty Read, Non-Repeatable Read, Phantom Read possible. High Low
Read Committed Reads only committed data. Prevents Dirty Read. Non-Repeatable & Phantom Read possible. Medium-High Medium-Low
Repeatable Read Reads held via shared locks until commit. Prevents Dirty & Non-Repeatable Read. Phantom Read possible. Medium High
Serializable Strictest. Uses range locks. Prevents Dirty, Non-Repeatable & Phantom Reads. Low Highest

2⃣ What Problems Can Happen? (Accidents on the Road)

  1. – Dirty Read 🚨 → You look at another car’s lane change before it’s even finished. (Reading uncommitted data).
  2. – Non-Repeatable Read 🔄 → You check once, see a car in lane 1. You look again, and it has moved to lane 2. (Same row, different value).
  3. – Phantom Read 👻 → You look at the junction, count 2 cars. A moment later, new cars have appeared out of nowhere. (New rows magically appear).
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

3⃣ Transaction Examples (Traffic Flow Timelines)

Let’s watch how two cars (T1 & T2) drive through the intersection.
Time flows downward.
S-lock = car blocking a lane for looking.
X-lock = car blocking a lane for turning/changing.

Read Uncommitted (Dirty Read possible)
One car drives in without waiting, and another peeks too early.


| Time ↓ | T1                    | T2                 |
| ------ | --------------------- | ------------------ |
| t1     | BEGIN                 |                    |
| t2     | UPDATE Alice (X-lock) |                    |
| t3     |                       | READ Alice (dirty) |
| t4     | COMMIT                |                    |

Read Committed (Non-Repeatable Read possible)
Cars wait for the green light, but switching lanes between looks is allowed.


| Time ↓ | T1                                          | T2                    |
| ------ | ------------------------------------------- | --------------------- |
| t1     | BEGIN                                       |                       |
| t2     | READ Alice (S-lock,<br>released after read) |                       |
| t3     |                                             | UPDATE Alice (X-lock) |
| t4     |                                             | COMMIT                |
| t5     | READ Alice again (new value)                |                       |
| t6     | COMMIT                                      |                       |

Repeatable Read (Phantom Read possible)
Your lane is locked until you leave, but new lanes can still open.


| Time ↓ | T1                                   | T2                                |
| ------ | ------------------------------------ | --------------------------------- |
| t1     | BEGIN                                |                                   |
| t2     | READ Alice (S-lock held till commit) |                                   |
| t3     |                                      | UPDATE Alice (X-lock,<br>blocked) |
| t4     | COMMIT                               |                                   |
| t5     |                                      | UPDATE Alice (succeeds)           |
| t6     |                                      | COMMIT                            |

Serializable (No anomalies)
The traffic cop says: “One batch at a time. Wait your turn.”


| Time ↓ | T1                                                                            | T2                            |
| ------ | ----------------------------------------------------------------------------- | ----------------------------- |
| t1     | BEGIN                                                                         |                               |
| t2     | READ Accounts WHERE balance>1000<br>(S-lock + range lock<br>held till commit) |                               |
| t3     |                                                                               | INSERT Bob(2000)<br>(blocked) |
| t4     | COMMIT                                                                        |                               |
| t5     |                                                                               | INSERT succeeds               |
| t6     |                                                                               | COMMIT                        |

4⃣ Crux / Key Differences

Feature / Isolation Read Uncommitted Read Committed Repeatable Read Serializable
Dirty Read Can read uncommitted (dirty) data ✅ Prevents dirty read ❌ Prevents dirty read ❌ Prevents dirty read ❌
Non-Repeatable Read Non-Repeatable read possible ✅ Non-Repeatable read possible ✅ Prevents Non-Repeatable read ❌ Prevents Non-Repeatable read ❌
Phantom Read Phantom read possible ✅ Phantom read possible ✅ Phantom read possible ✅ Prevents Phantom read ❌
S-lock on read None Short-lived Held till commit Held + Range
X-lock on write Exclusive lock applied ✅ Exclusive lock applied ✅ Exclusive lock applied ✅ Exclusive lock applied ✅
Concurrency High Medium-High Medium Low
Isolation strength Lowest Medium-Low High Highest


This content originally appeared on DEV Community and was authored by Fatima Alam