Java – sqlitedatabase multi thread locking mode

I use this class to manage connections to the underlying SQLite database

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 reading and the second for writing But I occasionally get such an exception:

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)

This means that when trying to get a lock in getwritabledatabase, the database is locked in some way

My sqliteopenhelper is in singleton mode, and datasources only uses basic datasource as the parent class

What improvements can be made to avoid sqlitedatabaselockedexception in the displayed code?

Solution

In SQLite, you can have as many readers as you want, but any writer will block all other readers and writers

You must use a single lock for readers and writers

Please note that the lock must remain as long as you actually access the database

If you want to support multiple readers, use a lock that implements readwritelock, such as reentrantreadwritelock Something like this:

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();
        }
    }
}