Patrón de bloqueo de múltiples hilos de SQLiteDatabase

Utilizo esta clase para manejar la conexión al SQLiteDatabase subyacente

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

Contiene dos cerraduras, una para leer, otra para escribir. Pero todavía estoy ocasionalmente recibiendo este tipo de excepción:

 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)

Lo que significa que la base de datos está bloqueada de alguna manera al intentar obtener bloqueo en getWritableDatabase.

Mi SQLiteOpenHelper es singleton patrón, y DataSources sólo están usando BasicDataSource como la clase padre.

¿Cuál es la mejora que puedo hacer para evitar SQLiteDatabaseLockedException en código mostrado?

En SQLite, puede haber arbitrariamente muchos lectores, pero cualquier escritor bloquea a todos los demás lectores y escritores.

Usted tiene que utilizar un solo bloqueo para los lectores y los escritores.

Tenga en cuenta que las cerraduras deben mantenerse mientras esté accediendo a la base de datos.

Si desea admitir varios lectores, utilice un bloqueo que implemente ReadWriteLock , como ReentrantReadWriteLock . Algo como esto:

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