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?

You say leaving the cursor open for a while. I believe I have the same issue, but my cursors are being maintained by a listView. Did you ever manage to fix this? — taer, Jun 27 '10 at 14:11
Yes I fixed this by reading the contents of the cursor immediately into a collection, close the cursor and then itterate over the collection to do the time consuming work. If you're using a listView, you should be able to do the same and use an ArrayAdapter or write your own adapter extending ArrayAdapter. — TheArchedOne, Jul 01 '10 at 09:41
http://stackoverflow.com/questions/7657223/sqlite-exception-database-is-locked-issue/9503044#9503044 — Mudassar, Feb 29 '12 at 16:45

score 7 · Answer 1 · answered Feb 15 '12 at 12:18

7

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.

answered Feb 15 '12 at 12:18

Graham Borland

60,055
21
138
179

I'm not opening and closing database multiple times, though i'm getting this error please take a look [link is here](http://stackoverflow.com/questions/13859840/application-crashes-on-installation-with-error-sqlite3-exec-failed-to-set-sync) – MobileEvangelist Dec 17 '12 at 10:09

score 2 · Answer 2 · answered Mar 08 '10 at 08:22

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.

answered Mar 08 '10 at 08:22

dan04

87,747
23
163
198

score 1 · Answer 3 · answered Feb 25 '11 at 20:44

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.

score 0 · Answer 4 · answered Jul 01 '13 at 08:02

0

Its caused by beginTransaction() function.Look at your code, the problem is solved for my app to making a comment line this function(beginTransaction) line

answered Jul 01 '13 at 08:02

Serkan

641
3
11
19

How to avoid SQLiteException locking errors

4 Answers4