确定SQLite数据库是否被锁定 [英] Determine whether SQLite database is locked
问题描述
我已经阅读了有关如何检测SQLite数据库是否被锁定的其他答案，它建议使用 sqlite3_busy_handler / sqlite3_busy_timeout .我正在使用支持SQLite数据库的Qt，但这在这里无关紧要.
I've read other answers on how to detect whether the SQLite database is locked, and it suggests to use sqlite3_busy_handler/sqlite3_busy_timeout. I'm using Qt which has support of SQLite databases but that doesn't matter here.
因为在我的应用程序的用例中，另一个应用程序可能会访问同一数据库，所以我需要处理这种情况.
Because in the use case of my application another application might access the same database, I need to handle this case.
以下是我在打开数据库时设置的选项:
Here's the options I set to the database when opening it:
auto db = QSqlDatabase::database();
db.setDatabaseName(m_sDatabasePath);
db.setConnectOptions("QSQLITE_BUSY_TIMEOUT=0");
if (!db.open())
return false;
我有一个应该确定数据库是否被锁定的功能:
I have a function which should determine whether database is locked or not:
int callback(void *data, int)
{
std::cout << "callback" << std::endl;
return 0;
}
bool isDatabaseLocked(const QSqlDatabase &db)
{
if (auto driver = db.driver())
{
// get driver handler
QVariant v = driver->handle();
if (v.isValid() && qstrcmp(v.typeName(), "sqlite3*") == 0)
{
// v.data() returns a pointer to the handle
auto handle = *static_cast<sqlite3 **>(v.data());
if (handle)
{
std::cout << sqlite3_busy_handler(handle, callback, nullptr) << std::endl;
std::cout << sqlite3_busy_timeout(handle, 0) << std::endl;
}
}
}
return true;
}
当数据库结焦后，我执行此函数并输出两个0( SQLITE_OK )，而我希望得到5( SQLITE_BUSY ).而且回调函数也不被调用.
When the database is lcoked, I execute this function and get printed two 0s (SQLITE_OK) while I'd expect to get 5 (SQLITE_BUSY). And the callback function isn't called either.
我在做什么错了?
推荐答案
正如文档中明确指出的那样， sqlite3_busy_handler 函数将回调函数设置为连接的忙处理程序.每当遇到锁时，都会调用此类回调可能.在您的代码中，对 sqlite3_busy_handler 的调用返回 SQLITE_OK ，这非常好:这意味着尝试成功设置回调，没有理由返回 SQLITE_BUSY
As clearly stated in the documentation, the sqlite3_busy_handler function sets a callback function as the busy handler for the connection. Such callback might be invoked whenever a lock is met. In your code, the call to sqlite3_busy_handler returns SQLITE_OK, which is perfectly fine: it means the attempt to set the callback succeeded, no reason to return SQLITE_BUSY at this time, since the call only affects the connection, not the database.
sqlite3_busy_timeout 函数大致相同，该函数依次设置另一种繁忙的处理程序(顺便说一下，它替换了您刚刚设置的回调)，该处理程序仅睡眠指定数量的直到释放锁定为止的毫秒数.
About the same applies to the sqlite3_busy_timeout function, which in turn sets a different kind of busy handler (which replace the callback you just set, by the way) which simply sleeps for the specified amount of milliseconds until the lock is released.
现在，一般而言，如何检测资源是否被锁定?通常的方法是:尝试将其锁定.
Now, generally speaking, how to detect if a resource is locked? The usual way is: try to lock it.
所以您可以使用如下函数:
So you could use a function like this:
bool isDatabaseLocked(const QSqlDatabase & db)
{
QSqlQuery q(db);
if(q.exec("BEGIN EXCLUSIVE")) //tries to acquire the lock
{
q.exec("COMMIT"); //releases the lock immediately
return false; //db is not locked
}
return true; //failed to acquire the lock: returns true (db is locked)
}
要使其在无法获取锁时立即返回，请清除忙碌处理程序，将此连接的忙碌超时设置为零(在打开前):
To make it return immediately when the lock cannot be acquired, clear the busy handler, setting the busy timeout to zero for this connection (before opening it):
QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE");
db.setConnectOptions("QSQLITE_BUSY_TIMEOUT=0");
要测试该功能，请在数据库上打开命令行 sqlite3 会话，然后通过键入以下内容将其锁定:
To test the function, open a command line sqlite3 session on the database, and lock it by typing:
sqlite> BEGIN EXCLUSIVE;
要解除锁定时，只需键入
When you want to release the lock, just type
sqlite> COMMIT;
这篇关于确定SQLite数据库是否被锁定的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
