使用sqlite的api执行删表操作被锁问题

项目场景：

A表记录了另外B表表名相关的信息,当删除A表与B表相关记录时需要删除B表。

---

问题描述

在删除A表记录之前需要查询其记录的信息，以便删除B表，大致代码如下：

std::string strSql = "SELECT id, sid, layer_index_id, object_id, object_type FROM ";
strSql += m_strIndexTable;
strSql += " WHERE recycle = 1";
strSql += ";";
sqlite3_stmt* stmt;
int ret = sqlite3_prepare_v2(m_pDB, strSql.data(), strSql.size(), &stmt, NULL);
if (ret != SQLITE_OK)
{
	AcLogError() << "SELECT " << m_strIndexTable << " Error! " << sqlite3_errmsg(m_pDB);
	sqlite3_finalize(stmt);
	return false;
}

std::vector<__int64> vecDelete;
std::vector<__int64> vecDeleteLayerId;
while(SQLITE_ROW == sqlite3_step(stmt))
{
	__int64 nId = sqlite3_column_int64(stmt, 0); 
	__int64 nSid = sqlite3_column_int64(stmt, 1);
	__int64 nLayerIndexId = sqlite3_column_int64(stmt, 2);
	__int64 nObjectId = sqlite3_column_int64(stmt, 3);
	int nObjectType = sqlite3_column_int(stmt, 4); 

	if (-1 == nSid)
	{
        //判断nObjectType类型
        //执行删表操作
    }
}

sqlite3_finalize(stmt);
return true;

可以发现此处我并没有开启事务，当执行到删表代码时数据库抛出database is locked，即数据库被锁。

---

原因分析：

原本代码删表之前其实有执行删记录操作，网上查找方案有如下信息：

在 SQLite 中，DDL 命令（如 CREATE TABLE、DROP TABLE 等）不能与 DML 命令（如 INSERT、UPDATE、DELETE 等）一起在同一个事务中执行。这是因为 SQLite 将 DDL 和 DML 命令视为不同的事务类型。如果您在同一个事务中尝试同时执行 DDL 和 DML 命令，则可能会导致死锁或其他错误。 

所以我调试时跳过了所有的删除记录操作，只保留了上述代码结构的逻辑，他提到的DML命令中也并没有SELECT操作，刚开始以为是之前代码没有提交事务，导致的数据库被锁，检查代码发现这部分代码是最先执行的，也就是删表之前的代码只有上面代码中的查询操作。然后使用Navicat配合代码做测试，当代码执行到删表操作时，不执行代码，而使用Navicat的视图界面的右键菜单进行删表，此时也提示database is locked，那么问题找到了，查询操作时也不能进行删表，具体的原因没找到，猜测是为了防止被查询的表被删除？

---

解决方案：

既然问题找到了，那么解决方案就是不执行删表操作，把删表相关的信息记录到内存中，当上面的查询和我说的删除记录操作提交之后再进行删表。

完整代码如下： 

if (NULL == m_pHandler)
{
    return false;
}
if (NULL == m_pDB)
{
	return false;
}

while (!m_pFSMState->StateTrans(E_DBWRITE_STATE_SYNC))
{
	Sleep(1000); //休眠一秒
}
m_pHandler->Transaction();

std::string strSql = "SELECT id, sid, layer_index_id, object_id, object_type FROM ";
strSql += m_strIndexTable;
strSql += " WHERE recycle = 1";
strSql += ";";
sqlite3_stmt* stmt;
int ret = sqlite3_prepare_v2(m_pDB, strSql.data(), strSql.size(), &stmt, NULL);
if (ret != SQLITE_OK)
{
	AcLogError() << "SELECT workspace" << " Error! " << sqlite3_errmsg(m_pDB);
	sqlite3_finalize(stmt);
	return false;
}

std::vector<__int64> vecDeleteLayerId;
while(SQLITE_ROW == sqlite3_step(stmt))
{
	__int64 nId = sqlite3_column_int64(stmt, 0); 
	__int64 nSid = sqlite3_column_int64(stmt, 1);
	__int64 nLayerIndexId = sqlite3_column_int64(stmt, 2);
	__int64 nObjectId = sqlite3_column_int64(stmt, 3);
	int nObjectType = sqlite3_column_int(stmt, 4); 

	if (-1 == nSid)
	{
		if (nObjectType > 100)
		{
			bool bDelete = DeleteLayerRecordByLayerId(nObjectId, nObjectType);
			if (!bDelete)
			{
				AcLogError() << "DeleteLayerRecordByLayerId Error! ";
				m_pHandler->RollBack();
				if (!m_pFSMState->StateTrans(E_DBWRITE_STATE_NONE))
				{
					//退出失败,强制初始化为无操作状态
					m_pFSMState->SetInitState(E_DBWRITE_STATE_NONE);
				}
				return false;
			}
			vecDeleteLayerId.push_back(nObjectId);
		}
	}
}

sqlite3_finalize(stmt);
m_pHandler->Commit();
if (!m_pFSMState->StateTrans(E_DBWRITE_STATE_NONE))
{
	//退出失败,强制初始化为无操作状态
	m_pFSMState->SetInitState(E_DBWRITE_STATE_NONE);
}

while (!m_pFSMState->StateTrans(E_DBWRITE_STATE_SYNC))
{
	Sleep(1000); //休眠一秒
}
m_pHandler->Transaction();
for (int i = 0; i < vecDeleteLayerId.size(); i++)
{
	std::string strTableShape = "s_shape_layer" + std::to_string(vecDeleteLayerId.at(i)) + "_w" + m_strWorkId;
	bool bCheck = CheckTableIsExist(strTableShape);
	if (!bCheck)
	{
		//return true;//表已删除
		continue;
	}
	bool bDrop = DropTable(strTableShape);
	if (!bDrop)
	{
		AcLogError() << "DropTable Error! ";
		m_pHandler->RollBack();
		if (!m_pFSMState->StateTrans(E_DBWRITE_STATE_NONE))
		{
			//退出失败,强制初始化为无操作状态
			m_pFSMState->SetInitState(E_DBWRITE_STATE_NONE);
		}
		return false;
	}
}
m_pHandler->Commit();
if (!m_pFSMState->StateTrans(E_DBWRITE_STATE_NONE))
{
	//退出失败,强制初始化为无操作状态
	m_pFSMState->SetInitState(E_DBWRITE_STATE_NONE);
}

return true;