java SQLiteDatabase 多线程锁定模式

SQLiteDatabase multi-thread locking pattern

提问by Marek Sebera

I use this class to manage connection to underlying SQLiteDatabase

我使用这个类来管理与底层 SQLiteDatabase 的连接

public class BasicDataSource {

    protected DatabaseHandler dbHelper;
    protected volatile SQLiteDatabase readable_database;
    protected volatile SQLiteDatabase writable_database;
    protected Object read_lock = new Object();
    protected Object write_lock = new Object();
    protected Context context;

    protected BasicDataSource(Context ctx) {
        dbHelper = DatabaseHandler.getInstance(ctx);
        getReadableDatabase();
        dbHelper.onCreate(getWritableDatabase());
        this.context = ctx;
    }

    public synchronized void close() {
        dbHelper.close();
    }

    protected void closeInsertHelpers(InsertHelper... helpers) {
        for (InsertHelper ih : helpers) {
            if (ih != null)
                ih.close();
        }
    }

    protected SQLiteDatabase getReadableDatabase() {
        synchronized (read_lock) {
            if (readable_database == null || !readable_database.isOpen()) {
                readable_database = dbHelper.getReadableDatabase();
            }
            return readable_database;
        }
    }

    protected SQLiteDatabase getWritableDatabase() {
        synchronized (write_lock) {
            if (writable_database == null || !writable_database.isOpen()) {
                writable_database = dbHelper.getWritableDatabase();
            }
            return writable_database;
        }
    }

    protected synchronized void open() throws SQLException {
        getReadableDatabase();
        getWritableDatabase();
    }
}

It contains two locks, one for read, second for write. But I'm still occasionally getting this kind of exception:

它包含两个锁，一个用于读取，第二个用于写入。但我仍然偶尔会遇到这种异常：

java.lang.RuntimeException: An error occured while executing doInBackground()
        at android.os.AsyncTask.done(AsyncTask.java:299)
        at java.util.concurrent.FutureTask$Sync.innerSetException(FutureTask.java:273)
        at java.util.concurrent.FutureTask.setException(FutureTask.java:124)
        at java.util.concurrent.FutureTask$Sync.innerRun(FutureTask.java:307)
        at java.util.concurrent.FutureTask.run(FutureTask.java:137)
        at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1076)
        at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:569)
        at java.lang.Thread.run(Thread.java:856)
Caused by: android.database.sqlite.SQLiteDatabaseLockedException: database is locked (code 5): , while compiling: PRAGMA journal_mode
        at android.database.sqlite.SQLiteConnection.nativePrepareStatement(Native Method)
        at android.database.sqlite.SQLiteConnection.acquirePreparedStatement(SQLiteConnection.java:882)
        at android.database.sqlite.SQLiteConnection.executeForString(SQLiteConnection.java:627)
        at android.database.sqlite.SQLiteConnection.setJournalMode(SQLiteConnection.java:313)
        at android.database.sqlite.SQLiteConnection.setWalModeFromConfiguration(SQLiteConnection.java:287)
        at android.database.sqlite.SQLiteConnection.open(SQLiteConnection.java:215)
        at android.database.sqlite.SQLiteConnection.open(SQLiteConnection.java:193)
        at android.database.sqlite.SQLiteConnectionPool.openConnectionLocked(SQLiteConnectionPool.java:463)
        at android.database.sqlite.SQLiteConnectionPool.open(SQLiteConnectionPool.java:185)
        at android.database.sqlite.SQLiteConnectionPool.open(SQLiteConnectionPool.java:177)
        at android.database.sqlite.SQLiteDatabase.openInner(SQLiteDatabase.java:804)
        at android.database.sqlite.SQLiteDatabase.open(SQLiteDatabase.java:789)
        at android.database.sqlite.SQLiteDatabase.openDatabase(SQLiteDatabase.java:694)
        at android.app.ContextImpl.openOrCreateDatabase(ContextImpl.java:804)
        at android.content.ContextWrapper.openOrCreateDatabase(ContextWrapper.java:221)
        at android.database.sqlite.SQLiteOpenHelper.getDatabaseLocked(SQLiteOpenHelper.java:224)
        at android.database.sqlite.SQLiteOpenHelper.getWritableDatabase(SQLiteOpenHelper.java:164)
        at com.mycompany.myapplication.sql.BasicDataSource.getWritableDatabase(BasicDataSource.java:57)
        at com.mycompany.myapplication.sql.datasources.SomeDataSource.fillUpDatabaseMethod(SomeDataSource.java:264)
        at com.mycompany.myapplication.sql.datasources.SomeDataSource.renewCacheMethod(SomeDataSource.java:560)
        at com.mycompany.myapplication.activities.lists.ListsActivity$Worker.doInBackground(ListsActivity.java:315)
        at com.mycompany.myapplication.activities.lists.ListsActivity$Worker.doInBackground(ListsActivity.java:1)
        at android.os.AsyncTask.call(AsyncTask.java:287)
        at java.util.concurrent.FutureTask$Sync.innerRun(FutureTask.java:305)
        ... 4 more
