## Summary Concurrent reads through `Registry.get(...)` or `DataStore.query_stream(...)` corrupt result sets — the readers in `sdmxlib/storage/readers.py` call `conn.execute(...).fetchone()` / `.fetchall()` directly on the connection passed in by the registry, which is shared across threads. DuckDB serialises a single connection's *execute*, but each new `execute()` clobbers the connection's default-cursor row buffer; concurrent threads racing between `execute()` and `fetchone()` read each other's results. Empirically this surfaces as a mix of: - `IndexError: tuple index out of range` (`readers.py:264`, inside `_read_spine` — the row returned has fewer columns than expected because it's actually a result from a *different* query), - `ValueError: not enough values to unpack (expected 12, got 1)` (`readers.py:1328`, inside `_read_dataflow_binding` — same root cause, different read shape). Severity: **data integrity, not just perf.** Under any concurrency the server can return either a 500 or, worse, a `Codelist`/`DataflowBinding`/etc. silently populated with fields from an unrelated artefact (when both reads happen to yield rows but the wrong ones). ## Reproduction Service: `cpt-sdmx-api` (FastAPI shim over `sdmxlib.FederatedCatalog`). One pod, `workers: 2` uvicorn workers. Catalog opened via `Registry.from_connection(conn)` against a read-only `meta.duckdb`. Load: k6, mixed 70/25/5 structure/data-small/data-large workload, 30 VUs sustained for 90s against the deployed cluster. Result (this run, ~1755 requests): ``` status count % 200 1448 82.5% 500 185 10.5% ← the exceptions below 503 35 2.0% 404 85 4.8% ``` Excerpt from a 500 inside the load window: ```python Traceback (most recent call last): File ".../sdmxlib/local/_registry.py", line 255, in get result = readers.get_artefact(self._c, str(urn), eager=eager) File ".../sdmxlib/storage/readers.py", line 1409, in get_artefact return reader(conn, urn) if reader is not None else None File ".../sdmxlib/storage/readers.py", line 1311, in _read_dataflow_binding meta = _read_spine(conn, urn) File ".../sdmxlib/storage/readers.py", line 264, in _read_spine "agency_id": row[1], ~~~^^^ IndexError: tuple index out of range ``` And the sister error shape: ```python File ".../sdmxlib/storage/readers.py", line 1328, in _read_dataflow_binding ( ^ ...<12 lines>... ) = row ^ ValueError: not enough values to unpack (expected 12, got 1) ``` Both fire on every-request hot paths (each `/v1/data/...` request does `catalog.bindings.get(binding_urn)` early; that walks `Registry.get → readers.get_artefact → _read_dataflow_binding → _read_spine`). 5xx rate climbs predictably with concurrency: | VUs | RPS | 5xx rate | |----:|----:|---------:| | 5 | 2.8 | 1.2% | | 10 | 5.8 | 2.7% | | 20 | 11 | 6.6% | | 40 | 17 | 14.1% | | 60 | 19 | 14.6% | The cap at ~18 RPS is the rate at which DuckDB happens to serialise the shared connection enough that corruption probability holds steady — not a true throughput ceiling. ## Root cause `sdmxlib/local/_registry.py:255`: ```python result = readers.get_artefact(self._c, str(urn), eager=eager) ``` passes `self._c` — a stored cursor/connection — straight to the readers. Every reader in `readers.py` then does: ```python row = conn.execute(SQL, params).fetchone() ``` There's no per-call cursor isolation. Under threaded execution (FastAPI's default sync route handlers run in `anyio.to_thread.run_sync` which uses a thread pool), two threads issuing `conn.execute(...)` in quick succession cause the second `execute()` to overwrite the result the first hasn't fully consumed yet. `DataStore.query_stream` (`sdmxlib/local/_data_store.py:357`) has the same shape: `conn.execute(sql, sql_params).to_arrow_reader(chunk_rows)` on the shared `self.connection`. DuckDB's docs are explicit that concurrent reads need separate cursors: <https://duckdb.org/docs/api/python/dbapi#using-cursor>. ## Suggested fixes Any of these closes the race; (1) is the minimal change: 1. **`Registry.get` opens a per-call cursor** and passes it down to `readers.get_artefact`: ```python cur = self._c.cursor() try: return readers.get_artefact(cur, str(urn), eager=eager) finally: cur.close() ``` Same pattern in `DataStore.query_stream` (use `cur = self.connection.cursor()` before `cur.execute(sql, ...).to_arrow_reader(...)`). 2. **Readers create their own cursor** internally: each `def _read_*(conn, urn)` does `cur = conn.cursor()` at the top, uses `cur.execute(...)` throughout. More invasive (touches every reader) but localises the responsibility to where the unsafe call lives. 3. **A thread-local cursor on `Registry`** managed by a context manager (`with registry.read() as cur:`). Cleaner public API but bigger surface change. Cursors on a read-only DuckDB connection are cheap (no transaction state), so per-call cursor in (1) has negligible overhead. ## Expected impact After fix: 5xx rate should drop to ~0 at 60 VUs (currently 14.6%). The actual throughput ceiling of the service is unknown until this is resolved — current numbers are dominated by failures, not real capacity. ## Environment - sdmxlib 0.27.1 - DuckDB 1.5.x, read-only connection opened via `duckdb.connect(meta_db, read_only=True)` - Registry constructed via `Registry.from_connection(conn)` - FastAPI 0.115 (sync route handlers; runs in `anyio` threadpool) - Python 3.13 - Bundle: `cenprof-2021` (Census Profile), `DF_CP_2021` dataflow - One pod, 2 uvicorn workers per pod (so two `Registry` instances each holding their own connection — each one races within its worker's threadpool) ## Related - #72 — `_resolve_refs` fan-out (fixed in 0.27.1) - #73 — `_read_concept_scheme` annotations N+1 (fixed in 0.27.1) - #74 — `_read_metadata_structure` re-read on every MetadataSet (open)