Fixing Android ANRs: How SQLite WAL Rescued Our Mobile App

Background write transactions in SQLite default rollback journal mode require exclusive file locks, forcing main-thread UI queries to wait until the write completes. When SQLite operates in default DELETE journal mode, it copies unmodified database pages to a separate journal file before executing edits, requiring two full disk syncs per commit. On low-end Android hardware with slow flash storage, these disk writes hold the database lock for seconds. When Jetpack Room or raw queries try to read data on the main thread during this window, the UI thread freezes and triggers an Application Not Responding (ANR) crash.

We hit this exact wall in October 2023 with FleetPulse, our Android logistics app deployed across 50,000 active handheld devices. Our background synchronization workers regularly ingested updates for offline catalog entries, inserting up to 3,000 records inside a single transaction. FleetPulse bound Jetpack Room Flow observers directly to main-thread UI components for real-time list rendering. Every time the background worker ran, the main thread entered an IOBlocked state while waiting for the background database write lock to clear.

Budget devices like the Nokia C21 and Moto E6 running low-grade eMMC 5.1 storage suffered worst.

On these phones, write-lock hold times routinely surpassed 5,000 milliseconds when disk I/O latency spiked under thermal throttling. Because Android OS input dispatching times out after 5 seconds of unresponsiveness, Google Play Console flagged thousands of ANRs on our dashboard in a single week. We realized our database architecture needed to support true reader-writer isolation without starving main-thread queries.

SQLite Write-Ahead Logging (WAL) replaces rollback journals by appending modified pages to a separate log file while leaving the original database file untouched during active writes. This decoupling allows concurrent readers to stream data from the main database file and shared-memory index without waiting for background write locks to release. Unlike DELETE or TRUNCATE modes where readers and writers block each other entirely, WAL isolation keeps UI queries moving on mobile hardware.

The differences between these SQLite modes directly impact disk I/O patterns, write amplification, and thread blocking behavior in mobile production environments.

To enable SQLite Write-Ahead Logging and set up thread connection pools in Android Jetpack Room, set the journal mode to JOURNAL_MODE_WRITE_AHEAD_LOGGING on your RoomDatabase.Builder instance. This setting executes PRAGMA journal_mode=WAL; during initial connection setup, creating the necessary -wal and -shm files on device storage. You can then attach a custom executor pool and tune runtime SQLite PRAGMA parameters inside the database callback.

Here is our working setup for configuring Room with WAL support and thread pooling:

@Database(entities = [UserEntity::class, SyncRecordEntity::class], version = 4, exportSchema = true)
abstract class AppDatabase : RoomDatabase() {
    abstract fun userDao(): UserDao
    abstract fun syncDao(): SyncDao

    companion object {
        @Volatile
        private var INSTANCE: AppDatabase? = null

        fun getInstance(context: Context): AppDatabase {
            return INSTANCE ?: synchronized(this) {
                val instance = Room.databaseBuilder(
                    context.applicationContext,
                    AppDatabase::class.java,
                    "fleetpulse_production.db"
                )
                .setJournalMode(JournalMode.WRITE_AHEAD_LOGGING)
                .enableMultiInstanceInvalidation()
                .setQueryExecutor(Executors.newFixedThreadPool(4))
                .setTransactionExecutor(Executors.newSingleThreadExecutor())
                .addCallback(object : RoomDatabase.Callback() {
                    override fun onOpen(db: SupportSQLiteDatabase) {
                        super.onOpen(db)
                        db.execSQL("PRAGMA synchronous = NORMAL;")
                        db.execSQL("PRAGMA busy_timeout = 3000;")
                    }
                })
                .build()

                INSTANCE = instance
                instance
            }
        }
    }
}

In November 2023, our platform team initially tried setting PRAGMA synchronous = OFF; to squeeze out extra write throughput during background syncs. That was a mistake. We lost several local records during battery-drain stress testing when devices unexpectedly powered down mid-sync. Switching to PRAGMA synchronous = NORMAL; retained crash safety while limiting disk syncs to WAL checkpoint boundaries.

WAL reader-writer isolation prevents UI thread contention by isolating database writes in an append-only log file while readers query the main file using a shared-memory index. When an application writes new records in WAL mode, SQLite appends those edits to a separate <database>-wal file instead of modifying the main .db file. Concurrent read queries map page locations using the companion <database>-shm file, allowing the main thread to render UI components without waiting on active write transactions.

Instead of locking the entire database file during writes, SQLite manages traffic through distinct background channels. Writers append changes sequentially to the log file and update the shared memory index. Meanwhile, readers check that same shared memory index to locate the newest version of a page. If a newer page version exists in the WAL file, SQLite returns it directly; otherwise, it reads from the original database file.

This structure ensures that disk append operations on background threads never intercept or delay main-thread read queries executed by Jetpack Room drivers.

Unchecked WAL file growth is controlled through automated checkpoint operations that transfer committed WAL pages back into the main database file. SQLite runs passive checkpoints automatically when the WAL log hits 1,000 pages (roughly 4MB). However, if long-running read transactions hold open handles on the database, passive checkpoints cannot complete, causing the WAL file to expand and slowing down read performance over time.

To prevent storage bloat on client devices, we run a scheduled WorkManager background task during app idle states to perform explicit WAL checkpoints:

class DatabaseMaintenanceWorker(
    context: Context,
    params: WorkerParameters
) : CoroutineWorker(context, params) {

    override suspend fun doWork(): Result = withContext(Dispatchers.IO) {
        return@withContext try {
            val db = AppDatabase.getInstance(applicationContext).openHelper.writableDatabase
            
            val cursor = db.query("PRAGMA wal_checkpoint(PASSIVE);")
            if (cursor.moveToFirst()) {
                val busy = cursor.getInt(0)
                val logPages = cursor.getInt(1)
                val checkpointedPages = cursor.getInt(2)
                Log.d("FleetPulseWAL", "Checkpoint - Busy: $busy, Log: $logPages, Checkpointed: $checkpointedPages")
            }
            cursor.close()
            
            db.execSQL("PRAGMA wal_checkpoint(TRUNCATE);")
            Result.success()
        } catch (e: Exception) {
            Log.e("FleetPulseWAL", "WAL maintenance failed", e)
            Result.failure()
        }
    }
}

Executing wal_checkpoint(TRUNCATE) when the user is inactive resets the -wal file size back to 0 bytes and keeps page lookup times fast across long operational sessions.

Monitoring data from Firebase Crashlytics and Google Play Console across 50,000 active devices showed a 98% drop in total ANR occurrences within two weeks of deploying WAL mode. Main-thread database wait times during background sync dropped from a 99th-percentile peak of 6,200ms down to less than 12ms. Overall app ANR rates stabilized at 0.04% of daily active users, well below the Google Play bad behavior threshold of 0.47%.

Fleet Performance Impact

• Daily ANR rate dropped from 2.15% to 0.04% across active devices
• 99th-percentile main-thread database lock time reduced from 6,200ms to under 12ms
• Background sync batch write throughput improved by 3.8x
• Slow frame occurrences dropped by 32% during active network sync jobs