[Bug] cairn-server SQLite database is locked 导致 heartbeat 500，部分 explore intent 失败

🔍 Root Cause Analysis:

The error encountered in [Bug] cairn-server SQLite database is locked 导致 heartbeat 500，部分 explore intent 失败 (database is locked / SQLITE_BUSY [code 5]) occurs when concurrent database operations clash on SQLite's file-locking mechanism.

1. Root Cause Breakdown:

1. Default Rollback Journal Concurrency Limitation: By default, SQLite runs in DELETE rollback journal mode. In this mode, locks are database-wide:
   • A single write transaction acquires an EXCLUSIVE lock, completely blocking all concurrent readers and writers.
   • Any active reader holding a SHARED lock prevents writers from committing, holding the writer in a PENDING state which quickly causes subsequent operations to time out with SQLITE_BUSY.
2. Missing or Insufficient busy_timeout: Most default SQLite drivers / ORMs default to a zero or very short busy timeout (0ms), throwing OperationalError: database is locked immediately instead of waiting for existing transactions to finalize.
3. Multi-Threaded / Multi-Process Connection Pooling: SQLite is fundamentally a serverless, file-backed engine. Attempting to use standard multi-connection pools across parallel worker processes (such as Gunicorn, Uvicorn, PM2 cluster, or Celery workers) without write serialization guarantees lock contention.
4. Unclosed / Hanging Transactions: Leaving uncommitted transactions open (e.g., forgotten rollback on error or delayed commits) holds locks indefinitely across the database file.

2. Recommended Production Solutions:

Step 1: Enable Write-Ahead Logging (WAL) Mode

WAL mode fundamentally alters SQLite's locking model by allowing concurrent readers while a write is occurring:

PRAGMA journal_mode = WAL;
PRAGMA synchronous = NORMAL;

Benefits: Readers never block writers, and writers never block readers. Only simultaneous writes still contend.

Step 2: Configure a Non-Zero Busy Handler Timeout

Instruct SQLite to wait and retry before throwing an error:

PRAGMA busy_timeout = 5000; -- Wait up to 5000 milliseconds for lock release

Step 3: Application & ORM Configuration

Python (sqlite3 & SQLAlchemy):

from sqlalchemy import create_engine, event

# Use single connection or configure WAL on connect hook
engine = create_engine(
    "sqlite:///data.db?timeout=30",
    connect_args={"check_same_thread": False, "timeout": 30}
)

@event.listens_for(engine, "connect")
def set_sqlite_pragma(dbapi_connection, connection_record):
    cursor = dbapi_connection.cursor()
    cursor.execute("PRAGMA journal_mode=WAL")
    cursor.execute("PRAGMA synchronous=NORMAL")
    cursor.execute("PRAGMA busy_timeout=5000")
    cursor.close()

Node.js (better-sqlite3):

const Database = require('better-sqlite3');
const db = new Database('data.db', { timeout: 7000 });
db.pragma('journal_mode = WAL');
db.pragma('synchronous = NORMAL');

Go (mattn/go-sqlite3):

// Append query parameters to connection DSN
db, err := sql.Open("sqlite3", "file:data.db?_journal_mode=WAL&_busy_timeout=5000&_synchronous=NORMAL")

Step 4: Serialize Writes in Multi-Worker Setups

• For high-write throughput across multiple distributed processes, use a dedicated queue worker (e.g. BullMQ, Celery, or Redis queue) to funnel all database writes through a single thread/connection, leaving other workers read-only.
• If high concurrent write concurrency across independent processes is required, migrate the backing store to PostgreSQL.

Eng. MHD. Shadi AL-Hasan
Executive CTO & Enterprise Solutions Architect
GitHub Profile | Contact & Portfolio