fix(store): busy_timeout set after journal_mode=WAL — unprotected SQLITE_BUSY window on open

[halindrome]

Root Cause

configure_pragmas() in src/store/store.c sets SQLite pragmas in the following order:

// Current (buggy) order:
exec_sql(s, "PRAGMA journal_mode = WAL;");
exec_sql(s, "PRAGMA synchronous = NORMAL;");
exec_sql(s, "PRAGMA busy_timeout = 10000;");

sqlite3_open_v2 returns a live connection handle with SQLite's default zero busy timeout. configure_pragmas runs after open_v2 returns. Any lock contention in the window between open_v2 and the PRAGMA busy_timeout execution causes an immediate SQLITE_BUSY failure — no retry, no wait.

Additionally, PRAGMA journal_mode = WAL itself must acquire an exclusive lock to perform the WAL mode transition. With busy_timeout not yet set at that point, a concurrent reader holding a shared lock causes the WAL transition to fail immediately.

Reproduction Scenario

Two processes accessing the same project database simultaneously:

1. MCP server running (holds shared read lock on the project .db via an open store handle)
2. CLI invocation: codebase-memory-mcp cli search_graph '{"repo_path":"..."}'

The CLI calls store_open_internal → sqlite3_open_v2 → configure_pragmas. When configure_pragmas reaches PRAGMA journal_mode = WAL, the connection has zero timeout. If the MCP server holds a lock at that instant, the WAL pragma fails with SQLITE_BUSY immediately instead of waiting 10 seconds.

Fix

Reorder the pragmas so busy_timeout is set first:

// Fixed order:
exec_sql(s, "PRAGMA busy_timeout = 10000;");
exec_sql(s, "PRAGMA journal_mode = WAL;");
exec_sql(s, "PRAGMA synchronous = NORMAL;");

This ensures the connection has a 10-second retry window before journal_mode=WAL attempts to acquire its exclusive lock, and closes the open-to-first-pragma window entirely.

Relationship to Prior Issues

• Issue Server hangs and requires SIGKILL after list_projects on large project (SQLite lock contention) #52 (closed 2026-03-20): same WAL hang symptom, but root cause was a Go ref-counting race in the Go v0.4.8 server. The Go fix increased busy_timeout from 5s to 10s — it did not reorder pragmas. The C rewrite eliminated the ref-counting race but the ordering hazard was not addressed.
• PR fix(store): keep WAL journal mode during bulk write to prevent DB corruption on crash #72 (merged 2026-03-20): WAL preservation during bulk writes — orthogonal to this issue.

A PR with the fix is attached to this issue.

[DeusData]

PR #117 fixes this by reordering the pragma execution.