Database Isolation Levels

[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

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

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                        |

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

Continue reading...

 

Join 𝕋𝕄𝕋 on Telegram
Channel PREVIEW: