how to handle Sqlite database is locked in iOS?

Question

I am using following method to add data to sqlite table. Using this method first record get inserted and after that for adding second record i am getting "database is locked". Any help will be appreciated.

-(BOOL)insertData:(float)old_otp old_generated_at:(NSString*)old_generated_at old_msp_delivery_time:(NSString*)old_msp_delivery_time old_valid_upto:(NSString*)old_valid_upto rc_profile_master_pm_id:(double)rc_profile_master_pm_id otp_validity:(BOOL)otp_validity
{
    @try
    {
        NSString *documentsFolder = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES)[0];
        NSString *documentsPath   = [[documentsFolder stringByAppendingPathComponent:@"RCDB"] stringByAppendingPathExtension:@"sqlite"];

        BOOL success = [DBManager initDatabase];
        if(!success)
        {
            NSLog(@"Cannot locate database file '%@'.", documentsPath);
        }
        if(!(sqlite3_open([documentsPath UTF8String], &db) == SQLITE_OK))
        {
            NSLog(@"An error has occurred.");
        }


        NSString *insertSQL = [NSString stringWithFormat: @"insert into rc_otp_log_details values(null,%f,'%@','%@','%@',%f,%d)",old_otp,old_generated_at,old_msp_delivery_time,old_valid_upto,rc_profile_master_pm_id,otp_validity];

        const char *sql = [insertSQL UTF8String];

        sqlite3_stmt *sqlStatement;
        if(sqlite3_prepare(db, sql, -1, &sqlStatement, NULL) == SQLITE_OK)
        {
            char *errMsg;
            if (sqlite3_exec(db, sql, NULL, NULL, &errMsg) != SQLITE_OK) {
                NSLog(@"Failed to create errMsg   %s" ,errMsg);
                sqlite3_finalize(sqlStatement);
                sqlite3_close(db);
                return false;
            }
            else
            {
                NSLog(@"inserted new");
                sqlite3_finalize(sqlStatement);
                sqlite3_close(db);
                return true;
            }
        }
        else
        {
            NSLog(@"insert statement problem");
            sqlite3_finalize(sqlStatement);
            sqlite3_close(db);
            return true;
        }

        //
        while (sqlite3_step(sqlStatement)==SQLITE_ROW) {
            NSLog(@"SQLITE_ROW %%d,SQLITE_ROW");

        }
        sqlite3_finalize(sqlStatement);
        sqlite3_close(db);
        return true;
    }
    @catch (NSException *exception) {
        NSLog(@"An exception occured: %@", [exception reason]);
        return false;

    }
    @finally {
        return true;
    }
}

Answer 1

There are two ways of performing a SQL statement. First, you can call the sequence of sqlite3_prepare_v2, optionally call sqlite3_bind_XXX to bind place holders, then call sqlite3_step, and then finally call sqlite3_finalize. Second, you can just call sqlite3_exec.

But this code is doing both, which is incorrect. Worse, you've started the sqlite3_prepare_v2 process, but stopped half way tried to perform sqlite3_exec, and then continue the first process.

Bottom line, do not both prepare a sqlite3_stmt and call sqlite3_exec. Do one or the other. For example, if you're going to build your SQL manually like that, you can just call sqlite3_exec and be done with it:

- (BOOL)insertData:(float)old_otp old_generated_at:(NSString*)old_generated_at old_msp_delivery_time:(NSString*)old_msp_delivery_time old_valid_upto:(NSString*)old_valid_upto rc_profile_master_pm_id:(double)rc_profile_master_pm_id otp_validity:(BOOL)otp_validity {
    int rc;
    NSString *documentsPath = ...

    BOOL success = [DBManager initDatabase];
    if (!success) {
        NSLog(@"Cannot locate database file '%@'.", documentsPath);
    }
    if ((rc = sqlite3_open([documentsPath UTF8String], &db)) != SQLITE_OK) {
        NSLog(@"An error has occurred %ld.", (long)rc);
        return false;
    }

    NSString *insertSQL = ...

    const char *sql = [insertSQL UTF8String];

    if ((rc = sqlite3_exec(db, sql, NULL, NULL, &errMsg) != SQLITE_OK) {
        NSLog(@"Failed to create errMsg %s (%ld)", errMsg, (long)rc);
        sqlite3_free(errMsg);
    }

    sqlite3_close(db);

    return rc == SQLITE_OK;
}

---

A few unrelated observations:

1. If you use the fifth parameter of sqlite3_exec, you really should free that result, as shown above.

2. I'd suggest you alway save the numeric return code from your sqlite3_XXX calls (particularly sqlite3_open). It's often an important diagnostic tool.

3. I wouldn't open and close the database all the time like this. That's inefficient. Generally we open the database when we start the app and then close it before the app is terminated. But it's inefficient to constantly open and close it like that.

4. I wouldn't advise building SQL statements with stringWithFormat. Perhaps you're OK here, but in general you'd use ? placeholders, e.g.

   const char *sql = "insert into rc_otp_log_details values (null, ?, ?, ?, ?, ?, ?)";
   

   You'd then call sqlite3_prepare_v2 to prepare this and then use the sqlite3_bind_XXX functions to bind your values. This pattern is especially important when you're inserting user supplied text, thereby preventing problems stemming from user supplied strings having ' character in them (thus breaking your manually built SQL).

   Clearly, if you go down this sqlite3_stmt road, you would not use sqlite3_exec, too.

5. The while (sqlite3_step(sqlStatement) == SQLITE_ROW) { ... } makes no sense in conjunction with INSERT statement because it will never return SQLITE_ROW. Only SELECT statements will return SQLITE_ROW. A successful INSERT statement will return SQLITE_DONE. And you'd only ever call sqlite3_step once for an INSERT statement.

6. I would advise against using exception handling in your code. If you've come from other programming environments, this may sound paradoxical, but in Objective-C we use error handling (passing around NSError objects) in our runtime code, not exception handling. Exceptions should be eliminated from your code during the development process. If you want to catch them during the development process, use an exception breakpoint in your debugger. But avoid programmatically catching exceptions, as if you don't happen to notice it, you can be hiding deeper problems in your code.