Opening a long thread blocks the database writer for 7–10 s#21840

Thanks for the measurements. Current main still behaves the way you describe.

The windowed thread snapshot read runs in a plain sql.withTransaction (apps/server/src/orchestration-v2/ProjectionStore.ts:5225-5274). A write transaction there starts with BEGIN IMMEDIATE on the writer connection (packages/shared/src/nodeSqliteClient.ts:818), so it holds the write lock for as long as the read takes. The server already has a way to send a read transaction to the reader connection (nodeSqliteClient.ts:54-63), and some other reads use it, but this snapshot read doesn't.

None of the open pull requests move this read off the writer. #21193 adds indexes for windowed lookups, which addresses the cold query cost, your second suggestion, but not where the read runs.

Accepting this as a bug.

#21869 would move getThreadSnapshotWindow to the reader. Four other ProjectionStore reads still default to the writer on main at e45ddea067e8, because neither they nor their callers below opt into readOnly. On 0.1.1-nightly.20261019.3360 (1451e7cb2303), with a real 36.7 GB statev2.sqlite, each one blocked the server's main event loop for the whole statement. Times are CEST, UTC+2.

While each statement ran, no other server span started or ended, and every row with a stall record matched its statement to within a second. That fits the writer being a synchronous DatabaseSync on the main thread that gets every statement not marked readOnly.

The host was overloaded (Apple M1, 8 cores, 16 GB, load average 57.28 at 18:25:57), and the times include whatever that added. For comparison, the same shell-row query ran on the reader worker in the same minutes: all 12 runs over 5 s, taking 8.72 to 20.93 s. Six had stall records of at most 4,813 ms in their windows, and four had none. One shares a 30 s window with the heartbeat read above. The last shares a window with a 15,962 ms record I can't explain, although server spans kept starting and ending throughout it. I read this as the writer connection blocking the event loop while the reader worker lets other server work run. It doesn't separate out the host load or explain why the statements took 8 to 20 s.

If you'd rather keep this issue to the snapshot window, I can file the remaining reads separately. Span trees are available on request.

Traces analyzed with GPT-6.1 Sol in Codex and Claude Opus 5.5 in Claude Code, written by Claude Opus 5.5, on behalf of @ImSynem.

More evidence from 0.1.2-nightly.20261021.3499 (17 GB statev2.sqlite, macOS). Right after a restart with a cold cache, readShellSnapshotRows takes about 34 s, and several callers run it inside a writer sql.transaction: ThreadPullRequestServiceV2.synchronize (68.7 s and 34.3 s), StorageCleanup.readThreads (35.0 s) and server.startup.heartbeat.record (33.8 s). Writer statements waited up to 68.8 s behind them, and the event loop recorded a 34 s stall in the same window. Once the cache was warm, the same read took about 4 s.

Another effect of the same cold-start wait: today the desktop window failed at startup with DesktopLocalEnvironmentAuthSessionBootstrapError: Failed to create the local desktop bearer session. The desktop's POST to /oauth/token timed out after 10000 ms, because SessionStore.issue waited behind two ProjectionStore.readShellSnapshotRows reads (34.5 s and 35.4 s) that ran inside writer transactions. The longest write wait in that minute was 35.3 s. A minute later the server answered normally, so the 10 s client timeout turns a slow first minute into a failed launch.

Another data point, from a larger database on the current nightly: 0.1.2-nightly.20261022.3526, macOS desktop app, statev2.sqlite at 44.2 GiB.

Database size

The largest thread has about 512,000 events and 78,268 turn items (about 210 MB of payload_json).

Stalls on this build (event-loop-stalls.ndjson)

• In the 54 minutes after a restart at 06:48 local, the log has 56 records over 1 s. 23 of them are over 5 s, and the longest is 13.3 s.
• In the 20 minutes before that, the first run of this build after the nightly update, it has 21 records. The longest is 13.6 s.

What the user sees

The desktop shows "Main Workstation is reconnecting" for 15 to 45 s, and actions wait until it reconnects. Cheap calls wait behind the blocked loop: on this build ServerSecretStore.get took up to 9.6 s and SessionStore.verify up to 14.1 s. On the 20261021 nightly the same calls took up to 45.7 s.

Several long-running orchestrator threads on this machine read other threads through the T3 tools, so reads of large threads happen often. The four writer reads @ImSynem listed above match what we see. History and timeline pages are covered in #20427.

Same pattern here, through MCP rather than the UI.

• Setup: Windows, Nightly 0.1.2-nightly.20261021.3473, statev2.sqlite 31.1 GB. The longest thread has over 6,300 runs.
• OrchestratorMcpService.readThread → loadReadableThread ran a writer-connection transaction that executed 25.5 s (twice in about 95 minutes).
• OrchestratorMcpService.listThreads and ws.orchestrationV2.threadSnapshotThenLive also each held the writer for 5 s or more.
• Over the same window, writer-connection statements spent 97.3% of their time waiting for the lock (p99 5.7 s, max 90.8 s).
• So agents reading threads through MCP freeze writes the same way opening a thread does. +1 to running these snapshot reads on the reader connection, outside a write transaction.

The four reads are filed separately, as you offered, and a pull request is up for them: #23689.

It covers all four named here, plus getThreadShell, which is the shell row for one thread and is read on every thread read:

It is the same shape of change as #21869: each read is wrapped in NodeSqliteClient.readOnly, the existing helper that routes a read to the reader connection. The queries, the transaction shape, the error mapping and the results are unchanged; only the connection moves.

One extra measurement, from a host serving a 25.5 GB statev2.sqlite under load 68-116 on 10 cores, which is the MCP-heavy case:

78.5% of every query in that window ran inside an MCP call, and the worst single lock wait was 6,622 ms on SELECT payload_json FROM orchestration_v2_projection_nodes WHERE node_id = ?. The reader line in the transaction table is the same starvation you measured: the reader pool was used for 8 transactions in ten minutes, and those were the ones that averaged 497.7 ms.

For the test, the reader-worker and the writer are put on either side of a held write lock rather than a stream of inserts, so the read cannot land after the writes are done. The same read then behaves differently on the two connections:

The read on the reader connection completes in 13 ms with the lock held; the same read on the writer connection cannot acquire a connection at all. The uncommitted_rows_visible=0 is the check that the writer's row was still uncommitted when the read finished, so the write really was in flight for the whole read.

I have not touched the query cost, which you also raised - the indexes are separate work against a different bottleneck, and #21869's scope note says the same.