All topics
8 / 66
transactions & locking

Isolation Levels

Trade off concurrency anomalies vs throughput by choosing how much isolation each transaction gets.

Running transactions one at a time would be perfectly safe and far too slow. Isolation levels let you allow some overlap, in exchange for specific, named things going wrong.

A shared spreadsheet with different view modes. "Read Committed" — you see changes as people save. "Repeatable Read" — you see a snapshot from when you opened the file. "Serializable" — like editing offline; the system reconciles at the end.

Key Concepts

1
Picking a level is picking which of those you can live with.
2
The three anomalies, as timelines.Dirty read — you read something that never really happened:
3
    T1: UPDATE balance = 500
    T2:                    SELECT balance  -> 500
    T1: ROLLBACK                              (500 never existed)
4
Non-repeatable read — the same row, read twice, gives two answers:
5
    T1: SELECT price -> 100
    T2: UPDATE price = 150; COMMIT
    T1: SELECT price -> 150
6
Phantom read — the same range query returns a different number of rows:
7
    T1: SELECT COUNT(*) WHERE status='OPEN' -> 8
    T2: INSERT a new OPEN row; COMMIT
    T1: SELECT COUNT(*) WHERE status='OPEN' -> 9
8
Which level stops which.
    level              dirty   non-repeatable   phantom
    READ UNCOMMITTED   allowed    allowed       allowed
    READ COMMITTED     stopped    allowed       allowed
    REPEATABLE READ    stopped    stopped       allowed*
    SERIALIZABLE       stopped    stopped       stopped
9
READ COMMITTED is the default in PostgreSQL, Oracle and SQL Server. REPEATABLE READ is MySQL/InnoDB's. The asterisk is because InnoDB's gap locks stop most phantoms anyway, so the standard's table and real behaviour differ — worth saying out loud in an interview.
READ COMMITTEDREPEATABLE READ
10
Why readers usually do not block any more. Most databases now use multi-version concurrency control. A write creates a new version of the row rather than overwriting it, and each transaction reads the version that was current when it started.
11
So readers never wait for writers and writers never wait for readers. Isolation levels then differ mainly in when your snapshot is taken: per statement for READ COMMITTED, once per transaction for REPEATABLE READ.
READ COMMITTEDREPEATABLE READ
12
The anomaly that survives a snapshot. Two transactions read, decide, and write, and each decision was valid on its own snapshot:
13
    Both check: "are there 2 doctors on call?" -> yes
    Both then:  "so I can go off call"         -> both go off call
14
Nobody read dirty data. Nobody's row changed under them. The result is still wrong, because the rule spans rows neither transaction wrote. This is a write skew, SERIALIZABLE is what prevents it, and it is the usual reason to pay for that level.
SERIALIZABLE
15
Choosing. READ COMMITTED for ordinary web traffic. Step up when a read decides a write — inventory, balances, booking a seat — and be ready to retry, because higher levels abort transactions rather than make them wait. Any code running at SERIALIZABLE needs a retry loop.
READ COMMITTEDSERIALIZABLE
16
What the interviewer is probing.1. "Name the anomalies each level permits." Probing: the matrix. Stalls: "They just differ." Moves up: read uncommitted allows dirty reads, read committed allows non-repeatable reads, repeatable read allows phantoms in some engines, serialisable allows none.
17
2. "What is a phantom read?" Probing: precision. Stalls: "Changed data." Moves up: a second execution of the same query returning new rows because another transaction inserted matching ones.
18
3. "What is the default in PostgreSQL and in MySQL InnoDB?" Probing: practical knowledge. Stalls: "Serialisable." Moves up: read committed in PostgreSQL, repeatable read in InnoDB — a difference that changes behaviour when porting.
19
4. "What does serialisable cost?" Probing: the trade. Stalls: "Nothing." Moves up: more blocking or more aborted transactions, so the application must be prepared to retry.
Use cases:IsolationMVCCLocking