The Four Isolation Levels
Read Uncommitted through Serializable and what each permits.
The Question Behind the Question
When an interviewer asks "name the four isolation levels", the real test is whether you can explain the trade-off: stronger isolation means fewer anomalies but lower concurrency.
The SQL standard defines four levels, ordered from weakest to strongest:
- READ UNCOMMITTED
- READ COMMITTED
- REPEATABLE READ
- SERIALIZABLE
Each level permits or forbids a specific set of read anomalies. This lesson covers the levels; the next covers the anomalies in detail.
Setting the Isolation Level
You set isolation per transaction or per session. The syntax is nearly identical across engines.
If you do not set it, every database has a default. Knowing the defaults is a frequent interview question, so we will cover them at the end.
-- Per transaction (standard SQL)
BEGIN TRANSACTION ISOLATION LEVEL READ COMMITTED;
-- ... statements ...
COMMIT;
-- Per session
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;All lessons in this course
- ACID Properties Explained
- The Four Isolation Levels
- Dirty, Non-Repeatable and Phantom Reads
- Deadlocks, Locking and MVCC