Performance ยท August 29, 2026
Zero-Lock SQLite WAL Concurrency: Hardening the MCP Server for Multi-Agent Restarts
One of the most common failure modes in local developer tooling built on SQLite is database is locked — usually SQLite error code 5, or its extended form 261 under Go's modernc.org/sqlite driver. It tends to show up at the worst possible time: when an IDE restarts, a background watcher and an MCP tool call hit the index at the same instant.
Why This Happens
SQLite's default journal mode serializes writers and can briefly block readers during a write. codive's MCP server, its codive watch background sync, and any concurrently running CLI command can all open the same .codive/index.db file at once — and a naive sql.Open call with no pragmas will surface a lock error instead of just waiting.
The Production DSN Pattern
Rather than issuing PRAGMA statements after connecting (which race against other connections), codive encodes them directly in the SQLite DSN, so every connection in the pool gets them atomically on open:
dsn := fmt.Sprintf(
"file:%s?_pragma=busy_timeout(10000)"+
"&_pragma=journal_mode(WAL)"+
"&_pragma=synchronous(NORMAL)"+
"&_pragma=cache_size(-64000)",
filepath.ToSlash(dbPath),
)
database, err := sql.Open("sqlite", dsn)
| Pragma | Value | Why |
|---|---|---|
busy_timeout | 10000ms | A connection that hits a lock waits up to 10s instead of failing immediately — long enough to ride out an IDE restart or a concurrent write. |
journal_mode | WAL | Readers no longer block writers (or vice versa) — the classic single-writer/no-reader SQLite bottleneck goes away. |
synchronous | NORMAL | Safe under WAL (durable across app crashes, not OS crashes) while avoiding the fsync cost of FULL on every commit. |
cache_size | -64000 | Negative value = KiB, not pages — a 64MB page cache, since find_symbol/search_code re-read the same hot pages constantly. |
Bounding the Connection Pool
A busy-timeout alone isn't enough if the pool itself keeps too many idle connections open across restarts. codive caps the standard database/sql pool tightly:
database.SetMaxOpenConns(4) database.SetMaxIdleConns(2) database.SetConnMaxLifetime(10 * time.Minute)
Four open connections is deliberately small: codive's MCP server is typically driven by a single AI agent process at a time, plus the background auto-sync watcher. Limiting idle connections means a stale connection from a previous codive serve process doesn't sit holding a lock during the window where an IDE is restarting the MCP subprocess.
codive serve subprocess while a previous one is still tearing down — the bounded pool plus the 10-second busy timeout absorbs the handoff instead of surfacing a "database is locked" error to the agent.
What This Doesn't Solve
WAL mode still requires the database directory to be writable and to not sit on a network filesystem with poor fcntl locking support (some network drives are known-bad for SQLite WAL). If you index a repository over a network mount and see intermittent lock errors despite this configuration, that's the first thing to check — it's a filesystem limitation, not something a busier timeout can paper over.