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

Question

I use the SQLite database in my application for iPhone. At startup, there are some database actions that I want to perform in a separate thread. (I do this mainly to minimize startup time.)

Sometimes / accidentally, when these database calls are made from the background thread, the application crashes with the following 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 cannot reliably reproduce the error, I convinced myself that this is due to the fact that SQLite functions are called in both active threads. How should I call SQLite functions from a separate thread? Is there a trick I'm missing out on? I am new to iPhone, SQLite and Objective-C, so this may be obvious to you, but not so obvious to me.

Here are some sample code.

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

This error message is displayed in SQLITE_MISUSE (source code available at http://www.sqlite.org ).

See http://www.sqlite.org/faq.html#q6 for restrictions on using the sqlite3 * database descriptor from multiple threads. In fact, you are allowed to reuse the database descriptor and thread instructions, but one thread must be fully executed to access the database before the start of the other thread (i.e., Blocking access is unsafe). This is similar to what is happening for you and corresponds to the SQLITE_MISUSE error code.

If you need to access a single database from multiple threads, I recommend instead opening the database separately from each thread and setting a timeout using sqlite3_busy_timeout (). Sqlite then handles the conflicts for you, blocking for a short time in one thread if another thread writes data while keeping reading at the same time.

Answer 2

I tried these two solutions and they worked perfectly. You can use critical sections or NSOperationQueue, and I prefer the first, 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 use 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 writing to the database is complete, which you can consider in most cases.

Answer 3

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

Answer 4

If you want to use SQLite for multiple threads without any restrictions, do the following before opening the connection:

sqlite3_shutdown ();
sqlite3_config (SQLITE_CONFIG_SERIALIZED);
sqlite3_initialize ();

http://www.sqlite.org/threadsafe.html

Answer 5

I would use NSOperation and just do everything there at launch time. Breeds NSOperation. I said how much NSOperation is swinging? It does. Scala, that is.

Answer 6

If you're still unlucky above, you can try using this EnormEGO shell https://github.com/jdp-global/egodatabase

They use asynchronous callbacks that can kill two birds with one stone.

Check out my Readme for EGODatabaseRequest - Asynchronous Db Queries

Answer 7

It is best to use GCD (Grand Central Dispatch) queues to prevent concurrent access to the sqlite database.

Using any form of locking (including file locking that will be used by multiple database instances) can cause a busy wait, which is wasteful.

See my answer to a similar question.

Answer 8

My solution is to remove the application on my device (I want to delete the database I created). This solves the problem for me.