CODEWITHSUNDEEP
iphonemultithreadingsqlite
mrrmatinsi
mrrmatinsi

Reputation: 283

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;
}

Upvotes: 2

Views: 8359

Answers (4)

user2403761
user2403761

Reputation:

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

Upvotes: 2

Mousa
Mousa

Reputation: 3036

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.

Upvotes: 1

Blitz
Blitz

Reputation: 5671

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/

Upvotes: 3

willcodejavaforfood
willcodejavaforfood

Reputation: 44103

This is all explained in the Core Data Programming Guide in the section for Concurrency.

The pattern recommended for concurrent programming with Core Data is thread confinement.

You should give each thread its own entirely private managed object context and keep their associated object graphs separated on a per-thread basis.

There are two possible ways to adopt the pattern:

Create a separate managed object context for each thread and share a single persistent store coordinator. This is the typically-recommended approach.

Create a separate managed object context and persistent store coordinator for each thread. This approach provides for greater concurrency at the expense of greater complexity (particularly if you need to communicate changes between different contexts) and increased memory usage.

Upvotes: 1

Related Questions