SQLite切换日志模式优化

发表于 2年以前 | 总阅读数：352 次

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

单线程：该模式下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）


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