Main Thread blocked using FMDatabaseQueue

[swl367]

Hi, I have a method being called as follows:

dispatch_async(dispatch_get_global_queue(DISPATCH_QUEUE_PRIORITY_DEFAULT, 0), ^{
[[self dataAccess] downloadCustomers];
});

...

and the method does the following.

FMDatabaseQueue *queue = [FMDatabaseQueue databaseQueueWithPath:[self dbPath]];

[manager GET:URL parameters:nil success:^(AFHTTPRequestOperation *operation, id responseObject) {
    NSArray *customers = [NSArray arrayWithArray:responseObject];

    for (NSDictionary *propertyValueDictionary in customers) {
        NSString *customerId = [propertyValueDictionary objectForKey:@"CustomerId"];
        NSString *property = [propertyValueDictionary objectForKey:@"Property"];
        NSString *value =  [propertyValueDictionary objectForKey:@"Value"];
        BOOL deleted = [[propertyValueDictionary objectForKey:@"Deleted"] boolValue];
        NSString *revision = [propertyValueDictionary objectForKey:@"Revision"];

        [queue inDatabase:^(FMDatabase *db) {
            if (deleted == NO) {
                NSString *sql = [NSString stringWithFormat:@"INSERT OR REPLACE INTO Customer (CustomerId, Property, Value) VALUES ('%@', '%@', '%@');", customerId, property, value];
                [db executeUpdate:sql];
            } else {
                NSString *query = [NSString stringWithFormat:@"DELETE FROM Customer WHERE CustomerId = '%@' AND Property = '%@' AND Value = '%@';", customerId, property, value];
                [db executeUpdate:query];
            }
        }];
    }
} failure:^(AFHTTPRequestOperation *operation, NSError *error) {
    NSLog(@"Error retrieving customers: %@", error);
}];

However, it seems to be blocking my UI/main thread although I'm trying to use dispatch_async and following this wiki's instructions on using the FMDatabaseQueue instead of opening a new DB on every call. Is there something I'm missing? Thanks!

[ccgus]

Can you do a "bt all" in your debugger when the stall happens, and paste that here?

[swl367]

wow that was insanely fast haha. thanks for the quick response. The stall is very brie, but it's noticeable when I don't download my data versus when I do use it. About 1.5 seconds of delay on the UI. I'm not sure if this is correct but i used 'bt all' from a breakpoint on one of the first db calls:

sorry, i shouldn't paste that here. here's the paste bin: http://pastebin.com/raw.php?i=fJtCfV9t

[ccgus]

Oh- so it's not a deadlock, just a pause.

I would move your inDatabase: outside of the for loop, and use inTransaction: instead. So it's like:

[queue inTransaction:^(FMDatabase *db) {

    for (NSDictionary *propertyValueDictionary in customers) {
        NSString *customerId = [propertyValueDictionary objectForKey:@"CustomerId"];
        NSString *property = [propertyValueDictionary objectForKey:@"Property"];
        NSString *value =  [propertyValueDictionary objectForKey:@"Value"];
        BOOL deleted = [[propertyValueDictionary objectForKey:@"Deleted"] boolValue];
        NSString *revision = [propertyValueDictionary objectForKey:@"Revision"];

        if (deleted == NO) {
            NSString *sql = [NSString stringWithFormat:@"INSERT OR REPLACE INTO Customer (CustomerId, Property, Value) VALUES ('%@', '%@', '%@');", customerId, property, value];
            [db executeUpdate:sql];
        } else {
            NSString *query = [NSString stringWithFormat:@"DELETE FROM Customer WHERE CustomerId = '%@' AND Property = '%@' AND Value = '%@';", customerId, property, value];
            [db executeUpdate:query];
        }

    }
}];

[robertmryan]

A couple of issues:

1. As Gus said, rather than using inDatabase inside the for loop, use inTransaction outside the for loop. I don't know how many rows you have, but if you use inDatabase it will commit each one of these updates individually (which behind the scenes takes a lot of work). I've seen performance improvements of several orders of magnitude by using inTransaction. But it only makes sense if you wrap the whole thing in a inTransaction.

2. Unrelated, instead of:

   NSString *sql = [NSString stringWithFormat:@"INSERT OR REPLACE INTO Customer (CustomerId, Property, Value) VALUES ('%@', '%@', '%@');", customerId, property, value];
   [db executeUpdate:sql];
   

   You should instead do:

     NSString *sql = @"INSERT OR REPLACE INTO Customer (CustomerId, Property, Value) VALUES (?, ?, ?);";
     [db executeUpdate:sql, customerId, property, value];
   

   If any of your strings happened to have an apostrophe in it, the former syntax would fail. You're also theoretically susceptible to SQL injection attacks. Using the ? placeholder (without any quotes) avoids that risk. You should always be very wary of using stringWithFormat to build SQL.

3. I find it a little worrying that you're instantiating a FMDatabaseQueue inside this block. You should have a single FMDatabaseQueue instance that you share amongst all of your threads (otherwise you lose all of the queue "goodness"). Do not instantiate separate FMDatabaseQueue objects.

[swl367]

@ccgus and @robertmryan thank you so much for the quick and detailed responses. I definitely see noticeable improvements using the suggestions provided. I really appreciate it 👍