SQLiteDatabase多线程锁定模式

Question

我使用此类来管理与基础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();
    }
}

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

java.lang.RuntimeException: An error occured while executing doInBackground()
        at android.os.AsyncTask$3.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$2.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$2.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)

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

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

我可以做些什么来避免在显示的代码中出现SQLiteDatabaseLockedException？

Answer 1

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

读者和作家都必须使用一个锁。

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

如果要支持多个阅读器，请使用实现的锁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();
        }
    }
}