Sqlite database locked issue in iOS

Question

I am opening and closing db properly. But still when i try to insert record in table, it is giving database locked error. Please suggest what to do.

-(void)insertInDatabase:(NSMutableDictionary *) dict
{

    NSArray *dbPath = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory,    NSUserDomainMask, YES);
    NSString *documentsDirectory = [dbPath objectAtIndex:0];
    NSString *DBPath=[documentsDirectory stringByAppendingPathComponent:@"InvoiceScannerDB.sqlite"];
    AppDelegate *appDelegate = (AppDelegate *)[[UIApplication sharedApplication] delegate];
    [appDelegate checkAndCreateDatabase: DBPath];

    sqlite3 * database;


    if(sqlite3_open([DBPath UTF8String], &database) == SQLITE_OK){
//      NSLog(@"open");
        NSString *sql = [[NSString alloc] initWithFormat:@"select *  from Ticket where DELIVERY_SEQ='%@'and DOC_DATE='%@' and ROUTE_CODE='%@'", [dict valueForKey:@"DELIVERY_SEQ"],[dict valueForKey:@"DOC_DATE"],[dict valueForKey:@"ROUTE_CODE"]];


        sqlite3_stmt *statement, *statement1, *statement2;

        if(sqlite3_prepare_v2(database, [sql UTF8String], -1, &statement, NULL) == SQLITE_OK){

            NSLog(@"sqlite3_prepare_v2: %s", sqlite3_errmsg(database));

                //  NSLog(@"Statement %@",statement);
            if(sqlite3_step(statement) == SQLITE_ROW){
                NSLog(@"statement: %s", sqlite3_errmsg(database));

                    // Update Ticket for total scan count
                NSLog(@"Same record found in  local db");
                NSString *sqlUpdate = [[NSString alloc] initWithFormat:@"update Ticket set SCAN_INVOICE = '%ld' where DELIVERY_SEQ='%@'and DOC_DATE='%@' and ROUTE_CODE='%@'", (long)[[dict valueForKey:@"SCAN_INVOICE"] integerValue], [dict valueForKey:@"DELIVERY_SEQ"], [dict valueForKey:@"DOC_DATE"], [dict valueForKey:@"ROUTE_CODE"]];
                if(sqlite3_prepare_v2(database, [sqlUpdate UTF8String], -1, &statement2, NULL) == SQLITE_OK){

                    sqlite3_step(statement2);
                        //                  NSLog(@"Scan count updated successfully");

                }
                else{
                    NSLog(@"Not updated data");
                }
                sqlite3_finalize(statement2);
            }
            else {
                    // Insert
                NSDateFormatter *dateFormatter = [[NSDateFormatter alloc] init];
                dateFormatter.locale = [[NSLocale alloc] initWithLocaleIdentifier:@"en_US"];
                [dateFormatter setDateFormat:@"dd-MMM-yyyy"];
                NSString *newDateString = [dateFormatter stringFromDate:[NSDate date]];

                NSString *sqlInsert = [[NSString alloc] initWithFormat:@"insert into Ticket (DELIVERY_SEQ, DOC_DATE, ROUTE_CODE, TOTAL_INVOICE, SCAN_INVOICE, InsertedDate) values('%@','%@', '%@','%ld','%ld', '%@')", [dict valueForKey:@"DELIVERY_SEQ"], [dict valueForKey:@"DOC_DATE"], [dict valueForKey:@"ROUTE_CODE"], (long)[[dict valueForKey:@"TOTAL_INVOICE"] integerValue], (long)[[dict valueForKey:@"SCAN_INVOICE"] integerValue], newDateString];

                NSLog(@"Insertion Query %@", sqlInsert);

                if(sqlite3_prepare_v2(database, [sqlInsert UTF8String], -1, &statement1, NULL) == SQLITE_OK){

                    if(sqlite3_step(statement1) == SQLITE_DONE)
                    {

                    } else {
                        NSLog(@"error: %s", sqlite3_errmsg(database));
                    }
                }
                else{
                        NSLog(@"Not inserted data");
                }
                sqlite3_finalize(statement1);
            }
        }
        else{
            NSLog(@"COULD NOT OPEN DB");
        }
        sqlite3_finalize(statement);
    }
    sqlite3_close(database);


}

Kindly help me on this Issue

Answer 1

You are executing statement1 and statement2 before you finalize the original statement. You should close this first SELECT statement that tests to see if there are any rows of data before you try to do the INSERT or UPDATE. That might not be the actual source of the busy database error, but it's a wonderful illustration of how easy it is to accidentally (and unnecessarily) perform two SQL statements at the same time.

If fixing this does not remove the error, then you must have some other code that is locking the database. I know that you assured us that you've checked all of the open and close statements and that you don't have any multithreaded database interaction, but the "busy" message means that the database is simply busy doing something else, so you have to figure out what is doing this.

If the problem persists, I might suggest centralizing the opening/closing of the database (that way, it's easy to insert a "is it already open" check). Frankly, I might go a step further and eliminate the process of opening and closing the database for every database interaction, but that's up to you.