SQLiteDatabase multi-thread locking pattern

Question

I use this class to manage connection to underlying 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$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)

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

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

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

CL. · Accepted Answer · 2012-10-06T10:59:04.100

16

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

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:

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

edited Oct 06 '12 at 10:59

answered Oct 06 '12 at 09:57

CL.

173,858
17
217
259

In multi-thread conception, If I have multiple thread using this R/W locking pattern, concurrent reading/writing won't be handled, isn't it? I mean, when two DataSources are writing from two thread, this lock will be only valid for each single DS... – Marek Sebera Oct 06 '12 at 18:05
The purpose of the lock is to coordinate multiple threads. So all the threads would share the same lock (in this example, the `MyData` class). – CL. Oct 06 '12 at 18:58
I feel that no locks are needed, as SQLiteDatabase handles locking internally. My complex multi-thread app doesn't need any such mechanism as you describe. Write locking is handled by SQLiteDatabase.beginTransaction/endTransaction. Also, why using Lock class and not synchronized java keyword? – Pointer Null Jan 31 '13 at 13:39
3

SQLite has locking, but no synchronization, i.e., on a lock conflict, it just waits for a random amount of time and tries again. A reader/writer lock allows multiple readers at the same time. – CL. Jan 31 '13 at 15:50
@CL. Solution you propose allow sync across R/W operation. How can fixed multiple thread simultaneously write operation issue. Can you suggest ? – CoDe Apr 20 '17 at 11:09
@CoDe What do you mean with "fix"? This lock mechanism correctly handles multiple threads that attempt to write at the same time. – CL. Apr 20 '17 at 11:53
You solution Serialise all Write operation request, so even if multple thread come with Write request they have to wait in queue. Correct me if I'm wrong. – CoDe Apr 20 '17 at 12:29
@CoDe See the first sentence of my answer. – CL. Apr 20 '17 at 12:30
Do you mean WRITE operation will block of other operation but http://stackoverflow.com/a/19996964/2624806 saying about possible solution. Please check. – CoDe Apr 20 '17 at 13:35
@CoDe If you have a question, [ask a question](http://stackoverflow.com/questions/ask). – CL. Apr 20 '17 at 13:40
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/142233/discussion-between-code-and-cl). – CoDe Apr 21 '17 at 04:38
One very important note: the lock should be used on onside methods and not on the helper methods. I've put it on OnCreateDatabase() and got a very hard to trace deadlock in release version of Android app. As OnCreateDatabase was called from other thread. – Boris Treukhov Jan 08 '18 at 21:53
Another problem with this code: `readableDatabase` should be initialized out of the block because all calls of the method `getReadableDatabase()`, `getWritableDatabase` of `SQLiteOpenHelper` should be thread confined, if you study the implementation of `SQLiteOpenHelper` you'll notice that these methods synchronize on `this` but repeat the old as java design error of `Vector` and `StringBuffer` classes - they won't work with interleaving calls which open and close resources i.e. do several operations as one atomic operation. – Boris Treukhov Apr 24 '18 at 20:23

SQLiteDatabase multi-thread locking pattern

1 Answers1

Linked