Skip to content

Commit 9faa80d

Browse files
committed
fix(sqlite): isolate transactions per request
1 parent 8f5ae3b commit 9faa80d

15 files changed

Lines changed: 1296 additions & 52 deletions

‎CHANGELOG.md‎

Lines changed: 22 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -6,6 +6,28 @@ adheres to [Semantic Versioning](https://semver.org/).
66

77
## [Unreleased]
88

9+
### Fixed
10+
11+
- **SQLite requests no longer share transaction ownership across concurrent
12+
MCP workers (HC-CORTEX-002).** The fallback store previously exposed one
13+
process-wide connection with `check_same_thread=False`, so an acknowledged
14+
request could commit unfinished writes from a rejected request. Each
15+
active execution thread now owns the native connection it uses, while the
16+
handler boundary tracks exact handles propagated through
17+
`asyncio.to_thread`, rolls back
18+
unfinished request work, rejects false success, and quarantines a connection
19+
when rollback fails. An unrecoverable rollback failure on the in-memory
20+
lifetime anchor now invalidates the registry instead of silently exposing a
21+
fresh empty database. Non-anchor request handles are released after
22+
finalization, and relative database paths are frozen at construction so
23+
worker opens cannot drift after a process `cwd` change. Nested request scopes
24+
fail closed. Deterministic regressions cover the original supersede/insert
25+
store-path interference, reused workers, nested offload, unnamed handlers,
26+
cleanup failure, close/reopen persistence, FTS, vectors, foreign keys, and
27+
integrity checks. The
28+
fresh-process, cross-backend load ladder is tracked separately and is not
29+
claimed by this correction.
30+
931
## [4.18.0] - 2026-08-25
1032

1133
### Security
Lines changed: 230 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,230 @@
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.
Lines changed: 17 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,17 @@
1+
"""Backend transaction finalization at the MCP handler boundary."""
2+
3+
from __future__ import annotations
4+
5+
from collections.abc import Iterator
6+
from contextlib import contextmanager
7+
8+
from mcp_server.infrastructure.sqlite_request_scope import (
9+
sqlite_request_scope,
10+
)
11+
12+
13+
@contextmanager
14+
def handler_transaction_scope() -> Iterator[None]:
15+
"""Reject or roll back unfinished SQLite work at the handler boundary."""
16+
with sqlite_request_scope():
17+
yield

‎mcp_server/infrastructure/sqlite_compat.py‎

Lines changed: 24 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -21,7 +21,7 @@
2121

2222
import sqlite3
2323
from datetime import datetime
24-
from typing import Any
24+
from typing import Any, Protocol
2525

2626
from mcp_server.infrastructure.sqlite_sql_translate import (
2727
_returning_was_stripped,
@@ -65,6 +65,28 @@ def _adapt_datetime_iso(value: datetime) -> str:
6565
sqlite3.register_adapter(datetime, _adapt_datetime_iso)
6666

6767

68+
class SqliteConnectionLike(Protocol):
69+
"""Native or thread-local connection surface used by the SQL adapter."""
70+
71+
row_factory: Any
72+
73+
def execute(self, sql: str, parameters: Any = (), /) -> sqlite3.Cursor: ...
74+
75+
def executemany(self, sql: str, parameters: Any, /) -> sqlite3.Cursor: ...
76+
77+
def cursor(self, /) -> sqlite3.Cursor: ...
78+
79+
def executescript(self, sql: str, /) -> Any: ...
80+
81+
def commit(self) -> None: ...
82+
83+
def rollback(self) -> None: ...
84+
85+
def close(self) -> None: ...
86+
87+
def enable_load_extension(self, enabled: bool) -> None: ...
88+
89+
6890
class _CompatCursor:
6991
"""Wraps a sqlite3.Cursor to mimic psycopg result access."""
7092

@@ -169,7 +191,7 @@ class PsycopgCompatConnection:
169191
will work transparently with this wrapper.
170192
"""
171193

172-
def __init__(self, conn: sqlite3.Connection) -> None:
194+
def __init__(self, conn: SqliteConnectionLike) -> None:
173195
self._real = conn
174196

175197
def execute(

0 commit comments

Comments
 (0)