How to properly call SQLite functions from background thread on iPhone?

Question

I'm using an SQLite database in my iPhone app. At startup, there are some database actions that I want to perform in a separate thread. (I'm doing this mainly to minimize startup time.)

Occasionally/randomly, when these database calls are made from the background thread, the app will crash with these errors:

2009-04-13 17:36:09.932 Action Lists[1537:20b] *** Assertion failure in -[InboxRootViewController getInboxTasks], /Users/cperry/Dropbox/Projects/iPhone GTD/GTD/Classes/InboxRootViewController.m:74
2009-04-13 17:36:09.932 Action Lists[1537:3d0b] *** Assertion failure in +[Task deleteCompletedTasksInDatabase:completedMonthsAgo:], /Users/cperry/Dropbox/Projects/iPhone GTD/GTD/Classes/Data Classes/Task.m:957
2009-04-13 17:36:09.933 Action Lists[1537:20b] *** Terminating app due to uncaught exception 'NSInternalInconsistencyException', reason: 'Error: failed to prepare statement with message 'library routine called out of sequence'.'
2009-04-13 17:36:09.933 Action Lists[1537:3d0b] *** Terminating app due to uncaught exception 'NSInternalInconsistencyException', reason: 'Error: failed to prepare statement with message 'library routine called out of sequence'.'

Although I can't reliably reproduce the error, I've convinced myself that it is due to the fact that SQLite functions are being called in both active threads. How should I be calling SQLite functions from a separate thread? Is there a trick I'm missing? I'm pretty new to the iPhone, SQLite, and Objective-C, so it might be obvious to you, but not so obvious to me.

Here are some code samples.

MainApplication.m:

- (void)applicationDidFinishLaunching:(UIApplication *)application {

    // Take care of jobs that have to run at startup
    [NSThread detachNewThreadSelector:@selector(startUpJobs) toTarget:self withObject:nil];
}

// Jobs that run in the background at startup
- (void)startUpJobs {

    // Anticipating that this method will be called in its NSThread, set up an autorelease pool.
    NSAutoreleasePool *pool = [[NSAutoreleasePool alloc] init];

    // Get user preferences
    NSUserDefaults *defaults = [NSUserDefaults standardUserDefaults];

    // This Class Method calls SQLite functions and sometimes causes errors.
    [Task revertFutureTasksStatus:database];


    [pool release];
}

Task.m:

static sqlite3_stmt *revert_future_statement = nil;

+ (void) revertFutureTasksStatus:(sqlite3 *)db {

    if (revert_future_statement == nil) {
        // Find all tasks that meet criteria
        static char *sql = "SELECT task_id FROM tasks where ((deleted IS NULL) OR (deleted=0)) AND (start_date > ?) AND (status=0) AND (revert_status IS NOT NULL)";
        if (sqlite3_prepare_v2(db, sql, -1, &revert_future_statement, NULL) != SQLITE_OK) {
            NSAssert1(0, @"Error: failed to prepare update statement with message '%s'.", sqlite3_errmsg(db));
        }
    }

    // Bind NOW to sql statement
    NSDate *now = [[NSDate alloc] init];
    NSDateFormatter *formatter = [[NSDateFormatter alloc] init];
    [formatter setDateFormat:@"yyyy-MM-dd"];
    NSString *nowString = [formatter stringFromDate:now];
    sqlite3_bind_text(revert_future_statement, 1, [nowString UTF8String], -1, SQLITE_TRANSIENT);
    [now release];
    [formatter release];

    // We "step" through the results - once for each row.
    while (sqlite3_step(revert_future_statement) == SQLITE_ROW) {

        // Do things to each returned row

    }

    // Reset the statement for future reuse.
    sqlite3_reset(revert_future_statement);
}

Answer 1

I've tried these two solutions and they worked perfectly. You can either use critical sections or NSOperationQueue and I prefer the first one, here is the code for both of them:

define some class "DatabaseController" and add this code to its implementation:

static NSString * DatabaseLock = nil;
+ (void)initialize {
    [super initialize];
    DatabaseLock = [[NSString alloc] initWithString:@"Database-Lock"];
}
+ (NSString *)databaseLock {
    return DatabaseLock;
}

- (void)writeToDatabase1 {
    @synchronized ([DatabaseController databaseLock]) {
        // Code that writes to an sqlite3 database goes here...
    }
}
- (void)writeToDatabase2 {
    @synchronized ([DatabaseController databaseLock]) {
        // Code that writes to an sqlite3 database goes here...
    }
}

OR to use the NSOperationQueue you can use:

static NSOperationQueue * DatabaseQueue = nil;
+ (void)initialize {
    [super initialize];

    DatabaseQueue = [[NSOperationQueue alloc] init];
    [DatabaseQueue setMaxConcurrentOperationCount:1];
}
+ (NSOperationQueue *)databaseQueue {
    return DatabaseQueue;
}

- (void)writeToDatabase {
    NSInvocationOperation * operation = [[NSInvocationOperation alloc] initWithTarget:self selector:@selector(FUNCTION_THAT_WRITES_TO_DATABASE) object:nil];
    [operation setQueuePriority:NSOperationQueuePriorityHigh];
    [[DatabaseController databaseQueue] addOperations:[NSArray arrayWithObject:operation] waitUntilFinished:YES];
    [operation release];
}

these two solutions block the current thread until the writing to database is finished which you may consider in most of the cases.

Answer 2

That error message maps to SQLITE_MISUSE (the source code is available at http://www.sqlite.org).

See http://www.sqlite.org/faq.html#q6 for limitations on using an sqlite3 * database handle from more than one thread. Effectively, you are allowed to reuse a database handle and statements across threads but one thread must be completely done accessing the database before the other thread starts (i.e. overlapping access is not safe). That sounds like what's happening for you and is consistent with the SQLITE_MISUSE error code.

If you need to access the same database from more than one thread, I recommend instead opening the database separately from each thread and setting a timeout using sqlite3_busy_timeout(). Sqlite will then handle contention for you, blocking for a short time in one thread if the other thread is writing data while still allowing simultaneous reads.

Answer 3

SQLite handles (sqlite3_stmt * for sure, and sqlite3 * I think) are thread-specific. The correct way to call them from multiple threads is to maintain a separate set of handles for each thread.