SessionDB._lock serializes all concurrent writes across sessions

[leiyikang-tech]

Performance: SessionDB._lock Serializes All Concurrent Writes

Severity: MEDIUM
File: hermes_state.py:336
Affected versions: v0.14.0 (and likely all prior)

Problem

All session SQLite writes share a single threading.Lock (self._lock). N concurrent sessions have fully serialized writes. Current config: 1s SQLite timeout × 15 jitter retries.

Contention Chain

Thread A: acquire _lock → BEGIN IMMEDIATE → write → commit → release _lock
Thread B: blocked on _lock → ... → acquire → BEGIN IMMEDIATE → write → commit → release

Trigger Condition

• Large concurrent sessions (>20) writing long conversation transcripts simultaneously
• Single write takes long (large transcript INSERT)

Suggested Fixes

• Option A: Per-thread SQLite connections (already check_same_thread=False, but currently sharing single connection)
• Option B: Connection pool (needs aiosqlite or custom pool since sqlite3 doesn't natively support)
• Option C: Batch writes (accumulate messages, INSERT in bulk, reducing lock acquisitions)

Current Impact

At current scale (10 profiles, few concurrent sessions per profile), writes complete in <1ms, contention is negligible. This becomes a bottleneck at higher concurrency.

[teknium1]

Thanks for the careful write-up — the analysis is correct as far as it goes, but closing as won't-fix. A few things make the suggested rewrite disproportionate to the impact:

The lock is load-bearing for correctness, not just write serialization. SessionDB holds a single shared sqlite3.Connection (self._conn, hermes_state.py:384). A Connection is not safe for concurrent cross-thread use even with check_same_thread=False — concurrent .execute() against the shared connection/cursor state can corrupt or crash. self._lock is what keeps that single connection correct, and it guards reads as well as writes (e.g. get_session at L1119). So this isn't a gratuitous write serializer that can be narrowed; removing or splitting it means re-architecting connection ownership across the whole module.

WAL already caps writes at one-writer anyway. We run WAL mode (concurrent readers + a single writer at the SQLite level). Even a perfect per-thread-connection or pool rewrite (Options A/B) would not let two sessions write simultaneously — SQLite permits exactly one writer regardless of the Python lock. The win would be limited to letting reads run concurrently with a write, which the current 1s timeout × 15-retry jittered backoff already absorbs without user-visible latency.

Negligible at any realistic scale. As you noted, single writes complete in <1ms and contention is negligible at current concurrency. The trigger condition (>20 concurrent sessions writing large transcripts at once) isn't something we hit, and the per-connection rewrite would have to stay correct under WAL + the NFS/SMB journal_mode=DELETE fallback + per-process passive checkpointing — real complexity for a P3/cosmetic gain.

If we ever see write contention show up in practice, Option C (batch INSERTs to reduce lock acquisitions) is the lowest-risk lever and we'd revisit then. Appreciate the contention-chain breakdown either way.