sql_materialize holds the per-session scratch lock for the whole remote source read, blocking all other scratch work #1485

Problem

While one sql_materialize reads from a slow remote source, every other scratch operation in the session (sql_execute on scratch, sql_check, schema_inspect, another sql_materialize from a different source) queues behind it, including calls from sub-agents.

Mechanism

• sql_materialize runs preflight() and executeSource() against the remote source inside withScratchConnection(...) (packages/opencode/src/altimate/tools/sql-materialize.ts:373-380).
• That callback is exclusive() (packages/opencode/src/altimate/native/connections/scratch.ts:107), a single promise chain per root session shared by all sub-agents (:51-61, :170).
• Only the write phase (BEGIN/CREATE/INSERT/COMMIT, sql-materialize.ts:472-480) needs the lock.
• Reported tool durationMs includes queue time, which also misleads timing analysis.

Evidence

Recorded sessions:

• One session was blocked for 1,464 s: three SQLite reads that take 0.6 s alone, issued from 4 parallel sub-agents, all finished together with a multi-minute remote read.
• Other blocks of 706, 404, 376, 129 and 43 s; 10,753 call-seconds of waiting across 7 sessions, none of which produced a correct answer.
• Up to 96 scratch calls in flight at once, completing in bursts; one-row COUNT(*) queries waited 140-280 s.
• In another set of sessions 824 s of queueing across 59 sessions; a 4-row lookup waited 115 s behind an unrelated materialization; a 6-row aggregate took 48.7 s because it queued behind a 48.3 s materialization; an estimated 370 s of wall time lost in 89 generations.

Impact

Time (and, under a time budget, answers): it prevents fan-out from running in parallel and adds directly to budget pressure. Observed with both Claude Opus and gpt-5.6-sol sessions, worst under sub-agent fan-out.

Proposed fix

• Run preflight and the source read outside the lock (stream to a staging file or buffer); take exclusive() only for the write phase.
• Serve read-only scratch queries on separate DuckDB connections to the same file so reads do not queue behind writes.
• Report queue_ms and exec_ms separately in tool metadata and traces.

Acceptance check

• Test: while a materialize from a deliberately slow source is running, a SELECT 1 on scratch from another sub-agent returns in under 1 s.
• Two concurrent materializations from different sources overlap their read phases.

Related: #1479 (Mongo byte-bounded reads fetched one document per round trip; fix in #1480).