cocoa - SQLite3 : "Database is locked" error

Question

在我的 cocoa 应用程序中，我在资源文件夹中维护一个 SQLite 数据库，并尝试在其中执行一些选择、删除操作，但一段时间后它开始给我“数据库已锁定”错误。

我用于选择删除操作的方法如下:

//检索数据的方法

if (sqlite3_open([databasePath UTF8String], &database) != SQLITE_OK) 
        {
            sqlite3_close(database);
            NSAssert(0, @"Failed to open database");
        }

        NSLog(@"mailBodyFor:%d andFlag:%d andFlag:%@",UId,x,Ffolder);
        NSMutableArray *recordsToReturn = [[NSMutableArray alloc] initWithCapacity:2];
        NSString *tempMsg;


        const char *sqlStatementNew;
        NSLog(@"before switch");
        switch (x) {
            case 9:
            //  tempMsg=[NSString stringWithFormat:@"SELECT * FROM users_messages"];
                tempMsg=[NSString stringWithFormat:@"SELECT message,AttachFileOriName as oriFileName,AttachmentFileName as fileName FROM users_messages WHERE id = (select message_id from users_messages_status where id= '%d')",UId];
                NSLog(@"mail body query - %@",tempMsg);
                break;
            default:
                break;
        }
        sqlStatementNew = [tempMsg cStringUsingEncoding:NSUTF8StringEncoding];
        sqlite3_stmt *compiledStatementNew;

        NSLog(@"before if statement");
        if(sqlite3_prepare_v2(database, sqlStatementNew, -1, &compiledStatementNew, NULL) == SQLITE_OK) {
            NSLog(@"the sql is finalized");
            while(sqlite3_step(compiledStatementNew) == SQLITE_ROW) {
                NSMutableDictionary *recordDict = [[NSMutableDictionary alloc] initWithCapacity:3];

                NSString *message;
                if((char *)sqlite3_column_text(compiledStatementNew, 0)){
                    message = [NSString stringWithUTF8String:(char *)sqlite3_column_text(compiledStatementNew, 0)];
                }
                else{
                    message = @"";
                }
                NSLog(@"message - %@",message);

                NSString *oriFileName;
                if((char *)sqlite3_column_text(compiledStatementNew, 1)){
                    oriFileName = [NSString stringWithUTF8String:(char *)sqlite3_column_text(compiledStatementNew, 1)];
                }
                else{
                    oriFileName = @"";
                }
                NSLog(@"oriFileName - %@",oriFileName);

                NSString *fileName;
                if((char *)sqlite3_column_text(compiledStatementNew, 2)){
                    fileName = [NSString stringWithUTF8String:(char *)sqlite3_column_text(compiledStatementNew, 2)];
                }
                else{
                    fileName = @"";
                }
                NSLog(@"fileName - %@",fileName);

                [recordDict setObject:message forKey:@"message"];
                [recordDict setObject:oriFileName forKey:@"oriFileName"];
                [recordDict setObject:fileName forKey:@"fileName"];

                [recordsToReturn addObject:recordDict];
                [recordDict release];
            }

            sqlite3_finalize(compiledStatementNew);
            sqlite3_close(database);
            NSLog(@"user messages return -%@",recordsToReturn);

            return recordsToReturn;
        }
        else{
            NSLog(@"Error while creating retrieving mailBodyFor in messaging '%s'", sqlite3_errmsg(database));
            sqlite3_close(database);

        }

//删除数据的方法

if (sqlite3_open([databasePath UTF8String], &database) != SQLITE_OK) 
    {
        sqlite3_close(database);
        NSAssert(0, @"Failed to open database");
    }

    NSString *deleteQuery = [[NSString alloc] initWithFormat:@"delete from users_messages_status where id IN(%@)",ids];
    NSLog(@"users_messages_status msg deleteQuery - %@",deleteQuery);

    sqlite3_stmt *deleteStmnt;
    const char *sql = [deleteQuery cStringUsingEncoding:NSUTF8StringEncoding];

    if(sqlite3_prepare_v2(database, sql, -1, &deleteStmnt, NULL) != SQLITE_OK){
        NSLog(@"Error while creating delete statement. '%s'", sqlite3_errmsg(database));
    }   
    else{
        NSLog(@"successful deletion from users_messages");
    }

    if(SQLITE_DONE != sqlite3_step(deleteStmnt)){
        NSLog(@"Error while deleting. '%s'", sqlite3_errmsg(database));
    }

    sqlite3_close(database);

这个序列中出现了问题

1. 已检索数据
2. 执行删除操作时出现“数据库已锁定”错误。
3. 当我重试执行第一步时......它现在给出相同的错误。

有人可以建议我吗:

1. 如果我做错了什么或遗漏了一些检查？
2. 出现锁定错误时有办法解锁吗？

谢谢

米拉杰

Answer 1

每次访问时都打开/关闭数据库的情况确实很少见。

如果这是您想要的场景，那么使用普通文件会更好。

启动时打开数据库，退出时关闭。这可以解决您的所有问题。