Explain the four SQL standard isolation levels and the anomalies they prevent
Quick Answer
From weakest to strongest: **Read Uncommitted** allows dirty reads (seeing another transaction's uncommitted changes). **Read Committed** prevents dirty reads but allows non-repeatable reads (a row you read twice can change between reads). **Repeatable Read** prevents non-repeatable reads but standard-allows phantom reads (a query re-run can return new rows). **Serializable** prevents all of these by making concurrent transactions behave as if run one at a time. Higher isolation = stronger correctness guarantees, at the cost of more blocking/aborts and lower concurrency.
Detailed Answer
The three anomalies
| Anomaly | Description |
|---|---|
| Dirty read | Transaction A reads data that Transaction B has written but not yet committed. If B rolls back, A read data that "never really happened." |
| Non-repeatable read | Transaction A reads a row, Transaction B commits an update to that same row, A reads it again and gets a different value within the same transaction. |
| Phantom read | Transaction A runs a query with a WHERE filter, Transaction B commits a new row matching that filter, A re-runs the same query and sees an extra row that wasn't there before. |
The four levels
| Level | Dirty read | Non-repeatable read | Phantom read |
|---|---|---|---|
| Read Uncommitted | Possible | Possible | Possible |
| Read Committed | Prevented | Possible | Possible |
| Repeatable Read | Prevented | Prevented | Possible (per SQL standard) |
| Serializable | Prevented | Prevented | Prevented |
BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SELECT balance FROM accounts WHERE id = 1; -- e.g., 500
-- (another transaction commits a change to this row)
SELECT balance FROM accounts WHERE id = 1; -- still 500, guaranteed, under REPEATABLE READ
COMMIT;
Engine-specific reality checks
- Read Uncommitted is rarely meaningfully different from Read Committed in practice — PostgreSQL doesn't implement it at all (it silently upgrades to Read Committed); it exists mostly in the standard and in SQL Server.
- PostgreSQL's Repeatable Read actually prevents phantom reads too (stricter than the SQL standard requires for this level), because it's implemented via snapshot isolation rather than row-level locking — worth knowing that "the same isolation level name" doesn't guarantee identical behavior across engines.
- MySQL/InnoDB's default is Repeatable Read, while PostgreSQL's and SQL Server's default is Read Committed — a common source of subtly different application behavior when porting code between engines without adjusting isolation level assumptions.
The tradeoff
Higher isolation levels give stronger guarantees but at a real cost: more locking/blocking, more transaction aborts due to serialization conflicts (an application must be prepared to retry a transaction that fails at Serializable), and lower overall throughput under contention. Read Committed is the practical default for most OLTP workloads because it prevents the most dangerous anomaly (dirty reads) cheaply; Serializable is reserved for logic that's genuinely sensitive to subtle concurrency bugs (e.g., enforcing an invariant across multiple rows, like "total allocated seats can never exceed capacity").
Don't reach for Serializable by default "to be safe" — it can meaningfully hurt throughput and requires retry logic for serialization failures. Instead, identify which specific anomaly your business logic is actually vulnerable to, and choose the lowest isolation level that prevents it (often supplemented with an explicit row lock via SELECT ... FOR UPDATE rather than raising the whole transaction's isolation level).