Sharing SQLITE connection on iPhone threads may leave you hanging

iPhone SDK makes asynchronous operations really easy to use. There is no excuse for not making an application where the UI freezes whilst you’re downloading a URL or running a time-consuming SQL query. Here is an example of creating a background task (using NSInvocationOperation class) that you could call once the UI view has been initialized. In this example, the method init_data_details will be assigned its own thread and it will run in the background leaving the main thread for accepting user input. You’ll need to create a queue which here I’ve done in the Application Delegate class (essentially as a global variable that can be accessed from all views).

AppDelegate *appDelegate = (AppDelegate *)[[UIApplication sharedApplication] delegate];
NSInvocationOperation *operation = 
    [[NSInvocationOperation alloc] 
[appDelegate.queue addOperation:operation];
[operation release];

In the init_data_details, I was running SQL queries on a local SQLITE3 database. To improve the performance of SQLITE, I was reusing a connection and all prepared statements. In case of memory warnings, I closed the connection and sqlite3_finalize all prepared statements. The problem was that I was sharing the same connection across multiple threads. This resulted in mysterious crashes with SQLITE3 stack traces that always ended up at sqlite3VdbeMemGrow. The fix in this case was to open a new connection (and prepare new statements) for all SQL accesses that were taking place in the background thread. Here is the outline of the operation:

- (void) init_data_details
// SQL is my encapsulation of the access to SQLITE3
// It creates a connection, handles open/close and 
// prepare statements

	SQL *sql2 = [[SQL alloc] init];
// pass this new connection to methods that need to access 
// the database in the background thread.

	NSMutableArray *data_array =
        [Someclass/object method  connection:sql2];

// process the data, update the UI, ...
// Here updating a UITableView object called
// overview_table

	[overview_table reloadData];

// Close connection, finialize prepare statements 
// for this connection, ...
	[sql2 close_connection];
	[sql2 release];

One Response to “Sharing SQLITE connection on iPhone threads may leave you hanging”

  1. Bill Says:

    You can’t call [tableView reloadData] from the worker thread. Cocoa UI calls should only be made from the main thread or else you risk deadlock.

    Use performSelector:onMainThread: instead, which will queue the reloadData invocation in your main run loop.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

%d bloggers like this: