What are the four transaction isolation levels in SQL?
From weakest to strongest they are READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ and SERIALIZABLE. Higher levels prevent more anomalies such as dirty reads, non-repeatable reads and phantoms, at the cost of concurrency.
Explain it simply
Imagine several people editing one shared answer sheet. Isolation decides how much of each other's half-finished work they can see. At the loosest level you see everything, even scribbles. At the strictest level it is as if people took the sheet one at a time.
Example
| Level | Dirty read | Non-repeatable read | Phantom read |
|---|---|---|---|
| READ UNCOMMITTED | Possible | Possible | Possible |
| READ COMMITTED | No | Possible | Possible |
| REPEATABLE READ | No | No | Possible (the SQL standard) |
| SERIALIZABLE | No | No | No |
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN;
SELECT balance FROM accounts WHERE id = 1;
-- ...
COMMIT;Defaults: PostgreSQL, Oracle and SQL Server use READ COMMITTED; MySQL InnoDB uses REPEATABLE READ. Real engines differ from the standard table: InnoDB's REPEATABLE READ largely prevents phantoms too, and PostgreSQL treats READ UNCOMMITTED as READ COMMITTED.
Higher isolation means more locking or more transaction retries, so throughput drops.
What to say in the interview
"The four levels trade correctness for concurrency: READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, SERIALIZABLE. Each stronger level blocks another anomaly. Most apps run on the database default and raise isolation only for specific critical transactions."
Common mistakes
- Assuming every database implements the levels exactly like the textbook table.