Isolation Levels and Their Anomalies

Accept SET TRANSACTION ISOLATION LEVEL READ COMMITTED, REPEATABLE READ or SERIALIZABLE before a transaction starts. READ COMMITTED takes a new view for each statement, REPEATABLE READ keeps one view for the transaction, and SERIALIZABLE turns plain reads back into locking reads.

Not run yet · 7 tests

Why this stage exists

Read views gave every transaction one fixed picture of the data. That is one choice among several, each with a price. A picture per statement lets a long transaction see recent commits; locks on reads close the last gap that views leave open. SQL names these choices isolation levels, and each is defined by the anomalies it rules out.

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

The level belongs to the session and applies to the transactions it starts from then on. Setting it in the middle of a transaction would change the rules halfway, so that is 01805. The default stays REPEATABLE READ.

Three levels

Level	A plain read
READ COMMITTED	takes a new view for each statement
REPEATABLE READ	uses one view for the whole transaction, from its first read
SERIALIZABLE	takes no view: it reads the newest committed rows and locks them, as reads did before read views

At every level, UPDATE and DELETE work on the newest committed row and lock it. A REPEATABLE READ transaction that read 10, and then adds one after another transaction committed 11, writes 12, not 11. Its view is for reading; writing must not undo someone else's commit.

The anomalies, one test each

Anomaly	READ COMMITTED	REPEATABLE READ	SERIALIZABLE
dirty read	never	never	never
non-repeatable read	happens	never	never
phantom	happens	never, for plain reads	never: the scan holds a lock
lost update	never: the row lock	never	never
write skew	happens	happens	never: one waits, one is a deadlock victim

Non-repeatable read. A READ COMMITTED transaction reads a balance, another commits a change, and the first reads again: a different number. Its second statement took a new view.

Phantom. The same with a count: a row inserted in between appears.

Lost update. Two transactions each add one to the same counter. Under every level here both increments survive, because the second UPDATE waits for the first's row lock and then reads the newest value.

Write skew is the subtle one. The rule: at least one doctor must stay on call. Two doctors are on call; each, in their own transaction, counts the doctors on call, sees 2, and takes themself off. Neither change alone breaks the rule, and the two transactions never touch the same row, so no row lock collides. Under REPEATABLE READ both commit, and nobody is on call.

Drawing the diagram…

Under SERIALIZABLE the counts lock the table S, which both may hold. Each UPDATE then needs IX on the table, which conflicts with the other's S: the first waits, the second closes a cycle and is the deadlock victim. One doctor stays on call.

The algorithm in words

SET TRANSACTION

A setting of the session, for transactions not yet started.

1.Read SET, then the words TRANSACTION ISOLATION LEVEL and one of READ COMMITTED, REPEATABLE READ, SERIALIZABLE, in any case. Anything else after SET is 00302.
2.If the session's transaction is open, it is 01805 and nothing changes.
3.Otherwise store the level in the session and print the success line.

A plain read at each level

The same statement, three behaviours.

1.READ COMMITTED: take a view at the start of the statement, read through it, drop it at the end.
2.REPEATABLE READ: use the transaction's view, taking it at the first plain read.
3.SERIALIZABLE: take no view; read the newest committed rows, taking S locks on rows or S on a scanned table, and wait when they conflict.
4.At every level, UPDATE and DELETE read and change the newest committed rows, with X locks.

Output contract for this stage

These are the exact lines the tests check. The full definition is on the output contract page, section 88.

Input	Output
`set transaction isolation level read committed;`	`Set transaction statement executed successfully`
the same inside a transaction	`[ERROR:01805] ...`
`set transaction isolation level read uncommitted;`	`[ERROR:00302] ...`
`sessions`	`... iso=READ_COMMITTED`, `iso=REPEATABLE_READ` or `iso=SERIALIZABLE`
`ast set transaction isolation level read committed;`	`Statement: SET TRANSACTION ISOLATION LEVEL READ COMMITTED`
`explain set transaction isolation level serializable;`	`[PLAN] set transaction isolation level serializable`, `[PLAN] └── SetTransaction`

What you should see when done

shell

$droid --db i.db
Welcome to droid!
>create table t (id int primary key, n int);
Create table statement executed successfully
>insert into t (id, n) values (5, 10);
Insert statement executed successfully (1 rows)
>@1 set transaction isolation level read committed;
@1| Set transaction statement executed successfully
>@1 begin;
@1| Begin statement executed successfully
>@1 select n from t where id = 5;
@1| n
@1| 10
@1| (1 rows)
@1| [EXEC] rows_examined=1
>@2 update t set n = 20 where id = 5;
@2| Update statement executed successfully (1 rows)
@2| [EXEC] rows_examined=1
>@1 select n from t where id = 5;
@1| n
@1| 20
@1| (1 rows)
@1| [EXEC] rows_examined=1
>.exit
exiting.. good bye!

Common bugs in this stage

Applying SET to the open transaction. It is 01805; the level applies to transactions started later.
Keeping a READ COMMITTED view after its statement. It holds back purge and repeats reads it should not.
Updating through the view. UPDATE acts on the newest committed row at every level.
SERIALIZABLE reads that do not wait. They lock like the reads of the row-locks stage, and wait like them.
Making read or level keywords. They are only words after SET; a column may still be called level.

Language notes

The page above is the same for every language. These are the only language-specific hints.

C: an enum in the session struct; the parser matches the words after SET with strcasecmp on identifier tokens.
C++: the same enum; a small table from word sequences to levels keeps the parser short.
Rust: enum Isolation { ReadCommitted, RepeatableRead, Serializable } with a Display impl that prints the sessions form.
Zig: an enum with a name() function returning READ_COMMITTED and so on for sessions.

Loading starter code…

No run yet

7 tests wait for your first run

Run tests builds your code, drives the binary through every case for this stage, and shows expected and received output side by side here. Failing cases come first.