"database locked" error in ios while updating query

Question

I am using the below code to update the query using sqlite.
But am getting "database is locked error".
I tried searching some SO link and it was suggested to close database, but I did that again am getting the same error. I have mentioned where I am getting error in the code.

const char *dbpath = [databasePath UTF8String];
if (sqlite3_open(dbpath, &database) == SQLITE_OK)
{
    NSString *locationNo =NULL;
    NSString *querySQL = [NSString stringWithFormat:@"select count(*) from code"];
    const char *query_stmt = [querySQL UTF8String];

    if (sqlite3_prepare_v2(database,query_stmt, -1, &statement, NULL) == SQLITE_OK)
    {
        if (sqlite3_step(statement) == SQLITE_ROW)
        {
            locationNo = [[NSString alloc] initWithUTF8String:(const char *) sqlite3_column_text(statement, 0)];
            int count= [locationNo intValue];
            sqlite3_close(database);
            NSLog(@"%@",locationNo);
            if(0==count)
            {
                NSString *insertSQL = [NSString stringWithFormat:@"insert into favourite_code (code_id,code_1,code_2,code_3,code_4,code_5,code_6, status, record_status) VALUES (\"%d\",\"%@\", \"%@\", \"%@\", \"%@\", \"%@\", \"%@\", \"%@\", \"%@\")",1 ,code1,code2,code3,code4,code5,code6,@"Y", @"Y"];

                const char *insert_stmt = [insertSQL UTF8String];
                sqlite3_prepare_v2(database, insert_stmt,-1, &statement, NULL);
                if (sqlite3_step(statement) == SQLITE_DONE)
                {
                    return YES;
                }
                else {
                    return NO;
                }
                sqlite3_reset(statement);
                sqlite3_close(database);
            }
            else{


                =========================== Getting Error in the below lines =========================

                const char *sq1l = "update code SET code_1=?, code_2=?, code_3=?, code_4=?, code_5=?,code_6=? WHERE code_id=1";

                if (sqlite3_prepare_v2(database, sq1l, -1, &statement, NULL) != SQLITE_OK)
                {
                    NSLog(@"Error: failed to prepare statement with message '%s'.", sqlite3_errmsg(database));
                }
                else
                {
                    sqlite3_bind_text(statement, 1, [code1 UTF8String], -1, SQLITE_TRANSIENT);
                    sqlite3_bind_text(statement, 2, [code1 UTF8String], -1, SQLITE_TRANSIENT);
                    sqlite3_bind_text(statement, 3, [code1 UTF8String], -1, SQLITE_TRANSIENT);
                    sqlite3_bind_text(statement, 4, [code1 UTF8String], -1, SQLITE_TRANSIENT);
                    sqlite3_bind_text(statement, 5, [code1 UTF8String], -1, SQLITE_TRANSIENT);
                    sqlite3_bind_text(statement, 6, [code1 UTF8String], -1, SQLITE_TRANSIENT);

                }

                int success = sqlite3_step(statement);
                if (success != SQLITE_DONE)
                {
                    NSLog(@"Error: failed to prepare statement with message '%s'.", sqlite3_errmsg(database));
                    //result = FALSE;
                }
                else
                {
                    NSLog(@"Error: failed to prepare statement with message '%s'.", sqlite3_errmsg(database));
                    //result = TRUE;
                }

                =================================END=========================================   


            }

            sqlite3_reset(statement);
        }
        else
        {
            NSLog(@"Not found");
            locationNo=@"1";
        }
        sqlite3_reset(statement);
    }
}

Answer 1

Generally you will get this if you have multiple queries going on at the same time (either you didn't finalize some early SQL statement, or you have multiple threads open, or you've opened the database multiple times).

This code has a slightly confusing use of sqlite3_close and sqlite3_reset (and a lack of sqlite3_finalize), which might be the source of the problem.

In An Introduction To The SQLite C/C++ Interface, they point out the correct order of the statements:

• sqlite3_open(), to open a database
• sqlite3_prepare(), to prepare a sql statement
• sqlite3_bind(), to bind values to ? placeholders as necessary
• sqlite3_step(), to execute the sql and/or step through the results
• sqlite3_column(), to retrieve the columns of data, as necessary
• sqlite3_finalize(), to complete/close the prepared sql statement
• sqlite3_close(), to close the database

Bottom line, your sqlite3_open call is not matched with a single sqlite3_close statement at the end (but you have an extraneous sqlite3_close in the middle of your code). Also, each sqlite3_prepare_v2 must have its own sqlite3_finalize (you only use sqlite3_reset if you want to reset a prepared statement so you can bind it with new values and step through it again; but you still need sqlite3_finalize when you're all done with the prepared statement).