Locks on DB queries FMDB?

Question

I am using a multi threaded environment in my app, I need to constantly access sqlite db in order to update my views and also update my DB with server data via multiple background threads. Right now I am using FMDB for DB interaction but still getting DB locked problem.

FMDatabaseQueue *_queue = [FMDatabaseQueue databaseQueueWithPath:databasePath];
NSOperationQueue *_writeQueue = [NSOperationQueue new];
[_writeQueue setMaxConcurrentOperationCount:1];
NSRecursiveLock *_writeQueueLock = [NSRecursiveLock new];
[_writeQueue addOperationWithBlock:^{
            BOOL tryLock = NO;
            @try {
                [_writeQueueLock lock];
                tryLock = YES;
                [_queue inDatabase:^(FMDatabase *db) {
                    @try {
                        [db logsErrors];
                        [db executeUpdate:updateSQL];
                    }
                    @catch (NSException *exception) {

                    }
                    @finally {

                    }
                }];
            }
            @catch (NSException *exception) {

                NSLog(@"Error while inserting data saveLocation inside operation queue. %@", exception.description);
            }
            @finally {
                if (tryLock) {
                    [_writeQueueLock unlock];
                }
            }
}];

This what I am doing every time I insert data and similar way when I read data from DB as I am locking, Process should not be able to access DB until one thread finishes. I don't know what is wrong please help me out.

score 2 · Accepted Answer · answered Apr 08 '15 at 13:20

Whenever multiple threads try to access same table to read and write or two threads wants to write on same table of same db sqlite produces db locked signal so to resolve this you need Locks

NSRecursiveLock *_writeQueueLock = [NSRecursiveLock new];

as you've added in your code, but this won't help you much as you're trying to craete a new lock every time you insert. This lock should be a single object for all your blocking calls to DB like insert, update, delete etc.

Try creating a singleton instance of lock, This should help:

static FMDatabaseQueue *_queue;
static NSOperationQueue *_writeQueue;
static NSRecursiveLock *_writeQueueLock;

+(SomeDBClass*)getSharedInstance{
    if (!sharedInstance) {
        sharedInstance = [[super allocWithZone:NULL]init];
        _queue = [FMDatabaseQueue databaseQueueWithPath:databasePath];
        _writeQueue = [NSOperationQueue new];
       [_writeQueue setMaxConcurrentOperationCount:1];
        _writeQueueLock = [NSRecursiveLock new];
    }
    return sharedInstance;
}

Now once your objects created you can call your insert, update, delete method on these queue and locks like:

[_writeQueue addOperationWithBlock:^{
            BOOL tryLock = NO;
            @try {
                [_writeQueueLock lock];
                tryLock = YES;
                [_queue inDatabase:^(FMDatabase *db) {
                    @try {
                        [db logsErrors];
                        [db executeUpdate:updateSQL];
                    }
                    @catch (NSException *exception) {

                    }
                    @finally {

                    }
                }];
            }
            @catch (NSException *exception) {

                NSLog(@"Error while inserting data saveLocation inside operation queue. %@", exception.description);
            }
            @finally {
                if (tryLock) {
                    [_writeQueueLock unlock];
                }
            }
}];

You can also refer this for better understanding, Hope this helps and I am new to the stack so please be little bit forgiving Thanks.

Great solution, thanks! But I can't understand, why we need `NSRecursiveLock` when code contains `[_writeQueue setMaxConcurrentOperationCount:1];` row? — General Failure, Dec 09 '16 at 07:31
NSRecursiveLock is to be used for read operations while some thread might be writing, at that point of time you can guarantee that you get data after updation. — pk75, Dec 09 '16 at 07:59

score 1 · Answer 2 · answered Apr 08 '15 at 13:27

Using FMDatabaseQueue completely eliminates the need for any other locking mechanism. Adding another locking mechanism is only going to complicate the issue further.

If you are getting messages about the database being "locked", it because either:

you have multiple FMDatabase/FMDatabaseQueue objects out there; or
you're calling inDatabase from within another inDatabase call.

You should have one FMDatabaseQueue object which is shared amongst all of the threads and you need to make sure that none of your functions called with a inDatabase block calls something else that itself tries another inDatabase call.

Rob i would be needing to get instant reply from DB when i apply a read call as i have to check some flags stored there to insert itself and if i use **FMDatabaseQueue** it won't provide that also I would need a notification from DB every time I read DB i can not return value directly to calling function. That is why I use Lock. — Ashish Chauhan, Apr 08 '15 at 13:42

Locks on DB queries FMDB?

2 Answers2