Does iOS Sqlite database get locked in simultaneous background and foreground operation?

Question

I am making iOS app which is having both background and foreground database operations. I am getting 'Database locked' exception. The one most common scenario is -when my app inserting/updating/fetching data from database in background at same time if I am fetching data from database in foreground database gets locked. I am using raw sqlite queries.

I have made one common class for Database operation, do I need to make it singleton class?
Does such type of operation are done using sqlite in iOS?
I have read about THREAD_SAFE for sqlite, but not getting how to do THREAD_SAFE.

Any other approach is also appreciated..

bfich bfich · Accepted Answer · 2015-04-08T17:35:16

I just recently overcame a similar problem. I too am working on an app with many concurrent queries. I was getting a lot of SQLITE errors until I did the following two things:

Switch the Journal Mode to WAL (write-ahead logging). By default the journal mode is 'rollback journal'. It is my understanding that WAL allows you to make reads from the database while a write is happening. You can read about WAL here. WAL DOES NOT allow you to make concurrent writes.
Make sure only 1 thread can write to your DB at a time. Even if threads are writing to different tables, they should only be able to do so in a serial manner. The easiest/cleanest way to do this in my opinion is to use dispatch_barrier_synch with a common GCD queue (i.e singleton). So for instance, anytime you are performing a write query, you should execute it like this:
```
dispatch_barrier_sync(singletonQueue, ^{
    sqlite3_step(yourWriteStatement);
});
```

There are other ways to accomplish this concurrency but this way worked for me.

Does iOS Sqlite database get locked in simultaneous background and foreground operation?

1 Answers