sqlite concurrency issues

I am working on an iPhone app that uses a sqlite database. The app downloads data off of the Internet in a background thread with the UI in the main thread. The background download thread can preform INSERT, UPDATE, and SELECT on the database. The UI layer can also interact with the database by doing UPDATEs and SELECTs. If I don't heavily interact with the UI while the background thread is downloading, everything works fine. However, I start to run into problems when a lot of UPDATEs on the main (UI) thread are performed while the download is going on.

The app always quits when it is trying to run a database function. It quits with EXC_BAD_ACCESS and I don't get to see any errors. For example, the last time it quit it ended at sqlite3_step:

    sqlite3_stmt *statement;
const char *query = "INSERT OR IGNORE INTO `names` (`id`,`name`) VALUES (?,?);";
if(sqlite3_prepare_v2(database, query, -1, &statement, NULL) != SQLITE_OK){
    NSAssert1(0, @"Error while creating insert statement. '%s'", sqlite3_errmsg(database));
    return NO;
}
sqlite3_bind_int(statement, 1, id);
sqlite3_bind_text(statement, 2, name, -1, SQLITE_TRANSIENT);

if(sqlite3_step(statement) != SQLITE_DONE)
    NSAssert1(0, @"Error while inserting. '%s'", sqlite3_errmsg(datab开发者_运维知识库ase));

sqlite3_finalize(statement);

It doesn't always quit on sqlite3_step, sometimes it quits on sqlite3_prepare_v2 or sqlite3_exec. I have tried putting these statements in a loop and try again if it doesn't return OK, but that doesn't work either:

int returnCode = 0;
do{
    returnCode = sqlite3_step(statement);
    if(returnCode != SQLITE_DONE){
        usleep(20);
    }
}while(returnCode != SQLITE_DONE);

I have also tried SQL transactions, but that doesn't make any difference. How can I solve this? It seems like it is a fairly basic concurrency issue but I haven't seen anything that works for me.

Thank you for all of your help, Justin

---

Unless you recompile it with a special setting, SQLite is not thread safe.

See http://www.sqlite.org/faq.html#q6

So it's up to you to take care of accessing the DB and invoking SQL operations on it from the same thread.

However, I came up with a solution on my side that seems to be ok even in a multi threaded environnement : I ensure that any SQLite operation is protected with a @synchronized directive to ensure that once a thread is doing something on the DB, any other thread is prevented from accessing it.

So, instead of saying "all SQlite operations should be done in the same thread", I would rather say "ensure that two operations are not performed in parallel in different threads".

---

I am in the process of writing a program in Objective-C that is nearly identical w.r.t behavior.

Here is how I intend to synchronize access (The question I asked there is kind of unrelated, but have a look at the code):

Calling sqlite3_close for a static sqlite3* handle

I am going to use a static NSLock instance and lock it while writing, and then unlock it when I'm done.

I don't know how much of a change that will be for your application, but it might be a solution.

---

I'm not sure if this is a valid solution, but what I'd do is download all the data in a separate thread. But when this is done downloading, return back to the main thread, and do your inserts in the main thread.

dispatch_async(dispatch_get_global_queue(0, 0), ^{

    //download data from internet

    dispatch_async(dispatch_get_main_queue(), ^{
        //update database here
    }
}

This way you don't get any possible multi-threading issues. Since the downloading is what will take the most time, it's done in another thread, but the updating of the database should not take that long... So it will only hold up the main thread for an almost unnoticeable period. At least it should if the queries are not slow, and there aren't tons of them.

---

I had the same problem in my app which works kind of the same way. Whenever the thread updating the data from the internet started to write into the database at the same time as I did some UI-interaction that triggered an access of the DB, the program crashed.

@synchronized statements on every database query inside my DB-handler seems to solve the problem.

---

As of version 3.5.0, you can share the same database connection among multiple threads: http://www.sqlite.org/34to35.html Check the version of SQLite that you're using.

Also check out the sqlite3_threadsafe function.

I wrote a C++ program that shares a database connection between two threads, and did not get seg faults (I believe that's the same thing as EXC_BAD_ACCESS): https://gist.github.com/allyourcode/7428159 That example shows use of in-memory database, but I get similar results with disk-backed database.

I would like to analyze this with a data race tool, such as tsan, but I need to figure out how to do that :P