SQLiteDatabasemultithreadinglocking模式

我使用这个类来pipe理底层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中的锁时被locking。

我的SQLiteOpenHelper是单例模式,DataSources只使用BasicDataSource作为父类。

在显示的代码中,我可以做些什么来避免SQLiteDatabaseLockedException?

Solutions Collecting From Web of "SQLiteDatabasemultithreadinglocking模式"

在SQLite中,可以有任意多个读者,但是任何作者都可以阻止所有其他的读者和作者。

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

请注意,只要您实际访问数据库,就必须lockinglocking。


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