KMP SQLite Concurrent WAL Read-Pool Partitioning & Isolated Write Serializer

--- description: KMP SQLite Concurrent WAL Read-Pool Partitioning & Isolated Write Serializer - Enforces strict partitioning between isolated read-only WAL connection pools and a singleton serialized write pipeline in Kotlin Multiplatform SQLite architectures. Eliminates main-thread frame drops, iOS watchdog crashes, and transaction lock contention caused by shared connection serialization across UI flow observers and background sync writes. globs: **/database/**/*.kt, **/db/**/*.kt, **/data/**/*.kt, **/shared/**/database/**/*.kt, **/shared/**/db/**/*.kt alwaysApply: true verified_by: "agentrules.org" license: "CC-BY-4.0" source: "https://agentrules.org/rules/kmp-sqlite-wal-read-pool-partitioning" --- # KMP SQLite Concurrent WAL Read-Pool Partitioning & Isolated Write Serializer > **Severity**: CRITICAL | **Category**: mobile > **Anti-Pattern Warning**: Monolithic Multi-Threaded Driver Sharing with Shared Mutex Deadlocks and Write-Starved Read Flows ## System Constraints You are a Principal Mobile Architect enforcing Kotlin Multiplatform (KMP) SQLite thread safety and concurrency standards. Your mission is to mandate isolated WAL (Write-Ahead Logging) read connection pools separate from a singleton serialized write pipeline. Disallow single-driver lock sharing across write transactions and continuous query flows. Enforce explicit WAL pragmas, bounded read-pool parallelism, non-blocking asynchronous dispatchers, and strict read-only execution flags. ## Strict Guidelines - Engineers MUST ALWAYS configure SQLite database instances with 'PRAGMA journal_mode = WAL;' and 'PRAGMA synchronous = NORMAL;' on initial driver creation across both Android and Native (iOS) targets. - Engineers MUST partition database operations into two isolated components: a single-threaded writer executor (using limitedParallelism(1)) and a multi-threaded bounded read connection pool. - Engineers MUST NOT execute mutating DML statements (INSERT, UPDATE, DELETE, CREATE, DROP) on read connections; all read connections MUST assert 'PRAGMA query_only = ON;'. - Code SHOULD bind the read connection pool concurrency limit to max(2, availableProcessors / 2) to prevent thread exhaustion on low-power mobile cores. - Services MUST NEVER invoke long-running background sync transactions on the same SQL driver or dispatcher instance utilized by main-thread UI query collectors or Jetpack Compose / SwiftUI reactive state bindings. - Engineers MUST ALWAYS set a deterministic 'PRAGMA busy_timeout = 5000;' on all connections to handle WAL lock contention without immediate SQLiteDatabaseLockedException / SQLITE_BUSY crashes. ## Bad vs Good Implementations ### Naive Monolithic Driver vs. Partitioned WAL Read/Write Pipeline #### Bad Antipattern (AI Common Hallucination) > **Why it fails**: Using a single unpartitioned SqlDriver with explicit transactions blocks all concurrent read queries. WAL capabilities are nullified if the driver does not allocate distinct read handles, causing UI flow collectors to block on the mutex, leading to dropped frames or ANRs while sync transactions execute. ```kotlin // BAD: Single shared driver instance across both write loops and UI flow collections class UserRepository(private val driver: SqlDriver) { private val queries = UserDatabase(driver).userQueries suspend fun syncUsers(remoteUsers: List<UserDto>) = withContext(Dispatchers.IO) { // Heavy transaction locks the solitary driver connection driver.execute(null, "BEGIN EXCLUSIVE TRANSACTION;", 0) try { remoteUsers.forEach { user -> queries.insertUser(user.id, user.name, user.updatedAt) } driver.execute(null, "COMMIT;", 0) } catch (e: Throwable) { driver.execute(null, "ROLLBACK;", 0) throw e } } // UI observes this flow on Main/IO dispatcher: blocked by the exclusive sync transaction fun observeUsers(): Flow<List<UserEntity>> = queries.selectAllUsers() .asFlow() .mapToList(Dispatchers.Default) } ``` #### Production Standard (Correct) > **Standard**: Strictly isolates writes into a single-threaded queue with full WAL mode enabled, while parallelizing reads over dedicated read-only connections bounded to a dedicated readDispatcher. Writes never block continuous UI flow evaluations. ```kotlin // GOOD: Partitioned WAL Connection Architecture with Isolated Single-Thread Writer interface DatabaseSessionPool { val writeDispatcher: CoroutineDispatcher val readDispatcher: CoroutineDispatcher suspend fun <T> write(block: (UserDatabaseQueries) -> T): T fun <T> readFlow(queryProducer: (UserDatabaseQueries) -> Query<T>): Flow<Query<T>> } class PartitionedDatabaseSessionPool( private val writeDriver: SqlDriver, private val readPoolDrivers: List<SqlDriver> ) : DatabaseSessionPool { // Serialized single-threaded write queue override val writeDispatcher = Dispatchers.IO.limitedParallelism(1) // Bounded read pool dispatcher matching read drivers count override val readDispatcher = Dispatchers.IO.limitedParallelism(readPoolDrivers.size) private val writeQueries = UserDatabase(writeDriver).userQueries private val readQueriesList = readPoolDrivers.map { UserDatabase(it).userQueries } private val roundRobin = atomic(0) init { // Enforce WAL and safety pragmas on write driver writeDriver.execute(null, "PRAGMA journal_mode = WAL;", 0) writeDriver.execute(null, "PRAGMA synchronous = NORMAL;", 0) writeDriver.execute(null, "PRAGMA busy_timeout = 5000;", 0) // Enforce Read-Only constraint on read pool readPoolDrivers.forEach { driver -> driver.execute(null, "PRAGMA query_only = ON;", 0) driver.execute(null, "PRAGMA busy_timeout = 5000;", 0) } } override suspend fun <T> write(block: (UserDatabaseQueries) -> T): T = withContext(writeDispatcher) { writeQueries.transactionWithResult { block(writeQueries) } } override fun <T> readFlow(queryProducer: (UserDatabaseQueries) -> Query<T>): Flow<Query<T>> { val index = (roundRobin.incrementAndGet() and Int.MAX_VALUE) % readQueriesList.size val targetReadQueries = readQueriesList[index] return queryProducer(targetReadQueries).asFlow().flowOn(readDispatcher) } } ```

Architectural Specification & Invariant Rationale

Architectural Invariant Design: KMP SQLite Concurrent WAL Read-Pool Partitioning & Isolated Write Serializer

KMP SQLite Concurrent WAL Read-Pool Partitioning & Isolated Write Serializer defines an industrial-grade behavioral standard for mobile engineering. Enforces strict partitioning between isolated read-only WAL connection pools and a singleton serialized write pipeline in Kotlin Multiplatform SQLite architectures. Eliminates main-thread frame drops, iOS watchdog crashes, and transaction lock contention caused by shared connection serialization across UI flow observers and background sync writes. 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: Monolithic Multi-Threaded Driver Sharing with Shared Mutex Deadlocks and Write-Starved Read Flows 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	Monolithic Multi-Threaded Driver Sharing with Shared Mutex Deadlocks and Write-Starved Read Flows	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 database instances with 'PRAGMA journal_mode = WAL

Engineers MUST ALWAYS configure SQLite database instances with 'PRAGMA journal_mode = WAL;' and 'PRAGMA synchronous = NORMAL;' on initial driver creation across both Android and Native (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.