Application Impact of Isolation Levels

The previous lesson measured two connections wanting to write to the same row, and one waiting for the other. A write–write conflict is a visible event: one waits, then writes. The ones on the read side are quiet.

What does another connection see if it reads the same rows while a transaction is in progress? The state before the change, the state after, or a mix that shifts from query to query? The answer depends on the isolation level. The Advanced SQL course defined the levels and the anomalies they permit; this lesson measures the same anomalies from application code and shows what choosing a level means in practice.

The Setup

The schema is small: one member, five books, loan records.

# setup.sh — resets the lesson database and signal files
rm -f library.db library.db-wal library.db-shm read-done write-done
sqlite3 library.db >/dev/null <<'SQL'
PRAGMA journal_mode = WAL;
CREATE TABLE member (member_id INTEGER PRIMARY KEY, name TEXT NOT NULL, loan_limit INTEGER NOT NULL);
CREATE TABLE book (book_id INTEGER PRIMARY KEY, title TEXT NOT NULL,
                    status TEXT NOT NULL CHECK (status IN ('on_shelf','on_loan')));
CREATE TABLE loan (loan_id INTEGER PRIMARY KEY, book_id INTEGER NOT NULL,
                    member_id INTEGER NOT NULL, return_date TEXT);
INSERT INTO member VALUES (4,'Derek',3);
INSERT INTO book VALUES (1,'Blindness','on_shelf'),(2,'The Disconnected','on_shelf'),
  (3,'The Book of Sand','on_shelf'),(4,'Yaban','on_shelf'),(5,'Silent House','on_shelf');
SQL

The two processes wait for each other using signal files. This is the smallest way to produce a specific ordering without leaving the timing to chance.

Two Anomalies, One Scenario

The reader runs the same two queries twice: the value of one row and the row count of one relation. The writer steps in between.

// reader.mjs — runs the same two queries twice; mode: autocommit | transaction
import { DatabaseSync } from "node:sqlite";
import { writeFileSync, existsSync } from "node:fs";

const mode = process.argv[2];
const db = new DatabaseSync("library.db");
const read = () => ({
  limit: db.prepare("SELECT loan_limit AS s FROM member WHERE member_id = 4").get().s,
  books: db.prepare("SELECT count(*) AS n FROM book").get().n,
});

if (mode === "transaction") db.exec("BEGIN");
const first = read();
writeFileSync("read-done", "");
while (!existsSync("write-done")) { /* wait for the writer to commit */ }
const second = read();
if (mode === "transaction") db.exec("COMMIT");

console.log(`mode=${mode}`);
console.log(`  first read : limit=${first.limit}  books=${first.books}`);
console.log(`  second read: limit=${second.limit}  books=${second.books}`);
console.log(`  nonrepeatable read: ${first.limit !== second.limit}`);
console.log(`  phantom row        : ${first.books !== second.books}`);

// writer.mjs — updates and adds a new row after the reader's first read
import { DatabaseSync } from "node:sqlite";
import { writeFileSync, existsSync } from "node:fs";

while (!existsSync("read-done")) { /* wait for the reader's first read */ }
const db = new DatabaseSync("library.db");
db.exec("BEGIN IMMEDIATE");
db.prepare("UPDATE member SET loan_limit = 5 WHERE member_id = 4").run();
db.prepare("INSERT INTO book VALUES (6,'Motherland Hotel','on_shelf')").run();
db.exec("COMMIT");
writeFileSync("write-done", "");
console.log("writer: limit set to 5, new book added");

The same scenario runs in two modes. In autocommit mode, the reader opens no transaction; each query runs on its own. In transaction mode, the two reads sit inside a single transaction boundary.

# experiment.sh — the same scenario in two modes
for mode in autocommit transaction; do
  sh setup.sh
  node writer.mjs >/dev/null &
  writer=$!
  node reader.mjs $mode
  wait $writer
  echo
done

sh experiment.sh

mode=autocommit
  first read : limit=3  books=5
  second read: limit=5  books=6
  nonrepeatable read: true
  phantom row        : true

mode=transaction
  first read : limit=3  books=5
  second read: limit=3  books=5
  nonrepeatable read: false
  phantom row        : false

Both anomalies showed up in the first mode. The loan_limit value turned from 3 into 5: the same row, the same query, a different result. This is called a nonrepeatable read. The book count rose from 5 to 6: the query asked the same condition, and a new row entered the result set. This is called a phantom row. The difference between the two is that the first is in the value of an existing row, and the second is in the membership of the result set.

In the second mode, both reads gave the picture from the first moment. Once a transaction opens, the reader works on a snapshot, and that snapshot stays fixed for the duration of the transaction.

The real observation here concerns the application. The code ran the same queries in both modes; the only thing that changed was a single BEGIN call. The isolation level the application sees is determined by whether reads are placed inside a transaction boundary. A service that runs queries without a boundary cannot see consistency between two consecutive reads, no matter what level the engine offers.

The Invariant’s Violation Rate

The scenario above set up the timing by hand. Under real load, the timing is not set up; it is left to chance. In that case, the question about an anomaly is no longer “does it happen” but “how often does it happen.”

The library must maintain an invariant: the number of books showing as on loan must equal the number of loan records without a return date. The writer never breaks this invariant; it performs every step inside a single transaction.

// roaming-writer.mjs — continuously issues and returns books; each step is a single transaction
import { DatabaseSync } from "node:sqlite";
import { writeFileSync, rmSync } from "node:fs";

