SQLite Database with FMDatabase Wrapper

Question

I am using the FMDatabase SQLite wrapper in Objective C and I have the following issue:

I am running an XML parse and DB insert in a background thread for some content that the user does not have access to, however the user is able to interact with a UI and database from the section they are in.

The FMDatabase <FMDatabase: 0x17b7b0> is currently in use.

Randomly, I will get a "FMDatabase already in use" notification and the array will never be populated by the database. I was under the impression that the FMDatabase class would handle the query once it became free, but I have a:

while(contents.count < 1){
     sleep(1);
}

Hoping that once the database frees up, the array will be populated. I have also tried rerunning the array population script if the DB is busy but to no avail.

Sorry if this question is confusing, I am happy to clarify.

dvkch · Accepted Answer · 2012-08-22T11:31:46.710

7

I experienced the same issue.

I switched to FMDatabaseQueue for every database query/update I had to do. It works like a charm !

Using performSelectorOnMainThread is a good idea but when it comes to actual coding it can be pretty tricky to pass your arguments and get the results for further use.

EDIT : here is a little example

-(void) updateTaskStateAsync:(NSNumber *)taskID withNewStatus:(NSNumber *)state andCompletionBlock:(void(^)(BOOL status))completionBlock{

    NSString *errInfo = [NSString stringWithFormat:@"taskID %d - state %d", [taskID intValue], [state intValue]];

    [queue inDatabase:^(FMDatabase *db) {
        BOOL r = [db executeUpdate:@"UPDATE tasks SET state=?, date_job_last_updated=? WHERE identifier=?", state, [NSDate dateWithTimeIntervalSinceNow:0], taskID];
        DB_DISPLAY_ERROR(errInfo); // convenient macro to log errors

        if(completionBlock)
            completionBlock(r);
    }];
}

-(BOOL) updateTaskStateSync:(NSNumber *)taskID withNewStatus:(NSNumber *)state {

    NSString *errInfo = [NSString stringWithFormat:@"taskID %d - state %d", [taskID intValue], [state intValue]];
    __block BOOL r = NO;
    __block BOOL ended = NO;

    [queue inDatabase:^(FMDatabase *db) {
        r = [db executeUpdate:@"UPDATE tasks SET state=?, date_job_last_updated=? WHERE identifier=?", state, [NSDate dateWithTimeIntervalSinceNow:0], taskID];
        DB_DISPLAY_ERROR(errInfo); // convenient macro to log errors

        ended = YES;
    }];

    NSCondition *cond = [[NSCondition alloc] init];
    [cond lock];
    while(!ended)
        [cond wait];

    [cond unlock];

    return r;
}

edited Aug 22 '12 at 11:31

answered Jul 02 '12 at 13:33

dvkch

1,079
1
12
20

I want to do the same as you, but for query in FMDatabaseQueue, the result must be return async..... it is not very convenient. Do you have any suggestion? – flypig Aug 20 '12 at 14:38
I donnot have the problem actually. I edited my answer to add a piece of code, please attach your if it doesnot help. – dvkch Aug 21 '12 at 07:50
the block code is executed by fmdb Queue asynchronously, and the r will be updated in the block when the block code is executed, but you simply return the r in the last line of your procedure.... i think you will get empty query result sometimes, depending on the fmdb queue status. This confused me for a few days.. may be we should pass a completion block to return the query result, e.g. "(BOOL) updateTaskState:(NSNumber *)taskID withNewStatus:(NSNumber *)state completion:(void (^)(BOOL r))completion; "and call the completion(r) in the [queue inDatabase:^(FMDatabase *db) {..}] block. – flypig Aug 21 '12 at 20:35
if i change all my query methods to follow the style as mentioned above, the code blocks nest with each other, so ugly..... – flypig Aug 21 '12 at 20:37
Indeed, this shouldn't be working. I never had the problem thought. Using an NSCondition should help with that but you will loose your async behavior. See edited answer – dvkch Aug 22 '12 at 11:32
But i'm not sure this is needed. I use functions like `executeQuery` and return retrieved object, and this work very well. Maybe this works only because the database has no pending operations in the queue, the implemntation of `inDatabase` doesnot seem to manage this – dvkch Aug 22 '12 at 11:36
Thanks dvkch. Yes, both of above two solutions should work. I agreed that your code works well currently because there is no pending operations.... if an App performs a lots of queries simultaneously, it should use one of above two solutions instead, or use FMDatabasePool, which is not suggested by the author of FMDB. – flypig Aug 22 '12 at 14:25
I don't think it should work when there is no pending operation either actually. There is no reason GCD would make the calling method wait for its `dispatch_sync` method to return. Could it be possible it works because I return a `NSObject*` so when the data is used (if it is finally available) it will access it without any trouble ? – dvkch Aug 23 '12 at 12:16

score 4 · Answer 2 · edited May 23 '17 at 12:29

You're hitting this issue because your application is multi-threaded and you're accessing the same FMDatabase from different threads. A similar question, but for Python, can be found here.

FMDatabase is a wrapper around the sqlite API. sqlite by default does not allow for concurrency, so FMDatabase uses an member variable, called "inUse", to track. To fix your issue, try one of these methods defined on NSObject so that all calls to FMDatabase occur on the same thread.

performSelectorOnMainThread:withObject:waitUntilDone:
performSelectorOnMainThread:withObject:waitUntilDone:modes:
performSelector:onThread:withObject:waitUntilDone:
performSelector:onThread:withObject:waitUntilDone:modes:

SQLite Database with FMDatabase Wrapper

2 Answers2