ios - 在 iOS 上使用 fmbd 删除 SQLite 索引失败并出现 SQLITE_LOCKED

Question

我在 iOS 应用程序中删除在 SQLite 中创建的索引时遇到了一些问题。我正在使用 fmdb .

尝试删除索引时，sqlite3_step 总是返回 SQLITE_LOCKED。结果，fmdb 陷入无限循环，不断尝试重试 drop 语句(每次，sqlite3_step 返回 SQLITE_LOCKED)，并且该语句永远不会成功。

据我所知，在 drop 语句正常工作之前，没有其他进程接触数据库和语句。我错过了什么？

这里几乎是失败代码的逐字副本:

[db open];
/* ... */
[db executeUpdate:@"DROP INDEX IF EXISTS bookmark_hash_idx;"];
[db close];

db 是指向我的文档目录中的 sqlite 数据库的指针。

如果有用的话，这是来自 fmdb 的相关代码:

do {
    rc      = sqlite3_step(pStmt);
    retry   = NO;

    if (SQLITE_BUSY == rc || SQLITE_LOCKED == rc) {
        // this will happen if the db is locked, like if we are doing an update or insert.
        // in that case, retry the step... and maybe wait just 10 milliseconds.
        retry = YES;
        if (SQLITE_LOCKED == rc) {
            rc = sqlite3_reset(pStmt);
            if (rc != SQLITE_LOCKED) {
                NSLog(@"Unexpected result from sqlite3_reset (%d) eu", rc);
            }
        }
        /* ... */
    }
    /* ... */
} while (retry);

Answer 1

您可能有开放的结果集，它阻止了 DROP INDEX 的通过。来自 SQLite docs :

The "DROP TABLE" Exception

When a call to sqlite3_step() returns SQLITE_LOCKED, it is almost always appropriate to call sqlite3_unlock_notify(). There is however, one exception. When executing a "DROP TABLE" or "DROP INDEX" statement, SQLite checks if there are any currently executing SELECT statements that belong to the same connection. If there are, SQLITE_LOCKED is returned. In this case there is no "blocking connection", so invoking sqlite3_unlock_notify() results in the unlock-notify callback being invoked immediately. If the application then re-attempts the "DROP TABLE" or "DROP INDEX" query, an infinite loop might be the result.

您应该在 FMDatabase 对象上调用 closeOpenResultSets 以确保在删除索引之前关闭所有打开的结果集。