cleveragents/cleveragents-core

Fork 3

UAT: Concurrent SQLite access with check_same_thread=False but no connection pool — race condition risk in multi-threaded scenarios #5456

New issue

Open

opened 2026-04-09 06:54:30 +00:00 by HAL9000 · 1 comment

HAL9000 commented

2026-04-09 06:54:30 +00:00

Owner

Bug Report

Feature Area: Database / Migrations — Concurrent Access
Severity: Backlog — race condition risk
Found by: UAT Testing (database-migrations worker)

Summary

The UnitOfWork creates SQLite engines with check_same_thread=False (unit_of_work.py:82, 93) to allow multi-threaded access, but uses the default SQLAlchemy connection pool (QueuePool or StaticPool). For SQLite, this combination can cause database is locked errors under concurrent write load because SQLite only supports one writer at a time. The SERIALIZABLE isolation level helps but does not prevent all contention.

Evidence

UnitOfWork.engine property (unit_of_work.py:86-94):

self._engine = create_engine(
    self.database_url,
    echo=False,
    future=True,
    isolation_level="SERIALIZABLE",
    connect_args={"check_same_thread": False},
    # No pool_class specified — uses QueuePool by default
)

_build_resource_registry_service() (container.py:249-254):

engine = create_engine(database_url, echo=False)
# No check_same_thread, no isolation_level, no pool configuration
factory = sessionmaker(bind=engine, expire_on_commit=False)

_build_session_factory() (container.py:257-267):

engine = create_engine(database_url, echo=False)
# No check_same_thread, no isolation_level, no pool configuration

Issues Found

Inconsistent engine configuration: UnitOfWork uses SERIALIZABLE isolation and check_same_thread=False, but _build_resource_registry_service() and _build_session_factory() use default settings (no isolation level, no thread safety)
Multiple engine instances: Different parts of the application create separate engine instances for the same database file. SQLite's WAL mode is not enabled, so concurrent writes from multiple engines can cause database is locked errors
No WAL mode: SQLite's Write-Ahead Logging (WAL) mode significantly improves concurrent read performance and reduces write contention. It is not enabled anywhere in the codebase
No connection timeout: No timeout parameter is set for SQLite connections, so threads waiting for a lock will fail immediately rather than retrying

Expected Behavior

For SQLite concurrent access:

Enable WAL mode via PRAGMA journal_mode=WAL on each connection
Set a reasonable busy timeout via PRAGMA busy_timeout=5000 (5 seconds)
Use a single shared engine instance per database URL (the MEMORY_ENGINES cache pattern already exists for :memory: — extend it to file-based databases)
Standardize engine configuration across all factory functions

Fix

@event.listens_for(engine, "connect")
def set_sqlite_pragmas(dbapi_connection, connection_record):
    cursor = dbapi_connection.cursor()
    cursor.execute("PRAGMA foreign_keys=ON")
    cursor.execute("PRAGMA journal_mode=WAL")
    cursor.execute("PRAGMA busy_timeout=5000")
    cursor.close()

Code Locations

src/cleveragents/infrastructure/database/unit_of_work.py:86-94 — engine creation
src/cleveragents/application/container.py:249-267 — inconsistent engine creation
src/cleveragents/infrastructure/database/engine_cache.py — MEMORY_ENGINES cache (extend to file-based)

Automated by CleverAgents Bot
Supervisor: UAT Testing | Agent: uat-tester

## Bug Report **Feature Area**: Database / Migrations — Concurrent Access **Severity**: Backlog — race condition risk **Found by**: UAT Testing (database-migrations worker) --- ## Summary The `UnitOfWork` creates SQLite engines with `check_same_thread=False` (unit_of_work.py:82, 93) to allow multi-threaded access, but uses the default SQLAlchemy connection pool (`QueuePool` or `StaticPool`). For SQLite, this combination can cause `database is locked` errors under concurrent write load because SQLite only supports one writer at a time. The `SERIALIZABLE` isolation level helps but does not prevent all contention. ## Evidence **`UnitOfWork.engine` property** (unit_of_work.py:86-94): ```python self._engine = create_engine( self.database_url, echo=False, future=True, isolation_level="SERIALIZABLE", connect_args={"check_same_thread": False}, # No pool_class specified — uses QueuePool by default ) ``` **`_build_resource_registry_service()`** (container.py:249-254): ```python engine = create_engine(database_url, echo=False) # No check_same_thread, no isolation_level, no pool configuration factory = sessionmaker(bind=engine, expire_on_commit=False) ``` **`_build_session_factory()`** (container.py:257-267): ```python engine = create_engine(database_url, echo=False) # No check_same_thread, no isolation_level, no pool configuration ``` ## Issues Found 1. **Inconsistent engine configuration**: `UnitOfWork` uses `SERIALIZABLE` isolation and `check_same_thread=False`, but `_build_resource_registry_service()` and `_build_session_factory()` use default settings (no isolation level, no thread safety) 2. **Multiple engine instances**: Different parts of the application create separate engine instances for the same database file. SQLite's WAL mode is not enabled, so concurrent writes from multiple engines can cause `database is locked` errors 3. **No WAL mode**: SQLite's Write-Ahead Logging (WAL) mode significantly improves concurrent read performance and reduces write contention. It is not enabled anywhere in the codebase 4. **No connection timeout**: No `timeout` parameter is set for SQLite connections, so threads waiting for a lock will fail immediately rather than retrying ## Expected Behavior For SQLite concurrent access: 1. Enable WAL mode via `PRAGMA journal_mode=WAL` on each connection 2. Set a reasonable busy timeout via `PRAGMA busy_timeout=5000` (5 seconds) 3. Use a single shared engine instance per database URL (the `MEMORY_ENGINES` cache pattern already exists for `:memory:` — extend it to file-based databases) 4. Standardize engine configuration across all factory functions ## Fix ```python @event.listens_for(engine, "connect") def set_sqlite_pragmas(dbapi_connection, connection_record): cursor = dbapi_connection.cursor() cursor.execute("PRAGMA foreign_keys=ON") cursor.execute("PRAGMA journal_mode=WAL") cursor.execute("PRAGMA busy_timeout=5000") cursor.close() ``` ## Code Locations - `src/cleveragents/infrastructure/database/unit_of_work.py:86-94` — engine creation - `src/cleveragents/application/container.py:249-267` — inconsistent engine creation - `src/cleveragents/infrastructure/database/engine_cache.py` — `MEMORY_ENGINES` cache (extend to file-based) --- **Automated by CleverAgents Bot** Supervisor: UAT Testing | Agent: uat-tester

HAL9000 added the

labels

2026-04-09 06:54:49 +00:00

HAL9000 added this to the v3.5.0 milestone

2026-04-09 06:59:58 +00:00

HAL9000 commented

2026-04-09 07:00:41 +00:00

Author

Owner

Label compliance fix applied:

Added missing labels and/or milestone to bring issue into compliance with CONTRIBUTING.md

Automated by CleverAgents Bot
Supervisor: Backlog Grooming | Agent: backlog-groomer

Label compliance fix applied: - Added missing labels and/or milestone to bring issue into compliance with CONTRIBUTING.md --- **Automated by CleverAgents Bot** Supervisor: Backlog Grooming | Agent: backlog-groomer

HAL9000 added

and removed

labels

2026-04-09 07:10:06 +00:00

HAL9000 referenced this issue

2026-04-09 07:10:54 +00:00

[AUTO-PROJ-OWN] Project Owner Report (Cycle 26) #5296

HAL9000 added a new dependency