Using FMDB on multiple threads and two connections

Question

I'm using two different types of fmdb connections in my app:

FMDatabase for all READ queries and FMDatabaseQueue for all UPDATE queries.

Both are handled by a singleton, which keeps both types open the whole time while the app is running.

Both, read and update queries, are used in different threads as some tasks in my app are progressed in background; like getting data from a server and inserting it in the db via FMDatabaseQueue in an own background thread - while reading some information from db via FMDatabase and updating an ViewController with it on the main thread.

My problem is that after inserting data into the db via FMDatabaseQueue the second connection (FMDatabase) does not return the updated information as it does not find them. But I know the data was inserted as I have checked the db with an db browser tool + no errors occur while inserting it. To avoid this, I have to close the FMDatabase db connection and reopen it to see the changes made by the other connection. Unfortunately when my app starts up there are a many inserts, updates + reads as a lot of new data is loaded from server which needs to be processed - so closing and opening the db every time an update was made occurs in many "database busy" messages.

I have used one single FMDatabaseQueue for all threads and executes (read, update) before but it was quite slow when using read queries with __block variables to get the resultset out of the callback while another thread does some inserts(between 50-100 in a single transaction).

On top of it the database is encrypted via sqlcipher - not sure if it's important but want to mentioned it. So every time i have to close and open the database I'm doing a setKey.

My question: Is it possible to use a setup with two different connection types on multiple threads and if yes, do I have to close and open the FMDatabase connection? Or is there a better solution for this usecase?

UPDATE

My code to perform an insert / update looks like

-(void) create:(NSArray *)transactions
{
    NSMutableString *sqlQuery = [[NSMutableString alloc] initWithString:STANDARD_INSERT_QUERY];

    [sqlQuery appendString:@"(transaction_id, name, date) VALUES (?,?,?)"];

    FMDBDataSource *ds = [FMDBDataSource sharedManager];
    FMDatabaseQueue *queue = [ds getFMDBQ];
    [queue inTransaction:^(FMDatabase *db, BOOL *rollback) {
        [db setKey:[ds getKey]]; // returns the key to decrypt the database
        for (Transaction *transaction in transactions)
        {
            [db executeUpdate:sqlQuery, transaction.transactionId, transaction.name, transaction.date];
        }
    }];
}

and a read query

-(Transaction *)read:(NSString *)transactionId
{
    NSString *sqlQuery = [[NSString alloc] initWithString:STANDARD_SELECT_QUERY];
    Transaction *transaction = nil;

    FMDBDataSource *ds = [FMDBDataSource sharedManager];
    FMResultSet *rs = [[ds getFMDB] executeQuery:sqlQuery];

    while ([rs next]) {
        transaction = [[Transaction alloc] init];
        [transaction setTransactionId:[rs stringForColumn:@"transaction_id"]];
        [transaction setName:[rs stringForColumn:@"name"]];
    }

[rs close];
return transaction;
}

The FMDBDataSource is a singleton holding both, FMDatabase and FMDatabaseQueue, connections

- (FMDatabaseQueue *)getFMDBQ
{
    if (self.fmdbq == nil)
    {
        self.fmdbq = [FMDatabaseQueue databaseQueueWithPath:[self getDBPath]];
    }

    return self.fmdbq;
}

- (FMDatabase *) getFMDB
{
    if(self.fmdb == nil)
    {
        self.fmdb = [FMDatabase databaseWithPath:[self getDBPath]];
        [self openAndKeyDatabase]; // opens the db and sets the key as the db is encrypted
    }
    return self.fmdb;
}

As I said, when using this code the FMDatabase connection does not get the information which was inserted via FMDatabaseQueue.

Answer 1

Personally, I would suggest using the single FMDatabaseQueue for both threads and let the queue coordinate the actions on the two threads. That's what it was created for. It completely eliminates those "database busy" problems.

On your performance update, if doing a bulk update, are you using the FMDatabase method beginTransaction before the update and commit at the end? Or use the inTransaction method. Inserting 10,000 records without transactions in my test takes 36.8 seconds, but with transactions it takes 0.25 seconds.

Or, if your bulk update is slow by necessity (e.g. you're downloading some big data source from a web service using some streaming protocol), you can either:

• Load all the results into memory first, with no database interaction, and then use the bulk update with transactions as described in the previous paragraph; or

• If your database updates are necessarily constrained by a slow network connection, then use separate inDatabase calls so that it doesn't tie up the FMDatabaseQueue while downloading data from your web service.

Bottom line, through the use of transactions or the judicious use of separate inDatabase calls, you can minimize how long your background operation is ties up the FMDatabaseQueue and you can achieve synchronized multi-threaded interaction with your database without blocking your UI too significantly.