sqlite concurrency issues

Question

Hello,

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(database));

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

Answer 1

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):

http://stackoverflow.com/questions/2324046/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.

Answer 2

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".