Bun 1.2 Multi-Worker SQLite WAL Concurrency Isolation & Busy Timeout Guardrail

Ensures transaction serializability and prevents thread-locking stalls across clustered Bun workers executing concurrent read-write transactions on embedded SQLite. Mandates Write-Ahead Logging (WAL) configuration, explicit busy timeouts, immediate transactions for mutations, and proper resource disposal to avoid database lock contention (SQLITE_BUSY). Prevents event-loop thread blocking and process deadlocks across separate OS processes or web workers accessing shared SQLite database files.

--- description: Bun 1.2 Multi-Worker SQLite WAL Concurrency Isolation & Busy Timeout Guardrail - Ensures transaction serializability and prevents thread-locking stalls across clustered Bun workers executing concurrent read-write transactions on embedded SQLite. Mandates Write-Ahead Logging (WAL) configuration, explicit busy timeouts, immediate transactions for mutations, and proper resource disposal to avoid database lock contention (SQLITE_BUSY). Prevents event-loop thread blocking and process deadlocks across separate OS processes or web workers accessing shared SQLite database files. globs: src/**/*.ts, src/db/**/*.ts, src/workers/**/*.ts alwaysApply: true verified_by: "agentrules.org" license: "CC-BY-4.0" source: "https://agentrules.org/rules/bun-multi-worker-sqlite-wal-concurrency" --- # Bun 1.2 Multi-Worker SQLite WAL Concurrency Isolation & Busy Timeout Guardrail > **Severity**: CRITICAL | **Category**: backend > **Anti-Pattern Warning**: Unbounded Event-Loop Starvation via Default Deferred Transactions and Missing WAL Busy Handlers in Multi-Worker Bun ## System Constraints You are a Principal Software Architect enforcing production-grade concurrency standards for Bun 1.2 multi-worker SQLite architectures. You must guarantee non-blocking event loops, safe WAL pragma initialization, immediate transaction isolation for state mutations, and robust retry backoff mechanisms across all database instances. ## Strict Guidelines - Engineers MUST ALWAYS enable WAL mode (`PRAGMA journal_mode = WAL;`) and synchronous normal (`PRAGMA synchronous = NORMAL;`) on connection bootstrap before running any queries. - Engineers MUST ALWAYS configure an explicit `PRAGMA busy_timeout` (minimum 5000ms) or pass `timeout` in `bun:sqlite` Database constructor options to prevent immediate `SQLITE_BUSY` crashes across clustered workers. - Engineers MUST NEVER execute write operations inside implicit or default DEFERRED transactions (`db.transaction()`); all write operations MUST use `db.transaction(() => { ... }).immediate()` or explicit `BEGIN IMMEDIATE` to prevent deadlock upgrade race conditions. - Code MUST isolate synchronous long-running SQLite batch operations or utilize microtask yielding (`setImmediate` / asynchronous chunking) to prevent blocking the Bun main event loop thread. - Workers MUST NEVER run shared in-memory databases (`:memory:`) or create unmanaged multiple writer pools that fail to close descriptors on shutdown (`SIGINT`/`SIGTERM` handlers must call `db.close()`). - Services MUST ensure that multi-process file-locking relies on uniform SQLite pragmas (e.g., `PRAGMA busy_timeout = 5000; PRAGMA foreign_keys = ON;`) executed consistently on every worker instantiation. ## Bad vs Good Implementations ### Anti-Pattern: Default Deferred Transactions with No Busy Timeout in Bun Workers #### Bad Antipattern (AI Common Hallucination) > **Why it fails**: Default transactions begin with DEFERRED lock acquisition. When two Bun workers read simultaneously and both subsequently attempt an UPDATE, SQLite attempts to escalate from SHARED to EXCLUSIVE locks simultaneously, resulting in immediate SQLITE_BUSY lock escalation deadlocks. Furthermore, default journal mode blocks readers during writes. ```typescript import { Database } from 'bun:sqlite'; // Flaw: Unconfigured journal mode (defaults to DELETE), no busy_timeout, // and default DEFERRED transaction causes SQLITE_BUSY deadlocks under concurrent worker load. export const db = new Database('app.db'); export function transferCredits(senderId: string, recipientId: string, amount: number) { // Default transaction is DEFERRED: lock upgrades halfway through fail when another worker writes. const txn = db.transaction(() => { const sender = db.query('SELECT balance FROM accounts WHERE id = ?').get(senderId) as { balance: number } | null; if (!sender || sender.balance < amount) throw new Error('Insufficient funds'); db.query('UPDATE accounts SET balance = balance - ? WHERE id = ?').run(amount, senderId); db.query('UPDATE accounts SET balance = balance + ? WHERE id = ?').run(amount, recipientId); }); txn(); } ``` #### Production Standard (Correct) > **Standard**: Configuring PRAGMA journal_mode = WAL enables concurrent readers and writers. PRAGMA busy_timeout = 5000 forces Bun workers to poll internally rather than throwing SQLITE_BUSY immediately. Invoking .immediate() on the transaction starts with BEGIN IMMEDIATE, ensuring that write locks are acquired deterministically without lock escalation race conditions. ```typescript import { Database } from 'bun:sqlite'; // Correct: Explicit busy_timeout and WAL configuration across all worker instances export function createDatabaseConnection(filePath = 'app.db'): Database { const db = new Database(filePath, { create: true }); // Enforce high-concurrency WAL pragmas immediately db.exec(` PRAGMA journal_mode = WAL; PRAGMA synchronous = NORMAL; PRAGMA busy_timeout = 5000; PRAGMA foreign_keys = ON; `); return db; } export const db = createDatabaseConnection(); export function transferCredits(senderId: string, recipientId: string, amount: number): void { // BEGIN IMMEDIATE acquires RESERVED lock upfront, serializing concurrent writes without upgrading deadlocks const txn = db.transaction(() => { const sender = db.query<{ balance: number }, [string]>( 'SELECT balance FROM accounts WHERE id = ?' ).get(senderId); if (!sender || sender.balance < amount) { throw new Error('Insufficient funds'); } db.query('UPDATE accounts SET balance = balance - ? WHERE id = ?').run(amount, senderId); db.query('UPDATE accounts SET balance = balance + ? WHERE id = ?').run(amount, recipientId); }).immediate(); txn(); } // Graceful shutdown ensures WAL is properly checkpointed process.on('SIGTERM', () => { db.close(); process.exit(0); }); ```

