KMP Room 2.7 Multiplatform SQLite WAL Concurrency & Isolation Invariant

--- description: KMP Room 2.7 Multiplatform SQLite WAL Concurrency & Isolation Invariant - Enforces strict Write-Ahead Logging (WAL) mode, connection pooling bounds, and coroutine-isolated transactional execution across Kotlin Multiplatform (Room 2.7+). Prevents cross-platform SQLite lock contention (SQLITE_BUSY/SQLITE_LOCKED) between iOS background Darwin threads and Android concurrent background services. globs: **/database/**/*.kt, **/di/**/*.kt, **/data/**/*.kt, **/commonMain/**/*.kt, **/iosMain/**/*.kt, **/androidMain/**/*.kt alwaysApply: true verified_by: "agentrules.org" license: "CC-BY-4.0" source: "https://agentrules.org/rules/kmp-room-wal-concurrency-invariant" --- # KMP Room 2.7 Multiplatform SQLite WAL Concurrency & Isolation Invariant > **Severity**: CRITICAL | **Category**: mobile > **Anti-Pattern Warning**: Unmanaged Multi-Threaded Dispatching Without Explicit WAL Initialization and Busy Timeouts in KMP Room ## System Constraints You are a Principal Mobile Architect enforcing concurrency invariants for Room 2.7+ in Kotlin Multiplatform (KMP). Mandate explicit WAL pragma configuration, proper SQLite driver binding (BundledSQLiteDriver), structured coroutine transactions via Room's connection management, and defensive busy_timeout settings to eliminate database lock contention across shared iOS Darwin runtimes and Android platforms. ## Strict Guidelines - Engineers MUST ALWAYS configure SQLite Write-Ahead Logging (`PRAGMA journal_mode = WAL;`) and defensive retry latency (`PRAGMA busy_timeout = 5000;`) inside `RoomDatabase.Callback.onOpen` across all KMP platforms. - Engineers MUST NOT execute raw mutations or uncoordinated concurrent writes across arbitrary unmanaged coroutine contexts (`Dispatchers.IO` or `Dispatchers.Default`); writes MUST traverse Room's transactional mechanisms (`@Transaction` suspend functions or `useWriterConnection`). - Engineers MUST configure `setDriver(BundledSQLiteDriver())` consistently across common source sets to avoid diverging native SQLite behavior between Apple Darwin (NativeSQLiteDriver) and Android runtime environments. - Code SHOULD configure `PRAGMA synchronous = NORMAL;` when WAL is active to eliminate disk sync bottlenecks on mobile solid-state drives without sacrificing crash durability. - Components NEVER expose low-level raw database driver pointers directly to platform-specific code (e.g., direct Swift/Obj-C interop or Android services) bypassing Room's internal connection pool and coroutine schedulers. ## Bad vs Good Implementations ### KMP Room 2.7 Database Builder & WAL Concurrency Configuration #### Bad Antipattern (AI Common Hallucination) > **Why it fails**: Omitting explicit WAL configuration defaults SQLite to rollback journal mode, which acquires an exclusive lock during writes and blocks all concurrent reads. Omitting PRAGMA busy_timeout causes immediate SQLITE_BUSY (5) failures under background thread contention. Manually dispatching batch inserts without a managed transaction or writer connection saturates SQLite I/O. ```kotlin // ANTI-PATTERN: Default journal mode (DELETE/TRUNCATE), missing busy_timeout, // and manual uncoordinated writes on Dispatchers.IO causing SQLITE_BUSY on iOS fun createDatabase(builder: RoomDatabase.Builder<AppDatabase>): AppDatabase { return builder .setDriver(BundledSQLiteDriver()) .build() } // Bad transaction execution: suspend fun saveTelemetryUnsafe(db: AppDatabase, data: List<Event>) { // Bypasses Room's transactional scheduler; concurrent writes from background // threads will trigger SQLiteBusyException / SQLITE_LOCKED kotlinx.coroutines.withContext(kotlinx.coroutines.Dispatchers.IO) { data.forEach { event -> db.eventDao().insertRaw(event) } } } ``` #### Production Standard (Correct) > **Standard**: Configuring WAL allows parallel readers without blocking the single active writer. A 5000ms busy_timeout queues pending background threads on iOS Darwin and Android until the lock releases. Using `useWriterConnection` with `immediateTransaction` guarantees strict atomic serial execution without contention crashes. ```kotlin // PRODUCTION STANDARD: Explicit WAL, busy_timeout, normal sync, and managed writer connection import androidx.room.RoomDatabase import androidx.room.useWriterConnection import androidx.sqlite.SQLiteConnection import androidx.sqlite.driver.bundled.BundledSQLiteDriver import androidx.sqlite.execSQL fun createDatabase(builder: RoomDatabase.Builder<AppDatabase>): AppDatabase { return builder .setDriver(BundledSQLiteDriver()) .addCallback(object : RoomDatabase.Callback() { override fun onOpen(connection: SQLiteConnection) { super.onOpen(connection) // Enforce concurrent readers alongside single writer connection.execSQL("PRAGMA journal_mode = WAL;") // Prevent immediate failure when a background thread is writing connection.execSQL("PRAGMA busy_timeout = 5000;") // Optimize WAL disk write performance connection.execSQL("PRAGMA synchronous = NORMAL;") connection.execSQL("PRAGMA foreign_keys = ON;") } }) .build() } suspend fun saveTelemetrySafe(db: AppDatabase, data: List<Event>) { // Safely executes batch writes inside Room 2.7's dedicated writer connection pool db.useWriterConnection { transactor -> transactor.immediateTransaction { val dao = db.eventDao() data.forEach { event -> dao.insert(event) } } } } ```

Architectural Specification & Invariant Rationale

Architectural Invariant Design: KMP Room 2.7 Multiplatform SQLite WAL Concurrency & Isolation Invariant

KMP Room 2.7 Multiplatform SQLite WAL Concurrency & Isolation Invariant defines an industrial-grade behavioral standard for mobile engineering. Enforces strict Write-Ahead Logging (WAL) mode, connection pooling bounds, and coroutine-isolated transactional execution across Kotlin Multiplatform (Room 2.7+). Prevents cross-platform SQLite lock contention (SQLITE_BUSY/SQLITE_LOCKED) between iOS background Darwin threads and Android concurrent background services. 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: Unmanaged Multi-Threaded Dispatching Without Explicit WAL Initialization and Busy Timeouts in KMP Room 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	Unmanaged Multi-Threaded Dispatching Without Explicit WAL Initialization and Busy Timeouts in KMP Room	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 SQLite Write-Ahead Logging (`PRAGMA journal_mode = WAL

Engineers MUST ALWAYS configure SQLite Write-Ahead Logging (`PRAGMA journal_mode = WAL;`) and defensive retry latency (`PRAGMA busy_timeout = 5000;`) inside `RoomDatabase.Callback.onOpen` across all KMP platforms.

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.