android.database.sqlite.SQLiteDatabaseLockedException: database is locked (code 5): , while compiling: PRAGMA journal_mode
        at android.database.sqlite.SQLiteConnection.nativePrepareStatement(Native Method)
        at android.database.sqlite.SQLiteConnection.acquirePreparedStatement(SQLiteConnection.java:882)
        at android.database.sqlite.SQLiteConnection.executeForString(SQLiteConnection.java:627)
        at android.database.sqlite.SQLiteConnection.setJournalMode(SQLiteConnection.java:313)
        at android.database.sqlite.SQLiteConnection.setWalModeFromConfiguration(SQLiteConnection.java:287)
        at android.database.sqlite.SQLiteConnection.open(SQLiteConnection.java:215)
        at android.database.sqlite.SQLiteConnection.open(SQLiteConnection.java:193)
        at android.database.sqlite.SQLiteConnectionPool.openConnectionLocked(SQLiteConnectionPool.java:463)
        at android.database.sqlite.SQLiteConnectionPool.open(SQLiteConnectionPool.java:185)
        at android.database.sqlite.SQLiteConnectionPool.open(SQLiteConnectionPool.java:177)
        at android.database.sqlite.SQLiteDatabase.openInner(SQLiteDatabase.java:804)
        at android.database.sqlite.SQLiteDatabase.open(SQLiteDatabase.java:789)
        at android.database.sqlite.SQLiteDatabase.openDatabase(SQLiteDatabase.java:694)
        at android.app.ContextImpl.openOrCreateDatabase(ContextImpl.java:804)
        at android.content.ContextWrapper.openOrCreateDatabase(ContextWrapper.java:221)
        at android.database.sqlite.SQLiteOpenHelper.getDatabaseLocked(SQLiteOpenHelper.java:224)
        at android.database.sqlite.SQLiteOpenHelper.getWritableDatabase(SQLiteOpenHelper.java:164)
        at com.mycompany.myapplication.sql.BasicDataSource.getWritableDatabase(BasicDataSource.java:57)
        at com.mycompany.myapplication.sql.datasources.SomeDataSource.fillUpDatabaseMethod(SomeDataSource.java:264)
        at com.mycompany.myapplication.sql.datasources.SomeDataSource.renewCacheMethod(SomeDataSource.java:560)
        at com.mycompany.myapplication.activities.lists.ListsActivity$Worker.doInBackground(ListsActivity.java:315)
        at com.mycompany.myapplication.activities.lists.ListsActivity$Worker.doInBackground(ListsActivity.java:1)
        at android.os.AsyncTask.call(AsyncTask.java:287)
        at java.util.concurrent.FutureTask$Sync.innerRun(FutureTask.java:305)
        at java.util.concurrent.FutureTask.run(FutureTask.java:137)
        at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1076)
        at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:569)
        at java.lang.Thread.run(Thread.java:856)

Which means, the database is somehow locked when trying to aquire lock in getWritableDatabase.

这意味着，当试图在 getWritableDatabase 中获取锁时，数据库以某种方式被锁定。

My SQLiteOpenHelper is singleton pattern, and DataSources are only using BasicDataSource as parent class.

我的 SQLiteOpenHelper 是单例模式，DataSources 仅使用 BasicDataSource 作为父类。

What is the improvement I can do to avoid SQLiteDatabaseLockedException in showed code?

为了避免显示代码中的 SQLiteDatabaseLockedException，我可以做哪些改进？

回答by CL.

In SQLite, there can be arbitrarily many readers, but any writer blocks all other readers and writers.

在 SQLite 中，可以有任意多个读取器，但任何写入器都会阻止所有其他读取器和写入器。

You have to use a single lock for both readers and writers.

您必须为读者和作者使用一个锁。

Please note that locks must be held as long as you're actually accessing the database.

请注意，只要您实际访问数据库，就必须持有锁。

---

---

If you want to support multiple readers, use a lock that implements ReadWriteLock, such as ReentrantReadWriteLock. Something like this:

如果要支持多个读取器，请使用实现 的锁ReadWriteLock，例如ReentrantReadWriteLock。像这样的东西：

class MyData {
    private final ReentrantReadWriteLock rwl = new ReentrantReadWriteLock();
    private final Lock r = rwl.readLock();
    private final Lock w = rwl.writeLock();

    public Data ReadSomething(int id) {
        r.lock();
        try {
            Cursor c = readableDatabase.query(...);
            return c.getString(0);
        } finally {
            r.unlock();
        }
    }

    public void ChangeSomething(int id, int value) {
        w.lock();
        try {
            writeableDatabase.update(...);
        } finally {
            w.unlock();
        }
    }
}