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.

Inelegant

have you tried your own code? This looks okay at a first glance – Qr

I did and it works. I am just wondering if I am missing something, or if there is a race condition if the two queues access the same database at the same time. – Inelegant

Don't take my word for it but I'm pretty sure that as long as you're not writing on both queues you should be fine. What you could try is a for loop of, say, 1000 times the same update statement, and at the same time do some read-queries and see how it reacts, just to make sure. But from what I remember of my FMDB, I think you can read while you write – Qr

Well, that's not really a scientific way to find out... :/ – Inelegant

Statistics are maths, and maths are kind of scientific right ? :o Haha sorry I can't offer you more I'm just trying my best, but in the end I don't know as much as the next guy – Qr

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.

Bergess

Thanks Rob for pointing this. Although most of the heavy lifting is indeed outside the db queue. Now back to Instruments to measure this and ask my users why they feel it is slow, I might have understood that wrong also. I'll check back when it's fixed. – Inelegant

Well, the INSERT and UPDATE statements do indeed to a loooooot of time. But this is related to my use of SQLite FTS. I'll have to look into this. You answered to my initial question, thanks. – Inelegant

For the record, replacing UPDATE docs SET contents = ? WHERE sid = ? by UPDATE docs SET contents = ? WHERE sid MATCH ? in my FTS table changed the processing time from 1257ms to... 2ms. – Inelegant

@Bergess Can I make a singleton FMDatabaseQueue for all the write operations. and then use FMDatabase directly (separate objects from different threads) for SELECT operations. I don't care when the write operations take place in the background however, I want the SELECT result immediately (i.e. synchronously on main thread) – Bedchamber

@Bedchamber - Sadly, that's not really going to work because you generally don't want any reads happening when writes are taking place. You could entertain a reader-writer pattern (where writes are synchronized with respect to both reads and writes) but reads can take place concurrently (but with respect other reads, only). But, you'd have to refactor FMDB considerably to accomplish that. Much easier, make sure you use transactions with groups of write operations (to get in and out much more quickly) and that's often enough to get the desired performance improvements. – Bergess

@Rob, I did same as Sam suggested, will it be big impact if i do same as Sam suggested? – Symbolics

@RajpalThakur - At best, I'd expect the intermittent "database busy" error, so make sure you handle failures and retry accordingly. Personally, I'd optimize writes so that they never tie up the database for too long (e.g. use transactions where possible) and then use a single, shared FMDatabaseQueue for all threads. – Bergess

@Rob, Thanks fro help, Now i am using singlton FMDatabaseQueue instance and my all threads are using same FMDatabaseQueue but i am still getting BAD ACCESS issue. My Case Is: 1) A transaction is going on in background thread. 2) At same time foreground thread is fetching the results set then problem occurred. – Symbolics

@RajpalThakur - I'd suggest you post your own question with minimal, yet reproducible, example of the problem. And when you include details of the BAD ACCESS issue, include full error messages and stack trace. I might also suggest adding an exception breakpoint so you can diagnose at precisely which line it is failing. We're not going to solve it here in comments, so post your own question. – Bergess