The SQLDelight documentation provides this example:

val players: Flow<List<HockeyPlayer>> = 
  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).

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.

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). 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.

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 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:

// 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:

val playerDbDispatcher = newSingleThreadContext("player-db")

val players: Flow<List<HockeyPlayer>> = 
  playerQueries.selectAll()
    .asFlow()
    .mapToList(playerDbDispatcher)

WAL: moar SQLite connections

SQLite has a Write-Ahead Log (WAL) option, which can lead to a significant improvement in performance and was enabled by default in Android 9. 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:

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).

When you execute a query, the Android Framework automatically figures out if the query is a SELECT or a write query (source: SQLiteProgram.java). 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) and configurable by apps since API 28 (source: CursorWindow.java).

  • 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 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

Secondary connections

The secondary connections are used for SELECT queries (source: SQLiteProgram.java).

• The documentation 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).

• The default AOSP value for R.integer.db_connection_pool_size is 4 (source: config.xml)

• 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)

  • The max number of connections includes the primary connection, so we effectively get 1 primary connection and 3 secondary connection (source: SQLiteConnectionPool.java).

  • 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:

// 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 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).

However, the sqlite documentation on 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:

val dbWriteDispatcher = Dispatchers.IO.limitedParallelism(1)

val connectionPoolSize = getWALConnectionPoolSize()
val dbReadDispatcher = Dispatchers.IO.limitedParallelism(connectionPoolSize)

Or, if you'd rather use dedicated thread pools:

val dbWriteDispatcher = newSingleThreadContext("$dbName-writes")

val connectionPoolSize = getWALConnectionPoolSize()
val dbReadDispatcher = newFixedThreadPoolContext(connectionPoolSize, "$dbName-reads")

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).

The elephant in the Room

How does all this apply to 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).

Room can be configured to use custom executors (RoomDatabase.Builder.setQueryExecutor() & RoomDatabase.Builder.setTransactionExecutor(), or RoomDatabase.Builder.setQueryCoroutineContext()).

The RoomDatabase.Builder.setQueryExecutor kdoc 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 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):

abstract class RoomDatabase {
  open class Builder<T : RoomDatabase> {
    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). That thread pool is also used as the default fetch scheduler for Rx paging (source: RxPagedListBuilder.kt) and the default executor for ComputableLiveData (source: ComputableLiveData.kt).

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.

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). 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!