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

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的线程模式，有三种：

1. 单线程 ：该模式下sqlite不使用互斥体保护自己，假定用户使用单线程访问DB，如果用户同时使用多线程访问，则不安全。
2. 多线程 ：该模式下sqlite线程安全，但前提是一个数据库连接只能被一个线程使用。（注：可以有多个数据库连接同时使用，但每个连接只能同时被一个线程使用）
3. 串行 ：该模式下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）

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;
}

综上探究的过程，我们可以得知道，默认日志模式rollback journal模式，直接切换至wal模式后是安全的，并能带来更好的并发性能。

团队介绍

我们来自淘宝逛逛客户端团队，“逛逛”主入口位于淘宝首页的一级入口，菜单栏的第二栏，是淘宝的中心化内容平台。

逛逛客户端团队追求极致的性能体验，炫酷的动效，先进的多媒体技术，用最前沿的技术给用户提供更好的内容社区体验。