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

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 l开发者_如何学运维ot 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?

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.

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

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.

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;