davide
97545ad91f
create_async_engine had no connect_args and there was no PRAGMA anywhere in the repo. SQLite's default rollback-journal mode lets a writer block every reader for the duration of its transaction, and a second writer arriving while one is already active fails immediately with "database is locked" rather than waiting at all - realistic given five concurrent background tasks (scheduler, confirmation poller, RBF bumper, two reconcilers) plus every HTTP handler share one file, and nothing previously handled that error. app/db/base.py now registers a "connect" event on the engine that sets journal_mode=WAL, synchronous=NORMAL and a 5-second busy_timeout on every new DBAPI connection - applied only when the dialect is sqlite, so a future PostgreSQL DATABASE_URL is unaffected. WAL lets readers and writers proceed without blocking each other, and busy_timeout gives a second writer a real window to wait instead of failing instantly. Left out: an explicit application-level retry wrapper for "database is locked" in the background loops, the other half of the proposed fix - busy_timeout already gives SQLite itself several seconds to resolve writer-vs-writer contention before ever raising, and every background loop already catches and logs an unhandled exception before its next scheduled tick, which is itself a retry, just not an immediate one. Suite grows from 211 to 214 tests. BUGS.md moves B-39 to Previously fixed.
62 lines
2.3 KiB
Python
62 lines
2.3 KiB
Python
"""Regression tests for B-39: SQLite must run in WAL mode with a busy_timeout,
|
|
since this app has five concurrent background tasks plus every HTTP handler
|
|
sharing one database file, and the default rollback-journal mode lets a writer
|
|
block every reader and fails a second writer immediately instead of waiting."""
|
|
|
|
import pytest
|
|
from sqlalchemy.ext.asyncio import create_async_engine
|
|
|
|
from app.db.base import _SQLITE_BUSY_TIMEOUT_MS, _register_sqlite_pragmas
|
|
|
|
|
|
@pytest.fixture
|
|
async def sqlite_engine(tmp_path):
|
|
# WAL needs a real file (it writes a companion -wal/-shm file alongside it) —
|
|
# ":memory:" wouldn't exercise the same path.
|
|
engine = create_async_engine(f"sqlite+aiosqlite:///{tmp_path}/test.db")
|
|
yield engine
|
|
await engine.dispose()
|
|
|
|
|
|
async def _pragma(engine, name: str):
|
|
async with engine.connect() as conn:
|
|
result = await conn.exec_driver_sql(f"PRAGMA {name}")
|
|
return result.fetchone()[0]
|
|
|
|
|
|
async def test_register_sqlite_pragmas_enables_wal_and_busy_timeout(sqlite_engine):
|
|
_register_sqlite_pragmas(sqlite_engine)
|
|
|
|
assert (await _pragma(sqlite_engine, "journal_mode")).lower() == "wal"
|
|
assert await _pragma(sqlite_engine, "busy_timeout") == _SQLITE_BUSY_TIMEOUT_MS
|
|
assert await _pragma(sqlite_engine, "synchronous") == 1 # NORMAL
|
|
|
|
|
|
async def test_register_sqlite_pragmas_applies_to_every_new_connection(sqlite_engine):
|
|
"""The pool can open more than one underlying DBAPI connection over the
|
|
engine's lifetime — the pragmas must be re-applied to each one, not just
|
|
the first, or a later connection would silently fall back to SQLite's
|
|
defaults."""
|
|
_register_sqlite_pragmas(sqlite_engine)
|
|
|
|
async with sqlite_engine.connect() as first:
|
|
await first.exec_driver_sql("PRAGMA journal_mode")
|
|
|
|
async with sqlite_engine.connect() as second:
|
|
result = await second.exec_driver_sql("PRAGMA busy_timeout")
|
|
assert result.fetchone()[0] == _SQLITE_BUSY_TIMEOUT_MS
|
|
|
|
|
|
def test_register_sqlite_pragmas_is_a_noop_for_other_dialects():
|
|
"""Must not touch (or crash on) a non-sqlite engine — e.g. a future
|
|
PostgreSQL DATABASE_URL, which neither needs nor understands these
|
|
pragmas."""
|
|
|
|
class _FakeDialect:
|
|
name = "postgresql"
|
|
|
|
class _FakeEngine:
|
|
dialect = _FakeDialect()
|
|
|
|
_register_sqlite_pragmas(_FakeEngine()) # must not raise
|