Fixed a 5-bug concurrency cascade in my SQLite-backed SDK — went from silent failures to 50 workers, 0 errors in under 5s
Building StreamCtx (open-source Python SDK for LLM agent observability) and spent the last few days chasing what I thought was one SQLite locking issue. Turned out to be five separate bugs stacked on top of each other.
The cascade, in the order I found them:
- WAL mode wasn't enabled — obvious first fix, but nowhere near enough on its own
- A storage "singleton" that wasn't actually being reused across connections — each call was silently opening a fresh one
- A connection leak:
with self._connect() as conn:looks like it closes the connection, but sqlite3's context manager only manages the transaction, not the connection itself - Reads and writes competing for the same connection pool under load
- Missing indexes on the column every query was filtering by
Fixed all five, then load tested with 50 concurrent workers. Before: sporadic failures under load. After: 50 workers, 0 errors, done in under 5 seconds — and I re-verified this on the actual published PyPI package, not just the local dev version, since I've been burned before by "works on my machine" claims in my own docs.
The annoying part: none of these bugs are exotic. Every one of them is "well known" if you already know to look for it. But the standard advice online stops at "enable WAL mode," which is necessary but not sufficient, and the docs don't really do a good job flagging the connection-closing gotcha.
Wrote up the honest before/after in the README rather than just claiming it's fixed — figured that's worth more than a badge.
Repo's open source if anyone wants to see the actual fix: github.com/streamctx/streamctx
Bug #3 is the one that gets people the most, and it's worth calling out explicitly:
with self._connect() as conn:looking like connection cleanup is a Python-specific trap because most other context-manager patterns (files, locks, most DB drivers) DO close the resource on exit. sqlite3's Connection object breaks that convention -__exit__commits or rolls back the transaction, but the connection itself stays open until you call.close()explicitly. It's documented, but it's the kind of thing you only really internalize after getting bitten by it once, because the code reads like it should be safe.On #4 (reads/writes competing for the connection pool under load): did you end up separating read and write connections, or handling it with a single writer + WAL's concurrent-reader model? WAL is supposed to let readers proceed without blocking on a writer, so if you were still seeing contention after enabling WAL, that's usually a sign something else was serializing access underneath - possibly tied to bug #2's singleton issue if a shared connection was being used across threads without per-thread connections. Curious if that was the actual root cause once you traced it.
The fixes are impressive, but the stronger insight is about trust. Observability tools don't earn adoption because they collect more telemetry—they earn it because developers believe the tool itself won't become another source of uncertainty. I'd keep validating whether StreamCtx's advantage is richer observability or engineering reliability people can confidently build production workflows around.
Re-verifying the published PyPI package is the bit most fixes miss. I'd add one regression test that repeatedly creates and tears down the SDK from separate processes, because the fake singleton and context-manager leak are lifecycle bugs, not just load bugs. Fifty workers proves the steady state; install-and-restart cycles prove the boundary.