hermes - 💡(How to fix) Fix SessionDB._lock serializes all concurrent writes across sessions [2 pull requests]

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.