How to prevent SQLite SQLITE_BUSY lock contention across concurrent multi-agent worker threads in CodexMap?

[somuai]

Problem Context

In CodexMap, four specialized agent threads (Cartographer, Sentinel, Healer, and Broadcaster) run concurrently to inspect source code, compute alignment scores, queue repairs, and broadcast WebSocket state.

When multiple agents attempt to write telemetry and AST node grades simultaneously, standard SQLite configurations can trigger SQLITE_BUSY: database is locked exceptions, stalling the pipeline.

How does CodexMap architect its database configuration and write isolation to guarantee zero database lock contention under continuous multi-agent load?

[somuai]

Architectural Solution: Multi-Agent Concurrency & WAL Mode Isolation

CodexMap eliminates SQLITE_BUSY contention through a four-tiered database architecture combining SQLite engine PRAGMAs, connection pooling defaults, and serialized transactional boundaries:

---

1. Write-Ahead Logging (WAL) Mode Activation

By default, SQLite uses rollback journal mode (DELETE), which locks the entire database file for both readers and writers during any write transaction.

CodexMap configures WAL mode immediately upon connection initialization in lib/db.js:

PRAGMA journal_mode = WAL;

• Concurrency Gain: In WAL mode, SQLite allows multiple concurrent readers to read the database at the exact same time a writer is committing transactions into the -wal file. Readers never block writers, and writers never block readers.

---

2. Engine-Level Busy Timeout with Backoff

When two writer threads attempt to commit simultaneously, SQLite defaults to returning an immediate SQLITE_BUSY error with 0ms wait time.

CodexMap sets a 5000ms engine busy handler:

PRAGMA busy_timeout = 5000;

• If a thread attempts a write while another transaction is committing, the SQLite C core sleeps in exponential backoff increments for up to 5.0 seconds before raising an error. Because CodexMap transactions execute in under 5ms, lock queues resolve almost instantaneously.

---

3. Synchronous Flag Tuning

To maximize disk I/O throughput during rapid file-save bursts without risking corruption:

PRAGMA synchronous = NORMAL;

• In WAL mode with synchronous = NORMAL, SQLite only syncs to disk at WAL checkpoint boundaries rather than on every single COMMIT. This yields a 3x–5x write speedup while remaining 100% crash-resilient against process crashes and power loss.

---

4. Implementation Pattern in lib/db.js

import Database from 'better-sqlite3';

export function initializeDatabase(dbPath) {
  const db = new Database(dbPath, { timeout: 5000 });
  
  // Set performance and concurrency PRAGMAs
  db.pragma('journal_mode = WAL');
  db.pragma('synchronous = NORMAL');
  db.pragma('busy_timeout = 5000');
  db.pragma('temp_store = MEMORY');
  
  // Create tables with indexed lookup keys
  db.exec(`
    CREATE TABLE IF NOT EXISTS nodes (
      id TEXT PRIMARY KEY,
      filepath TEXT NOT NULL,
      summary TEXT,
      grade TEXT DEFAULT 'pending',
      s_final REAL DEFAULT 0.0,
      updated_at INTEGER
    );
    CREATE INDEX IF NOT EXISTS idx_nodes_grade ON nodes(grade);
    CREATE INDEX IF NOT EXISTS idx_nodes_filepath ON nodes(filepath);
  `);
  
  return db;
}

This guarantees zero SQLITE_BUSY errors even when Cartographer writes 50 AST nodes while Sentinel concurrently evaluates score matrices and Healer processes the repair queue.