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 layer
to 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;
}
本文分享自微信公众号 – 大淘宝技术(AlibabaMTT)。
如有侵权,请联系 support@oschina.cn 删除。
本文参与“OSC源创计划”,欢迎正在阅读的你也加入,一起分享。