Use two FMDB queues (read / write) on the same database

Question

I believe that my use case is fairly common, but I could not find an authoritative answer for this.

I have a sync mechanism that runs in background and write data to my database. This sync can take a lot of time (I use FTS). For this I use a FMDatabaseQueue. When I want to read on the database, I use the same queue to make a query.

Now, when the sync process already queued a lot of transactions to the queue then the app wants to do a read, it has to wait for all the write transactions to finish before being able to do a query, as this is a serial queue. The code might look like this:

FMDatabaseQueue *queue = [self getDatabaseQueue];

[queue inTransaction:^(FMDatabase *db, BOOL *rollback) {
    // Very slow process
    [db executeUpdate:@"UPDATE docs SET name = ?", "value..."];
}];

[queue inTransaction:^(FMDatabase *db, BOOL *rollback) {
    // Very slow process
    [db executeUpdate:@"UPDATE docs SET name = ?", "value..."];
}];

[queue inTransaction:^(FMDatabase *db, BOOL *rollback) {
    // Very slow process
    [db executeUpdate:@"UPDATE docs SET name = ?", "value..."];
}];

[queue inDatabase:^(FMDatabase *db) {
    FMResultSet *resultSet = [db executeQuery:@"SELECT name..."];
}];

I want to have the query results instantaneously (even if the sync is not done) and not wait to the UPDATE to be done.

Can I create two FMDatabaseQueues, one for the write queries and one for the read queries? What will happen if a read query starts right in the middle of a write transaction?

The code might look like this:

FMDatabaseQueue *writeQueue = [self getWriteDatabaseQueue];
FMDatabaseQueue *readQueue = [self getReadDatabaseQueue];

[writeQueue inTransaction:^(FMDatabase *db, BOOL *rollback) {
    // Very slow process
    [db executeUpdate:@"UPDATE docs SET name = ?", "value..."];
}];

[writeQueue inTransaction:^(FMDatabase *db, BOOL *rollback) {
    // Very slow process
    [db executeUpdate:@"UPDATE docs SET name = ?", "value..."];
}];

[writeQueue inTransaction:^(FMDatabase *db, BOOL *rollback) {
    // Very slow process
    [db executeUpdate:@"UPDATE docs SET name = ?", "value..."];
}];

[readQueue inDatabase:^(FMDatabase *db) {
    FMResultSet *resultSet = [db executeQuery:@"SELECT name..."];
}];

Edit:

Also, what confuses me is the documentation which states:

It has always been OK to make a FMDatabase object per thread. Just don't share a single instance across threads.

So what I understand from that is that I can create two isntances with the same database but that I just need to keep it on their own thread.

Answer 1

No, you do not want to have two FMDatabaseQueue interacting with the same database. The entire purpose of FMDatabaseQueue is to provide a single queue for coordinating database calls from different threads through a shared single serial queue.

I wonder, though, about your code in the "very slow process" blocks. If that's not database-specific stuff, then it should not be inside the inTransaction and/or inDatabase calls.

NSOperationQueue *backgroundQueue = [[NSOperationQueue alloc] init]; 
FMDatabaseQueue *databaseQueue = [FMDatabaseQueue databaseWithPath:path]; 

[backgroundQueue addOperationWithBlock:^{
    // very slow process

    [databaseQueue inTransaction:^(FMDatabase *db, BOOL *rollback) {
        [db executeUpdate:@"UPDATE docs SET name = ?", "value..."];
    }];
}];

[backgroundQueue addOperationWithBlock:^{
    // very slow process

    [databaseQueue inTransaction:^(FMDatabase *db, BOOL *rollback) {
        [db executeUpdate:@"UPDATE docs SET name = ?", "value..."];
    }];
}];

And while those are queued and running, the main queue can then make its own databaseQueue calls, using the FMDatabaseQueue to ensure that this is coordinated in such a way that it won't occur simultaneously with the inTransaction blocks above:

[databaseQueue inDatabase:^(FMDatabase *db) {
    FMResultSet *resultSet = [db executeQuery:@"SELECT name..."];
    while (![resultSet next]) {
        ....
    }
}];

Clearly, you should manage your background tasks however is suitable for your app (I used a concurrent operation queue, but you can use serial queues or dispatch queues or whatever you want). Don't get caught up in the specifics of the backgroundQueue above, as your implementation will vary.

The key observation is that you should not use the databaseQueue to manage your "very slow process" tasks as well as the database interaction. Take anything that is not database-related out of the inTransaction and inDatabase calls. Use your own queues to manage your own non-database-related code. Always get in and out of the databaseQueue as quickly as possible and let the single, shared FMDatabaseQueue coordinate the interaction with the database happening from multiple threads.