How to check transaction isolation level

Open two processes against the same database, hold an uncommitted UPDATE in one and read the row from the other. On SQLite there is no level to set: it is serializable, one writer at a time. The reader saw 500 throughout the writer's transaction and 900 only after its own COMMIT.

Why check this

Isolation decides what a concurrent request sees, and the answer changes with the engine and with the journal mode. Check it when a service moves to a new database, when a report and a writer start running at the same hour, and when a bug report says two users saw different totals a second apart.

The failure this catches is the stale read that nobody notices. A reporting query opens a long transaction, a writer commits during it, and the report keeps serving the snapshot it opened with. The numbers are internally consistent and minutes old, and no error is logged anywhere.

Prerequisites

• A sqlite3 command line client. The shell here is the Android platform-tools build, 3.50.6, 32-bit. It is not the same library Node uses.
• Node 22, which bundles node:sqlite over SQLite 3.51.3. The two processes on this page are one shell and one Node process, which is closer to a service and a console than two copies of the same client.
• The isolation documentation states the model this page measures.
• A table to contend over. Save as schema.sql and create with sqlite3 shop.db < schema.sql before each run.

CREATE TABLE orders (id INTEGER PRIMARY KEY, ref TEXT NOT NULL UNIQUE, total INTEGER NOT NULL);
INSERT INTO orders (ref, total) VALUES ('A-1000', 500);

• The writer. Save as hold.sh, and keep it as a separate process, because two connections inside one program can share a cache and stop being a fair test.

#!/bin/sh
# Hold a write lock on shop.db from the sqlite3 shell for $1 seconds, then commit.
{ echo ".bail on"
  echo "BEGIN IMMEDIATE;"
  echo "UPDATE orders SET total = 900 WHERE ref = 'A-1000';"
  echo "SELECT 'shell: uncommitted UPDATE in place';"
  sleep "$1"
  echo "COMMIT;"
  echo "SELECT 'shell: COMMIT returned without error';"
} | sqlite3 shop.db

• The reader. Save as reader.mjs.

import { DatabaseSync } from 'node:sqlite';
const db = new DatabaseSync('shop.db');
const t0 = Date.now();
const at = () => String(Date.now() - t0).padStart(4) + ' ms';
const total = () => db.prepare("SELECT total FROM orders WHERE ref='A-1000'").get().total;
db.exec('BEGIN');                       // deferred: the snapshot opens at the first read
console.log('%s reader  read 1 inside transaction: %d', at(), total());
setTimeout(() => {
  console.log('%s reader  read 2 inside the same transaction: %d', at(), total());
  db.exec('COMMIT');
  console.log('%s reader  read 3 after COMMIT: %d', at(), total());
  db.close();
}, 4000);

Steps

1. 1

   Step 1.

   Ask the engine for its isolation level the way the other engines are asked.

   sqlite3 shop.db <<'EOF'
   .bail off
   SELECT 'journal_mode        = ' || (SELECT * FROM pragma_journal_mode);
   SELECT 'read_uncommitted    = ' || (SELECT * FROM pragma_read_uncommitted);
   SHOW TRANSACTION ISOLATION LEVEL;
   SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
   EOF
   

   journal_mode        = delete
   read_uncommitted    = 0
   Parse error near line 4: near "SHOW": syntax error
   SHOW TRANSACTION ISOLATION LEVEL;
   ^--- error here
   Parse error near line 5: near "SET": syntax error
   SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
   ^--- error here

   There is no level to report and none to set. What SQLite has instead is a journal mode, and delete is the default. Record that value before you interpret anything below it.

2. 2

   Step 2.

   Run the writer and the reader together in the default journal mode.

   ( ./hold.sh 3 & sleep 0.7; node --disable-warning=ExperimentalWarning reader.mjs; wait )
   

   shell: uncommitted UPDATE in place
    0 ms reader  read 1 inside transaction: 500
   Runtime error near line 5: database is locked (5)
   4005 ms reader  read 2 inside the same transaction: 500
   4006 ms reader  read 3 after COMMIT: 500

   Read line 3. The writer's COMMIT failed because the reader was holding a read transaction. In this mode a long report can stop a write from landing, and the value stays 500 everywhere.

