SQLite 并发读写问题

Android 中对 SQLiteDatabase 多线程并发读写时,很容易抛出以下异常:

java.lang.IllegalStateException: Cannot perform this operation because the connection pool has been closed.
	at android.database.sqlite.SQLiteConnectionPool.throwIfClosedLocked(SQLiteConnectionPool.java:962)
	at android.database.sqlite.SQLiteConnectionPool.waitForConnection(SQLiteConnectionPool.java:599)
	at android.database.sqlite.SQLiteConnectionPool.acquireConnection(SQLiteConnectionPool.java:348)
	at android.database.sqlite.SQLiteSession.acquireConnection(SQLiteSession.java:894)

一般首先想到的肯定是 Singleton 或 sychronized 解决方式,但这样其实是 block 的,并不能解决性能问题。
最近在 SQLite 官网搜索 concurrency 找到的一篇文章中发现,开启 WAL(Write-Ahead Logging) 即可解决并发读写问题。

It does this by opening multiple connections to the database and using a different database connection for each query.
It is a good idea to enable write-ahead logging whenever a database will be concurrently accessed and modified by multiple threads at the same time.

而 Android 从 3.0 (API 11) 开始就提供了 WAL 支持:

SQLiteDatabase db = SQLiteDatabase.openDatabase("db_filename", cursorFactory,
	SQLiteDatabase.CREATE_IF_NECESSARY | SQLiteDatabase.ENABLE_WRITE_AHEAD_LOGGING,
	myDatabaseErrorHandler);

或者

db.enableWriteAheadLogging();

但是,如果没有并发读写需求,最好不要开启 WAL;因为会对增大内存占用并可能对读写速度稍有影响:

The maximum number of connections used to execute queries in parallel is dependent upon the device memory and possibly other properties.
Write-ahead logging uses significantly more memory than ordinary journaling because there are multiple connections to the same database.
WAL might be very slightly slower (perhaps 1% or 2% slower) than the traditional rollback-journal approach in applications that do mostly reads and seldom write.

参考:

Write-Ahead Logging

SQLiteDatabase | Android Developers