Isolation Levels and Their Anomalies

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

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

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.

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.

Output contract for this stage

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

What you should see when done

$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.