Bun 1.2 Clustered SQLite WAL Contention Barrier and Write Lease Coordination

--- description: Bun 1.2 Clustered SQLite WAL Contention Barrier and Write Lease Coordination - Standardizes multi-process and multi-worker SQLite database access under Bun 1.2 cluster mode. Mandates strict WAL mode initialization, transaction immediacy (BEGIN IMMEDIATE), explicit busy timeouts, and an isolated writer-lease barrier to eliminate SQLITE_BUSY lock starvation and unbounded WAL file bloat under saturated microservice traffic. globs: src/**/*.ts, workers/**/*.ts, lib/db/**/*.ts alwaysApply: true verified_by: "agentrules.org" license: "CC-BY-4.0" source: "https://agentrules.org/rules/bun-sqlite-wal-contention-barrier" --- # Bun 1.2 Clustered SQLite WAL Contention Barrier and Write Lease Coordination > **Severity**: CRITICAL | **Category**: backend > **Anti-Pattern Warning**: Uncoordinated Multi-Worker Concurrent SQLite Writes with Default Deferred Transactions and Missing Busy Timeouts ## System Constraints You are a Principal Engineer enforcing Bun 1.2 Clustered Worker SQLite WAL Contention Barrier. Mandate strict SQLite connection settings, BEGIN IMMEDIATE write barriers, cooperative write leases, and coordinated WAL checkpointing to eliminate SQLITE_BUSY errors and WAL file bloat across multi-threaded cluster workers. ## Strict Guidelines - Engineers MUST ALWAYS initialize SQLite connections with `PRAGMA journal_mode = WAL;`, `PRAGMA busy_timeout = 5000;`, and `PRAGMA synchronous = NORMAL;` immediately upon opening database instances in Bun. - Engineers MUST ALWAYS initiate write transactions with `BEGIN IMMEDIATE` (or `db.transaction()` in Bun) rather than default deferred transactions (`BEGIN DEFERRED`) to immediately acquire a reserved lock and avert mid-transaction deadlocks. - Engineers MUST NOT allow non-coordinated ad-hoc `PRAGMA wal_checkpoint` runs from random cluster worker threads; WAL checkpointing MUST be governed by a designated primary process or an isolated periodic timer using `PRAGMA wal_checkpoint(PASSIVE)` or `TRUNCATE`. - Services MUST implement exponential backoff with randomized full-jitter retry wrappers around write operations that encounter transient `SQLITE_BUSY` errors. - Workers MUST isolate read connections from write connections where possible, ensuring high-throughput query workers do not hold long-running read transactions open that block WAL checkpoint sweeps. ## Bad vs Good Implementations ### Naive Uncoordinated SQLite Writer vs. WAL Contention-Hardened Worker Lease Pattern #### Bad Antipattern (AI Common Hallucination) > **Why it fails**: Opening SQLite databases without setting WAL journal mode and busy_timeout results in lock failures under load. Performing default writes upgrades locks deferredly, creating SQLITE_BUSY deadlocks between Bun cluster workers. Uncoordinated FULL checkpoints stall active read transactions. ```typescript import { Database } from 'bun:sqlite'; // ANTI-PATTERN: Default configuration in clustered workers // 1. Missing WAL and busy_timeout pragmas causes immediate SQLITE_BUSY failures. // 2. Default deferred transactions upgrade mid-flight, causing mutual deadlocks. // 3. Workers trigger synchronous checkpoints unpredictably, starving concurrent readers. export class UnsafeClusterDatabase { private db: Database; constructor(path: string) { this.db = new Database(path); } public saveMetric(key: string, value: number): void { // Implicit BEGIN DEFERRED can deadlock when two workers upgrade simultaneously this.db.run('INSERT INTO metrics (key, value) VALUES (?, ?)', [key, value]); // Checkpointing from individual workers blocks all threads this.db.run('PRAGMA wal_checkpoint(FULL)'); } } ``` #### Production Standard (Correct) > **Standard**: Configures WAL mode, normal synchronous disk writes, and a 5000ms busy timeout. Uses Bun's `transaction()` (which acquires an immediate write lock) combined with exponential backoff and jitter for transient busyness. Offloads checkpointing to a designated primary worker using non-blocking PASSIVE checkpoints. ```typescript import { Database } from 'bun:sqlite'; export interface DatabaseOptions { readonly isPrimaryWorker?: boolean; readonly busyTimeoutMs?: number; } export class ResilientClusterDatabase { private readonly db: Database; private readonly busyTimeoutMs: number; private checkpointTimer?: Timer; constructor(path: string, options: DatabaseOptions = {}) { this.busyTimeoutMs = options.busyTimeoutMs ?? 5000; this.db = new Database(path, { create: true }); this.configurePragmas(); if (options.isPrimaryWorker) { this.startCheckpointCoordinator(); } } private configurePragmas(): void { // Critical WAL & concurrency settings for Bun clustered workers this.db.exec('PRAGMA journal_mode = WAL;'); this.db.exec(`PRAGMA busy_timeout = ${this.busyTimeoutMs};`); this.db.exec('PRAGMA synchronous = NORMAL;'); this.db.exec('PRAGMA foreign_keys = ON;'); } // Execute writes inside an immediate transaction with jittered retry public async executeWriteWithLease<T>( work: (db: Database) => T, maxRetries = 5 ): Promise<T> { let attempt = 0; while (attempt < maxRetries) { try { // Bun db.transaction wraps execution in BEGIN IMMEDIATE const transaction = this.db.transaction(() => work(this.db)); return transaction(); } catch (err: unknown) { const isBusy = err instanceof Error && err.message.includes('SQLITE_BUSY'); if (!isBusy || attempt === maxRetries - 1) { throw err; } const baseDelay = 10 * Math.pow(2, attempt); const jitter = Math.random() * baseDelay; await Bun.sleep(baseDelay + jitter); attempt++; } } throw new Error('Write lease acquisition timed out due to WAL contention.'); } private startCheckpointCoordinator(): void { // Dedicated primary-only non-blocking checkpoint loop this.checkpointTimer = setInterval(() => { try { this.db.exec('PRAGMA wal_checkpoint(PASSIVE);'); } catch (err) { console.error('Coordinated WAL checkpoint deferred:', err); } }, 30000); } public close(): void { if (this.checkpointTimer) { clearInterval(this.checkpointTimer); } this.db.close(); } } ```

