Appearance
Isolation Levels
Isolation levels define how much one transaction is visible to another when running concurrently. It's a tradeoff between consistency vs performance.
3 Problems Isolation Levels Solve
1. Dirty Read
Reading uncommitted data of another transaction.
T1: UPDATE balance = 1000 (not committed yet)
T2: READ balance → sees 1000 (T1 might rollback!)2. Non-Repeatable Read
Same query returns different results within same transaction.
T1: READ balance → 500
T2: UPDATE balance = 800 → COMMIT
T1: READ balance → 800 (changed within T1's session!)3. Phantom Read
Same query returns different rows within same transaction.
T1: SELECT * WHERE age > 20 → 5 rows
T2: INSERT new row with age = 25 → COMMIT
T1: SELECT * WHERE age > 20 → 6 rows (new row appeared!)The 4 Isolation Levels
Level 1 — Read Uncommitted (Lowest)
Transactions can read uncommitted changes of other transactions. Dirty Read, Non-Repeatable Read, and Phantom Read are all possible. Rarely used — maybe for approximate analytics.
Level 2 — Read Committed
Can only read data that has been committed. Prevents Dirty Reads. Non-Repeatable Read and Phantom Read still possible. Default in PostgreSQL, Oracle, SQL Server.
Level 3 — Repeatable Read
Locks the rows you've read — no one can update them. Prevents Dirty Read and Non-Repeatable Read. Phantom Read still possible. Default in MySQL InnoDB.
Level 4 — Serializable (Highest)
Transactions run as if they are serial. Fully isolated — complete lock on read/write/range. All three problems prevented. Strictest but slowest.
Comparison Table
| Isolation Level | Dirty Read | Non-Repeatable Read | Phantom Read | Performance |
|---|---|---|---|---|
| Read Uncommitted | Possible | Possible | Possible | Fastest |
| Read Committed | Prevented | Possible | Possible | Fast |
| Repeatable Read | Prevented | Prevented | Possible | Slower |
| Serializable | Prevented | Prevented | Prevented | Slowest |
Higher Isolation → More Consistency → Less Performance Lower Isolation → More Performance → More Anomalies
Interview Tip: MySQL (InnoDB) default → Repeatable Read. PostgreSQL / Oracle / SQL Server → Read Committed.