sqlite and threading with iPhone SDK

I have an iPhone app that is using sqlite 3.6 (not with FMDB) to store and load data. I load the database when the app loads and uses the same database connection through the whole app.

In a background thread the app downloads some data from a webserver and writes to the database. At the same time the main thread also might need to write to the same database. This sometimes leads to EXC_BAD_ACCESS as both threads are trying to access the database.

What is the best and easiest way to be able to use the database from different threads?

This is an example that shows the problem:

sqlite3 *database;   

- (BOOL)application:(UIApplication *)application didFinishLaunchingWithOptions:(NSDictionary *)launchOptions {   

    NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
    NSString *documentsDirectory = [paths objectAtIndex:0];
    NSString *path = [documentsDirectory stringByAppendingPathComponent:@"database.db"];

    if (sqlite3_open([path UTF8String], &database) != SQLITE_OK) {
        sqlite3_close(database);
        return YES;
    }

    [NSThread detachNewThreadSelector:@selector(test) toTarget:self withObject:nil];
    [self test];
    return YES;
}

-(void)test {
    for (int i = 0; i < 2000; i++) {
        NSLog(@"%i",i);
        sqlite3_exec([self getDb],"UPDATE mytable SET test=''", 0, 0, 0);
    }
}

EDIT:

After willcodejavaforfood's answer below I've tried to change my code to use a separate database object (connection) for each separate thread and also added sqlite3_busy_timeout() so that sqlite will retry to write if the database is busy. Now I don't get EXC_BAD_ACCESS anymore but I've noticed that not all data get inserted. So this is not a stable solution either. It seems to be really hard to get sqlite working with threading..

My new solution with separate connections:

-(void)test {
    sqlite3 *db = [self getNewDb];
    for (int i = 0; i < 2000; i++) {
        NSLog(@"%i",i);
        sqlite3_exec(db,"UPDATE mytable SET test=''", 0, 0, 0);
    }
}

- (sqlite3 *)getNewDb {
    sqlite3 *newDb = nil;
    if (sqlite3_open([[self getDbPath] UTF8String], &newDb) == SQLITE_OK) {
        sqlite3_busy_timeout(newDb, 1000);
    } else {
        sqlite3_close(newDb);
    }
    return newDb;
}

Answers


I solved this problem by using one thread and an NSOperationQueue to insert the Data. I would give it some thought. I've never been able to get a stable System with mutliple threads, and most writes aren't that important that queuing really helps.

As per request, some more Infos:

I have a subclass of NSOperation that I instantiate with the model object I want to store. These operations are than submitted to an extension of NSOperationsQueue that runs in a seperate thread. This custom Queue just adds a pointer to the database instance. When the operation is executed, it uses the [NSOperationsQueue currentQueue] property to access the queue and than the database. On purpose, i used non-concurrent operations (maxOperations was set to 1) Hence, only one query (or update) is executed at a time consecutivly, completely in the background.

Obviously you need some kind of callback after you're finished.

It is possibly not the fast, but the most stable and cleanest solution i could find.

Docs: http://developer.apple.com/library/ios/documentation/General/Conceptual/ConcurrencyProgrammingGuide/OperationObjects/OperationObjects.html http://www.cimgf.com/2008/02/16/cocoa-tutorial-nsoperation-and-nsoperationqueue/ http://icodeblog.com/2010/03/04/iphone-coding-turbo-charging-your-apps-with-nsoperation/


As you've noticed only one thread can access an sqlite database at a time. Options to prevent simultaneous access:

  1. Create a new database connection in each thread and rely on file locking (costly).
  2. Turn on sqlite3_config(SQLITE_CONFIG_SERIALIZED).
  3. Use NSLock's.
  4. Use GCD (Grand Central Dispatch) queue's.

The first three options may cause busy waiting (one thread waiting on another to release the lock) which is wasteful.

I use option 4 because it simplifies the task of creating new queries to run in the background and has no busy waiting. It also makes sure all queries execute in the order they were added (which my code tends to assume).

dispatch_queue_t _queue = dispatch_queue_create("com.mycompany.myqueue", DISPATCH_QUEUE_SERIAL);

// Run a query in the background.
dispatch_async(_queue, ^{

    ...some query

    // Perhaps call a completion block on the main thread when done?
    dispatch_async(dispatch_get_main_queue(), ^{

        //completion(results, error);
    });
});

// Run a query and wait for the result.
// This will block until all previous queries have finished.
// Note that you shouldn't do this in production code but it may
// be useful to retrofit old (blocking) code.
__block NSArray *results;

dispatch_sync(_queue, ^{

    results = ...
});

...use the results

dispatch_release(_queue);

In a perfect world sqlite would let you perform simultaneous reads but only one write at a time (eg. like using dispatch_barrier_async() for writes and dispatch_async() for reads).


Need Your Help

Magento: SQLSTATE: Syntax error or access violation: 1044 Access denied for user 'DBUSER'@'%' to database 'DBNAME'

sql database magento

I've been banging my head off the wall for hours with this problem. Can anyone help please? I've not been able to find anything online about it.

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.