我们有一个连续接收数据的线程。这被写入sqldb(方法名称:dbExecSql)。每隔一小时,该线程就会从另一个线程接收信号,以通过从主线程中选择几行并在主数据库中删除相同的行来创建新的数据库。
此请求有时在事务之间发生,新事务甚至在旧事务完成之前就开始了,因此数据库锁定错误。
该如何解决?
此方法写入db
int Database::dbExecSql()
{
.
.
.
rc = sqlite3_prepare_v2(db,insertTableSQL.c_str(),-1,&stmt,0);
.
.
sqlite3_exec(db,"BEGIN TRANSactION",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);
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(),&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(),&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(),&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(),&err_msg);
cout << "DELETE: " << rc << endl;
if (rc != SQLITE_OK)
{
cout << "ERROR: Hour Log Deletion: " << err_msg << endl;
sqlite3_free(err_msg);
return rc;
}
}
这两种方法都在单线程中。
如何确保仅在完成prev之后才开始下一个事务。