c - 如何完成所有待处理的 SELECT 语句？

Question

SQLite wiki page for "database is locked"说:

Sometimes people think they have finished with a SELECT statement because sqlite3_step() has returned SQLITE_DONE. But the SELECT is not really complete until sqlite3_reset() or sqlite3_finalize() have been called. As of check-in [3902] (2007-05-02 after version 3.3.17) this is now allowed for CREATE statement.

当然，我可以关闭并重新打开数据库，但这将使所有准备好的语句无效。是否存在另一种方法，不需要我跟踪所有待处理的 SELECT 语句？

Answer 1

I can, of course, close and reopen the database, but this would invalidate all prepared statements. Does there exist another way that would not involve me tracking all pending SELECT statements?

SQLite 已经跟踪您所有准备好的语句，并且它提供 sqlite3_next_stmt()作为一种可以遍历其列表的机制。还有sqlite3_stmt_busy()通过它，您可以测试语句是否使事务处于打开状态，但这并不能帮助您识别已运行完成的语句，因此与实时事务无关，但仍然有数据库资源分配给它们尚未最终确定或重置。

这里有一种方法可以重置 SQLite 当前已知的所有准备好的语句:

    sqlite3 *db = /* ... */
    sqlite3_stmt *stmt;

    /* ensure that there is no open transaction; will fail harmlessly if
       there already is none */
    sqlite3_exec(db, "rollback", NULL, NULL, NULL);

    /* Clean up any outstanding prepared statements */
    for (stmt = sqlite3_next_stmt(db, NULL); stmt; stmt = sqlite3_next_stmt(db, stmt)) {
        int result = sqlite3_reset(stmt);
        /* handle errors ... */
    }

重置不需要的语句应该不会有害。

话虽如此，您应该跟踪当前运行的语句，并确保在完成它们提供的当前结果集后重置它们。这需要一些纪律，但这是正确的做法。