sqlite error database is locked

Question

I am have a sqlite database in the iPhone application I am writing. I get an error with following code that I am running in a background thread. In the background thread, I call this method:

 - (BOOL) songIsInDatabase:(NSString *)songTitle
{
NSString *docsDir;
NSArray *dirPaths;

dirPaths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
docsDir = [dirPaths objectAtIndex:0];


//Build the path to the database file
NSString *databasePath = [[NSString alloc] initWithString:[docsDir stringByAppendingPathComponent:@"Database.db"]];

const char *dbpath = [databasePath UTF8String];

sqlite3_stmt *statement;

if(sqlite3_open(dbpath, &DB) == SQLITE_OK){

    NSString *insertSQL = [NSString stringWithFormat:@"select * from Bpm_Table where song_title = '%@'", songTitle];

    const char *insert_stmt = [insertSQL UTF8String];
    if(sqlite3_prepare_v2(DB, insert_stmt, -1, &statement, NULL) == SQLITE_OK)
    {
        while (sqlite3_step(statement) == SQLITE_ROW)
        {
            return YES;
            break;
        }
    }else{
        NSLog(@"the error is %s", sqlite3_errmsg(DB));
    }
    sqlite3_finalize(statement); 
}

[databasePath release];
return NO;
}

Then I call this method:

- (void) addSongToDatabase: (NSString *) songTitle andBPM: (int)bpm andGenre: (NSString *) genre
{
NSString *docsDir;
NSArray *dirPaths;

dirPaths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
docsDir = [dirPaths objectAtIndex:0];


//Build the path to the database file
NSString *databasePath = [[NSString alloc] initWithString:[docsDir stringByAppendingPathComponent:@"Database.db"]];

const char *dbpath = [databasePath UTF8String];

sqlite3_stmt *statement;

if(sqlite3_open(dbpath, &DB) == SQLITE_OK){

    NSString *insertSQL = [NSString stringWithFormat:@"insert or replace into Bpm_Table (song_title, bpm, genre) values (\"%@\", \"%d\", \"%@\")", songTitle, bpm, genre];

    const char *insert_stmt = [insertSQL UTF8String];
    if(sqlite3_prepare(DB, insert_stmt, -1, &statement, NULL) == SQLITE_OK){
        if(sqlite3_step(statement) == SQLITE_DONE)
        {

        } else {
            NSLog(@"error: %s", sqlite3_errmsg(DB));
        }
    }sqlite3_finalize(statement);
}

[databasePath release];
}

If I run both these methods, one right after the other, I get an error that says database is locked. I added the sqlite3_finalize statements after poking around google in hopes that would fix the issue. If I comment out either of the methods, I don't get this error.

Does anyone know what's wrong?

Answer 1

you must always close the sqlite database after using it ... so add this line sqlite3_close(DB); just after sqlite3_finalize(statement);

Update -

You are returning YES in one of your while loop -

        while (sqlite3_step(statement) == SQLITE_ROW)
        {
            return YES;
            break;
        }

so here you are nither finalizing nor closing the database.. you need to close if everytime you open it

Answer 2

But note that if you access the same SQLite DB from multiple threads, without some sort of separate synchronization (some way to know that you'll never have near-simultaneous accesses from multiple threads) then you're likely to get "database locked" errors even if you close everything correctly on each access.

SQLite does not provide any sort of "lock wait" mechanism.

Your basic options are:

1. Do all accesses from one thread.
2. Use a separate lock protocol around all accesses.
3. If you get a "database locked" error wait a brief period of time and retry.