Database Isolation Levels
💡 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)
- – Dirty Read 🚨 → You look at another car’s lane change before it’s even finished. (Reading uncommitted data).
- – 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).
- – 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 |
