Concurrency & Isolation Levels
Isolation levels trade correctness for concurrency by allowing certain read anomalies.
Cheat sheet
- ▸Dirty read: reading uncommitted data.
- ▸Non-repeatable read: same row read twice gives different values.
- ▸Phantom read: same query returns new rows.
- ▸Read Uncommitted < Read Committed < Repeatable Read < Serializable.
- ▸MySQL InnoDB default: Repeatable Read; PostgreSQL / SQL Server / Oracle default: Read Committed.
- ▸Lost update: two writes overwrite each other.
Anomalies allowed
| Level | Dirty | Non-repeatable | Phantom |
|---|---|---|---|
| Read Uncommitted | Yes | Yes | Yes |
| Read Committed | No | Yes | Yes |
| Repeatable Read | No | No | Yes* |
| Serializable | No | No | No |
Top 10 campus interview questions
Quick quiz
1. Which level prevents dirty reads but allows non-repeatable reads?
2. Phantom reads are fully prevented by:
3. MySQL InnoDB default isolation:
4. Optimistic locking typically uses:
5. New rows appearing in a re-run query is a: