Transaction isolation levels define how isolated a transaction is from others — i.e., how much one transaction can “see” the uncommitted changes of another.
They are standardized by SQL ANSI and help balance data consistency vs performance.
🔒 The 4 Standard Isolation Levels (from lowest to highest):
| Isolation Level | Prevents | Allows | Performance | Consistency |
|---|---|---|---|---|
| READ UNCOMMITTED | ✖ Dirty reads | ✓ All anomalies | 🔥 Fastest | 😵 Weakest |
| READ COMMITTED | ✔ Dirty reads | ✖ Non-repeatable, phantom reads | ⚡ Fast | 😐 Fair |
| REPEATABLE READ | ✔ Dirty + non-repeatable reads | ✖ Phantom reads | ⏳ Moderate | 😊 Strong |
| SERIALIZABLE | ✔ All anomalies | ✖ Everything | 🐢 Slowest | 💎 Strongest |
🧪 What Does Each Level Do?
1. READ UNCOMMITTED
- Transactions can see uncommitted changes of others.
- Prone to dirty reads (reading data that may be rolled back).
- Rarely used due to danger of corrupt logic.
2. READ COMMITTED (default in many DBs)
- Can only read committed data.
- Prevents dirty reads, but still allows:
- ❌ Non-repeatable reads (same query returns different results)
- ❌ Phantom reads (rows appear/disappear in repeated queries)
3. REPEATABLE READ
- Prevents dirty reads and non-repeatable reads.
- Ensures same query in same transaction always sees same rows.
- Still allows phantom reads (new rows may show up if using
SELECT WHERE)
4. SERIALIZABLE
- Strictest level: transactions are executed as if they were serial (one at a time).
- Prevents:
- Dirty reads
- Non-repeatable reads
- Phantom reads
- Ensures full isolation but with lower concurrency and higher locking overhead.
💡 Summary Diagram:
READ UNCOMMITTED < READ COMMITTED < REPEATABLE READ < SERIALIZABLE
(More concurrency) (More isolation)