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.

Answer 1

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.