Root cause of the test_sync_entity_circular_relations CI failure (#940,
len(entity_b.outgoing_relations) == 0): the in-memory SQLite URL
(sqlite+aiosqlite://) falls back to SQLAlchemy's StaticPool, which hands the
same DBAPI connection to every concurrently checked-out session. Concurrent
asyncio tasks therefore share one SQLite transaction scope. A rollback issued
through one session — scoped_session's exception handler, or the pool's
reset-on-return at connection checkin (~40 real ROLLBACKs per sync, measured)
— also rolls back any other task's executed-but-uncommitted statements.
During batch indexing, per-file tasks run concurrently (asyncio.gather bounded
by index_entity_max_concurrent). If a sibling session's checkin ROLLBACK lands
between one task's relation INSERT and its COMMIT, the relation row is silently
erased: no error is raised, the sync reports success. The final sync-level
resolve_relations() pass cannot heal this because it only re-queries rows with
to_id IS NULL — the destroyed INSERT leaves no row at all.
Fix: give MEMORY-type engines a single-connection AsyncAdaptedQueuePool
(pool_size=1, max_overflow=0). The lone connection keeps the in-memory
database alive for the engine's lifetime (the reason StaticPool was used),
while the blocking checkout serializes sessions at transaction granularity,
restoring the isolation the repositories assume. File-based SQLite and
Postgres engines are unchanged; production never uses MEMORY engines.
The regression test pins the invariant directly: a session that rolls back in
one task must never destroy another task's uncommitted writes. It fails
deterministically against StaticPool and passes with the serialized pool.
tests/repository/test_entity_repository.py held a scoped session open while
calling a repository method that opens its own session — tolerated on a shared
connection, a deadlock under a serialized pool — so the nested call moved out
of the session block.
Refs #940
Co-Authored-By: Claude Fable 5 <noreply@anthropic.com>
Signed-off-by: phernandez <paul@basicmachines.co>
phernandez