database - SQLITE：database.locked 错误 - 如何等到一个事务完成才能在单个线程中启动下一个事务

Question

我们有一个连续接收数据的线程。这被写入 sqldb (method name : dbExecSql) 。每隔一小时，该线程会反复接收来自另一个线程的信号，以通过从主数据库中选择几行并在主数据库中删除相同的行来创建新数据库。

此请求有时发生在事务之间，新事务甚至在旧事务完成之前就开始了，因此 database.locked 错误。

如何解决这个问题？

此方法写入 db

int Database::dbExecSql()
{
.
.
.
 rc = sqlite3_prepare_v2(db, insertTableSQL.c_str(), -1, &stmt, 0);
.
.
 sqlite3_exec(db, "BEGIN TRANSACTION", NULL, NULL, NULL);

..
rc = sqlite3_bind_int(stmt, j + 1, insertValueInt);
.
.
 rc = sqlite3_step(stmt);

 sqlite3_clear_bindings(stmt);
 sqlite3_reset(stmt);

 rc = sqlite3_exec(db, "END TRANSACTION", NULL, NULL, NULL);
 rc = sqlite3_finalize(stmt);

}

此方法从数据库中删除并创建另一个数据库。有时在执行 detach 时，我们会得到 database.locked 错误。

int Database::dbDelete()
.
.
.
 attachQuery = "ATTACH DATABASE '" + db_log_dir + "/" + system_id + "_" + dbLogName + ".sql'" + " AS '" + system_id + "_" + dbLogName + "';";
 detachQuery = "DETACH DATABASE '" + system_id + "_" + dbLogName + "';";
 copyTableQuery = "CREATE TABLE '" + system_id + "_" + dbLogName + "'.'" + tableName + "_" + dbLogName + "' AS SELECT * FROM main." + tableName;


  deleteQuery = dbDeleteSelected(tableName, timeStart, customLog);

. 
. 
. 
rc = sqlite3_exec(db, attachQuery.c_str(), NULL, 0, &err_msg);
    cout << "ATTACH: " << rc << endl;
    if (rc != SQLITE_OK)
    {
        cout << "ERROR: Hour Log Attach: " << err_msg << endl;
        cout << "SQL -> " << attachQuery.c_str() << endl;
        sqlite3_free(err_msg);
        return rc;
    }

    rc = sqlite3_exec(db, copyTableQuery.c_str(), NULL, 0, &err_msg);
    cout << "COPY: " << rc << endl;
    if (rc != SQLITE_OK)
    {
        cout << "ERROR: Hour Log copyTable: " << err_msg << endl;
        cout << "SQL -> " << copyTableQuery.c_str() << endl;
        sqlite3_free(err_msg);
        return rc;
    }

    rc = sqlite3_exec(db, detachQuery.c_str(), NULL, 0, &err_msg);
    cout << "DETACH: " << rc << endl;
    if (rc != SQLITE_OK)
    {
        cout << "ERROR: Hour Log Detach: " << err_msg << endl;
        cout << "SQL -> " << detachQuery.c_str() << endl;
        sqlite3_free(err_msg);
        return rc;
    }

    rc = sqlite3_exec(db, deleteQuery.c_str(), NULL, 0, &err_msg);
    cout << "DELETE: " << rc << endl;
    if (rc != SQLITE_OK)
    {
        cout << "ERROR: Hour Log Deletion: " << err_msg << endl;
        sqlite3_free(err_msg);
        return rc;
    }
}

这两种方法都在单线程中。

如何确保只有在上一个完成后才开始下一个事务。

标签： databasesqliteerror-handlinglocked