SQLite切换日志模式优化
SQLite是一款轻型的数据库，SQLite 是一款轻量级数据库，是一个 关系型数据库管理系统，它包含在一个相对小的 C 库中，实现了自给自足的、无服务器的、零配置的、事务性的 SQL 数据库引擎。它能够支持 Windows/Linux/Unix/Android/iOS 等等主流的操作系统，占用资源非常的低，因此在移动端也有很广泛的应用。
SQLIte有多种日志模式（具体见背景知识），在项目的开发迭代中，会遇见原本就版本app使用的SQLite日志模式是旧版默认的rpllback journal，当用户设备存在待恢复的.journal文件，新版本app的SQLite又需要将日志模式切换至wal时，我们就需要探究从默认日志模式rollback journal模式，直接切换至wal模式后是否安全呢？
#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 /* Use write-ahead logging */
单线程：该模式下sqlite不使用互斥体保护自己，假定用户使用单线程访问DB，如果用户同时使用多线程访问，则不安全。 多线程：该模式下sqlite线程安全，但前提是一个数据库连接只能被一个线程使用。（注：可以有多个数据库连接同时使用，但每个连接只能同时被一个线程使用） 串行：该模式下sqlite的操作完全串行，因此完全线程安全，不对用户做任何限制。
cflags: [..."-DSQLITE_THREADSAFE=2",...],
源码：android_database_SQLiteGlobal.cpp（地址：https://www.sqlite.org/android/file?name=sqlite3/src/main/jni/sqlite/android_database_SQLiteGlobal.cpp）
将sqlite的线程模式改为多线程：
static void sqliteInitialize() {// Enable multi-threaded mode. In this mode, SQLite is safe to use by multiple// threads as long as no two threads use the same database connection at the same// time (which we guarantee in the SQLite database wrappers).sqlite3_config(SQLITE_CONFIG_MULTITHREAD);<<====关键步骤======...// Initialize SQLite.sqlite3_initialize();}
平时我们是经过android封装的SqliteOpenHelper来访问sqlite的，常用的room和ormlite等数据库本质上是使用SqliteOpenHelper，android的封装中有一个primary connection的概念，只有primary connecton可以写，其他connection只能读。
阅读源码可以发现，SQLiteStatement和SQLiteQuery都会根据自己要执行的sql语句提前判断这个是不是readOnly的，只有非readOnly的才需要primary connection，若nonPrimaryConnecion拿不到，也会尝试获取primary connection。
跟踪源码可以发现android封装了SQLiteConnectionPool，primary connection有且仅有一个，noPrimaryConnection可以有多个。
源码：SQLiteConnectionPool.java（地址：https://cs.android.com/android/platform/superproject/+/master:frameworks/base/core/java/android/database/sqlite/SQLiteConnectionPool.java）
但是其中会有一个最大的nonPrimaryConnecton的逻辑，rollback journal模式下最大为1，WAL模式下最小为2。
private void setMaxConnectionPoolSizeLocked() {if (!mConfiguration.isInMemoryDb()&& (mConfiguration.openFlags & SQLiteDatabase.ENABLE_WRITE_AHEAD_LOGGING) != 0) {mMaxConnectionPoolSize = SQLiteGlobal.getWALConnectionPoolSize();<<=====关键步骤===} else {// We don't actually need to always restrict the connection pool size to 1// for non-WAL databases. There might be reasons to use connection pooling// with other journal modes. However, we should always keep pool size of 1 for in-memory// databases since every :memory: db is separate from another.// For now, enabling connection pooling and using WAL are the same thing in the API.mMaxConnectionPoolSize = 1;}}/*** Gets the connection pool size when in WAL mode.*/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);}
项目中，正常使用的数据库模式不是内存db，没有进行日志模式优化前，也不是WAL日志模式，所以走的是else里面的逻辑，nonPrimaryConnection最大值为1。
WAL模式下，系统性默认配置的是最大4个nonPrimaryConnection。
源码：config.xml（地址：https://cs.android.com/android/platform/superproject/+/master:frameworks/base/core/res/res/values/config.xml）
<!-- Maximum number of database connections opened and managed by framework layerto handle queries on each database when using Write-Ahead Logging. --><integer name="db_connection_pool_size">4</integer>
首先，WAL比rollback journal的并发性更好，因为WAL写不阻塞读，而rollback journal下，写会阻塞读。
其次，若业务中DatabaseManager通常会配置的是1写多读的连接池，实际android封装的sqlite使用的是1写1读的连接池，会导致读线程池存在一些竞争。
如果切换到WAL，理论上android封装的sqlite会变成1写4读的连接池，读线程池不再存在竞争。
源码：FrameworkSQLiteOpenHelper.java（地址：https://cs.android.com/androidx/platform/frameworks/support/+/androidx-main:sqlite/sqlite-framework/src/main/java/androidx/sqlite/db/framework/FrameworkSQLiteOpenHelper.java;l=32?q=Framework&ss=androidx）
当android版本高于4.1(jellyBean)，sqlite会自动开启WAL日志模式。
private OpenHelper getDelegate() {// getDelegate() is lazy because we don't want to File I/O until the call to// getReadableDatabase() or getWritableDatabase(). This is better because the call to// a getReadableDatabase() or a getWritableDatabase() happens on a background thread unless// queries are allowed on the main thread.// We defer computing the path the database from the constructor to getDelegate()// because context.getNoBackupFilesDir() does File I/O :(synchronized (mLock) {if (mDelegate == null) {final FrameworkSQLiteDatabase[] dbRef = new FrameworkSQLiteDatabase[1];if (Build.VERSION.SDK_INT >= Build.VERSION_CODES.M&& mName != null&& mUseNoBackupDirectory) {File file = new File(mContext.getNoBackupFilesDir(), mName);mDelegate = new OpenHelper(mContext, file.getAbsolutePath(), dbRef, mCallback);} else {mDelegate = new OpenHelper(mContext, mName, dbRef, mCallback);}if (Build.VERSION.SDK_INT >= Build.VERSION_CODES.JELLY_BEAN) {<<============关键步骤==================>>mDelegate.setWriteAheadLoggingEnabled(mWriteAheadLoggingEnabled);}}return mDelegate;}}
源码：SupportSQLiteCompat.java（地址：https://cs.android.com/androidx/platform/frameworks/support/+/androidx-main:sqlite/sqlite/src/main/java/androidx/sqlite/db/SupportSQLiteCompat.java）
public static void setWriteAheadLoggingEnabled(@NonNull SQLiteOpenHelper sQLiteOpenHelper,boolean enabled) {sQLiteOpenHelper.setWriteAheadLoggingEnabled(enabled);}
理论上，如果切换到WAL，一个是存取并发性能提高，另一个是读线程池可以充分利用。
sqlite3_open_v2方法，位于sqlite的main.c中。#define PAGER_JOURNALMODE_DELETE 0 /* Commit by deleting journal file */当调用enableWriteAheadLogging，实际会通过nativeExecuteForString执行PRAGMA指令。
private void setJournalMode(String newValue) {String value = executeForString("PRAGMA journal_mode", null, null);if (!value.equalsIgnoreCase(newValue)) {try {<<=======关键步骤=========>>String result = executeForString("PRAGMA journal_mode=" + newValue, null, null);if (result.equalsIgnoreCase(newValue)) {return;}// PRAGMA journal_mode silently fails and returns the original journal// mode in some cases if the journal mode could not be changed.} catch (SQLiteDatabaseLockedException ex) {// This error (SQLITE_BUSY) occurs if one connection has the database// open in WAL mode and another tries to change it to non-WAL.}...}}
static int executeOneRowQuery(JNIEnv* env, SQLiteConnection* connection, sqlite3_stmt* statement) {int err = sqlite3_step(statement);<<======关键步骤==========if (err != SQLITE_ROW) {throw_sqlite3_exception(env, connection->db);}return err;}
跟随代码进度走到sqlite3VdbeExec，在里面可以找到case_OP_JournalMode，就能看到相关的处理逻辑。
最关键的地方就是调用了sqlite3PageSetJournalMode这个方法里会尝试调用sqlite3PageSharedLock这个方法来判断是否hasHotJouenal，有的话会尝试获取EXECLUSIVE_LOCK，进行回滚。因此，在打开数据库时切换日志模式是安全的。
int sqlite3PagerSetJournalMode(Pager *pPager, int eMode){u8 eOld = pPager->journalMode; /* Prior journalmode */...if( eMode!=eOld ){/* Change the journal mode. */assert( pPager->eState!=PAGER_ERROR );pPager->journalMode = (u8)eMode;...if( !pPager->exclusiveMode && (eOld & 5)==1 && (eMode & 1)==0 ){...sqlite3OsClose(pPager->jfd);if( pPager->eLock>=RESERVED_LOCK ){sqlite3OsDelete(pPager->pVfs, pPager->zJournal, 0);}else{int rc = SQLITE_OK;int state = pPager->eState;assert( state==PAGER_OPEN || state==PAGER_READER );if( state==PAGER_OPEN ){rc = sqlite3PagerSharedLock(pPager);<<=====关键步骤==============}...assert( state==pPager->eState );}}else if( eMode==PAGER_JOURNALMODE_OFF ){sqlite3OsClose(pPager->jfd);}}/* Return the new journal mode */return (int)pPager->journalMode;}
sqlite3PagerShareLock中会判断是否有hot journal，执行pagerSyncJournal，进行hot journa文件的回滚。int sqlite3PagerSharedLock(Pager *pPager){int rc = SQLITE_OK; /* Return code *//* This routine is only called from b-tree and only when there are no** outstanding pages. This implies that the pager state should either** be OPEN or READER. READER is only possible if the pager is or was in** exclusive access mode. */assert( sqlite3PcacheRefCount(pPager->pPCache)==0 );assert( assert_pager_state(pPager) );assert( pPager->eState==PAGER_OPEN || pPager->eState==PAGER_READER );assert( pPager->errCode==SQLITE_OK );if( !pagerUseWal(pPager) && pPager->eState==PAGER_OPEN ){int bHotJournal = 1; /* True if there exists a hot journal-file */assert( !MEMDB );assert( pPager->tempFile==0 || pPager->eLock==EXCLUSIVE_LOCK );rc = pager_wait_on_lock(pPager, SHARED_LOCK);if( rc!=SQLITE_OK ){assert( pPager->eLock==NO_LOCK || pPager->eLock==UNKNOWN_LOCK );goto failed;}/* If a journal file exists, and there is no RESERVED lock on the** database file, then it either needs to be played back or deleted.*/if( pPager->eLock<=SHARED_LOCK ){rc = hasHotJournal(pPager, &bHotJournal);<<=========关键步骤=============}if( rc!=SQLITE_OK ){goto failed;}if( bHotJournal ){if( pPager->readOnly ){rc = SQLITE_READONLY_ROLLBACK;goto failed;}/* Get an EXCLUSIVE lock on the database file. At this point it is** important that a RESERVED lock is not obtained on the way to the** EXCLUSIVE lock. If it were, another process might open the** database file, detect the RESERVED lock, and conclude that the** database is safe to read while this process is still rolling the** hot-journal back.*/...if( isOpen(pPager->jfd) ){assert( rc==SQLITE_OK );rc = pagerSyncHotJournal(pPager); <<============关键步骤==============if( rc==SQLITE_OK ){rc = pager_playback(pPager, !pPager->tempFile);pPager->eState = PAGER_OPEN;}}else if( !pPager->exclusiveMode ){
HasHotJournal：的代码如下：
static int hasHotJournal(Pager *pPager, int *pExists){sqlite3_vfs * const pVfs = pPager->pVfs;int rc = SQLITE_OK; /* Return code */int exists = 1; /* True if a journal file is present */int jrnlOpen = !!isOpen(pPager->jfd);assert( pPager->useJournal );assert( isOpen(pPager->fd) );assert( pPager->eState==PAGER_OPEN );assert( jrnlOpen==0 || ( sqlite3OsDeviceCharacteristics(pPager->jfd) &SQLITE_IOCAP_UNDELETABLE_WHEN_OPEN));*pExists = 0;if( !jrnlOpen ){rc = sqlite3OsAccess(pVfs, pPager->zJournal, SQLITE_ACCESS_EXISTS, &exists);}if( rc==SQLITE_OK && exists ){int locked = 0; /* True if some process holds a RESERVED lock *//* Race condition here: Another process might have been holding the** the RESERVED lock and have a journal open at the sqlite3OsAccess()** call above, but then delete the journal and drop the lock before** we get to the following sqlite3OsCheckReservedLock() call. If that** is the case, this routine might think there is a hot journal when** in fact there is none. This results in a false-positive which will** be dealt with by the playback routine. Ticket #3883.*/rc = sqlite3OsCheckReservedLock(pPager->fd, &locked);if( rc==SQLITE_OK && !locked ){Pgno nPage; /* Number of pages in database file */assert( pPager->tempFile==0 );rc = pagerPagecount(pPager, &nPage);if( rc==SQLITE_OK ){/* If the database is zero pages in size, that means that either (1) the** journal is a remnant from a prior database with the same name where** the database file but not the journal was deleted, or (2) the initial** transaction that populates a new database is being rolled back.** In either case, the journal file can be deleted. However, take care** not to delete the journal file if it is already open due to** journal_mode=PERSIST.*/if( nPage==0 && !jrnlOpen ){sqlite3BeginBenignMalloc();if( pagerLockDb(pPager, RESERVED_LOCK)==SQLITE_OK ){sqlite3OsDelete(pVfs, pPager->zJournal, 0);if( !pPager->exclusiveMode ) pagerUnlockDb(pPager, SHARED_LOCK);}sqlite3EndBenignMalloc();}else{/* The journal file exists and no other connection has a reserved** or greater lock on the database file. Now check that there is** at least one non-zero bytes at the start of the journal file.** If there is, then we consider this journal to be hot. If not,** it can be ignored.*/if( !jrnlOpen ){int f = SQLITE_OPEN_READONLY|SQLITE_OPEN_MAIN_JOURNAL;rc = sqlite3OsOpen(pVfs, pPager->zJournal, pPager->jfd, f, &f);}if( rc==SQLITE_OK ){u8 first = 0;rc = sqlite3OsRead(pPager->jfd, (void *)&first, 1, 0);if( rc==SQLITE_IOERR_SHORT_READ ){rc = SQLITE_OK;}if( !jrnlOpen ){sqlite3OsClose(pPager->jfd);}*pExists = (first!=0);}else if( rc==SQLITE_CANTOPEN ){/* If we cannot open the rollback journal file in order to see if** it has a zero header, that might be due to an I/O error, or** it might be due to the race condition described above and in** ticket #3883. Either way, assume that the journal is hot.** This might be a false positive. But if it is, then the** automatic journal playback and recovery mechanism will deal** with it under an EXCLUSIVE lock where we do not need to** worry so much with race conditions.*/*pExists = 1;rc = SQLITE_OK;}}}}}return rc;}