并发 BEGIN–SELECT–INSERT 导致即时 SQLite3.7.6“数据库锁定”错误，覆盖繁忙超时？
在 sqlite3 控制台中：
sqlite>
CREATE TABLE items (id PRIMARY KEY);
BEGIN;
SELECT * FROM items;
INSERT INTO items VALUES(78);
sqlite> _
然后在第二个控制台中：
sqlite>
.timeout 10000;
BEGIN;
SELECT * FROM items;
INSERT INTO items VALUES(78);
Error: database is locked
sqlite> _
“数据库已锁定”错误立即发生，这不可能是正确的，对吧？
如果我在第二个控制台中省略 SELECT，则繁忙的处理程序将在 INSERT 处等待 10 秒。我发现使用 BEGIN EXCLUSIVE 也会使第二个事务等待 10 秒，但随后会在 BEGIN 语句处等待。 （我已决定将其作为解决方法。）
我的问题：这是一个错误，还是应该如此？如果这是预期的行为，那么为什么呢？
谢谢！
（SQLite v3.7.6）
In an sqlite3-console:
sqlite>
CREATE TABLE items (id PRIMARY KEY);
BEGIN;
SELECT * FROM items;
INSERT INTO items VALUES(78);
sqlite> _
And then in a second console:
sqlite>
.timeout 10000;
BEGIN;
SELECT * FROM items;
INSERT INTO items VALUES(78);
Error: database is locked
sqlite> _
The "database is locked"-error happends instantly, which can't be right, right?
If I omit the SELECT in the second console the busy handler waits for 10 seconds at the INSERT. I've found that using BEGIN EXCLUSIVE also makes the second transaction wait for 10 seconds, but then at the BEGIN-statement. (Which I've resolved to as a workaround.)
My question: Is this a bug, or as it should be? If this the expected behaviour, then why?
Thanks!
(SQLite v3.7.6)
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
在第一个事务释放其写锁之前，第二个事务无法完成；在第二个事务释放其读锁之前，第一个事务无法完成。因此，立即回滚第二个是有意义的，因为它无法在任何时间内完成，因此第一个可以完成，您可以重试。
BEGIN EXCLUSIVE立即获取排他锁，而不是等待第一个查询，这解释了您所看到的差异。您应该在事务中留意锁定的数据库 (
SQLITE_BUSY)，如果发生这种情况，请回滚并重试。超时繁忙处理程序不会保护这些。The second transaction cannot complete until the first one releases its write lock, and the first one can't complete until the second transaction releases its read lock. It thus makes sense to rollback the second one right away, since it cannot complete in any amount of time, so the first one can finish and you can try again.
BEGIN EXCLUSIVEacquires the exclusive lock right away, instead of waiting for the first query, which explains the difference you see.You should watch out for locked database (
SQLITE_BUSY) while in transactions, and rollback and try again if that happens. The timeout busy handler won't protect these.是的，这是正常的。请阅读：http://www.sqlite.org/lockingv3.html
Yes this is normal. Please read: http://www.sqlite.org/lockingv3.html