如何处理Sqlite数据库在iOS中被锁定？

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.

我使用以下方法将数据添加到sqlite表。使用此方法插入第一条记录，然后添加第二条记录，我得到“数据库被锁定”。任何帮助将不胜感激。

-(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;
    }
}

1 个解决方案

#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.

有两种方法可以执行SQL语句。首先，你可以调用sqlite3_prepare_v2的序列，可选地调用sqlite3_bind_XXX绑定占位符，然后调用sqlite3_step，最后调用sqlite3_finalize。其次，你可以调用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.

但是这段代码正在做两件事，这是不正确的。更糟糕的是，你已经启动了sqlite3_prepare_v2进程，但是在中途停止尝试执行sqlite3_exec，然后继续第一个进程。

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:

最重要的是，不要同时准备一个sqlite3_stmt并调用sqlite3_exec。做一个或另一个。例如，如果你要像这样手动构建SQL，你可以调用sqlite3_exec并完成它：

- (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:

一些不相关的观察：

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

如果你使用sqlite3_exec的第五个参数，你真的应该释放该结果，如上所示。
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.

我建议你总是从sqlite3_XXX调用中保存数字返回码（特别是sqlite3_open）。它通常是一种重要的诊断工具。
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.

我不会像这样一直打开和关闭数据库。那效率很低。通常我们在启动应用程序时打开数据库，然后在应用程序终止之前关闭它。但是这样不断打开和关闭它是低效的。
I wouldn't advise building SQL statements with stringWithFormat. Perhaps you're OK here, but in general you'd use ? placeholders, e.g.

我不建议使用stringWithFormat构建SQL语句。也许你在这里很好，但一般来说你会用吗？占位符，例如
```
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).

然后，您可以调用sqlite3_prepare_v2来准备它，然后使用sqlite3_bind_XXX函数来绑定您的值。当您插入用户提供的文本时，此模式尤其重要，从而防止来自用户提供的字符串中出现“字符”的问题（从而破坏您手动构建的SQL）。

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

显然，如果你沿着这个sqlite3_stmt路走下去，你也不会使用sqlite3_exec。
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.

while（sqlite3_step（sqlStatement）== SQLITE_ROW）{...}与INSERT语句一起没有意义，因为它永远不会返回SQLITE_ROW。只有SELECT语句才会返回SQLITE_ROW。成功的INSERT语句将返回SQLITE_DONE。并且你只需要为INSERT语句调用sqlite3_step一次。
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.

我建议不要在代码中使用异常处理。如果您来自其他编程环境，这可能听起来很矛盾，但在Objective-C中，我们在运行时代码中使用错误处理（传递NSError对象），而不是异常处理。在开发过程中，应该从代码中消除异常。如果要在开发过程中捕获它们，请在调试器中使用异常断点。但是，避免以编程方式捕获异常，就好像您没有注意到它一样，您可以在代码中隐藏更深层次的问题。

#1