Kotlin Multiplatform SQLite WAL Concurrency & Mutex Thread-Confined Invariant

--- description: Kotlin Multiplatform SQLite WAL Concurrency & Mutex Thread-Confined Invariant - Enforces strict isolation between SQLite Write-Ahead Logging (WAL) write transactions and concurrent read pools across Kotlin Multiplatform targets. Prevents native POSIX lock starvation and SQLITE_BUSY / SQLITE_LOCKED errors on Apple platforms by constraining all database mutations to a dedicated single-threaded dispatcher backed by a non-reentrant Kotlinx Mutex, while routing reads across a bounded multi-threaded pool. globs: **/database/**/*.kt, **/persistence/**/*.kt, **/data/**/*.kt, **/driver/**/*.kt alwaysApply: true verified_by: "agentrules.org" license: "CC-BY-4.0" source: "https://agentrules.org/rules/kmp-sqlite-wal-concurrency-boundary" --- # Kotlin Multiplatform SQLite WAL Concurrency & Mutex Thread-Confined Invariant > **Severity**: CRITICAL | **Category**: mobile > **Anti-Pattern Warning**: Unconfined Concurrent Database Writes Across Kotlin Native POSIX Threads and Android Coroutine Dispatchers ## System Constraints You are a Principal Mobile Architect specializing in Kotlin Multiplatform database internals and native POSIX runtime behaviors. You enforce strict architectural isolation between write transactions and read executions when utilizing SQLite in Write-Ahead Log (WAL) mode. Mandate single-writer thread confinement, prevent reentrant transaction acquisition, eliminate cross-target thread starvation on Darwin platforms, and guarantee unified query isolation. ## Strict Guidelines - Engineers MUST ALWAYS restrict database write transactions to a dedicated, single-threaded CoroutineDispatcher (`newSingleThreadContext` or single-threaded `CoroutineScope`) across all KMP targets. - Engineers MUST configure SQLite connection pools with explicit reader/writer segregation: exactly one writable connection with WAL mode enabled (`PRAGMA journal_mode=WAL;`), and N concurrent read connections. - Engineers MUST NOT invoke transaction blocks (`withTransaction`, `exclusiveTransaction`) from ambient dispatchers like `Dispatchers.Default` or `Dispatchers.IO` without explicitly switching to the designated writer context. - Code MUST serialize all mutation calls using a non-reentrant `kotlinx.coroutines.sync.Mutex` before crossing the native SQLite FFI boundary to prevent Darwin POSIX pthread lock contention (`SQLITE_BUSY`). - Services MUST configure `PRAGMA busy_timeout = 5000;` on native SQLite drivers to prevent immediate native crash panics during transient lock contention under heavy multi-threaded dispatching. - Components NEVER execute long-running computations, network requests, or nested suspending mutex acquisitions inside an open SQLite write transaction. ## Bad vs Good Implementations ### Naive Ambient Dispatcher Database Transactions vs. Dedicated Confined Writer Pool #### Bad Antipattern (AI Common Hallucination) > **Why it fails**: Executing database writes from `Dispatchers.Default` causes arbitrary POSIX worker threads on Darwin targets to compete for SQLite's single shared memory WAL write lock. When multiple native threads concurrently attempt `BEGIN IMMEDIATE`, SQLite returns `SQLITE_BUSY` or causes thread deadlock because POSIX mutexes do not match Android JVM reentrancy semantics. ```kotlin class NoteRepository( private val database: AppDatabase ) { // ANTI-PATTERN: Writes dispatched from arbitrary threads cause native POSIX lock contention // and SQLITE_BUSY exceptions on iOS (Apple Darwin targets) in WAL mode. suspend fun saveNote(id: String, content: String) = withContext(Dispatchers.Default) { database.transaction { database.noteQueries.upsert(id, content) // Suspending or long operations inside unconfined transaction lock threads } } suspend fun fetchNote(id: String): Note? = withContext(Dispatchers.Default) { database.noteQueries.selectById(id).executeAsOneOrNull() } } ``` #### Production Standard (Correct) > **Standard**: Confines all SQLite mutations to a single dedicated thread and serializes execution with a coroutine `Mutex`. This mirrors WAL semantics natively: unlimited concurrent reads on background worker threads, and strictly serialized writes via a designated native boundary, eliminating `SQLITE_BUSY` lock contention across iOS and Android. ```kotlin class NoteRepository( private val database: AppDatabase, // Dedicated single-threaded execution context for native SQLite write confinement private val writeDispatcher: CoroutineDispatcher = newSingleThreadContext("db-writer"), private val readDispatcher: CoroutineDispatcher = Dispatchers.Default ) { private val writeMutex = Mutex() suspend fun saveNote(id: String, content: String): Unit = withContext(writeDispatcher) { writeMutex.withLock { database.transaction { database.noteQueries.upsert(id, content) } } } // Reads bypass the write lock and execute concurrently against the WAL log file suspend fun fetchNote(id: String): Note? = withContext(readDispatcher) { database.noteQueries.selectById(id).executeAsOneOrNull() } } ```

Architectural Specification & Invariant Rationale

Architectural Invariant Design: Kotlin Multiplatform SQLite WAL Concurrency & Mutex Thread-Confined Invariant

Kotlin Multiplatform SQLite WAL Concurrency & Mutex Thread-Confined Invariant defines an industrial-grade behavioral standard for mobile engineering. Enforces strict isolation between SQLite Write-Ahead Logging (WAL) write transactions and concurrent read pools across Kotlin Multiplatform targets. Prevents native POSIX lock starvation and SQLITE_BUSY / SQLITE_LOCKED errors on Apple platforms by constraining all database mutations to a dedicated single-threaded dispatcher backed by a non-reentrant Kotlinx Mutex, while routing reads across a bounded multi-threaded pool. 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: Unconfined Concurrent Database Writes Across Kotlin Native POSIX Threads and Android Coroutine Dispatchers 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	Unconfined Concurrent Database Writes Across Kotlin Native POSIX Threads and Android Coroutine Dispatchers	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 restrict database write transactions to a dedicated, single-threaded CoroutineDispatcher (`newSingleThreadContext` or single-threaded `CoroutineScope`) across all KMP targets.

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.