KMP Room SQLite WAL Concurrency & POSIX Lock Contention Invariant

--- description: KMP Room SQLite WAL Concurrency & POSIX Lock Contention Invariant - Prevents POSIX advisory lock collisions (SQLITE_BUSY), coroutine deadlocks, and thread starvation in Kotlin Multiplatform Room databases across iOS and Android runtimes. Mandates explicit Write-Ahead Logging (WAL), calibrated busy-timeout pragmas, and disciplined dispatcher bounds across native SQLite engines. globs: **/database/**/*.kt, **/db/**/*.kt, **/*Database*.kt, **/*Dao*.kt, **/di/*Database*Module.kt alwaysApply: true verified_by: "agentrules.org" license: "CC-BY-4.0" source: "https://agentrules.org/rules/kmp-room-sqlite-wal-concurrency-invariant" --- # KMP Room SQLite WAL Concurrency & POSIX Lock Contention Invariant > **Severity**: CRITICAL | **Category**: mobile > **Anti-Pattern Warning**: Unbounded coroutine dispatching and missing busy_timeout pragmas causing iOS Darwin POSIX lock contention and SQLITE_BUSY fatal crashes. ## System Constraints You are a Principal Software Architect enforcing SQLite WAL concurrency safety and coroutine thread invariants in Kotlin Multiplatform (Room 2.7+) applications. You must mandate standard connection pooling, explicit busy timeouts, and disciplined query dispatcher allocations to guarantee zero POSIX advisory lock collisions across iOS and Android. ## Strict Guidelines - Engineers MUST ALWAYS configure `BundledSQLiteDriver` or equivalent managed drivers with a calibrated `PRAGMA busy_timeout = 5000;` and `PRAGMA journal_mode = WAL;` during native database initialization. - Engineers MUST NOT execute mutating Room transactions across unconfined or arbitrarily parallelized coroutine scopes that context-switch across native pthread boundaries mid-transaction. - Code MUST isolate write operations to serialized or single-threaded transactional contexts to prevent Darwin SQLite engine lock starvation. - Services MUST ALWAYS set `setQueryCoroutineContext` to a bounded, platform-appropriate dispatcher (e.g., `Dispatchers.IO.limitedParallelism(...)` or designated connection pool) rather than the default unbounded `Dispatchers.IO`. - Components NEVER execute raw SQL write queries bypassing Room's compiled DAOs without explicitly verifying active transaction isolation state. ## Bad vs Good Implementations ### Room KMP Database Configuration & Pragma Enforcement #### Bad Antipattern (AI Common Hallucination) > **Why it fails**: Failing to set `busy_timeout` causes immediate `SQLITE_BUSY (code 5)` errors when multiple coroutines access SQLite simultaneously on iOS (Darwin fcntl lock collision). Defaulting to DELETE journal mode serializes all reads behind writes, and unbounded `Dispatchers.IO` causes heavy OS thread contention. ```kotlin // Anti-pattern: Missing busy_timeout, uncontrolled parallelism, default journal mode fun getAppDatabase(builder: RoomDatabase.Builder<AppDatabase>): AppDatabase { return builder // Missing PRAGMA busy_timeout: Causes instant SQLITE_BUSY on iOS // Missing WAL configuration .setDriver(BundledSQLiteDriver()) .setQueryCoroutineContext(Dispatchers.IO) // Unbounded IO leads to POSIX lock thrashing .build() } ``` #### Production Standard (Correct) > **Standard**: Explicitly enabling WAL mode allows concurrent readers alongside a writer. Setting `busy_timeout = 5000` allows native SQLite engines to sleep and retry instead of immediately failing with `SQLITE_BUSY`. Bounding dispatcher parallelism via `limitedParallelism(4)` prevents coroutine pool exhaustion. ```kotlin // Production standard: Explicit WAL, calibrated busy_timeout, bounded concurrency fun getAppDatabase( builder: RoomDatabase.Builder<AppDatabase>, sqliteDriver: SQLiteDriver = BundledSQLiteDriver() ): AppDatabase { return builder .setDriver(sqliteDriver) .addCallback(object : RoomDatabase.Callback() { override fun onOpen(connection: SQLiteConnection) { // Prevent POSIX lock contention across iOS/Android runtimes connection.execSQL("PRAGMA journal_mode = WAL;") connection.execSQL("PRAGMA busy_timeout = 5000;") connection.execSQL("PRAGMA synchronous = NORMAL;") } }) // Constrain query context to avoid thread starvation .setQueryCoroutineContext(Dispatchers.IO.limitedParallelism(4)) .build() } ```

Architectural Specification & Invariant Rationale

Architectural Invariant Design: KMP Room SQLite WAL Concurrency & POSIX Lock Contention Invariant

KMP Room SQLite WAL Concurrency & POSIX Lock Contention Invariant defines an industrial-grade behavioral standard for mobile engineering. Prevents POSIX advisory lock collisions (SQLITE_BUSY), coroutine deadlocks, and thread starvation in Kotlin Multiplatform Room databases across iOS and Android runtimes. Mandates explicit Write-Ahead Logging (WAL), calibrated busy-timeout pragmas, and disciplined dispatcher bounds across native SQLite engines. 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 coroutine dispatching and missing busy_timeout pragmas causing iOS Darwin POSIX lock contention and SQLITE_BUSY fatal crashes. 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 coroutine dispatching and missing busy_timeout pragmas causing iOS Darwin POSIX lock contention and SQLITE_BUSY fatal crashes.	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 configure `BundledSQLiteDriver` or equivalent managed drivers with a calibrated `PRAGMA busy_timeout = 5000

Engineers MUST ALWAYS configure `BundledSQLiteDriver` or equivalent managed drivers with a calibrated `PRAGMA busy_timeout = 5000;` and `PRAGMA journal_mode = WAL;` during native database initialization.

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.