|
| 1 | +# ADR-0055: Request-Scoped SQLite Connections With Thread Ownership |
| 2 | + |
| 3 | +**Status:** Proposed |
| 4 | +**Date:** 2026-08-30 |
| 5 | +**Decision-makers:** cdeust |
| 6 | +**Related:** HC-CORTEX-002; ADR-0045 R6; I10 |
| 7 | + |
| 8 | +## Context |
| 9 | + |
| 10 | +`SqliteMemoryStore` owns one `sqlite3.Connection` configured with |
| 11 | +`check_same_thread=False`. `safe_handler` executes concurrent MCP calls on |
| 12 | +worker threads, so unrelated operations share one implicit transaction. |
| 13 | + |
| 14 | +The HC-CORTEX-002 baseline at Cortex revision |
| 15 | +`8f5ae3b87b6969f3abcb3736859febfdab69304a` injected a failure after a |
| 16 | +supersession insert and compare-and-set. A concurrent acknowledged insert |
| 17 | +committed the shared connection before the rejected supersession rolled back. |
| 18 | +The rejected memory, its FTS row, and its `superseded_by_id` edge remained, |
| 19 | +while `PRAGMA integrity_check` still returned `ok`. |
| 20 | + |
| 21 | +A second RED fixture rejected an `insert_memory` after its first write, then |
| 22 | +ran an unrelated acknowledged request on the same reused worker. A connection |
| 23 | +per thread alone still let the second request commit the first request's dirty |
| 24 | +transaction. Thread ownership is therefore necessary but not sufficient: the |
| 25 | +request boundary must finalize unfinished work before a worker is reused. |
| 26 | + |
| 27 | +This is the documented SQLite boundary, not a damaged database file: |
| 28 | + |
| 29 | +- Python's `sqlite3` documentation says that disabling `check_same_thread` |
| 30 | + permits cross-thread access but requires the caller to serialize writes to |
| 31 | + avoid corruption: <https://docs.python.org/3/library/sqlite3.html#sqlite3.connect>. |
| 32 | +- SQLite provides isolation between separate connections and explicitly no |
| 33 | + isolation between operations on the same connection: |
| 34 | + <https://www.sqlite.org/isolation.html>. |
| 35 | +- Python exposes `Connection.in_transaction` as the read-only view of SQLite's |
| 36 | + low-level autocommit state, and documents `rollback()` as reverting a pending |
| 37 | + transaction: <https://docs.python.org/3/library/sqlite3.html#sqlite3.Connection.in_transaction>. |
| 38 | +- WAL permits simultaneous readers and a single serialized writer across |
| 39 | + separate connections: <https://www.sqlite.org/isolation.html#isolation_and_concurrency>. |
| 40 | + |
| 41 | +## Decision |
| 42 | + |
| 43 | +The SQLite adapter and handler boundary will enforce both ownership levels: |
| 44 | + |
| 45 | +1. Each execution thread lazily receives one native connection to the same |
| 46 | + database file. The on-disk path is resolved once at registry construction, |
| 47 | + so a later process `cwd` change cannot redirect worker handles. |
| 48 | +2. Every `safe_handler` path is offloaded through `asyncio.to_thread`; an |
| 49 | + optional tool name controls admission and named metrics, not execution or |
| 50 | + isolation. The request installs a thread-safe `ContextVar` state that |
| 51 | + records the exact registry/connection identities touched by the handler |
| 52 | + and any nested `to_thread` calls. Python documents that `to_thread` copies |
| 53 | + the current context into its worker: |
| 54 | + <https://docs.python.org/3/library/asyncio-task.html#asyncio.to_thread>. |
| 55 | +3. On an exception, the boundary rolls back every pending connection recorded |
| 56 | + for that request. If a handler tries to return success with pending work, |
| 57 | + the boundary rolls it back and raises |
| 58 | + `UncommittedSqliteTransactionError`; it cannot emit a false |
| 59 | + acknowledgement. A rollback failure quarantines and closes that native |
| 60 | + connection so later work cannot commit its unknown state. The original |
| 61 | + handler exception remains the reported failure. |
| 62 | + After finalization, every non-anchor request-opened handle is closed and |
| 63 | + removed from the registry. While healthy, one anchor remains until |
| 64 | + store shutdown so a named in-memory database cannot disappear between |
| 65 | + requests. If rollback itself fails on that last in-memory keeper, the |
| 66 | + registry becomes explicitly unusable; it never silently reopens an empty |
| 67 | + database as recovered state. |
| 68 | +4. Nested handler transaction scopes are rejected before the inner handler |
| 69 | + starts. This change does not implement savepoints and therefore does not |
| 70 | + pretend that an inner request can independently commit or roll back. |
| 71 | +5. The primary on-disk connection requests WAL once and records a warning if |
| 72 | + SQLite retains another mode; the benchmark records the observed mode. |
| 73 | + Every connection enables foreign keys and loads |
| 74 | + `sqlite-vec` when vector support was enabled by the primary connection. |
| 75 | + SQLite documents both WAL persistence and the in-memory `MEMORY`/`OFF` |
| 76 | + restriction: <https://www.sqlite.org/pragma.html#pragma_journal_mode>. |
| 77 | +6. The existing `_raw_conn` and psycopg-compatible `_conn` surfaces remain |
| 78 | + stable proxies, so the existing store/mixin contract does not gain backend |
| 79 | + or thread branching. |
| 80 | +7. Store shutdown, after requests are quiescent, closes every connection |
| 81 | + registered by the process. `check_same_thread=False` remains intentional |
| 82 | + only so that shutdown can close worker-owned handles centrally; normal SQL |
| 83 | + operations are routed to their owner by the proxy. |
| 84 | +8. Direct `SqliteMemoryStore(":memory:")` fixtures use a uniquely named |
| 85 | + in-memory URI so worker connections observe the same ephemeral database. |
| 86 | + They use SQLite's `MEMORY` journal mode because WAL is unavailable for an |
| 87 | + in-memory database. This is the only shared-cache use. It is test-only; |
| 88 | + production/plugin construction supplies the configured on-disk fallback |
| 89 | + path. SQLite discourages shared cache for production and recommends WAL: |
| 90 | + <https://sqlite.org/sharedcache.html#use_of_shared_cache_is_discouraged>. |
| 91 | + |
| 92 | +Transaction ownership follows the connection that performed the operation, |
| 93 | +and request finalization prevents a dirty transaction from crossing a worker |
| 94 | +reuse boundary. A commit or rollback from another request cannot affect it. |
| 95 | + |
| 96 | +## Options Considered |
| 97 | + |
| 98 | +### A. Global handler semaphore |
| 99 | + |
| 100 | +| Dimension | Assessment | |
| 101 | +|---|---| |
| 102 | +| Complexity | Low | |
| 103 | +| Integrity | Incomplete | |
| 104 | +| Scalability | Poor | |
| 105 | + |
| 106 | +Serializing every SQLite-backed MCP handler would close the demonstrated tool |
| 107 | +race, but direct store callers and any internal concurrency would remain |
| 108 | +unsafe. It would also serialize read-side ranking work that does not need the |
| 109 | +writer lock. Rejected because the invariant belongs at the storage boundary. |
| 110 | + |
| 111 | +### B. Transaction-aware lock around the shared connection |
| 112 | + |
| 113 | +| Dimension | Assessment | |
| 114 | +|---|---| |
| 115 | +| Complexity | Medium | |
| 116 | +| Integrity | Sensitive to every error path | |
| 117 | +| Scalability | One connection | |
| 118 | + |
| 119 | +A re-entrant lock could be retained from the first implicit write until |
| 120 | +`commit` or `rollback`. The lock would have to infer transaction ownership, |
| 121 | +survive cursor and raw-connection paths, and release correctly after every |
| 122 | +exception. SQLite would still define same-connection read/write interleaving |
| 123 | +as non-isolated. Rejected as a second transaction manager layered over |
| 124 | +SQLite's own. |
| 125 | + |
| 126 | +### C. Request-scoped worker connections plus one lifetime anchor |
| 127 | + |
| 128 | +| Dimension | Assessment | |
| 129 | +|---|---| |
| 130 | +| Complexity | Medium | |
| 131 | +| Integrity | Native SQLite isolation | |
| 132 | +| Scalability | Concurrent readers; one SQLite writer | |
| 133 | + |
| 134 | +Accepted. Separate connections use SQLite's documented isolation unit, while |
| 135 | +the handler scope closes the reused-worker gap demonstrated by the second RED |
| 136 | +fixture and releases its non-anchor handles after finalization. The lifetime |
| 137 | +anchor preserves the named in-memory fixture. Existing multi-statement methods |
| 138 | +keep one stable connection for their complete call. |
| 139 | + |
| 140 | +### D. Autocommit plus explicit unit-of-work contexts everywhere |
| 141 | + |
| 142 | +| Dimension | Assessment | |
| 143 | +|---|---| |
| 144 | +| Complexity | High | |
| 145 | +| Integrity | Explicit | |
| 146 | +| Migration risk | High | |
| 147 | + |
| 148 | +This would require auditing and rewriting every multi-statement method across |
| 149 | +the store and shared mixins. It may be useful for a later typed unit-of-work |
| 150 | +API, but it is not the smallest correction for the proven ownership defect. |
| 151 | + |
| 152 | +## Consequences |
| 153 | + |
| 154 | +- Unfinished work on a connection recorded by the request scope cannot be |
| 155 | + committed or rolled back by another worker. |
| 156 | +- A failed request cannot leave recorded, uncommitted writes for a later |
| 157 | + request to commit, and a successful response cannot acknowledge a recorded, |
| 158 | + unfinished transaction. |
| 159 | +- When the file is actually in WAL mode, WAL can serve readers while SQLite |
| 160 | + serializes the sole writer. The requested and observed modes are distinct |
| 161 | + benchmark evidence. |
| 162 | +- The first SQLite call in a request pays one connection-open cost; later calls |
| 163 | + on that request's worker reuse the handle. Non-anchor request handles are |
| 164 | + then released, so inactive per-request executors do not accumulate |
| 165 | + connections. |
| 166 | +- Concurrent writers may queue or return SQLite's documented busy result. |
| 167 | + The HC-CORTEX-002 load ladder must publish error/retry and saturation data; |
| 168 | + this ADR does not invent a throughput threshold. |
| 169 | +- On the current `safe_handler` host path, retained connection count returns to |
| 170 | + the construction-time anchor after requests become quiescent; peak handles |
| 171 | + follow the active request workers. Direct applications outside a request |
| 172 | + scope that create arbitrary ephemeral threads retain those handles until |
| 173 | + `SqliteMemoryStore.close()`; reclamation outside the host path is not claimed. |
| 174 | +- Shutdown assumes the store is quiescent; closing a handle while a request is |
| 175 | + using it remains outside the lifecycle contract. |
| 176 | +- A rollback failure on the in-memory anchor is unrecoverable without a durable |
| 177 | + backing file. The triggering handler keeps its original error, and every |
| 178 | + later registry access fails explicitly until the store is closed. |
| 179 | +- The request boundary does not undo writes that a store method has already |
| 180 | + committed before a later method in the same handler fails. Handler-wide |
| 181 | + atomic units of work require a separate explicit transaction API and are |
| 182 | + outside this correction. HC-CORTEX-002 claims only that unfinished work from |
| 183 | + a rejected request cannot cross a request/worker boundary or be finalized by |
| 184 | + an unrelated request. |
| 185 | +- Cancelling the coroutine awaiting `asyncio.to_thread` does not stop the |
| 186 | + native worker. A cancelled or transport-lost call is therefore |
| 187 | + `indeterminate`, not a rejected operation: the benchmark must wait for |
| 188 | + worker quiescence and reconcile its operation identifier against storage |
| 189 | + before assigning an outcome. This ADR does not claim cancellable |
| 190 | + handler-wide atomicity. |
| 191 | +- `ContextVar` propagation is guaranteed for `asyncio.to_thread`, not for an |
| 192 | + arbitrary manually-created `threading.Thread`, and a child task that outlives |
| 193 | + its handler is outside the quiescent request contract. |
| 194 | +- The in-memory compatibility path uses SQLite's documented named-memory |
| 195 | + shared-cache mechanism only because separate `:memory:` opens otherwise |
| 196 | + create different databases: <https://www.sqlite.org/inmemorydb.html>. |
| 197 | + |
| 198 | +## Verification |
| 199 | + |
| 200 | +- Preserve the deterministic rejected-supersede/concurrent-insert ledger as a |
| 201 | + regression: rejected row/FTS/edge count is zero; acknowledged insert count is |
| 202 | + one. |
| 203 | +- Preserve the failed-request/acknowledged-next-request fixture on one reused |
| 204 | + worker, plus a fixture proving that apparent success with pending work is |
| 205 | + rejected and rolled back. |
| 206 | +- Prove nested `to_thread` work is finalized through its exact native handle, |
| 207 | + nested handler scopes fail before inner work, and concurrent unnamed |
| 208 | + `safe_handler` calls do not share the event-loop thread's connection. |
| 209 | +- Force rollback cleanup to fail and prove the connection is quarantined, the |
| 210 | + original handler error is preserved, and a later acknowledged request opens |
| 211 | + a clean on-disk replacement. Prove the equivalent in-memory anchor failure |
| 212 | + invalidates the registry instead of exposing a fresh empty database. |
| 213 | +- Verify the same fixture after closing and reopening the database. |
| 214 | +- Verify worker handles cannot be reused after registry close, future worker |
| 215 | + handles receive `sqlite-vec`, and observed journal modes are `wal` for the |
| 216 | + file fixture and `memory` for the in-memory fixture. |
| 217 | +- Repeat direct and nested-worker requests and prove registry cardinality |
| 218 | + returns to its pre-request value after each one. |
| 219 | +- Construct from a relative on-disk path, change process `cwd`, and prove a |
| 220 | + later worker still opens the original database and creates no second file. |
| 221 | +- Run `PRAGMA integrity_check` and `PRAGMA foreign_key_check`. |
| 222 | +- Prove two worker threads receive isolated transaction outcomes on both an |
| 223 | + on-disk fixture and the in-memory compatibility path. |
| 224 | +- Run the preregistered concurrency ladder twice and publish throughput, |
| 225 | + latency percentiles, queueing/busy errors, retries, resources, and recovery. |
| 226 | +- Do not count a client cancellation or transport timeout as a rejection. |
| 227 | + Record it as `indeterminate`, wait for worker quiescence, and reconcile it |
| 228 | + before closing the store or publishing the cell. |
| 229 | +- Run the matched PostgreSQL reference cell; PostgreSQL storage code is |
| 230 | + unchanged. |
0 commit comments