# Parallelism with Android SQLite The [SQLDelight documentation](https://sqldelight.github.io/sqldelight/2.0.2/android_sqlite/coroutines/) provides this example: ```kotlin val players: Flow> = playerQueries.selectAll() .asFlow() .mapToList(Dispatchers.IO) ``` This looks reasonable, right? In the Square Point Of Sale application, we recently discovered that using `Dispatchers.IO` to run SQLite queries could slow down other application tasks. Let’s dig into SQLite Android internals! # Single connection On Android, **by default** SQLite can open **at most one connection per database** (source: [SQLiteConnectionPool.java](https://cs.android.com/android/platform/superproject/main/+/main:frameworks/base/core/java/android/database/sqlite/SQLiteConnectionPool.java;l=713;drc=05d9bfb652e9ec78502ba70515c49cf2eae3f988)). This means that **at most one thread at a time** can run an SQL query. If you run two SQLite queries on the same database in parallel using `Dispatchers.IO` , one **thread will block** while the other thread is running its query, and your application will consume two threads from the `Dispatchers.IO` pool. The first thread will be held for the duration of the first query. The second thread will be held for the **duration of the first + the second query**, as it will first wait for the first thread to release SQLite connection before it can use it. ![](https://cdn.hashnode.com/res/hashnode/image/upload/v1738722147770/88663283-abd4-4ba6-8839-ef5b10576e51.png align="center") # `Dispatchers.IO` parallelism What’s the big deal? After all, if there’s a single connection at most, then it makes sense that all queries have to run one after the other. `Dispatchers.IO` dispatches coroutines on at most 64 threads in parallel (source: [Dispatcher.kt](https://cs.android.com/android/platform/superproject/main/+/main:external/kotlinx.coroutines/kotlinx-coroutines-core/jvm/src/scheduling/Dispatcher.kt;l=67;drc=5a14ac176a9d56b989d046e46f3577db1edf8697)). As you increase the number of queries launched in parallel, the `Dispatchers.IO` threads will be held for longer & longer, and spend most of their time blocking and waiting for the lock. Eventually, all 63 out of the 64 threads from the `Dispatchers.IO` pool will be blocked waiting for the connection lock, and **new** `Dispatchers.IO` **tasks will wait in the dispatcher queue**. ![](https://cdn.hashnode.com/res/hashnode/image/upload/v1738722514497/ad97fd34-2fb0-4f36-9e11-2261c787ef47.png align="center") So, while it’s expected that all queries on the same database have to run one after another, if we spam `Dispatchers.IO` with queries we end up **blocking other unrelated IO tasks that aren’t bound to the same constraint**. This is actually a common issue with `Dispatchers.IO` , and its [kdoc](https://kotlinlang.org/api/kotlinx.coroutines/kotlinx-coroutines-core/kotlinx.coroutines/-dispatchers/-i-o.html) outlines how that can be worked around: ### Elasticity for limited parallelism > `Dispatchers.IO` has a unique property of elasticity: its views obtained with `CoroutineDispatcher.limitedParallelism` are not restricted by the `Dispatchers.IO` parallelism. Conceptually, there is a dispatcher backed by an unlimited pool of threads, and both `Dispatchers.IO` and views of `Dispatchers.IO` are actually views of that dispatcher. In practice this means that, despite not abiding by `Dispatchers.IO`'s parallelism restrictions, its views share threads and resources with it. In the following example: ```kotlin // 100 threads for MySQL connection val myMysqlDbDispatcher = Dispatchers.IO.limitedParallelism(100) // 60 threads for MongoDB connection val myMongoDbDispatcher = Dispatchers.IO.limitedParallelism(60) ``` > the system may have up to `64 + 100 + 60` threads dedicated to blocking tasks during peak loads, but during its steady state there is only a small number of threads shared among `Dispatchers.IO`, `myMysqlDbDispatcher` and `myMongoDbDispatcher`. So here, given that we know we can run at most one query at a time, we could use a distinct `Dispatchers.IO.limitedParallelism(1)` dispatcher instance for each database we query. If we had N databases, we’d end up using **at most** N + 64 threads for blocking operations and we’d never starve the `Dispatchers.IO` thread pool. # Thread hopping & CPU caches While `Dispatchers.IO.limitedParallelism(1)` guarantees that a single thread at a time will be running our SQLite database queries, it makes no guarantees on which thread. This means that each query could execute on a different thread. CPU governors try to ensure that threads stick to a single CPU core when possible, to avoid having to reload cpu caches constantly. So with `Dispatchers.IO.limitedParallelism(1)` we have high chances of blowing up cpu caches with every new query. We can instead use a **single thread dispatcher per database**: ```kotlin val playerDbDispatcher = newSingleThreadContext("player-db") ``` ```kotlin val players: Flow> = playerQueries.selectAll() .asFlow() .mapToList(playerDbDispatcher) ``` ![](https://cdn.hashnode.com/res/hashnode/image/upload/v1738722920325/9fdb603a-f910-4c86-9420-81b4a8f06d17.png align="center") # WAL: moar SQLite connections SQLite has a [Write-Ahead Log](https://sqlite.org/wal.html) (**WAL**) option, which can lead to a significant improvement in performance and was [enabled by default in Android 9](https://source.android.com/docs/core/perf/compatibility-wal). WAL also supports more concurrency, however that is **not turned on by default** on Android, to avoid breaking apps that rely on the previous behavior: a single connection leads to operations running serially, some apps might rely on that. To support additional concurrency, Android 11 added [SQLiteDatabase#enableWriteAheadLogging](https://developer.android.com/reference/android/database/sqlite/SQLiteDatabase#enableWriteAheadLogging\(\)): > This method enables **parallel execution of queries** from multiple threads on the same database. It does this by opening multiple connections to the database and using a different database connection for each query. \[…\] It is a good idea to enable write-ahead logging whenever a database will be concurrently accessed and modified by multiple threads at the same time. However, write-ahead logging uses significantly more memory than ordinary journaling because there are multiple connections to the sa me database. So if a database will only be used by a single thread, or if optimizing concurrency is not very important, then write-ahead logging should be disabled. After calling `SQLiteDatabase.enableWriteAheadLogging(true)` the database will support one **primary connection** and several **secondary connections**. ## The primary connection The **primary** connection is used for queries that **write** to the database, as well as for **any query executed as part of a transaction** (i.e. after `SQLiteDatabase.beginTransaction()` , source: [SQLiteDatabase.java](https://cs.android.com/android/platform/superproject/main/+/main:frameworks/base/core/java/android/database/sqlite/SQLiteDatabase.java;l=828-833;drc=05d9bfb652e9ec78502ba70515c49cf2eae3f988)). When you execute a query, the Android Framework automatically figures out if the query is a `SELECT` or a write query (source: [SQLiteProgram.java](https://cs.android.com/android/platform/superproject/main/+/main:frameworks/base/core/java/android/database/sqlite/SQLiteProgram.java;l=60;drc=05d9bfb652e9ec78502ba70515c49cf2eae3f988)). Apparently that custom query parsing had bugs in older versions of Android, so in the Room library all write queries are automatically wrapped in a transaction to ensure they use the primary connection. **Consider wrapping all your write queries in a transaction**. Should you ever be using `SQLiteDatabase.beginTransaction()` for **read only** queries? * Ideally, no, as starting a transaction means you’re now using the **primary connection**, so you can’t run concurrent queries. * However, the Android framework loads query results into a CursorWindow which has a max size defined by the private Android resource `R.integer.config_cursorWindowSize` (source: [config.xml](https://cs.android.com/android/platform/superproject/main/+/main:frameworks/base/core/res/res/values/config.xml;l=2562;drc=26ab55038d82a6b9ff4d9080a3c8ab94eaa92e1c)) and configurable by apps since API 28 (source: [CursorWindow.java](https://cs.android.com/android/platform/superproject/main/+/main:frameworks/base/core/java/android/database/CursorWindow.java;l=163;drc=6266b5b1795fbf4a986dd01485f77120fee932a1)). * If you run a read query that loads a cursor window and starts reading from it, and meanwhile a write query runs, and then the cursor refreshes its window, the write might end up changing the number of rows. This could lead to inconsistent data or a crash: `java.lang.IllegalStateException: Couldn't read row 4247, col 0 from CursorWindow. Make sure the Cursor is initialized correctly before accessing data from it.` * [SQLiteDatabase#beginTransactionReadOnly](https://developer.android.com/reference/android/database/sqlite/SQLiteDatabase#beginTransactionReadOnly\(\)) was added in API 35, presumably to support that use case and use secondary connections for read only transactions. * Before API 35, you can either use `SQLiteDatabase.beginTransaction()` and lose concurrency, or you can start paging your queries, loading small subsets of results that fit within the window. Paging correctly is really hard because rows can have a dynamic size (e.g. if including a blob or a string) so you can’t really predict the max number of rows to retrieve. * Read this to learn more: [Large Database Queries on Android](https://medium.com/androiddevelopers/large-database-queries-on-android-cb043ae626e8) ## Secondary connections The **secondary** connections are used for `SELECT` queries (source: [SQLiteProgram.java](https://cs.android.com/android/platform/superproject/main/+/main:frameworks/base/core/java/android/database/sqlite/SQLiteProgram.java;l=60;drc=05d9bfb652e9ec78502ba70515c49cf2eae3f988)). * The [documentation](https://developer.android.com/reference/android/database/sqlite/SQLiteDatabase#enableWriteAheadLogging\(\)) claims that “the **maximum number of connections** used to execute queries in parallel is dependent upon the device memory and possibly other properties.” * In practice, the max number of connections is defined by the private Android resource `R.integer.db_connection_pool_size` (source: [SQLiteGlobal.java](https://cs.android.com/android/platform/superproject/main/+/main:frameworks/base/core/java/android/database/sqlite/SQLiteGlobal.java;l=143-148;drc=05d9bfb652e9ec78502ba70515c49cf2eae3f988)). * The default AOSP value for `R.integer.db_connection_pool_size` is **4** (source: [config.xml](https://cs.android.com/android/platform/superproject/main/+/main:frameworks/base/core/res/res/values/config.xml;l=2485;drc=26ab55038d82a6b9ff4d9080a3c8ab94eaa92e1c)) * For read only transactions, `SQLiteConnectionPool.waitForConnection()` will try to acquire one of the secondary connections first, and otherwise fallback to the primary connection if it’s available (source: [SQLiteConnectionPool.java](https://cs.android.com/android/platform/superproject/main/+/main:frameworks/base/core/java/android/database/sqlite/SQLiteConnectionPool.java;l=713;drc=05d9bfb652e9ec78502ba70515c49cf2eae3f988)) * The max number of connections includes the primary connection, so we effectively get **1** primary connection and **3** secondary connection (source: [SQLiteConnectionPool.java](https://cs.android.com/android/platform/superproject/main/+/main:frameworks/base/core/java/android/database/sqlite/SQLiteConnectionPool.java;l=1018-1024;drc=05d9bfb652e9ec78502ba70515c49cf2eae3f988)). * That means we can either have 4 `SELECT` queries running in parallel, or 1 write query and 3 `SELECT` queries running in parallel. You can read `R.integer.db_connection_pool_size` at runtime with the following helper method: ```kotlin // Based on SQLiteGlobal.getWALConnectionPoolSize() fun getWALConnectionPoolSize() { val resources = Resources.getSystem() val resId = resources.getIdentifier("db_connection_pool_size", "integer", "android") return if (resId != 0) { max(2, resources.getInteger(resId)) } else { 2 } } ``` ## Non exclusive transactions The [SQLiteDatabase#enableWriteAheadLogging](https://developer.android.com/reference/android/database/sqlite/SQLiteDatabase#enableWriteAheadLogging\(\)) documentation makes a surprising recommendation: > Writers should use `beginTransactionNonExclusive()` to start a transaction. Non-exclusive mode allows database file to be in readable by other threads executing queries. In practice, this means `beginTransaction()` will execute `BEGIN EXCLUSIVE;` and `beginTransactionNonExclusive()` will execute `BEGIN IMMEDIATE;` (source: [SQLiteSession.java](https://cs.android.com/android/platform/superproject/main/+/main:frameworks/base/core/java/android/database/sqlite/SQLiteSession.java;l=341-348;drc=05d9bfb652e9ec78502ba70515c49cf2eae3f988)). However, the sqlite [documentation on transactions](https://www.sqlite.org/lang_transaction.html#deferred_immediate_and_exclusive_transactions) calls out: > EXCLUSIVE and IMMEDIATE are the same in WAL mode, but in other journaling modes, EXCLUSIVE prevents other database connections from reading the database while the transaction is underway. I’m not sure why the documentation is recommending using `beginTransactionNonExclusive()` when WAL is enabled as it’s effectively the same as `beginTransaction()`, that seems like a mistake. ## WAL & Dispatchers We can now leverage concurrent reads & write with WAL by using a serial dispatcher for writes and a concurrent dispatcher for reads with the appropriate elasticity, to increase concurrency without blocking more threads than necessary: ```kotlin val dbWriteDispatcher = Dispatchers.IO.limitedParallelism(1) val connectionPoolSize = getWALConnectionPoolSize() val dbReadDispatcher = Dispatchers.IO.limitedParallelism(connectionPoolSize) ``` Or, if you'd rather use dedicated thread pools: ```kotlin val dbWriteDispatcher = newSingleThreadContext("$dbName-writes") val connectionPoolSize = getWALConnectionPoolSize() val dbReadDispatcher = newFixedThreadPoolContext(connectionPoolSize, "$dbName-reads") ``` ![](https://cdn.hashnode.com/res/hashnode/image/upload/v1738724932706/486d56a0-3536-475c-b199-bd193ee015aa.png align="center") With both the previous approaches, we end up with **5 threads dedicated to do work for a pool of 4 connections**, so there will be times where 1 thread is blocked. Room took a different and more optimal approach: use a 4 thread pool for both reads and writes, but control the writes tasks to ensure we only try to run one at a time, in serial order (source: [TransactionExecutor.kt](https://cs.android.com/androidx/platform/frameworks/support/+/androidx-main:room/room-runtime/src/androidMain/kotlin/androidx/room/TransactionExecutor.android.kt;drc=067ed409b9bc2dcc59b93c8e9ab8d092beeff3a5)). # The elephant in the Room How does all this apply to [Room](https://developer.android.com/jetpack/androidx/releases/room)? ## Ballroom WAL Room dance When using Room, WAL with support for concurrent connections is enabled by default on API 16+, unless the device is a low ram device (source: [RoomDatabase.kt](https://cs.android.com/androidx/platform/frameworks/support/+/androidx-main:room/room-runtime/src/androidMain/kotlin/androidx/room/RoomDatabase.android.kt;l=800;drc=8bd971c6e30756ad97e8d3e4634f5a87167a256b)). Room can be configured to use custom executors (`RoomDatabase.Builder.setQueryExecutor()` & `RoomDatabase.Builder.setTransactionExecutor()`, or `RoomDatabase.Builder.setQueryCoroutineContext()`). The `RoomDatabase.Builder.setQueryExecutor` [kdoc](https://cs.android.com/androidx/platform/frameworks/support/+/androidx-main:room/room-runtime/src/androidMain/kotlin/androidx/room/RoomDatabase.android.kt;l=1138-1194;drc=8bd971c6e30756ad97e8d3e4634f5a87167a256b) mentions that the query executor should use a max number of threads — without specifying how many — and that the **transaction executor** will execute **at most one query at a time**. The `@Transaction` [kdoc](https://developer.android.com/reference/androidx/room/Transaction) mentions writes: “Insert, Update or Delete methods are always run inside a transaction”. The doc also mentions that *"when both the transaction executor and query executor are unset, then a* ***default*** `Executor` *will be used that allocates and shares threads amongst Architecture Components libraries"*. That default executor is actually `ArchTaskExecutor.getIOThreadExecutor()` (source: [RoomDatabase.android.kt](https://cs.android.com/androidx/platform/frameworks/support/+/androidx-main:room/room-runtime/src/androidMain/kotlin/androidx/room/RoomDatabase.android.kt;l=1604-1605;drc=8bd971c6e30756ad97e8d3e4634f5a87167a256b)): ```kotlin abstract class RoomDatabase { open class Builder { open fun build(): T { if (queryExecutor == null && transactionExecutor == null) { transactionExecutor = ArchTaskExecutor.getIOThreadExecutor() queryExecutor = transactionExecutor } // ... ``` `ArchTaskExecutor.getIOThreadExecutor()` delegates to `DefaultTaskExecutor.mDiskIO` which is a **fixed thread pool of size 4** (source: [DefaultTaskExecutor.java](https://cs.android.com/androidx/platform/frameworks/support/+/androidx-main:arch/core/core-runtime/src/main/java/androidx/arch/core/executor/DefaultTaskExecutor.java;l=42;drc=5a10386f8cb40c82abb5bbee4556caaf64699571)). That thread pool is also used as the default fetch scheduler for Rx paging (source: [RxPagedListBuilder.kt](https://cs.android.com/androidx/platform/frameworks/support/+/androidx-main:paging/paging-rxjava3/src/main/java/androidx/paging/rxjava3/RxPagedListBuilder.kt;l=299;drc=0ee887e405f72333e89394886bdf2d49de22f70f)) and the default executor for `ComputableLiveData` (source: [ComputableLiveData.kt](https://cs.android.com/androidx/platform/frameworks/support/+/androidx-main:lifecycle/lifecycle-livedata/src/main/java/androidx/lifecycle/ComputableLiveData.kt;l=44;drc=18b3e6dc4422300577c60324286c1bbdcecab607)). This design isn’t ideal as that thread pool ends up being shared for unrelated work. Even if it was only used by Room, it’s still not ideal as all databases end up sharing the same global thread pool and starving it. If you have more than one database managed by Room you should **provide your own executors**. ## Bundled SQLite It’s worth noting that Room now offers a **bundled version of SQLite** instead of the default Android one. This increases APK size (~1 MB) but means you get the latest SQLite, you get the exact same behavior on all Android versions, and you don’t end up using any of the Android Framework SQLite code. To learn more, I recommend watching [Powering Room with Bundled SQLite](https://www.droidcon.com/2024/10/17/bundle-up-and-save-powering-room-with-bundled-sqlite/). Room with bundled SQLite also offers proper support for coroutines, i.e. no more thread blocking while we’re waiting for an SQLite connection thanks to a suspending connection pool (source: [ConnectionPoolImpl.kt](https://cs.android.com/androidx/platform/frameworks/support/+/androidx-main:room/room-runtime/src/commonMain/kotlin/androidx/room/coroutines/ConnectionPoolImpl.kt;l=93;drc=62aa237a71dbedd8c696f68d9e8961e185f2e159)). This means threads only block when they’re actually running a query, and makes it less likely that you’ll end up starving a thread pool due to blocked threads waiting for connections. # Take Aways * **Paginate queries** to avoid returning results larger than the Cursor window size (that can be hard to tune right) or use `beginTransactionNonExclusive()` on API 35+ and otherwise fallback to `beginTransaction()`. * If you’re **using Room** * If you have **more than one Room database** * By default, all Room databases shared the same fixed thread pool of size 4, when instead you should be able to use up to 4 connections concurrently per database. * Call `RoomDatabase.Builder.setQueryCoroutineContext()` or `RoomDatabase.Builder.setQueryExecutor()` with a bounded executor to ensure each database can query on up to 4 different threads concurrently. * Consider **using Room with Bundled SQLite** for perf wins, less maintenance pain and eliminating the risk of blocking threads while waiting for a connection. * If you’re **not using Room** * Wrap all your write queries in a transaction. * Use bounded executors for your read and write queries, a different set for each database. * Consider using **Bundled SQLite** (I have not looked yet into the practical details of that). I would be remiss not to mention that your Android apps should probably not try to execute a bajillion SQL queries in parallel. You might have a “*N + 1 query problem*” (look that up!), or simply too much async code firing up. Huge thanks to Shane Tang for spelunking the SQLite docs, Yiğit Boyar and Dany Santiago for sharing a ton of insights on Room & SQLite internals, and Zach Klippenstein & Jesse Wilson for proof reading!