Sign up
View stargazers13Sign up

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

opened2d agobyRrealhasanshoaib

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:

ts
1// Repro: an outside read transaction stalls every T3 write once the WAL passes journal_size_limit.
2// Save in packages/shared, then: node repro-wal-gate.ts worker (or: noworker)
3import * as NodeSqlite from "node:sqlite";
4import { mkdtempSync, statSync } from "node:fs";
5import { tmpdir } from "node:os";
6import { randomBytes } from "node:crypto";
7import * as Effect from "effect/Effect";
8import * as Exit from "effect/Exit";
9import * as SqlClient from "effect/sql/SqlClient";
10import * as NodeSqliteClient from "./src/nodeSqliteClient.ts";
11
12const checkpointWorker = process.argv[2] === "worker";
13const filename = `${mkdtempSync(`${tmpdir()}/t3-wal-gate-`)}/statev2.sqlite`;
14const walMb = () => (statSync(`${filename}-wal`, { throwIfNoEntry: false })?.size ?? 0) / 1048576;
15const payload = randomBytes(64 * 1024);
16
17const program = Effect.gen(function* () {
18 const sql = yield* SqlClient.SqlClient;
19 // Same pragmas as apps/server/src/persistence/Sqlite.ts.
20 yield* sql`PRAGMA busy_timeout = 5000`;
21 yield* sql`PRAGMA journal_mode = WAL`;
22 yield* sql`PRAGMA synchronous = NORMAL`;
23 yield* sql`PRAGMA checkpoint_fullfsync = ON`;
24 yield* sql.unsafe(`PRAGMA journal_size_limit = ${32 * 1024 * 1024}`);
25 yield* sql`CREATE TABLE t (id INTEGER PRIMARY KEY, data BLOB)`;
26 yield* sql`INSERT INTO t (data) VALUES (${payload})`;
27
28 // An outside reader, like `sqlite3 statev2.sqlite` or a backup script.
29 const outside = new NodeSqlite.DatabaseSync(filename, { readOnly: true });
30 outside.exec("BEGIN");
31 outside.prepare("SELECT count(*) FROM t").get();
32 console.log(`checkpointWorker=${checkpointWorker}: outside reader holds a read transaction`);
33
34 for (let i = 1; i <= 1500; i++) {
35 const started = performance.now();
36 const exit = yield* Effect.exit(sql`INSERT INTO t (data) VALUES (${payload})`.pipe(Effect.timeout("5 seconds")));
37 if (Exit.isFailure(exit)) {
38 console.log(`write ${i} STALLED for 5s+ (WAL ${walMb().toFixed(1)} MB)`);
39 outside.exec("COMMIT");
40 const resumed = performance.now();
41 yield* sql`INSERT INTO t (data) VALUES (${payload})`;
42 console.log(`outside reader closed; next write took ${(performance.now() - resumed).toFixed(0)} ms`);
43 return;
44 }
45 if (performance.now() - started > 500) console.log(`write ${i} slow: ${(performance.now() - started).toFixed(0)} ms`);
46 }
47 console.log(`all 1500 writes finished with the reader open (WAL ${walMb().toFixed(1)} MB)`);
48 outside.exec("COMMIT");
49});
50
51await Effect.runPromise(
52 program.pipe(
53 Effect.provide(NodeSqliteClient.layer({ filename, readerWorker: true, checkpointWorker })),
54 ),
55);
1$ node repro-wal-gate.ts worker
2checkpointWorker=true: outside reader holds a read transaction
3write 552 STALLED for 5s+ (WAL 39.3 MB)
4outside reader closed; next write took 102 ms
5
6$ node repro-wal-gate.ts noworker
7checkpointWorker=false: outside reader holds a read transaction
8all 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 @ 5ffe045931, 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.

1start durationMs connection db.lock_wait_ms query
211:56:30 901121 writer 901120 SELECT * FROM scheduled_tasks WHERE enabled = 1 AND next_run_at IS NOT NULL ...
311:56:34 897756 writer 897755 SELECT MAX(sequence) AS sequence FROM orchestration_events INDEXED BY ...
411:56:48 883879 writer 883878 SELECT project_id AS "projectId", title, workspace_root AS "workspaceRoot", ...
511:57:28 844924 writer 844922 SELECT thread_id, transcript_imported_at FROM orchestration_v2_legacy_imports ...
612: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

  • #21799 added the admission gate.
  • #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.
  • #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.

Rrealhasanshoaib2d ago
Synced from GitHub

It froze again 40 minutes later with no outside process holding the database; only the server had statev2.sqlite open. This time the reader was the server's own shell snapshot. loadShellSnapshot ran a sql.transaction on the reader connection for 39 to 66 s while the machine was under load (load average 15) and the WAL was 33.7 MB. Writer statements waited up to 78 s for the writer lock until that read ended. It happened from 12:49 to 12:51 and again from 12:52 to 12:53 (UTC+4).

1start end s span connection lock wait s
212:52:29 12:53:27 58.2 sql.transaction (loadShellSnapshot) reader 0.0
312:52:42 12:53:37 55.4 sql.transaction writer 55.4
412:52:48 12:53:38 50.4 sql.transaction writer 50.4
512:53:00 12:53:39 38.9 sql.transaction writer 38.9

So the gate needs no outside reader. Once the WAL is past 32 MB, any slow read on the server's own reader connection freezes writes for as long as that read lasts.

Aaryaxo2d ago
Synced from GitHub

Another data point from a large Windows install, in case it helps size this.

Setup

  • T3 Code Nightly 0.1.2-nightly.20261021.3473, desktop on Windows 11.
  • 16 logical CPUs, 64 GB RAM.
  • statev2.sqlite is 31.1 GB with about 2,790 threads. 959 are unarchived, after I archived 1,829 finished ones today.
  • 10-20 agent threads run at once, with heavy MCP orchestration (about 17-28 tool calls a minute).

Stalls

  • From 20 Oct 22:31 to 22 Oct 13:30, event-loop-stalls.ndjson has 1,470 records. Median max delay is 4.6 s and p90 is 9.7 s; 22 records are over 20 s and the worst is 79.9 s. Utilisation median is 0.93.
  • Two full client resubscribes (the "Reconnecting" banner) at 13:23:51 and 13:27:31 lined up with 10.8 s and 8.5 s stalls.
  • Archiving the 1,829 threads didn't help. From 19:53 to 21:31 there were 67 records: median 7.2 s, p90 17.6 s, worst 27.0 s.

Where the time goes (trace, 19:53-21:30, UTC+5)

  • Writer-connection sqlite spans: 17,940.
    • 97.3% of their total duration is db.lock_wait_ms.
    • Lock wait: p99 5.7 s, max 90.8 s.
    • Execution: p50 about 0 ms, p99 384 ms.
  • 28 sql.transactions executed for 5 s or more, not counting lock wait:
    • reader: ThreadPullRequestServiceV2.synchronize 7 times, up to 120.7 s, and ShellStream.loadShellSnapshotParts 3 times, 31-32 s, via loadShellSnapshot and subscribeShell;
    • writer: Orchestrator.dispatchMessage (from sendToThread and dispatchContinuation) 42-53 s; Orchestrator.finalizeAppOwnedSubagentTask 29-69 s; providerTurnStart.start 31.5 s; OrchestratorMcpService.readThread/loadReadableThread 25.5 s.
  • The -wal file was 18.9-29.8 MB each time I sampled it. So I can't tell whether the 32 MB gate fired here, but the long reader transactions above are the kind that would pin it.

I can share span trees with timings only, no thread content, if useful.