const db = new DatabaseSync("library.db");
db.exec("PRAGMA busy_timeout = 5000");
const end = Date.now() + Number(process.argv[2] ?? 1500);
writeFileSync("writing", "");
let step = 0;
while (Date.now() < end) {
  const bookId = (step % 5) + 1;
  db.exec("BEGIN IMMEDIATE");
  const status = db.prepare("SELECT status FROM book WHERE book_id = ?").get(bookId).status;
  if (status === "on_shelf") {
    db.prepare("UPDATE book SET status = 'on_loan' WHERE book_id = ?").run(bookId);
    db.prepare("INSERT INTO loan (book_id, member_id, return_date) VALUES (?,4,NULL)").run(bookId);
  } else {
    db.prepare("UPDATE book SET status = 'on_shelf' WHERE book_id = ?").run(bookId);
    db.prepare(`UPDATE loan SET return_date = '2025-07-25'
                WHERE loan_id = (SELECT max(loan_id) FROM loan
                                  WHERE book_id = ? AND return_date IS NULL)`).run(bookId);
  }
  db.exec("COMMIT");
  step += 1;
}
rmSync("writing");
console.log(`writer: committed ${step} transactions`);

The reader continuously samples the invariant and counts how many samples it saw as broken.

// inconsistency-count.mjs — samples the invariant between two aggregate queries
import { DatabaseSync } from "node:sqlite";
import { existsSync } from "node:fs";

const mode = process.argv[2];
const db = new DatabaseSync("library.db");
db.exec("PRAGMA busy_timeout = 5000");
while (!existsSync("writing")) { /* wait for the writer to start */ }

let samples = 0, violations = 0;
while (existsSync("writing")) {
  if (mode === "transaction") db.exec("BEGIN");
  const booksOnLoan = db.prepare("SELECT count(*) AS n FROM book WHERE status = 'on_loan'").get().n;
  const openLoans = db.prepare("SELECT count(*) AS n FROM loan WHERE return_date IS NULL").get().n;
  if (mode === "transaction") db.exec("COMMIT");
  samples += 1;
  if (booksOnLoan !== openLoans) violations += 1;
}
console.log(`mode=${mode}  samples=${samples}  invariant violations=${violations}`);

# invariant.sh — the same invariant sampled in two read modes
for mode in autocommit transaction; do
  sh setup.sh
  rm -f writing
  node inconsistency-count.mjs $mode &
  reader=$!
  node roaming-writer.mjs 1500
  wait $reader
done

sh invariant.sh

writer: committed 27750 transactions
mode=autocommit  samples=21024  invariant violations=3489
writer: committed 24922 transactions
mode=transaction  samples=19476  invariant violations=0

The numbers vary by machine and by the load at the time; how many transactions commit in a second and a half is not fixed. What does not change is the distinction: in the unbounded reads, more than three thousand of twenty-one thousand samples saw the invariant broken; in the reads inside a transaction, none did.

The ratio is roughly one-sixth. This means the corresponding report returns an inconsistent pair of numbers on one out of every six calls. Nothing lands in the error log; the queries succeed, there is no constraint violation, only the two numbers fail to agree.

What Levels Mean for the Application

The Advanced SQL course listed four anomalies: dirty reads, nonrepeatable reads, phantom rows, and serializability violations. On the application side, their counterpart comes down to three questions.

Which queries are meaningful together? Queries that are meaningful together are placed inside the same transaction boundary. The two aggregate queries above were meaningful together; the pair they produced when run separately was meaningless.

Is a decision being made on a read value? If a write is made by looking at a read value, that value must not change in the gap between the read and the write. A service that reads the loan limit and writes on the basis of “there is room” must be protected against a change in that gap. This is a problem the isolation level does not solve on its own, and it is the subject of the next lesson.

Is stale data tolerable? A snapshot is consistent, but that is not the same as saying it is current. If a transaction runs long, the data it read may be stale by the time it commits. This is acceptable for long reports; it is not acceptable for something like a money transfer.

The cost of isolation sits at this third point. A high level either holds locks and makes others wait, or keeps old versions around and produces storage and cleanup costs. The mechanism covered in the Relational Database Administration course as multiversion concurrency control follows this second path: old versions cannot be deleted for as long as a reader needs them, and long-running open read transactions delay the cleanup.

Summary

• The same two queries, run unbounded, saw a row whose value had changed (a nonrepeatable read) and a new row entering the result set (a phantom row); inside a transaction boundary, neither showed up.
• The isolation level the application sees is determined by whether reads are placed inside a transaction boundary; with the code unchanged, a single BEGIN call changed the result.
• The invariant between the two aggregate queries showed up broken in roughly one-sixth of twenty-one thousand unbounded-read samples; in the reads inside a transaction, it never broke.
• This kind of inconsistency produces no error: the queries succeed, but together they are meaningless.
• The cost of high isolation is waiting or keeping old versions around; long open read transactions delay the cleanup.

Next Step

Isolation ensures that the data read is consistent; it does not guarantee the correctness of a write made on the basis of a read value. If two members want to borrow the last remaining copy at the same time, both see “book on shelf,” both write, and one invalidates the other’s result. There are two ways to resolve this conflict: check, at write time, that the record is still in the state it was read in, or lock the record at read time and make the other one wait. The next lesson builds both, produces the conflict, shows the lock error directly, and measures which one is cheaper under which load.