Android threading and database locking

Question

Hi,

We are using AsyncTasks to access database tables and cursors.

Unfortunately we are seeing occasional exceptions regarding the database being locked.

E/SQLiteOpenHelper(15963): Couldn't open iviewnews.db for writing (will try read-only):
E/SQLiteOpenHelper(15963): android.database.sqlite.SQLiteException: database is locked
E/SQLiteOpenHelper(15963):  at     android.database.sqlite.SQLiteDatabase.native_setLocale(Native Method)
E/SQLiteOpenHelper(15963):  at     android.database.sqlite.SQLiteDatabase.setLocale(SQLiteDatabase.java:1637)
E/SQLiteOpenHelper(15963):  at     android.database.sqlite.SQLiteDatabase.<init>(SQLiteDatabase.java:1587)
E/SQLiteOpenHelper(15963):  at android.database.sqlite.SQLiteDatabase.openDatabase(SQLiteDatabase.java:638)
E/SQLiteOpenHelper(15963):  at android.database.sqlite.SQLiteDatabase.openOrCreateDatabase(SQLiteDatabase.java:659)
E/SQLiteOpenHelper(15963):  at android.database.sqlite.SQLiteDatabase.openOrCreateDatabase(SQLiteDatabase.java:652)
E/SQLiteOpenHelper(15963):  at android.app.ApplicationContext.openOrCreateDatabase(ApplicationContext.java:482)
E/SQLiteOpenHelper(15963):  at android.content.ContextWrapper.openOrCreateDatabase(ContextWrapper.java:193)
E/SQLiteOpenHelper(15963):  at android.database.sqlite.SQLiteOpenHelper.getWritableDatabase(SQLiteOpenHelper.java:98)
E/SQLiteOpenHelper(15963):  at android.database.sqlite.SQLiteOpenHelper.getReadableDatabase(SQLiteOpenHelper.java:158)
E/SQLiteOpenHelper(15963):  at com.iview.android.widget.IViewNewsTopStoryWidget.initData(IViewNewsTopStoryWidget.java:73)
E/SQLiteOpenHelper(15963):  at com.iview.android.widget.IViewNewsTopStoryWidget.updateNewsWidgets(IViewNewsTopStoryWidget.java:121)
E/SQLiteOpenHelper(15963):  at com.iview.android.async.GetNewsTask.doInBackground(GetNewsTask.java:338)
E/SQLiteOpenHelper(15963):  at com.iview.android.async.GetNewsTask.doInBackground(GetNewsTask.java:1)
E/SQLiteOpenHelper(15963):  at android.os.AsyncTask$2.call(AsyncTask.java:185)
E/SQLiteOpenHelper(15963):  at java.util.concurrent.FutureTask$Sync.innerRun(FutureTask.java:256)
E/SQLiteOpenHelper(15963):  at java.util.concurrent.FutureTask.run(FutureTask.java:122)
E/SQLiteOpenHelper(15963):  at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:648)
E/SQLiteOpenHelper(15963):  at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:673)
E/SQLiteOpenHelper(15963):  at java.lang.Thread.run(Thread.java:1060)

Does anybody have a general example for code which writes to a database from a different thread than the one reading and how can we ensure thread safety.

One suggestion I've had is to use a ContentProvider, as this would handle the access of the database from multiple threads. I am going to look at this, but is this the recommended method of handling such a problem? It seems rather heavyweight considering we're talking about in front or behind

Thanks in advance.

Answer 1

You could use standard Java synchronization (e.g., synchronized(this) {} ) around your database accesses.

Answer 2

Are you talking of a single user action that, inside your program, causes multiple threads to be run, more than one of which may be accessing the database in update mode ?

That's bad design, period. There is no way for you to know in which order the threads will be scheduled by your OS (/VM), and therefore there is no way for you to know in which order the database accesses will happen, and that is very likely to imply that there is no way for you to know that database accesses will always happen in the order that you are expecting.

All database accesses generated by/coming from some user action should all be done in one single thread.

Answer 3

Take into account that SQLite databases are file based and are not intended to be able to be accessed in a multi-process way. The best procedure on mixing SQLite with multi-processing is using semaphores (aquire(), release()) in each database related access.

If you create a Db wrapper that aquires/releases a global semaphore your DB access will be thread safe. Indeed this means that you could get a bootleneck because you are queueing the access to the DB. So in addition you could only wrap the access using semaphores if it's an operation that alters the database, so while you are alterin the db no one will be able to access it and wait until the write process has been completed.

Answer 4

We used a ContentProvider in the end. This appeared to clear up the problems.