Isolation Levels in SQL
Isolation Levels in SQL
Transaction isolation levels define the degree to which one transaction must be isolated from concurrent modifications made by other transactions.
Isolation Levels Matrix
| Isolation Level | Dirty Read | Non-Repeatable Read | Phantom Read |
|---|---|---|---|
| READ UNCOMMITTED | Allowed | Allowed | Allowed |
| READ COMMITTED | Prevented | Allowed | Allowed |
| REPEATABLE READ | Prevented | Prevented | Allowed* |
| SERIALIZABLE | Prevented | Prevented | Prevented |
*Note: MySQL InnoDB prevents Phantom Reads in Repeatable Read using Next-Key Locks.
Concurrency Anomalies Explained
- Dirty Read: Transaction A reads data modified by Transaction B that has not yet been committed. If B rolls back, A holds invalid data.
- Non-Repeatable Read: Transaction A reads a row, Transaction B updates or deletes that row and commits, and Transaction A reads the same row again, obtaining different values.
- Phantom Read: Transaction A queries a range of rows matching a condition. Transaction B inserts new rows matching that condition and commits. A subsequent query by A returns new "phantom" rows.