EXC_BAD_ACCESS when using SQLite (FMDB) and threads on iOS 4.0

Question

I am using FMDB to deal with my database which works fine. The app uses a background thread which is doing some work and needs to access the database. At the same time the main thread needs to run some queries on the same database. FMDB itself has a little locking system, however, I added another to my classes.

Every query is only performed if my class indicates that the database is not in use. After performing the actions the database gets unlocked. This works as expected as long as the load is not too high. When I access a lot of data with the thread running on the main thread an EXC_BAD_ACCESS error occurs.

Here is the looking:

- (BOOL)isDatabaseLocked {
    return isDatabaseLocked;
}

- (Pile *)lockDatabase {
    isDatabaseLocked = YES;
    return self;        
}

- (FMDatabase *)lockedDatabase {
    @synchronized(self) {
        while ([self isDatabaseLocked]) {
            usleep(20);
            //NSLog(@"Waiting until database gets unlocked...");
        }
        isDatabaseLocked = YES;
        return self.database;       
    }
}

- (Pile *)unlockDatabase {
    isDatabaseLocked = NO;
    return self;            
}

The debugger says that the error occurs at [FMResultSet next] at the line

rc = sqlite3_step(statement.statement);

I double checked all retain counts and all objects do exist at this time. Again, it only occurs when the main thread starts a lot of queries while the background thread is running (which itself always produce heavy load). The error is always produced by the main thread, never by the background thread.

My last idea would be that both threads run lockedDatabase at the same time so they could get a database object. That's why I added the mutex locking via "@synchronized(self)". However, this did not help.

Does anybody have a clue?

Lambrecht

This thread for an FMDB issue gives some other useful insight into possible causes: github.com/ccgus/fmdb/issues/39 – Outsell

Answer 1

You should add the synchronized wrapper around your functions unlockDatabase and lockDatabase, as well as isDatabaseLocked - it's not always guaranteed that a store or retrieval of a variable is atomic. Of course, if you do you'll want to move your sleep outside of the synchronized block, otherwise you'll deadlock. This is essentially a spin lock - it's not the most efficient method.

- (FMDatabase *)lockedDatabase {
    do
    {
        @synchronized(self) {
            if (![self isDatabaseLocked]) {
                isDatabaseLocked = YES;
                return self.database; 
            }
        }
        usleep(20);      
    }while(true); // continue until we get a lock
}

Do you make sure that you don't use the FMDatabase object after having called unlockDatabase? You might want to consider a handle pattern - create an object that wraps the FMDatabase object, and as long as it exists, holds a lock on the database. In init you claim the lock, and in dealloc, you can release that lock. Then your client code doesn't need to worry about calling the various locking/unlocking functions, and you won't accidentally screw up. Try using NSMutex instead of the @synchronized blocks, see http://developer.apple.com/mac/library/documentation/Cocoa/Conceptual/Multithreading/ThreadSafety/ThreadSafety.html#//apple_ref/doc/uid/10000057i-CH8-SW16

Balaam

I found a piece of code where I access the database directly. So it did not get locked. After fixing this little issue, everything works perfectly fine. – Lambrecht

How did you fix this problem? Can you post the code which helped? – Faythe

I use the code I originally posted. My mistake was that the app made a database query without locking so without calling lockedDatabase. – Lambrecht

Answer 2

SQLite provides a much simpler serialization. By just setting the sqlite_config() option SQLITE_CONFIG_SERIALIZED you will probably avoid most of these kinds of headaches. I discovered this the hard way after fighting with threading issues for a long while.

Here's how you use it, you can put it in the init method of FMDatabase...

    if (sqlite3_config(SQLITE_CONFIG_SERIALIZED) == SQLITE_ERROR) {
        NSLog(@"couldn't set serialized mode");
    }

See the SQLite docs on threadsafety and serialized mode for more info.

Dempster

The SQLite documentation says that it defaults to serialized mode, but it looks like the version of the libraries that come with the MacOS are set to default to single-thread mode (which I discovered the hard way, while porting a program that worked perfectly on Linux and Windows to the Mac). – Eddie

Guys, can you explain for me one thing? SQLITE_CONFIG_SERIALIZED guarantees turning on mutex stuff inside the sqlite, cool. So all its methods are atomic, right? But who can guarantee that everything goes right in client methods in a concurrent queue? For, example we call void foo() { sqlite3_open() sqlite3_exec() sqlite3_next_stmt() sqlite3_finalize() sqlite3_close() } Each call inside the method is atomic and safe inside. But when we call the 'foo' from different threads methods are called chaotically. Like 'finalize' from thread 1 after 'open' from thread 2 and so on. – Benkley

Yes, sqlite3_open returns different instances each time. But in reality, I have 100% case when our app crashes if I remove @synchronized from a method which uses sqlite. And yes, it can be unrelated to sqlite and I'm going to investigate this issue more. – Benkley

Answer 3

You might also try FMDatabaseQueue - I created it specifically for situations like this. I haven't tried it, but I'm fairly sure it'll work for iOS 4.

Harebell

I think FMDatabaseQueue works only for queries, is that right? Is there a straightforward way to do the same for updates (apart from the SQLite setting above)? – Dempster

Answer 4

I was having this problem and was able to eliminate the problem merely by turning on caching of prepared statements.

FMDatabase *myDatabase = [FMDatabase databaseWithPath: pathToDatabase];
myDatabase.shouldCacheStatements = YES;

Outsell