Bun 1.2 Embedded SQLite WAL Shared-Memory Concurrency & Busy-Handler Guardrail

Q: How does adopting this rule prevent AI hallucinations?

This rule explicitly declares what the AI must avoid (Default Deferred Transactions and Zero-Timeout Pragmas Causing SQLITE_BUSY Deadlocks Across Bun Cluster Workers) and prescribes verified positive examples. Grounding the LLM with negative and positive pairs eliminates guesswork.

Q: Which AI coding environments are supported?

This specification is multi-platform compatible: Cursor (.cursor/rules/*.mdc & .cursorrules), Anthropic Claude Code (CLAUDE.md), Windsurf (.windsurfrules), Codex / Cline (AGENTS.md), and GitHub Copilot (.github/copilot-instructions.md).

Q: Can I combine this rule with other technology stacks?

Yes. Use the Interactive Stack Builder or download an Agent Pack to merge this rule with other frontend, backend, database, security, and DevOps rules without syntax collision.

Bun 1.2 Embedded SQLite WAL Shared-Memory Concurrency & Busy-Handler Guardrail

--- description: Bun 1.2 Embedded SQLite WAL Shared-Memory Concurrency & Busy-Handler Guardrail - Enforces strict configuration and concurrency semantics for bun:sqlite across multi-worker Bun clusters accessing shared WAL and SHM files. Mitigates SQLITE_BUSY lock exhaustion and uncoordinated write contention by enforcing explicit busy timeout pragmas, synchronous transaction modes (IMMEDIATE/EXCLUSIVE), and pooled single-writer multi-reader isolation invariants over shared memory mappings. globs: src/**/*.{ts,js,mjs}, server/**/*.{ts,js,mjs}, lib/db/**/*.{ts,js} alwaysApply: true verified_by: "agentrules.org" license: "CC-BY-4.0" source: "https://agentrules.org/rules/bun-sqlite-wal-shm-concurrency-standard" --- # Bun 1.2 Embedded SQLite WAL Shared-Memory Concurrency & Busy-Handler Guardrail > **Severity**: CRITICAL | **Category**: backend > **Anti-Pattern Warning**: Default Deferred Transactions and Zero-Timeout Pragmas Causing SQLITE_BUSY Deadlocks Across Bun Cluster Workers ## System Constraints You are a Principal Software Architect enforcing production-grade concurrency standards for Bun 1.2 embedded SQLite (`bun:sqlite`). In multi-worker or clustered environments sharing WAL (`-wal`) and shared-memory (`-shm`) files, you must strictly prevent SQLITE_BUSY lock cascades, silent deferred-transaction lock upgrades, and checkpoint starvation. Ensure all database initializations execute mandatory PRAGMAs (`journal_mode = WAL`, `synchronous = NORMAL`, `busy_timeout >= 5000`) before opening connections for traffic. Mandate that all write transactions specify `IMMEDIATE` or `EXCLUSIVE` to lock the WAL shm write-frame index up front, rather than allowing deferred promotion during execution. Reject naked SQL statements executed without explicit backoff or transaction boundaries across cluster boundaries. ## Strict Guidelines - Engineers MUST ALWAYS initialize `bun:sqlite` connections with explicit PRAGMAs: `PRAGMA journal_mode = WAL;`, `PRAGMA synchronous = NORMAL;`, and `PRAGMA busy_timeout = 5000;` (or higher) prior to handling worker traffic. - Engineers MUST ALWAYS begin mutation transactions with `BEGIN IMMEDIATE` or `BEGIN EXCLUSIVE` via the transaction driver API to prevent mid-flight lock upgrades from throwing `SQLITE_BUSY` when multiple workers hold read locks in shared memory. - Engineers MUST NOT rely on default SQLite deferred transactions (`BEGIN DEFERRED` or bare `db.transaction()` without immediate mode semantics) when writes may collide across clustered processes. - Code MUST isolate long-running CPU or network async tasks outside of active SQLite transactional scopes to prevent holding the shared WAL lock and starving readers and concurrent checkpoint operations. - Services MUST implement exponential backoff with jitter when catching unexpected `SQLITE_BUSY` or `SQLITE_LOCKED` errors during transaction commit sequences across Bun workers. - Components NEVER execute raw ad-hoc `db.run('PRAGMA wal_checkpoint(TRUNCATE)')` inside standard worker request paths; checkpointing must be delegated to passive operations or dedicated scheduler instances. ## Bad vs Good Implementations ### Bun SQLite Clustered WAL Initialization and Immediate Transaction Execution #### Bad Antipattern (AI Common Hallucination) > **Why it fails**: Opening SQLite without setting WAL, synchronous, and busy_timeout leaves default zero-wait locking in place. Furthermore, Bun's db.transaction defaults to deferred transactions, where read-to-write lock upgrades across separate Bun worker processes map to SQLITE_BUSY deadlocks on the shared -shm index. ```typescript import { Database } from 'bun:sqlite'; // ANTI-PATTERN: Default configuration leaves database in default journal mode or with // zero busy_timeout. Implicit deferred transaction upgrades lock midway through execution, // causing instant SQLITE_BUSY failures under Bun cluster multi-worker contention. export const db = new Database('data/app.db'); export function recordPurchase(userId: string, itemId: string, amount: number) { // Bare transaction begins in DEFERRED mode; reads shared SHM first, // then attempts promotion to write lock mid-stream, colliding with peer workers. const tx = db.transaction(() => { const user = db.query('SELECT balance FROM users WHERE id = ?').get(userId) as { balance: number } | null; if (!user || user.balance < amount) throw new Error('Insufficient funds'); db.query('UPDATE users SET balance = balance - ? WHERE id = ?').run(amount, userId); db.query('INSERT INTO audit_log (user_id, item_id, amount) VALUES (?, ?, ?)').run(userId, itemId, amount); }); return tx(); } ``` #### Production Standard (Correct) > **Standard**: Configuring PRAGMA busy_timeout forces Bun's C-level SQLite binding to back off cooperatively when the WAL index is contested. Calling .immediate() on the transaction instructs SQLite to acquire the RESERVED lock immediately, eliminating mid-transaction upgrade deadlocks between concurrent cluster workers. ```typescript import { Database } from 'bun:sqlite'; // PRODUCTION INVARIANT: Explicit WAL mode, normalized sync, and 5000ms busy-handler. export function createResilientConnection(path: string): Database { const db = new Database(path, { strict: true }); // Enforce WAL shared memory mapping and kernel-level cooperative busy waiting db.exec('PRAGMA journal_mode = WAL;'); db.exec('PRAGMA synchronous = NORMAL;'); db.exec('PRAGMA busy_timeout = 5000;'); db.exec('PRAGMA foreign_keys = ON;'); db.exec('PRAGMA temp_store = MEMORY;'); return db; } export const db = createResilientConnection('data/app.db'); // Use IMMEDIATE to acquire the WAL write lock immediately at transaction start, // preventing lock-upgrade deadlocks across clustered Bun worker instances. const purchaseTransaction = db.transaction((userId: string, itemId: string, amount: number) => { const user = db.query('SELECT balance FROM users WHERE id = ?').get(userId) as { balance: number } | null; if (!user || user.balance < amount) { throw new Error('Insufficient funds'); } db.query('UPDATE users SET balance = balance - ? WHERE id = ?').run(amount, userId); db.query('INSERT INTO audit_log (user_id, item_id, amount) VALUES (?, ?, ?)').run(userId, itemId, amount); return { success: true, newBalance: user.balance - amount }; }); export function recordPurchase(userId: string, itemId: string, amount: number) { // 'immediate' acquires the SQLite SHARED-MEMORY write lock prior to executing queries return purchaseTransaction.immediate(userId, itemId, amount); } ```

Architectural Specification & Invariant Rationale

Architectural Invariant Design: Bun 1.2 Embedded SQLite WAL Shared-Memory Concurrency & Busy-Handler Guardrail

Bun 1.2 Embedded SQLite WAL Shared-Memory Concurrency & Busy-Handler Guardrail defines an industrial-grade behavioral standard for backend engineering. Enforces strict configuration and concurrency semantics for bun:sqlite across multi-worker Bun clusters accessing shared WAL and SHM files. Mitigates SQLITE_BUSY lock exhaustion and uncoordinated write contention by enforcing explicit busy timeout pragmas, synchronous transaction modes (IMMEDIATE/EXCLUSIVE), and pooled single-writer multi-reader isolation invariants over shared memory mappings. 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: Default Deferred Transactions and Zero-Timeout Pragmas Causing SQLITE_BUSY Deadlocks Across Bun Cluster Workers 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	Default Deferred Transactions and Zero-Timeout Pragmas Causing SQLITE_BUSY Deadlocks Across Bun Cluster Workers	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 `bun

Engineers MUST ALWAYS initialize `bun:sqlite` connections with explicit PRAGMAs: `PRAGMA journal_mode = WAL;`, `PRAGMA synchronous = NORMAL;`, and `PRAGMA busy_timeout = 5000;` (or higher) prior to handling worker traffic.

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.