使用 PHP/PDO 避免锁定 SQLite 数据库超时
我目前正在尝试修复 Drupal 核心问题队列中的问题，该问题与有人访问时发生的超时有关执行搜索索引时的站点，这是一种相当写入密集的操作，通常在调用 cron 命令时发生（因此它很可能发生在活动站点上，同时仍然可供公众访问）。它源于 SQLite 对写入的锁定，这通常不是问题，但在本例中是这样，因为执行此索引会大大增加连接在等待锁释放时超时的可能性。
答案似乎是增加驱动程序等待锁释放的时间。在 PHP 中，这应该可以通过将 PDO::ATTR_TIMEOUT 选项设置为等待锁定的秒数来实现。然而，这似乎并没有真正起作用；无论传递什么值，超时都会发生。不过，我在互联网上的其他地方找不到任何提及它不起作用的信息，那么只有我们这样吗？还有其他人在尝试增加 SQLite 的超时时间时遇到过麻烦吗？我们是否有其他方法可以避免这个问题的发生？
I'm currently trying to fix an issue in Drupal core's issue queue having to do with timeouts happening when someone visits a site while it is performing search indexing, a rather write-intensive operation that usually happens at the invocation of a cron command (so it's quite likely to happen on a live site while still accessible to the public). It stems from SQLite's locking on writes, which usually isn't an issue, but is in this case because doing this indexing is greatly increasing the likelihood that a connection will time out while waiting for the lock to release.
The answer seems to be to increase the amount of time that the driver waits for the lock to release. In PHP, this is supposed to be possible by setting the PDO::ATTR_TIMEOUT option to the number of seconds to wait for the lock. However, this doesn't seem to actually work; the timeouts happen no matter what value pass along this. I can't find any mention of it not working anywhere else on the internet, though, so is it just us? Has anyone else encountered trouble trying to increase SQLite's timeout time? Is there perhaps some other way we can avoid this issue happening?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
只有一种可能的解决方案 -
不要忘记，时间以秒为单位:-)
There is only one possible solution -
Do not forget, that the time is in SECONDS :-)
我通过
在写作时使用事务来设法缓解这个问题。它不会完全解决这个问题，但它会让你的写入速度更快，特别是如果它是一堆同时完成的更新/插入。
I've managed to alleviate this by using
and using transactions when writing. It won't resolve it fully but it will make your writes much much faster, especially if it's a bunch of updates/inserts that are all done at the same time.
执行搜索索引时是否可以进入只读状态？
（只读比超时好......）
如果是这样，我建议这样做：
Does it possible to enter read-only state while performing search indexing?
(read-only is better than timeouts...)
If so I would suggest doing it like that:
当一起使用以下两个 PRAGMA 命令时，PDO::ATTR_TIMEOUT 似乎被忽略。单独使用其中之一，超时就会按预期工作。 SQLite 版本：3.7.7.1
我不明白为什么你想要包含这两个，但我确实遇到了它们都被使用的情况，并且我看到 SQLite 数据库正忙消息，因为超时没有被执行受到尊重。
PDO::ATTR_TIMEOUT seems to be ignored when using the following two PRAGMA commands together. Use one or the other by itself and the timeout works as expected. SQLite Version: 3.7.7.1
I don't see why you would want to include both of these, but I did run into a situation where they were both used, and I was seeing the SQLite database is busy message because the timeout was not being respected.