Is Switching Android SQLite from Rollback Journal to WAL Safe? A Deep Technical Dive

Background

SQLite is a lightweight, serverless, zero‑configuration relational database engine written in C. It runs on many platforms, including Android where it is accessed via SQLiteOpenHelper or higher‑level libraries such as Room.

Journal modes

Defined in sqlite3.h:

#define PAGER_JOURNALMODE_QUERY     (-1)  /* Query the value of journalmode */
#define PAGER_JOURNALMODE_DELETE      0   /* Commit by deleting journal file */
#define PAGER_JOURNALMODE_PERSIST     1   /* Commit by zeroing journal header */
#define PAGER_JOURNALMODE_OFF         2   /* Journal omitted */
#define PAGER_JOURNALMODE_TRUNCATE    3   /* Commit by truncating journal */
#define PAGER_JOURNALMODE_MEMORY      4   /* In‑memory journal file */
#define PAGER_JOURNALMODE_WAL         5   /* Write‑ahead logging */

The default is DELETE. WAL improves concurrency because writes do not block reads.

Thread modes

Single‑thread : No mutexes; the caller must ensure exclusive access.

Multi‑thread : Safe for multiple threads provided each connection is used by only one thread at a time.

Serialized : Fully thread‑safe; any thread may use any connection.

Android builds SQLite with SQLITE_THREADSAFE=2, selecting the multi‑thread mode.

Android SQLite connection pool

Android wraps SQLite in SQLiteConnectionPool. There is a single primary (write) connection; additional non‑primary connections are read‑only. The pool size depends on the journal mode:

private void setMaxConnectionPoolSizeLocked() {
    if (!mConfiguration.isInMemoryDb()
            && (mConfiguration.openFlags & SQLiteDatabase.ENABLE_WRITE_AHEAD_LOGGING) != 0) {
        mMaxConnectionPoolSize = SQLiteGlobal.getWALConnectionPoolSize(); // critical step
    } else {
        mMaxConnectionPoolSize = 1;
    }
}

public static int getWALConnectionPoolSize() {
    int value = SystemProperties.getInt("debug.sqlite.wal.poolsize",
            Resources.getSystem().getInteger(com.android.internal.R.integer.db_connection_pool_size));
    return Math.max(2, value);
}

When WAL is enabled the default maximum is 4 non‑primary connections; otherwise it is 1.

Enabling WAL on Android

Since Android 4.1 (Jelly Bean) calling setWriteAheadLoggingEnabled(true) triggers a PRAGMA journal_mode=WAL statement:

private OpenHelper getDelegate() {
    synchronized (mLock) {
        if (mDelegate == null) {
            // … create OpenHelper …
            if (Build.VERSION.SDK_INT >= Build.VERSION_CODES.JELLY_BEAN) {
                mDelegate.setWriteAheadLoggingEnabled(mWriteAheadLoggingEnabled);
            }
        }
        return mDelegate;
    }
}

Safety of switching from rollback journal to WAL

When the journal mode changes SQLite runs sqlite3PagerSetJournalMode. If a hot (non‑empty) .journal file exists, SQLite acquires an exclusive lock, rolls back the journal, and only then switches to the new mode. The detection is performed in hasHotJournal and the rollback in pagerSyncHotJournal:

int hasHotJournal(Pager *pPager, int *pExists) {
    // … check if journal file exists …
    if (exists) {
        // read first byte; non‑zero => hot journal
        *pExists = (first != 0);
    }
    return rc;
}

If a hot journal is found, SQLite runs pagerSyncHotJournal and replays the journal before opening the database in WAL mode, guaranteeing that no uncommitted changes are lost.

How Room/SQLiteOpenHelper enable WAL

The framework creates the helper lazily and, on API ≥ JELLY_BEAN, calls setWriteAheadLoggingEnabled on the delegate. The call reaches native code that executes PRAGMA journal_mode=WAL via nativeExecuteForString. The support library provides the same entry point through SupportSQLiteCompat.setWriteAheadLoggingEnabled.

Key native functions involved in the mode change

int sqlite3PagerSetJournalMode(Pager *pPager, int eMode) {
    int eOld = pPager->journalMode;
    if (eMode != eOld) {
        pPager->journalMode = (u8)eMode;
        if (!pPager->exclusiveMode && (eOld & 5) == 1 && (eMode & 1) == 0) {
            // close old journal, possibly delete it
        }
        // … additional handling …
    }
    return (int)pPager->journalMode;
}

int sqlite3PagerSharedLock(Pager *pPager) {
    // … acquire SHARED lock …
    if (pPager->eLock <= SHARED_LOCK) {
        rc = hasHotJournal(pPager, &bHotJournal);
        if (bHotJournal) {
            // obtain EXCLUSIVE lock, sync and replay hot journal
            rc = pagerSyncHotJournal(pPager);
            rc = pager_playback(pPager, !pPager->tempFile);
        }
    }
    // …
    return rc;
}

Conclusion

Source‑code tracing confirms that converting an existing SQLite database from the default rollback‑journal mode to WAL is safe: SQLite automatically detects a hot journal, rolls it back under an exclusive lock, and then enables WAL. On Android this also expands the connection pool from a 1‑write‑1‑read configuration to a 1‑write‑multiple‑read configuration, improving read concurrency.

References

Atomic Commit in SQLite – https://sqlite.org/atomiccommit.html

Write‑Ahead Logging – https://sqlite.org/wal.html

SQLite locking model – https://sqlite.org/lockingv3.html

SQLite thread‑safety – https://sqlite.org/threadsafe.html

Android source – Android.bp (SQLite build) – https://cs.android.com/android/platform/superproject/+/master:external/sqlite/dist/Android.bp

android_database_SQLiteGlobal.cpp – https://www.sqlite.org/android/file?name=sqlite3/src/main/jni/sqlite/android_database_SQLiteGlobal.cpp

SQLiteConnectionPool.java – https://cs.android.com/android/platform/superproject/+/master:frameworks/base/core/java/android/database/sqlite/SQLiteConnectionPool.java

FrameworkSQLiteOpenHelper – https://cs.android.com/androidx/platform/frameworks/support/+/androidx-main:sqlite/sqlite-framework/src/main/java/androidx/sqlite/db/framework/FrameworkSQLiteOpenHelper.java