Any read transaction left open on statev2.sqlite freezes every server write once the WAL passes 32 MB #23482

What happened

The desktop app froze ("T3Code is stuck"). For about 15 minutes threads and the sidebar did not load, and nothing the server writes went through. It recovered within a minute, without a restart, once an outside process that was reading statev2.sqlite exited.

Diagnosis

Since #21799 the checkpoint worker gates writer admission. gated() (packages/shared/src/nodeSqliteClient.ts:655) holds every write while blocked is true. The worker sets blocked when the WAL is at the 10,000-frame ceiling or the -wal file is larger than journal_size_limit (32 MB, apps/server/src/persistence/Sqlite.ts:12), and the checkpoint can't reach the end of the log.

A checkpoint can't move past the snapshot of an open read transaction. So once the WAL passes 32 MB, any connection that keeps a read open keeps blocked true, and every write waits for as long as that read lasts. runWithAdmission (line 794) has no deadline. The wait also happens while the statement holds the writer connection's lock (lines 1118 and 1026), so reads routed to the writer queue behind it too.

Holding writes doesn't help the checkpoint here: the reader blocks it, not the writes. So the gate turns WAL growth, which SQLite handles fine, into a full stall. A fix probably means not gating on blocked while a reader pins the WAL, or giving admission a short deadline after which writes go through and the WAL grows.

On this machine the reader was migrate-dev-db, run by an agent in a worktree, copying the live 17 GB statev2.sqlite in one read transaction. AGENTS.md says reading and copying the live database is fine, so agents do this routinely. A user script that snapshots a few tables every hour, holding a read for 60 to 171 s, is a second trigger on this machine. The server's own reader connection can pin the WAL the same way, because any open read does.

Steps to reproduce

Save this as packages/shared/repro-wal-gate.ts on main and run it from packages/shared:

// Repro: an outside read transaction stalls every T3 write once the WAL passes journal_size_limit.
// Save in packages/shared, then: node repro-wal-gate.ts worker   (or: noworker)
import * as NodeSqlite from "node:sqlite";
import { mkdtempSync, statSync } from "node:fs";
import { tmpdir } from "node:os";
import { randomBytes } from "node:crypto";
import * as Effect from "effect/Effect";
import * as Exit from "effect/Exit";
import * as SqlClient from "effect/sql/SqlClient";
import * as NodeSqliteClient from "./src/nodeSqliteClient.ts";

const checkpointWorker = process.argv[2] === "worker";
const filename = `${mkdtempSync(`${tmpdir()}/t3-wal-gate-`)}/statev2.sqlite`;
const walMb = () => (statSync(`${filename}-wal`, { throwIfNoEntry: false })?.size ?? 0) / 1048576;
const payload = randomBytes(64 * 1024);

const program = Effect.gen(function* () {
  const sql = yield* SqlClient.SqlClient;
  // Same pragmas as apps/server/src/persistence/Sqlite.ts.
  yield* sql`PRAGMA busy_timeout = 5000`;
  yield* sql`PRAGMA journal_mode = WAL`;
  yield* sql`PRAGMA synchronous = NORMAL`;
  yield* sql`PRAGMA checkpoint_fullfsync = ON`;
  yield* sql.unsafe(`PRAGMA journal_size_limit = ${32 * 1024 * 1024}`);
  yield* sql`CREATE TABLE t (id INTEGER PRIMARY KEY, data BLOB)`;
  yield* sql`INSERT INTO t (data) VALUES (${payload})`;

  // An outside reader, like `sqlite3 statev2.sqlite` or a backup script.
  const outside = new NodeSqlite.DatabaseSync(filename, { readOnly: true });
  outside.exec("BEGIN");
  outside.prepare("SELECT count(*) FROM t").get();
  console.log(`checkpointWorker=${checkpointWorker}: outside reader holds a read transaction`);

  for (let i = 1; i <= 1500; i++) {
    const started = performance.now();
    const exit = yield* Effect.exit(sql`INSERT INTO t (data) VALUES (${payload})`.pipe(Effect.timeout("5 seconds")));
    if (Exit.isFailure(exit)) {
      console.log(`write ${i} STALLED for 5s+ (WAL ${walMb().toFixed(1)} MB)`);
      outside.exec("COMMIT");
      const resumed = performance.now();
      yield* sql`INSERT INTO t (data) VALUES (${payload})`;
      console.log(`outside reader closed; next write took ${(performance.now() - resumed).toFixed(0)} ms`);
      return;
    }
    if (performance.now() - started > 500) console.log(`write ${i} slow: ${(performance.now() - started).toFixed(0)} ms`);
  }
  console.log(`all 1500 writes finished with the reader open (WAL ${walMb().toFixed(1)} MB)`);
  outside.exec("COMMIT");
});

