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.

Eloyelreath

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.

Socialize

The problem isn't the update part but the read part instead. I have updated my question with some code to explain it in detail. – Eloyelreath

Rob is right, it is the best to use a single FMDatabaseQueue. Works perfect for me. My problem was that I have removed and created a new database in some situations without closing the old connection and reconnect to the newly created db. SQLite or FMDB doesn't came up with an error message or something - that's why it wasn't quite easy to find the error. thx – Eloyelreath

Curious @Rob, whether you've ever had (or heard of) the use case where It'd be nice to have a long-running update not block concurrent reads (using SQLITE_OPEN_WAL, of course for the FMDatabaseQueue part? – Retsina

There have been discussions about precisely that, likely to be tackled in FMDB 3.0. – Socialize

Answer 2

I have spent lots of hours trying to making the same, when I found your post. I do not know if you have already find a solution. After a lot of tries and searches, I had to gave up and I'am looking for another solution now. But, I would like to share my conclusions.

SQLite has a function to define READONLY or READWRITE mode. FMDB implements like openWithFlags.

[db openWithFlags:SQLITE_OPEN_READONLY|SQLITE_OPEN_NOMUTEX];

Setting these flags do not permits reading while wrinting even if we set these flags. I could accomplish reading+writing (different connections) setting my database to use WAL journal_mode (http://www.sqlite.org/wal.html).

BUT, SQLCipher screw everthing up.

Conclusion READING + WRITING with 2 connections:

FMDB + openWithFlags = BE SAD AND ANGRY
FMDB + openWithFlags + WAL jornal_mode = BE HAPPY
FMDB + SQLCipher + openWithFlags = BE SAD AND ANGRY
FMDB + SQLCipher + openWithFlags + WAL jornal_mode = BE SAD AND ANGRY

As my application needs security, I do not know what to do yet.

Well, I hope it helps. Best

Hami.

Tourbillion

Yes, I have also tried using these flags with no success on my side - but finally I'm happy using just one single FMDatabaseQueue without any problems. Let me know if you need more information – Eloyelreath

Curious if you ever found a solution @Hamiseixas? – Retsina

No.. we changed our database structure :/ @ScottCorscadden and separate database to read and write. For reading, we use ATTACH database feature. – Tourbillion

Thanks Hami. Helpful to know. Interesting that SQLCipher causes this, but glad to hear that separating the database itself unblocks reads (as it ought to). – Retsina

Just wanted to comment for those that may find this answer that we're using Sqlcipher in a single DB with WAL mode and simultaneous reads/writes without issue. We use an FMDatabaseQueue for writes and an FMDatabasePool for reads. Perhaps Sqlcipher fixed some issue or Sqlite has improved since 2013. I'm not sure, but it's working perfectly for us. – Paolo