Architectural Specification & Invariant Rationale

Architectural Invariant Design: Bun 1.2 Clustered SQLite WAL Contention Barrier and Write Lease Coordination

Bun 1.2 Clustered SQLite WAL Contention Barrier and Write Lease Coordination defines an industrial-grade behavioral standard for backend engineering. Standardizes multi-process and multi-worker SQLite database access under Bun 1.2 cluster mode. Mandates strict WAL mode initialization, transaction immediacy (BEGIN IMMEDIATE), explicit busy timeouts, and an isolated writer-lease barrier to eliminate SQLITE_BUSY lock starvation and unbounded WAL file bloat under saturated microservice traffic. Modern software architectures require strict adherence to deterministic patterns to guarantee high availability, maintainability, and zero security vulnerabilities.

Framework Paradigm Shift & Drift MechanicsLLMs are trained on millions of open-source repositories containing outdated code, legacy idioms, and unpatched antipatterns. When coding assistants generate implementations for this stack, they default to common statistical tokens: Uncoordinated Multi-Worker Concurrent SQLite Writes with Default Deferred Transactions and Missing Busy Timeouts Without an explicit system instruction file, developers spend hours debugging silent failures.

Failure Modes & Production Blast Radius

Hazard Analysis: What Breaks Without This Rule

The following failure modes have been cataloged from hundreds of hours of production code reviews where AI assistants produced non-compliant implementations:

Failure Mode	AI Symptom (The Anti-Pattern)	Severity	Production Impact & Remedy
AI Hallucination & Deprecation Drift	Uncoordinated Multi-Worker Concurrent SQLite Writes with Default Deferred Transactions and Missing Busy Timeouts	Critical	Violating this industrial standard introduces regression bugs, security risks, and unexpected runtime failures in production. Enforce explicit behavioral system directives across all AI coding assistants in your repository.
Silent Architecture Inconsistency	AI assistants produce mixed paradigms across files, creating fragmented codebase conventions.	Medium	Increases cognitive load during code reviews and leads to maintenance debt across engineering teams. Pin project conventions in .cursor/rules, CLAUDE.md, and AGENTS.md so all team agents follow identical guardrails.

Architectural Directives in Detail

Step-by-Step Production Directives

Each rule constraint below is injected into the AI agent's system prompt to establish deterministic boundaries:

Engineers MUST ALWAYS initialize SQLite connections with `PRAGMA journal_mode = WAL

Engineers MUST ALWAYS initialize SQLite connections with `PRAGMA journal_mode = WAL;`, `PRAGMA busy_timeout = 5000;`, and `PRAGMA synchronous = NORMAL;` immediately upon opening database instances in Bun.

Rationale:AI models frequently generate unconstrained code here because statistical language distributions lack project-specific architectural context. Mandating this directive ensures that generated code adheres to verified industrial production requirements.