SQLite engines are built without WAL, busy timeout, or explicit write transactions #1542

Summary

Every SQLAlchemy engine the resource manager builds for SQLite uses SQLite's defaults. No journal_mode=WAL, no busy_timeout beyond pysqlite's implicit 5s, and no way to set either from configuration. Measured on an engine built exactly the way database_manager.async_get_sql_engine builds one:

pool class:       AsyncAdaptedQueuePool
PRAGMA journal_mode = delete
PRAGMA busy_timeout = 5000     (pysqlite's default timeout=5.0, not set by us)
PRAGMA synchronous  = 2        (FULL; stays FULL even after switching to WAL)

The only pragma set anywhere in the package is foreign_keys=ON, duplicated in two stores (sqlite_vector_store.py:691-698, sqlalchemy_segment_store.py:779-786).

Impact

• Rollback-journal mode: a writer takes an exclusive lock on the whole file at commit and blocks every reader, in every process. Concurrent access is far slower than WAL would allow, and past the 5s busy timeout callers get OperationalError: database is locked, which nothing classifies or retries.
• No configuration surface: SqlAlchemyConf has no connect_args field, so an operator cannot raise the busy timeout or pass any other DBAPI connect option without editing code.
• synchronous is unchosen: it does not follow journal_mode, so it stays FULL (one fsync per commit) whatever journal mode is set. For stores that are rebuildable derived indexes, NORMAL is the cheaper and defensible choice, but it has to be set explicitly.

Where

Three create_async_engine sites in packages/server/src/memmachine_server/common/resource_manager/database_manager.py:

• async_get_sql_engine (segment store, episode store, session data store)
• async_get_sqlite_vector_store
• async_get_sqlite_vec_vector_store

The first passes pool options only; the latter two build the engine bare.

Suggested fix

Split by whether the DBAPI can express the setting:

• connect_args as a generic config field (no SQLite-only knob): timeout maps to busy_timeout (verified: {"timeout": 30} gives busy_timeout = 30000), and isolation_level=None puts pysqlite in autocommit so SQLAlchemy controls transaction start.
• A shared connect listener for pragmas with no DBAPI equivalent: journal_mode=WAL, synchronous, foreign_keys=ON.

One helper applied at all three sites, a no-op for non-SQLite dialects, which also removes the duplicated foreign_keys listeners.

journal_mode=WAL persists in the database file, so setting it per connection is idempotent.

Not part of this: transaction control

isolation_level=None only enables per-operation control of when a transaction begins; BEGIN IMMEDIATE should stay in the code that needs it, not in the engine. Forcing it globally in a begin listener makes read-only transactions take a write lock: measured, a writer waited 0.47s behind a 0.5s read-only transaction, versus 0.0s without it.

Relatedly, the check-then-insert race that BEGIN IMMEDIATE would paper over is not SQLite-specific and is not a configuration problem. Measured on PostgreSQL 16 under READ COMMITTED, two connections that both SELECT a missing row and then INSERT produce a UniqueViolationError for the loser, exactly as SQLite does; with LOCK TABLE ... IN SHARE ROW EXCLUSIVE MODE both succeed. That belongs to the individual operation (a unique constraint plus ON CONFLICT, an explicit lock, or a scoped immediate transaction), not to engine setup.

Test coverage

There is currently no test that asserts a pragma on a manager-built engine, and no multi-process test anywhere in server_tests. The suite passes identically with and without any of this, which is why the gap is easy to reintroduce. A test that reads PRAGMA journal_mode / busy_timeout off an engine from database_manager would fail for any future bare create_async_engine.

🤖 Written by Claude Code (Opus 5) on behalf of @edwinyyyu.