Kotlin Multiplatform Room SQLite Concurrency & Serial Queue Isolation Standard

--- description: Kotlin Multiplatform Room SQLite Concurrency & Serial Queue Isolation Standard - Enforces strict serialized write isolation and pool-managed read concurrency across Android ART and iOS Darwin runtimes using Room KMP. Prevents SQLite 'database is locked' errors (SQLITE_BUSY 5), native thread deadlocks, and dirty reads by binding RoomDatabase builders to dedicated single-threaded write dispatchers and bounded multi-threaded read pools. globs: **/database/**, **/db/**, **/*Database*.kt, **/*Dao*.kt, **/di/**DatabaseModule*.kt alwaysApply: true verified_by: "agentrules.org" license: "CC-BY-4.0" source: "https://agentrules.org/rules/kmp-room-sqlite-concurrency-isolation-standard" --- # Kotlin Multiplatform Room SQLite Concurrency & Serial Queue Isolation Standard > **Severity**: CRITICAL | **Category**: mobile > **Anti-Pattern Warning**: Unconfined Concurrent SQLite Mutations Across Native Thread Boundaries Inducing SQLITE_BUSY Deadlocks and POSIX Signal Aborts ## System Constraints You are a Principal Mobile Architect enforcing the Kotlin Multiplatform Room SQLite Concurrency Isolation Standard. You must mandate strict thread confinement for Room KMP database drivers across Android and iOS Darwin runtimes. Reject any code constructing RoomDatabase instances without an isolated single-threaded write dispatcher (such as CoroutineDispatcher backed by a serial queue or newSingleThreadContext), using unconfined dispatchers for write transactions, or allowing simultaneous multi-threaded writes against SQLite databases configured without WAL mode or proper busy timeouts. ## Strict Guidelines - Engineers MUST ALWAYS configure RoomDatabase.Builder using .setQueryCoroutineContext() backed by a controlled CoroutineDispatcher hierarchy that strictly isolates mutative transactions to a single-threaded CoroutineDispatcher across both Android and iOS targets. - Engineers MUST ALWAYS enable SQLite Write-Ahead Logging (WAL) and set a non-zero busy timeout (minimum 3000ms via PRAGMA busy_timeout = 3000) during database initialization on Darwin and Linux targets to mitigate cross-process/cross-thread lock contention. - Engineers MUST NOT execute database write operations (insert, update, delete, or @Transaction blocks modifying state) within Dispatchers.Default or Dispatchers.IO directly without confining them to the serialized write dispatcher. - Code SHOULD expose read queries as Kotlin Flows using Room's native reactive invalidation tracking, dispatched over an asynchronous read pool (Dispatchers.IO on Android or a multi-threaded dispatch pool on Native). - Components NEVER instantiate multiple instances of RoomDatabase pointing to the same SQLite physical file within the application lifecycle; all access must be mediated through an application-scoped singleton managed via Dependency Injection. ## Bad vs Good Implementations ### Room KMP Database Construction and Serial Dispatcher Binding #### Bad Antipattern (AI Common Hallucination) > **Why it fails**: Failing to explicitly configure the Room coroutine context and write dispatcher causes SQLite statements to be invoked across disparate native threads. On iOS Darwin with POSIX pthread models, concurrent write transactions trigger SQLITE_BUSY (database is locked) exceptions or fatal process halts due to unhandled SQLite locking invariants. ```kotlin // ANTI-PATTERN: Default builder configuration allowing unconfined concurrent writes across Darwin and ART runtimes fun createDatabase(builder: RoomDatabase.Builder<AppDatabase>): AppDatabase { return builder // Missing setQueryCoroutineContext with dedicated dispatchers // Default dispatching causes concurrent native thread writes -> SQLITE_BUSY / POSIX deadlocks .build() } class Repository(private val db: AppDatabase) { suspend fun saveRecords(items: List<RecordEntity>) = withContext(Dispatchers.Default) { // Unconfined transactional write concurrently executed across multiple threads db.recordDao().insertAll(items) } } ``` #### Production Standard (Correct) > **Standard**: Configures BundledSQLiteDriver with explicit WAL PRAGMAs and a 5000ms busy timeout, while forcing all database query and transaction executions through a deterministic single-threaded serial dispatcher. This guarantees total write serialization and prevents multi-threaded locking race conditions between Android ART and iOS Darwin runtimes. ```kotlin // PRODUCTION STANDARD: Strictly isolated single-thread writer and pooled reader context import androidx.room.RoomDatabase import androidx.sqlite.driver.bundled.BundledSQLiteDriver import kotlinx.coroutines.asCoroutineDispatcher import kotlinx.coroutines.Dispatchers import kotlinx.coroutines.IO import java.util.concurrent.Executors val databaseWriteDispatcher = Executors.newSingleThreadExecutor { runnable -> Thread(runnable, "room-sqlite-writer").apply { isDaemon = true } }.asCoroutineDispatcher() fun createDatabase(builder: RoomDatabase.Builder<AppDatabase>): AppDatabase { return builder .setDriver(BundledSQLiteDriver()) // Bind write isolation directly to the serial dispatcher, read pool falls back to IO .setQueryCoroutineContext(databaseWriteDispatcher) .addCallback(object : RoomDatabase.Callback() { override fun onOpen(connection: androidx.sqlite.SQLiteConnection) { // Enable WAL and lock timeout for non-blocking concurrent reads connection.execSQL("PRAGMA journal_mode = WAL;") connection.execSQL("PRAGMA busy_timeout = 5000;") connection.execSQL("PRAGMA synchronous = NORMAL;") } }) .build() } class Repository(private val db: AppDatabase) { suspend fun saveRecords(items: List<RecordEntity>) = withContext(databaseWriteDispatcher) { db.useWriterConnection { db.recordDao().insertAll(items) } } } ```

Architectural Specification & Invariant Rationale

Architectural Invariant Design: Kotlin Multiplatform Room SQLite Concurrency & Serial Queue Isolation Standard

Kotlin Multiplatform Room SQLite Concurrency & Serial Queue Isolation Standard defines an industrial-grade behavioral standard for mobile engineering. Enforces strict serialized write isolation and pool-managed read concurrency across Android ART and iOS Darwin runtimes using Room KMP. Prevents SQLite 'database is locked' errors (SQLITE_BUSY 5), native thread deadlocks, and dirty reads by binding RoomDatabase builders to dedicated single-threaded write dispatchers and bounded multi-threaded read pools. 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 SQLite Mutations Across Native Thread Boundaries Inducing SQLITE_BUSY Deadlocks and POSIX Signal Aborts 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 SQLite Mutations Across Native Thread Boundaries Inducing SQLITE_BUSY Deadlocks and POSIX Signal Aborts	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 RoomDatabase.Builder using .setQueryCoroutineContext() backed by a controlled CoroutineDispatcher hierarchy that strictly isolates mutative transactions to a single-threaded CoroutineDispatcher across both Android and iOS targets.

Engineers MUST ALWAYS configure RoomDatabase.Builder using .setQueryCoroutineContext() backed by a controlled CoroutineDispatcher hierarchy that strictly isolates mutative transactions to a single-threaded CoroutineDispatcher across both Android and iOS 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.