SQLiteDatabase multi-thread locking pattern
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();
}
}
}
Marek Sebera
Updated on July 18, 2022Comments
-
Marek Sebera almost 2 years
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?
-
Marek Sebera over 11 yearsIn 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...
-
Wirsing over 11 yearsThe purpose of the lock is to coordinate multiple threads. So all the threads would share the same lock (in this example, the
MyData
class). -
Pointer Null over 11 yearsI 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?
-
Wirsing over 11 yearsSQLite 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.
-
CoDe about 7 years@CL. Solution you propose allow sync across R/W operation. How can fixed multiple thread simultaneously write operation issue. Can you suggest ?
-
Wirsing about 7 years@CoDe What do you mean with "fix"? This lock mechanism correctly handles multiple threads that attempt to write at the same time.
-
CoDe about 7 yearsYou 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.
-
Wirsing about 7 years@CoDe See the first sentence of my answer.
-
CoDe about 7 yearsDo you mean WRITE operation will block of other operation but stackoverflow.com/a/19996964/2624806 saying about possible solution. Please check.
-
Wirsing about 7 years@CoDe If you have a question, ask a question.
-
CoDe about 7 yearsLet us continue this discussion in chat.
-
Boris Treukhov over 6 yearsOne 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 about 6 yearsAnother problem with this code:
readableDatabase
should be initialized out of the block because all calls of the methodgetReadableDatabase()
,getWritableDatabase
ofSQLiteOpenHelper
should be thread confined, if you study the implementation ofSQLiteOpenHelper
you'll notice that these methods synchronize onthis
but repeat the old as java design error ofVector
andStringBuffer
classes - they won't work with interleaving calls which open and close resources i.e. do several operations as one atomic operation.