Architectural Specification & Invariant Rationale

Architectural Invariant Design: Bun 1.2 Multi-Worker SQLite WAL Concurrency Isolation & Busy Timeout Guardrail

Bun 1.2 Multi-Worker SQLite WAL Concurrency Isolation & Busy Timeout Guardrail defines an industrial-grade behavioral standard for backend engineering. Ensures transaction serializability and prevents thread-locking stalls across clustered Bun workers executing concurrent read-write transactions on embedded SQLite. Mandates Write-Ahead Logging (WAL) configuration, explicit busy timeouts, immediate transactions for mutations, and proper resource disposal to avoid database lock contention (SQLITE_BUSY). Prevents event-loop thread blocking and process deadlocks across separate OS processes or web workers accessing shared SQLite database files. 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: Unbounded Event-Loop Starvation via Default Deferred Transactions and Missing WAL Busy Handlers in Multi-Worker Bun 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	Unbounded Event-Loop Starvation via Default Deferred Transactions and Missing WAL Busy Handlers in Multi-Worker Bun	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 enable WAL mode (`PRAGMA journal_mode = WAL

Engineers MUST ALWAYS enable WAL mode (`PRAGMA journal_mode = WAL;`) and synchronous normal (`PRAGMA synchronous = NORMAL;`) on connection bootstrap before running any queries.

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.