[Bug]: the other ProjectionStore reads take the write lock instead of the reader connection#23687

I can confirm the getShellSnapshot part of this issue on the October 22 nightly, using a component reproduction against a consistent copy of my database. In the captured writer read, the delay was inside the synchronous query rather than waiting to acquire the connection. This validates the blocking mechanism; it doesn't reproduce an actual T3 client disconnect or cover getThreadShell and getThreadRecords.

What I saw live

On 0.1.2-nightly.20261022.3526 (f054d1b), macOS 27.0.1, M4 Pro with 24 GiB RAM and a 12.1 GB statev2.sqlite, the server trace showed a transaction containing readShellSnapshotRows running on db.connection = writer for 9,841 ms with db.lock_wait_ms = 0.001, and the event-loop monitor reported a 9,133 ms max delay for the 30 s window containing it. The writer is a DatabaseSync on the main thread (nodeSqliteClient.ts#L851-L854), reads only leave it when wrapped in readOnly (#L1072-L1081), and getShellSnapshot isn't (ProjectionStore.ts#L6406-L6409). While the synchronous statement runs, the main thread cannot handle incoming RPCs, pings or queued writes. That delay needn't appear as connection lock wait. The same trace records MCP listThreads → getShellSnapshot on writer too.

How the reproduction works

This runs the installed SQL and reader-worker source in isolation, not the full T3 service or its WebSocket client.

1. Consistent snapshot. The copy used SQLite's online backup API from a mode=ro + query_only connection holding one read transaction throughout. It passed quick_check and matched the pinned source's schema hash, page count, row counts and max event sequence: 12,125,323,264 bytes, 3,798 threads, 5,874 runs, 268,415 turn items, 56,083 messages, and 3,773 threads returned by the shell query. An earlier inconsistent copy was discarded. The snapshot monitor was corrected to use a separate process, and only the verified replacement was benchmarked.
2. Exact SQL and runtime. The harness extracted SQL from the installed bundle and ran it in readShellSnapshotRows order under the app's Electron 44.9.3 / Node 24.22.0 / SQLite 3.53.4. It didn't yield to the event loop between statements, so it represents the worst case for Effect scheduling; result decoding was excluded.
3. Writer vs reader. Writer cells use a main-thread DatabaseSync with the installed pragmas and BEGIN IMMEDIATE. Reader cells run the installed reader-worker source verbatim with BEGIN; that's where #23689 sends getShellSnapshot.
4. Probes. A separate process sends loopback HTTP pings in place of WebSocket RPC, the server process records event-loop delay, and the writer connection inserts into a harness-owned sandbox table every 250 ms.
5. Indexes. The comparison applied #23009's migration 068 SQL at 7180469 verbatim to a clone, with sqlite_stat1 left empty as in the live database.
6. Cache state. Each cell starts on a fresh APFS clone ("first read") and repeats in-process; page-cache residency wasn't measured, so first read isn't guaranteed to be empty-cache.

Results

For the full shell, each configuration had three first-read samples and two repeat samples. Query times are medians; event-loop figures are medians of each run's maximum delay. Probe RTT and queued-write delays are the maxima across the three first reads.

• Installed routing (writer): The query took 13,163 ms (range 13,135–13,496), with 13,170 ms of event-loop delay. The worst probe RTT was 13,419 ms and the worst queued-write delay was 13,433 ms. Repeat reads took 271 ms.
• Reader worker: The query still took 13,374 ms, but event-loop delay stayed at 12.7 ms. The worst probe RTT was 1.5 ms and the worst queued-write delay was 11.7 ms. Between 52 and 54 sandbox writes committed during each first read, compared with zero on the writer path. Repeat reads took 287 ms.
• #23009 indexes, writer: The query fell to 2,700 ms, but still blocked the event loop for 2,707 ms. The worst probe RTT was 2,909 ms and the worst queued-write delay was 2,919 ms. Repeat reads took 150 ms.
• #23009 indexes, reader: The query took 2,702 ms with 12.7 ms of event-loop delay. The worst probe RTT was 0.7 ms and the worst queued-write delay was 26 ms. Repeat reads took 159 ms.

Row counts and per-statement SHA-256 matched within each query shape across all 34 benchmark runs (20 full shell, 8 active, 6 project + active), covering writer vs reader, original vs indexed and first vs repeat. That's result equivalence, not a correctness review of either PR. The indexes change three probes in selectShellThreadRows (the blocked-run candidate drops its temp B-tree sort, both stop-request lookups become covering) and no other plans.

Limits

• Reader routing fixes main-loop blocking, not read latency. The reader is one worker behind a single lock, so a long shell read delays other readOnly reads. The live traces also contain a 14.4 s ShellStream reader transaction, with overlapping reader operations recording 14.2 s and 9.0 s of connection lock wait.
• I started on this because app.t3.codes showed disconnected/reconnecting, which I didn't reproduce with the real client. The installed client pings every 5 s and times out after 15 s without a frame, so an idle connection needs a block over about 10 s; a simulation of that rule timed out in 14 of 25 idle and 0 of 25 active cases across the five real-data first-read writer blocks (11.9–13.5 s), and the 9.8 s live block is too short by itself under that model. These are timing-phase tests, not a measured browser disconnect rate.
• The host was shared with the live app. Benchmark I/O could affect both workloads, and the main first-read configurations have only three samples each.
• #23689's ProjectionStore.ts hunks apply cleanly to f054d1b, but I didn't build or run the patched app.

Takeaway

Routing getShellSnapshot through the reader, as #23689 does, keeps the main loop and the writer free during the read with identical results; event-loop delay stays near its ~13 ms idle level instead of matching the 13 s statement. The #23009 indexes address query cost separately (about 4.9x faster on first read, 1.8x on repeat), but alone they still leave the writer path blocked for about 2.7 s.

Raw results and the harness are retained locally; the snapshot holds private data.

Thank you - this is exactly the measurement the issue needed, and it changed the pull request.

I checked the two mechanisms you cite against the source before crediting them: the writer is a DatabaseSync (packages/shared/src/nodeSqliteClient.ts), and readOnly really is the only route to the reader - route picks the reader solely on the ReadOnly context flag.

Your finding corrected my framing. The issue and the pull request both attributed the stall to the read waiting for the write lock. Your capture shows the delay was inside the synchronous statement, with db.lock_wait_ms = 0.001. Both are true and they are the same root cause, but the second one is broader: the writer runs on the main thread, so it stalls the event loop even when it never waits for a lock. A statement that takes 13 s to run stalls the loop for 13 s regardless of why it took that long. The pull request now says that explicitly, with your 9,841 ms / 0.001 ms capture as the example.

Your before/after table is now in the pull request, credited to you, together with the result-equivalence check (row counts and per-statement SHA-256 across 34 runs). I had the "the writer blocks the event loop" argument from the server's own trace log, but I had no before/after on the same query, and the write counts were the part I had no way to get: 52-54 writes committing during the read on the reader path, zero on the writer path, is the clearest statement of the win I could not make myself.

On the reader-contention caveat - you are right that it is a real limit, and I checked the code to answer it rather than argue it away. Both connections are one-deep semaphores (Semaphore.make(1) for the writer, and again for the reader), and the transaction acquirer holds its permit for the whole transaction. So a read never had a parallel slot on the writer to lose: the writer's queue was already one deep, and it was one deep in both directions, because it carried the writes as well.

That makes the change a move rather than an addition:

• reads that used to wait behind a write now wait behind reads only;
• writes that used to wait behind a read now do not wait at all;
• the main thread is free during the read, which is the point.

The reader's queue is strictly shorter than the writer's was, because writes no longer join it. The other half of your point stands and I have put it in the pull request as a stated limit rather than leaving it implicit: reader routing fixes main-loop blocking, not read latency, so a 13 s shell read still delays other readOnly reads by 13 s. That is what makes #23009's indexes complementary rather than redundant - they cut the read about 4.9x, and this decides where the read runs. On the #23009 indexes alone the writer path is still blocked for about 2.7 s.

One more thing that made me look harder at the shape of the change: the two hot shell paths were already on the reader before it - the HTTP shell stream (loadShellSnapshotParts) and ThreadPullRequestService both route there today. So this makes the routing consistent rather than adding a new consumer of a single-deep reader. That is also where your 14.4 s ShellStream reader transaction comes from.

On your limits - noted, and I have not treated any of it as more than what it is. You did not build or run the patched app, so the pull request's verification is still the test suite and the trace log, not a built binary; the hunk-applies-cleanly check is a good one and I am glad you ran it. The three-sample caveat on the main first-read configurations is fair, and the pull request's table says so by giving the range (13,135-13,496 ms) rather than a single number.

The reproduction, the numbers and the limits are in the pull request: #23689.

Another data point for getShellSnapshot on a large host, on 0.1.2 (macOS). statev2.sqlite is 11.5 GB, with 2,819 threads, 343k turn items and 10.9k runs.

• ProjectionStore.readShellSnapshotRows: median 9.51 s over 177 spans (Oct 23, 09:23–09:55 UTC).
• ProjectionStore.decodeShellSnapshot: median 38 ms over 97 spans. The time goes to the read, not the decode.
• Individual shellSnapshot HTTP requests took 9.8–11.4 s, so a client with a 10 s deadline timed out on them repeatedly. It stayed this slow throughout the ~45 minutes we sampled (09:12–09:55 UTC).
• At the same time, lightweight RPCs stayed fast, and bounded single-thread reads took 3–213 ms.

Another data point on Linux 0.1.3-nightly.20261023.3592 (48 GB statev2.sqlite, about 3,000 unarchived threads, three clients connected). Over 5 minutes sqlite spans totalled 2,386 s, of which 2,205 s was db.lock_wait_ms; the slowest-to-wait statements were point lookups (SELECT payload_json FROM orchestration_v2_projection_nodes WHERE node_id = ?, 546 calls in 2 min, waits up to 24 s). The largest single source of statements was ws.orchestrationV2.projectShellItems (about 1,166 per 3 minutes, each loading a full thread shell through four queries), followed by AgentAwarenessRelay.publishThread. A V8 profile of the server put about 30% of samples inside the synchronous sqlite run call on the main thread and about 15% in child_process.spawn (git).