await Effect.runPromise(
  program.pipe(
    Effect.provide(NodeSqliteClient.layer({ filename, readerWorker: true, checkpointWorker })),
  ),
);

$ node repro-wal-gate.ts worker
checkpointWorker=true: outside reader holds a read transaction
write 552 STALLED for 5s+ (WAL 39.3 MB)
outside reader closed; next write took 102 ms

$ node repro-wal-gate.ts noworker
checkpointWorker=false: outside reader holds a read transaction
all 1500 writes finished with the reader open (WAL 106.9 MB)

Expected: writes keep going while an outside reader is open, and the WAL grows until the reader leaves, as SQLite's WAL mode allows. Actual: once the WAL passes 32 MB, writes stop until the reader closes. The stall hit write 552 on both runs.

Version

Desktop 0.1.2-nightly.20261021.3499. The local server reports the same serverVersion. Line references are from main @ 5ffe045, which has no later change to nodeSqliteClient.ts.

Environment

macOS 26.6.2 on an Apple M4 MacBook Air with 16 GB. Desktop app with its bundled server (Electron 44.9.3, Node 24.22.0, SQLite 3.53.4). statev2.sqlite is 17 GB. About 14 Claude and Codex threads were running.

Evidence

sqlite spans on the writer connection from server.trace.ndjson, in local time (UTC+4). The outside copy started at 11:56 and was killed at about 12:11. Each duration is almost entirely db.lock_wait_ms, and all of them completed within a second of the kill.

start     durationMs  connection  db.lock_wait_ms  query
11:56:30  901121      writer      901120           SELECT * FROM scheduled_tasks WHERE enabled = 1 AND next_run_at IS NOT NULL ...
11:56:34  897756      writer      897755           SELECT MAX(sequence) AS sequence FROM orchestration_events INDEXED BY ...
11:56:48  883879      writer      883878           SELECT project_id AS "projectId", title, workspace_root AS "workspaceRoot", ...
11:57:28  844924      writer      844922           SELECT thread_id, transcript_imported_at FROM orchestration_v2_legacy_imports ...
12:01:09  624913      writer      624913           SELECT session_id AS "sessionId", subject AS "subject", scopes AS "scopes", ...

50 writer statements waited more than 10 minutes, the longest 901 s. From 11:57 to 12:10 the server started only 1 to 18 SQL statements a minute, against 1,000 to 2,800 a minute before. In the minute after the reader exited it ran 5,072, and after that every statement took 2 ms or less. At this event rate the trace keeps only about 20 minutes (10 MB files rotated every two minutes), so an earlier 104 s stall at 11:50 rotated out before I could classify it.

Related issues

• fix(shared): checkpoint WAL without blocking the event loop #21799 added the admission gate.
• Opening a long thread blocks the database writer for 7–10 s #21840 (open): snapshot reads that hold the writer stall writes for 7 to 10 s. Same symptom, different cause: this stall needs no T3 read at all, and it lasts as long as the outside reader does.
• Local Windows desktop disconnects during 36–44 second server stalls with a 12.5 GB database #21539 (closed): 36 to 44 s server stalls on Windows with a 12.5 GB database. Similar symptom; I did not check whether a reader was open there.

Fix applied or workaround

I killed the outside reader. The server drained its backlog within a minute and needed no restart. The local stopgap is to turn the checkpoint worker off (checkpointWorker: null in the bundled server's SQLite layer), which falls back to SQLite's passive auto-checkpoint. The noworker run above shows that path doesn't stall.

Filed by

Claude Code (Claude Opus 5.5), following .github/triage/PLAYBOOK.md.