sqlite3_wal_checkpoint_v2 总是返回 SQL_BUSY [英] sqlite3_wal_checkpoint_v2 always returns SQL_BUSY
问题描述
背景
我在 Windows C++ 程序中使用 sqlite 版本 3.7.7.1，我正在创建一个与 SQLITE_OPEN_READWRITE 只有一个连接的数据库 |SQLITE_OPEN_CREATE |SQLITE_OPEN_NOMUTEX.
我使用 SQLite 是因为我需要非常快速的 ACID 事务.数据库存储在 Windows XP sp3 上的 NTFS 固态驱动器上.
这个数据库有 3 个表，每个事务隐含 3 个表，最大的事务在其中一个表中插入 12 行，在另一个表中插入 1 行并更新第三个表.
我的应用程序有一个 400 毫秒的周期，数据库用于在每个周期开始时发生的 9 个事务的突发.知道我的平均事务需要 15 毫秒(在 60 毫秒处出现非常罕见的峰值(大约 60k 事务中的 1 个事务在 Windows FlushFileBuffers 函数中丢失了时间)，数据库仅在 400 毫秒周期的前 150 毫秒中使用.
经过基准测试后，WAL 期刊为我的应用程序提供了最佳性能.
但是，我对 WAL 日志有一个问题:xxx-wal 文件正在不断增长并且不断增长(在 500k 交易之后，wal 文件是 8GB！).激活 auto_checkpoint 没有帮助.
尝试通过调用带有 SQLITE_CHECKPOINT_RESTART 选项的 sqlite3_wal_checkpoint_v2 手动执行检查点时，sqlite3 一直返回 SQLITE_LOCKED.
我发现当我使用 sqlite3_exec 创建一个表时，连接永远变成了锁.怎么解释?有没有办法避免这种情况?
所以关闭连接并重新打开它，我重试调用 sqlite3_wal_checkpoint_v2，但这一次，它返回 SQLITE_BUSY.即使我用 1 秒调用 sqlite3_busy_timeout，它仍然返回 SQLITE_BUSY.
问题
是什么导致数据库繁忙，知道数据库上只使用了一个连接，这个连接被多个线程使用，但在我的应用程序端通过互斥锁进行序列化?>
更多信息:
- 我在循环中使用了几个准备好的语句(甚至一个用于 TRANSACTION BEGIN 和 TRANSACTION COMMIT)
- 提交第一个事务后，数据库似乎很忙.
尽管我在 sqlite 网站上阅读过，但我还是很难理解 sqlite 的锁定和繁忙方案.有没有人有一些好的链接?
我找到了问题的原因，这是我身边的两个愚蠢的事情:
使用PRAGMA page_size"查询页面大小，我只是在读取行，而不是使用sqlite3_reset"或 sqlite3_finalize 重置或完成语句(它有点被我测试的 C++ 包装器隐藏了).
这导致数据库被锁定.然后，我在关闭数据库之前没有完成此查询，这导致数据库在重新打开后返回忙碌状态.
Context
I'm using sqlite version 3.7.7.1 in a Windows C++ program, I'm creating a DB that has only one connection with SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE | SQLITE_OPEN_NOMUTEX.
I'm using SQLite because I need very fast ACID transaction. The Database is store on a NTFS solid state drive on Windows XP sp3.
This DataBase has 3 tables and each transaction implies the 3 tables and the biggest transaction inserts 12 rows in one of the table, insert 1 row in another and update the third table.
My application has a 400ms cycle and the database is used for a burst of 9 transactions that occurs at the beginning of each cycle. Knowing that my average transaction takes 15ms (with really rare peak at 60ms (about 1 of 60k transactions where time is lost in the Windows FlushFileBuffers function )), the DataBase is just used in the first 150ms of the 400ms cycle.
After benchmarking, the WAL journal offers the best performances for my application.
But, I have a problem with WAL journal : the xxx-wal file is growing and growing non stop (after 500k transaction, the wal file was 8GB!). Activating the auto_checkpoint doesn't help.
When trying to manually do the checkpoint by calling sqlite3_wal_checkpoint_v2 with SQLITE_CHECKPOINT_RESTART option, sqlite3 kept returning SQLITE_LOCKED.
I discover that when I create a table using sqlite3_exec, the connection becomes lock forever. What's the explanation ? Is there a way to avoid that ?
So closing the connection and reopening it, I retry to call sqlite3_wal_checkpoint_v2, but this time, it returned SQLITE_BUSY. Even if I call sqlite3_busy_timeout with 1 second, it still returns SQLITE_BUSY.
Questions
What cause a db to be busy, knowing that only one connection is used on the db, that this connection is used by several threads but are serialized via a mutex on my application side ?
More Info:
- I'm using several prepared statements that I keep through the cycles (even one for the TRANSACTION BEGIN and TRANSACTION COMMIT)
- The DB seems to be busy after the first transaction is commited.
I'm having a hard time to understand the locking and busy scheme of sqlite despite my reading on the sqlite web site. Does anyone have some good links on that ?
I found the cause to my problem and it was something 2 stupid things on my side :
Querying the page size with a "PRAGMA page_size", I was just reading the row and not resetting or finalizing the statement with "sqlite3_reset" or sqlite3_finalize (it was kind of hidden by the C++ wrapper I test with).
This was causing the database to be locked. Then, I was not finalizing this query before closing the database and this was causing the database to return busy after reopening it.
这篇关于sqlite3_wal_checkpoint_v2 总是返回 SQL_BUSY的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
