背景知识
#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 */
rollback journal:Atomic Commit In SQLite（地址：https://sqlite.org/atomiccommit.html）write-ahead logging: Write-Ahead Logging（地址：https://sqlite.org/wal.html）sqlite的锁模型：链接（地址：https://sqlite.org/lockingv3.html）sqlite的线程模型：链接（地址：https://sqlite.org/threadsafe.html）Android sqlite的线程模式
参考：Using SQLite In Multi-Threaded Applications（地址：https://www.sqlite.org/threadsafe.html）
sqlite的线程模式，有三种：
- 单线程：该模式下sqlite不使用互斥体保护自己，假定用户使用单线程访问DB，如果用户同时使用多线程访问，则不安全。
- 多线程：该模式下sqlite线程安全，但前提是一个数据库连接只能被一个线程使用。（注：可以有多个数据库连接同时使用，但每个连接只能同时被一个线程使用）
- 串行：该模式下sqlite的操作完全串行，因此完全线程安全，不对用户做任何限制。
可以在编译时，开始时（初始化数据库前），运行时（创建数据库连接时）指定线程模式，后面指定的可以覆盖前面的，但如果前面指定的是单线程模式，则无法被覆盖。
根据android源码，可知sqlite编译指定的是串型模式：Android.bp配置文件（地址：https://cs.android.com/android/platform/superproject/+/master:external/sqlite/dist/Android.bp?hl=zh-cn）
cflags: [ ... "-DSQLITE_THREADSAFE=2", ... ],
然而从4.4.2开始，android源码重写了sqlite封装的相关代码，里面出现了如下文件：
源码：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 sqlite的连接池
平时我们是经过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 layer to handle queries on each database when using Write-Ahead Logging. --> <integer name="db_connection_pool_size">4</integer>
sqlite切换至WAL的优点
首先，WAL比rollback journal的并发性更好，因为WAL写不阻塞读，而rollback journal下，写会阻塞读。
其次，若业务中DatabaseManager通常会配置的是1写多读的连接池，实际android封装的sqlite使用的是1写1读的连接池，会导致读线程池存在一些竞争。
如果切换到WAL，理论上android封装的sqlite会变成1写4读的连接池，读线程池不再存在竞争。
基于sqlite的数据库，如room，是如何开启WAL的
源码：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，一个是存取并发性能提高，另一个是读线程池可以充分利用。
日志模式从journal模式切换至WAL模式是否安全
对于一个已经是rollback journal模式的sqlite数据库，可不可以切换为WAL模式？切换后会不会导致一个hot journal被忽略，进而导致数据库损坏呢？
追踪源码如下：SQLiteOpenHelper打开db最终会调用的是 sqlite3_open_v2方法，位于sqlite的main.c中。
默认情况下，sqlite使用的日志模式是DELETE(rollback journal delete)
#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. } ... } }
最终调用到：源码：android_database_SQLiteConnection.cpp（地址：https://cs.android.com/android/platform/superproject/+/master:frameworks/base/core/jni/android_database_SQLiteConnection.cpp;l=559?q=executeOne）
tatic 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文件的回滚。
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;}
综上探究的过程，我们可以得知道，默认日志模式rollback journal模式，直接切换至wal模式后是安全的，并能带来更好的并发性能。
