How to avoid SQLiteException locking errors

Question

I'm developing an Android application. It has multiple threads reading from and writing to the Android SQLite database. I am receiving the following error:

SQLiteException: error code 5: database is locked

I understand the SQLite locks the entire db on inserting/updating, but these errors only seem to happen when inserting/updating while I'm running a select query. The select query returns a cursor which is being left open quite a wile (a few seconds some times) while I iterate over it. If the select query is not running, I never get the locks. I'm surprised that the select could lock the db. Is this possible, or is something else going on?

What's the best way to avoid such locks?

Answer 1

You are probably opening and closing multiple database connections in your various threads. This is a bad idea. Just open a single database connection, and reuse it everywhere; SQLite will then ensure that concurrent accesses are serialized correctly.

As with jcwenger's answer, using a ContentProvider is another way of achieving this, but will require much more intrusive changes to your code.

Answer 2

By avoiding leaving cursors open for "quite a while". If you can afford to have all your data in memory all at once, then do so.

If you can't, then try increasing the busy timeout.

Answer 3

Migrate to a ContentProvider rather than directly accessing the DB. ContentResolver marshals away all the threading issues for you and also allows for other useful features like sharing data between apps or syncing with a server.

The api overhead of ContentResolver is minimal. You just need to define an AUTHORITY string (A unique string identifying the "kind" of your data -- use a "com.example.myapp.contacts" type of string) and use ContentResolver.bla rather than db.bla.