Error when detaching SQLite database - database is locked

Question

I have a system that is based on the SQLite database. Each client has a local database, and once in a while the update arrives from the main server, just a small delta .db file. The task is to merge to local database with the delta file, the schema is identical in both.

For my database management I use fmdb wrapper that can be found here. In the main thread, I keep the connection to the local database open. The delta file arrives in the background, and I want to do the merge in the background to avoid any user interface freezes that this could cause.

As for the merge itself, the only option that I found is to attach the delta database to the local database, then insert/update the rows, and finally detach the delta. This does not work as smooth as I expected.

Code description:

• The onDeltaGenerated method is invoked in a background thread whenever delta database is ready to be processed (arrives from the server and is saved in the readable location).
• The deltaDBPath is the absolute location of the delta database in the filesystem.
• The db variable references open FMDataBase connection.

Code:

- (void)onDeltaGenerated:(NSNotification*)n {
NSString* deltaDBPath = [[n userInfo] objectForKey:@"deltaPath"];
@synchronized(db) {
    [db executeUpdate:@"ATTACH DATABASE ? AS delta", deltaDBPath];
    if ([db hadError]) {
        NSLog(@" ****ERROR*** %d: %@", [db lastErrorCode], [db lastErrorMessage]);
    } else {
        NSLog(@"Delta attached from %@", deltaDBPath);
    }
    [db beginTransaction];
    BOOL update1 = NO;
    BOOL update2 = NO;
    BOOL transaction = NO;
    update1 = [db executeUpdate:@"INSERT OR REPLACE INTO equipment SELECT * FROM delta.equipment"];
    if (!update1) {
        NSLog(@" *** ERROR *** update 1 failed!");
        NSLog(@" ****ERROR*** %d: %@", [db lastErrorCode], [db lastErrorMessage]);
    }
    update2 = [db executeUpdate:@"INSERT OR REPLACE INTO equipmentExt SELECT * FROM delta.equipmentExt"];
    if (!update2) {
        NSLog(@" *** ERROR *** update 2 failed!");
        NSLog(@" ****ERROR*** %d: %@", [db lastErrorCode], [db lastErrorMessage]);
    }
    transaction = [db commit];
    if (!transaction) {
        NSLog(@" *** ERROR *** transaction failed!");
        NSLog(@" ****ERROR*** %d: %@", [db lastErrorCode], [db lastErrorMessage]);
    }
    [db executeUpdate:@"DETACH DATABASE delta"];
    if ([db hadError]) {
        NSLog(@" ****ERROR*** %d: %@", [db lastErrorCode], [db lastErrorMessage]);
    } else {
        NSLog(@"Delta detached");
    }
}

}

After this method is invoked for the first time, all seem to be fine until I try to detach the database. When I try to do it, I get the following error:

2012-01-11 12:08:52.106 DBApp[1415:11507] Error calling sqlite3_step (1: SQL logic error or missing database) SQLITE_ERROR
2012-01-11 12:08:52.107 DBApp[1415:11507] DB Query: DETACH delta
2012-01-11 12:08:52.107 DBApp[1415:11507]  ****ERROR*** 1: database delta is locked

I also tried to the same but without putting inserts into transaction, the result is identical. Another thing was to remove the @synchronized clause, but also no luck. My guess is that if fails when trying to access local database connection from the background thread, but then how come it manages to attach and insert? Any help appreciated.

Edit

I moved the code to the main thread, so the db is now accessed from the main thread only. The problem remains.

Edit2

Ok, so after trying everything, I gave up on this for a moment and then came back when the first answer appeared here. Surprisingly, everything seems to work fine now, so my code must be correct. I suspect this was the problem with different threads locking the file, as I used XCode, SQLiteDatabaseBrowser and my application to open the database. Even though the lsof showed that the file was not locked, I think it was wrong and either XCode or SQLiteDatabaseBrowser was locking it. I consider the problem solved, and the lesson taken from this is not to thrust lsof so much and also plan the debugging better next time.

Answer 1

Just checking – does the NSLog(@"Delta attached from %@", deltaDBPath); get printed successfully, and the errors you describe happen after that?

---

The line Error calling sqlite3_step (1: SQL logic error or missing database) SQLITE_ERROR is the probably going to be the most interesting bit to look into.

After a little bit of Googling, one issue that comes up is the database file may not be writeable. http://www.iphonedevsdk.com/forum/iphone-sdk-development/20142-problem-insert-fmdb.html

If the main DB you’re updating is within the app’s bundle, you’re not allowed to modify it – you should make a copy into the Documents or other writeable directory first.

---

Is the error actually occurring when you try and detach, or is it actually happening when you try and perform the INSERT OR REPLACE transaction?

Should you put another if ([db hadError]) {… just after these statements to make sure?

Answer 2

did you already

[db open];

elsewhere?