社区编辑申请
注册/登录
SQLite切换日志模式优化
运维 新闻
我们需要探究从默认日志模式rollback journal模式,直接切换至wal模式后是否安全呢?

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模式后是安全的,并能带来更好的并发性能。

团队介绍

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

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

责任编辑:张燕妮 来源: 大淘宝技术

同话题下的热门内容

大意了!平常不重视慢SQL,搞得现在系统要重构……揭秘运维工程师职业生涯天花板 SRE (Site Reliability Engineering) 的工作职责Sentry日志监控系统基本介绍及部署使用基于 Flink 构建大规模实时风控系统在阿里巴巴的落地

编辑推荐

官方自爆了!去年今天的B站原来是这样崩溃的……日志系统成本飙升千万,吓得我赶紧把ES换成ClickHouse……不做运维大冤种!锐捷乐享智能运维管理平台提供新解运维大牛万字自述:道尽十多年血泪史与转型自救路自从上了 Prometheus 监控,睡觉真“香”
我收藏的内容
点赞
收藏

51CTO技术栈公众号