Isolation Levels

Across the previous two lessons, only one connection reached the database. A transaction saw its own changes, and nothing happened outside it. In real systems, many connections read and write at the same time, and two questions become meaningful: can a transaction see a change another transaction has not yet committed? Does the same query return the same result when run twice inside one transaction?

Isolation is the property that determines the answers to these questions. It is the third letter of the ACID sequence introduced in the Relational Theory course. Full isolation asks for the result of transactions running concurrently to be the same as running them one after another. This strong guarantee is costly, which is why the standard defines relaxed levels and describes each one by which anomaly it allows.

Three Anomalies

The standard defines three read anomalies. All three are about “data read while another transaction stepped in getting corrupted,” but the shape of the corruption differs.

Dirty read. A transaction reads a change another transaction has not yet committed. If the other transaction is rolled back, the value that was read is a value that never actually existed.

Non-repeatable read. A transaction reads the same row twice and sees different values; another transaction updated and committed that row in between.

Phantom row. A transaction queries the same condition twice and, the second time, sees new rows matching the condition; another transaction inserted rows in between. The difference from a non-repeatable read is that what changed is not a row’s value but the membership of a set.

The distinction matters in practice, because it separates what row locks can prevent from what they cannot: an existing row can be locked, a row that does not yet exist cannot.

Four Levels

The standard defines four levels. Each level also prevents everything a looser level prevents:

Level	Dirty read	Non-repeatable read	Phantom row
READ UNCOMMITTED	possible	possible	possible
READ COMMITTED	not possible	possible	possible
REPEATABLE READ	not possible	not possible	possible
SERIALIZABLE	not possible	not possible	not possible

Two points should not be missed when reading this table.

First, the table lists what is allowed, not what will happen. At READ COMMITTED, a non-repeatable read “is possible”; that does not mean it always happens. An engine can offer a stronger guarantee and still use that level’s name.

Second, the levels are not an implementation, they are a contract. Two engines carrying the same name can satisfy it in different ways: one through locking, another through multi-version concurrency control. The result is that the forbidden anomalies do not occur; the path there is unconstrained.

The level is selected with the standard’s SET TRANSACTION ISOLATION LEVEL statement. Which levels are actually offered depends on the engine: some accept all four but map some of them to a stronger level, others offer no choice at all. The engine used in the observation below belongs to the second group — there is no level selection, and reads are made through a consistent view.

An Observation with Two Connections

The script below opens two separate connections to the same database file and produces a controlled version of another transaction stepping in. First the reader reads twice without opening a transaction; then it performs the same read inside an open transaction. The writer does the same work in both cases: it updates one row and adds a new open loan record.

rm -f isolation.db isolation.db-wal isolation.db-shm
cat > isolation.mjs <<'JS'
import { DatabaseSync } from 'node:sqlite';

const reader = new DatabaseSync('isolation.db');
const writer = new DatabaseSync('isolation.db');

reader.exec('PRAGMA journal_mode = WAL');
writer.exec(`
  CREATE TABLE loan(id INTEGER PRIMARY KEY, book_id INT, member_id INT, pickup TEXT,
                    returned TEXT);
  INSERT INTO loan VALUES (1,1,4,'2024-03-01',NULL),(2,3,4,'2024-03-04',NULL),
                          (3,5,7,'2024-03-06',NULL);
`);

const read = (b) => b.prepare(
  `SELECT (SELECT pickup FROM loan WHERE id = 1) AS loan1_pickup,
          (SELECT COUNT(*) FROM loan WHERE returned IS NULL) AS open_count`).get();

console.log('— reading without a transaction —');
console.log('read 1:', JSON.stringify(read(reader)));
writer.exec("UPDATE loan SET pickup = '2024-03-02' WHERE id = 1");
writer.exec("INSERT INTO loan VALUES (4,7,4,'2024-03-11',NULL)");
console.log('writer updated one row, inserted one row, committed');
console.log('read 2:', JSON.stringify(read(reader)));

console.log('— reading inside a transaction —');
reader.exec('BEGIN');
console.log('read 1:', JSON.stringify(read(reader)));
writer.exec("UPDATE loan SET pickup = '2024-03-03' WHERE id = 1");
writer.exec("INSERT INTO loan VALUES (5,9,7,'2024-03-13',NULL)");
console.log('writer did the same work once more and committed');
console.log('read 2:', JSON.stringify(read(reader)));
reader.exec('COMMIT');
console.log('transaction ended, read 3:', JSON.stringify(read(reader)));
JS
node isolation.mjs
rm -f isolation.db isolation.db-wal isolation.db-shm