3. 3

   Step 3.

   Switch the database to write-ahead logging and run the identical test.

   rm -f shop.db* && sqlite3 shop.db < schema.sql && sqlite3 shop.db "PRAGMA journal_mode=WAL;" && ( ./hold.sh 3 & sleep 0.7; node --disable-warning=ExperimentalWarning reader.mjs; wait )
   

   wal
   shell: uncommitted UPDATE in place
    0 ms reader  read 1 inside transaction: 500
   shell: COMMIT returned without error
   4009 ms reader  read 2 inside the same transaction: 500
   4010 ms reader  read 3 after COMMIT: 900

   Four facts in five lines. The commit now succeeds while the reader is open. Read 1 is 500, so there is no dirty read. Read 2 is still 500 after the commit landed, so the transaction is repeatable. Read 3, on a new transaction, is 900.

4. 4

   Step 4.

   Try to get a dirty read on purpose. Save as dirty-reader.mjs, then run it against the same writer.

   import { DatabaseSync } from 'node:sqlite';
   const db = new DatabaseSync('shop.db');
   db.exec('PRAGMA read_uncommitted = 1');
   console.log('reader  read_uncommitted reports %d',
     db.prepare('PRAGMA read_uncommitted').get().read_uncommitted);
   console.log('reader  total while the writer holds an uncommitted UPDATE: %d',
     db.prepare("SELECT total FROM orders WHERE ref='A-1000'").get().total);
   db.close();
   

   Recreate the database in WAL mode and run the pair.

   rm -f shop.db* && sqlite3 shop.db < schema.sql && sqlite3 shop.db "PRAGMA journal_mode=WAL;" > /dev/null && ( ./hold.sh 3 & sleep 0.7; node --disable-warning=ExperimentalWarning dirty-reader.mjs; wait )
   

   shell: uncommitted UPDATE in place
   reader  read_uncommitted reports 1
   reader  total while the writer holds an uncommitted UPDATE: 500
   shell: COMMIT returned without error

   The pragma accepted the value and reads it back as 1. The read is still clean. read_uncommitted only lowers isolation between connections that share a cache inside one process, and two processes never do.

How to read the result

| What you see | What it means | What to do | | --- | --- | --- | | The reader sees a value the writer has not committed | A dirty read, and this engine does not do it | Recheck that both sides are separate processes on the same file. | | Two reads in one transaction differ | The transaction is not repeatable on that engine | Name the engine and its level in the defect, not the application code. | | The writer's COMMIT fails while a reader is open | Rollback journal mode, where a reader blocks the final write lock | Set PRAGMA journal_mode=WAL and rerun step 2 before filing anything. | | A value keeps its old total long after the writer committed | A read transaction left open across requests | Find the missing COMMIT. The snapshot lives as long as the transaction. |

Thresholds

Common mistakes

What to check next

• How to check database locks: the error in step 2, and the timeout that turns it into a wait.
• How to check transaction rollback: what happens to the writer's work when its commit fails.
• How to test connection pool exhaustion: what a long open transaction does to the rest of the service.
• How to check database integrity: the check to run after a write-ahead log has been interrupted.
• How to find slow queries: the long readers that keep a snapshot open in the first place.

FAQ

How do I show the transaction isolation level?

It depends on the engine, and on SQLite there is nothing to show. SHOW TRANSACTION ISOLATION LEVEL returns a parse error, as step 1 records. Report the journal mode and the result of the two process test instead, since that is what the behaviour actually follows.

Does WAL mode lower the isolation level?

No. It changes who blocks whom. In step 3 the reader kept a serializable snapshot while the writer committed, which rollback journal mode did not allow at all. The reader never saw an uncommitted value in either mode.

Why does my second read return the same stale row?

The transaction is still open, so it is still serving the snapshot taken at its first read. That is the guarantee working. Commit or roll back between reads when you want current data, as read 3 in step 3 does.

Can I test read committed or repeatable read here?

Not on SQLite. Those names belong to engines this procedure was not run against. Run the same two process script against that engine, with its own client, and record what its reads return before quoting a level.

Which process should hold the write?

The one that behaves like your service. This page holds the write in the shell and reads from Node, so the slow reader is the application. Swap them and rerun if your real writer is the application.

Verified