db executeUpdate… in FMDB block and doesn't go

Question

i'm using the amazing FMDB project in my app in development, i have a NSOperation like this:

- (void)main
{
 @autoreleasepool {

FMDatabaseQueue *queue = [FMDatabaseQueue databaseQueueWithPath:[[NSUserDefaults standardUserDefaults] valueForKey:@"pathDB"]];

[queue inDatabase:^(FMDatabase *db) {

    FMResultSet *toQuery;

    if (self._id == nil) {
        toQuery = [db executeQuery:@"SELECT id,language,update_time FROM task"];

    while ([toQuery next]) {

        [myarray addObject:[toQuery resultDictionary]];

    }
}];

for (int i = 0; i<[myarray count]; i++){

...Do Something

[queue inDatabase:^(FMDatabase *db) {

FMResultSet *checkImgQuery = [db executeQuery:@"SELECT img_url,img_path FROM task WHERE id = ? AND language = ?",myTask.id ,myTask.lang];

while ([checkImgQuery next]) {

 if (![[checkImgQuery stringForColumn:@"img_url"] isEqualToString:myTask.img]) {
                                    NSData *my_img = [NSData dataWithContentsOfURL:[NSURL URLWithString:myTask.img]];

if (my_img != nil) {

NSError *er;

[my_img writeToFile:[checkImgQuery stringForColumn:@"img_path"] options:NSDataWritingAtomic error:&er];

//In the line under here the code block, the app still running, but this operation doesn't
//go over this task

[db executeUpdate:@"UPDATE task SET img_url = ? WHERE id = ? AND language = ?",myTask.img,[NSNumber numberWithInt:myTask.id],[NSNumber numberWithInt:myTask.language];

NSLog(@"%@",[db lastErrorMessage]);
}
...Do Something
}
}
}
}];
}
}

The problem is in [db executeUpdate:...] that sometime works with no problem and sometime freeze and doesn't go over that line, the NSLog i have put there doesn't print anything, the app doesn't crash and continue working, but the thread is stuck there, if i shutdown the run of the app, and i restart it again the thread doesn't stop on the same task, but random on another, with no criteria, some time one works, and some time doesn't...anyone can help?

Answer 1

There are a couple issues that leap out at me, one or more of which may be contributing to your problem:

1. I notice that you're creating a FMDatabaseQueue object locally. You should only have one FMDatabaseQueue object shared for the entire app (I put it in a singleton). The purpose of the database queue is to coordinate database interactions, and it can't reasonably do that if you're creating new FMDatabaseQueue objects all over the place.

2. I'd advise against having an inDatabase block in which you're synchronously downloading a bunch of images from the network.

   When you submit an inDatabase task, any inDatabase calls on other threads using the same FMDatabaseQueue (and they should use the same queue, or else you're defeating the purpose in having a queue in the first place) will not proceed until the one running in your operation does (or vice versa).

   When doing database interaction from multiple threads, coordinated by the FMDatabaseQueue serial queue, you really want to make sure that you "get in and get out" as quickly as possible. Don't embed potentially slow network calls in the middle of the inDatabase block, or else all other database interaction will be blocked until it finishes.

   So, do an inDatabase to identify the images that need to be downloaded, but that's it. Then outside of the inDatabase call, retrieve your images, and if you need to update image paths or the like, separate inDatabase call do to do that. But don't include anything slow and synchronous inside the inDatabase block.

3. I also notice that you're doing a SELECT on task table, keeping that FMRecordSet open, and then trying to update the same record. You want to open your record set, retrieve what you need, and close that recordset before you try to update the same record you retrieved in your recordset.

   Always close the FMResultSet before you try to do the executeUpdate that updates the same record.

4. A bit unrelated, but I might suggest you consider including img_url and img_path in your original SELECT statement, that way your array of dictionary entries will already have everything you need and it saves you from have to do that second SELECT at all.

---

If you're wondering what the FMDatabaseQueue singleton might look like, you might have a DatabaseManager singleton whose interface looks like:

//  DatabaseManager.h

@import Foundation;
@import SQLite3;

#import "FMDB.h"

NS_ASSUME_NONNULL_BEGIN

@interface DatabaseManager : NSObject

@property (nonatomic, strong, readonly) FMDatabaseQueue *queue;

@property (class, readonly, strong) DatabaseManager *sharedManager;

- (id)init __attribute__((unavailable("Use +[DatabaseManager sharedManager] instead")));
+ (id)new __attribute__((unavailable("Use +[DatabaseManager sharedManager] instead")));

@end

NS_ASSUME_NONNULL_END

and the implementation might look like:

//  DatabaseManager.m

#import "DatabaseManager.h"

@implementation DatabaseManager

+ (DatabaseManager *)sharedManager {
    static id sharedMyManager = nil;
    static dispatch_once_t onceToken;
    dispatch_once(&onceToken, ^{
        sharedMyManager = [[self alloc] init];
    });
    return sharedMyManager;
}

- (id)init {
    if ((self = [super init])) {
        _queue = [[FMDatabaseQueue alloc] initWithPath:[[NSUserDefaults standardUserDefaults] valueForKey:@"pathDB"]];
    }
    return self;
}

@end

Then, any code that needs to interact with the database can retrieve the queue like so:

FMDatabaseQueue *queue = DatabaseManager.sharedManager.queue;