— reading without a transaction —
read 1: {"loan1_pickup":"2024-03-01","open_count":3}
writer updated one row, inserted one row, committed
read 2: {"loan1_pickup":"2024-03-02","open_count":4}
— reading inside a transaction —
read 1: {"loan1_pickup":"2024-03-02","open_count":4}
writer did the same work once more and committed
read 2: {"loan1_pickup":"2024-03-02","open_count":4}
transaction ended, read 3: {"loan1_pickup":"2024-03-03","open_count":5}

In the first section, the reader did not open a transaction; each SELECT was its own transaction. Between the two reads, both the row’s value changed — the definition of a non-repeatable read — and the count of matching rows grew — the definition of a phantom row. This is not a bug: the world changing between two separate transactions is expected.

In the second section, the reader opened a transaction with BEGIN. The writer made the same two changes and committed, but the reader’s second read did not change at all: neither the value nor the count. The third read, made after the transaction closed, showed the new state. The difference is that the transaction kept the consistent view it took on its first read all the way to the end — that is, it kept reading through a snapshot.

The observation makes the difference concrete across the table’s first three rows. The side reading without a transaction showed the behavior READ COMMITTED allows: it saw only committed data, but its repeated reads differed. The side reading inside a transaction showed the read-side behavior of SERIALIZABLE: both the non-repeatable read and the phantom row disappeared.

The Level That Cannot Be Shown

One of the four levels in the table cannot be observed on this engine. READ UNCOMMITTED allows reading an uncommitted change; the engine in use here does not offer that under any setting, because reads are always made from an already-committed snapshot. Real engine output cannot be produced to show what the result of a dirty read would be.

Instead, the behavior can be modeled. The script below is not a database; it is a small timing model holding the committed and uncommitted values of a single record. It compares what the reader would see with and without dirty reads allowed:

cat > dirty-model.mjs <<'JS'
// Dirty read model: mimics the behavior of an engine that can see an
// uncommitted value. It is not real engine output; the goal is to show
// what the result would be.
const record = { onShelf: 1, uncommitted: null };

const read = (dirtyReadAllowed) =>
  dirtyReadAllowed && record.uncommitted !== null ? record.uncommitted : record.onShelf;

const attempt = (dirtyReadAllowed) => {
  record.uncommitted = 0;               // writer: lent the book, not yet committed
  const seen = read(dirtyReadAllowed);  // reader steps in
  record.uncommitted = null;            // writer: rolled back the transaction
  return { seenByReader: seen, actualAfterRollback: record.onShelf };
};

console.log('level that allows dirty reads    :', JSON.stringify(attempt(true)));
console.log('level that disallows dirty reads :', JSON.stringify(attempt(false)));
JS
node dirty-model.mjs

level that allows dirty reads    : {"seenByReader":0,"actualAfterRollback":1}
level that disallows dirty reads : {"seenByReader":1,"actualAfterRollback":1}

In the first line, the reader saw the book as lent; because the writer rolled back the transaction, that value never became real. That is the harm of a dirty read: what gets read is not a stale value, it is a value that never existed. A decision made on that basis — such as not reserving the book — corresponds to no real state at all.

Because this block is a model, its output is not evidence of any engine’s behavior; it only makes the definition’s consequence visible. If an engine genuinely offers READ UNCOMMITTED, the same result needs to be measured on that engine.

Choosing a Level

Lower levels give less coordination and therefore more concurrency. The price is that the application has to handle the anomalies on its own.

The decision is made by looking at what the work does with the data it reads. If the value read is only displayed, a relaxed level is enough; if a write is made based on the value read — “check whether it is on the shelf, and if so, lend it” — a relaxed level opens the door to a silent error. Two transactions can both see the book on the shelf at the same time and lend the same book to two different members.

This is called a lost update, and it can be prevented not only by raising the isolation level but also by locking the read. Both paths cost the same thing: transactions touching the same row have to wait for each other. That waiting mechanism is the subject of the next lesson.

Summary

• A dirty read sees uncommitted data, a non-repeatable read sees a changed row value, a phantom row sees new rows matching a condition.
• The standard defines its four levels by which anomaly they allow; the table lists what is allowed, not what will happen.
• A level is a contract, not an implementation; two engines carrying the same name can satisfy it through locking or through multi-version control.
• In the two-connection observation, the side reading without a transaction saw both a changed value and a changed row count; the side reading inside an open transaction kept the view from its first read all the way to the end.
• If a write is made based on a value that was read, a relaxed level opens the door to a lost update.

Next Step

Isolation levels say what will and will not be visible, but they do not say what that costs. When two transactions want to write to the same row, one of them has to wait; how long the wait lasts depends on what got locked. If two transactions wait on resources held by each other, neither can proceed. The next lesson covers lock types, the real error an engine gives on an actual conflict, and how mutual waiting gets resolved.