为什么SQLite给出“数据库已锁定"?在使用Perl的DBD :: SQLite时在事务中进行第二次查询? [英] Why does SQLite give a "database is locked" for a second query in a transaction when using Perl's DBD::SQLite?
问题描述
使用Perl DBD :: SQLite时，SQLite在单个事务中为第二个查询给出数据库已锁定"错误是否存在已知问题?场景:Linux，Perl DBI，AutoCommit => 0，这是一个具有两个代码块的子例程(使用这些块来本地化变量名).在第一个代码块中，通过对select语句的prepare()创建查询句柄，执行该查询句柄，然后关闭该块.第二个代码块是通过准备更新语句来创建另一个查询句柄的，并且在这一阶段，频繁(30％的时间)SQLite/DBI会给出数据库锁定错误.我认为该错误发生在prepare()期间，而不是在execute()期间.
Is there a known problem with SQLite giving a "database is locked" error for a second query in a single transaction when using Perl DBD::SQLite? Scenario: Linux, Perl DBI, AutoCommit => 0, a subroutine with two code blocks (using the blocks to localize variable names). In the first code block a query handle is created by prepare() on a select statement, it is executed() and the block closed. The second code block another query handle is created by prepare for an update statement, and frequently (30% of the time) SQLite/DBI gives a database locked error at this stage. I think the error happens during prepare() and not during the execute().
我的解决方法是在第一个查询之后提交. (第一个查询的调用完成无济于事).由于与优雅和性能有关的几个原因，我不愿意承诺.原始代码在使用Postgres作为数据库的情况下已经运行了很多年.我尝试了sqlite_use_immediate_transaction没有效果.
My work around is to commit after the first query. ( Calling finish on the first query did not help). I prefer not to commit for several reasons relating to elegance and performance. The original code has worked fine for many years with Postgres as the database. I tried sqlite_use_immediate_transaction with no effect.
在所有其他情况下，我发现SQLite的性能很好，因此我怀疑这是DBD驱动程序中的一个疏忽，而不是SQLite的问题.不幸的是，我当前的代码是一大堆脚本和模块，所以我没有一个简短的单个文件测试用例.
In all other situations, I've found SQLite to perform very well, so I suspect this is an oversight in the DBD driver, rather than an issue with SQLite. Sadly, my current code is a big pile of scripts and modules, so I don't have a short, single file test case.
推荐答案
Not related to this in anyway is it: Transaction and Database Locking from the DBD::SQLite perldoc?
通过AutoCommit或begin_work进行的交易非常方便，但是有时您可能会遇到恼人的数据库已锁定"消息.错误.这通常在某人开始事务并尝试在另一人从数据库中读取(在另一事务中)时写入数据库时发生.您可能会感到惊讶，但是当您开始正常(延迟)事务以最大程度地提高并发性时，SQLite不会锁定数据库.当您发出要写的语句时，它会保留一个锁，但是直到您实际尝试使用commit语句进行写时，它才允许其他人从数据库中读取.但是，从数据库中读取还需要共享锁，这会阻止为您提供保留的独占锁，因此您将获得数据库已锁定"的信息.错误，如果其他人后来尝试写，也会收到相同的错误，因为您还有待处理的锁. busy_timeout在这种情况下无济于事.
Transaction by AutoCommit or begin_work is nice and handy, but sometimes you may get an annoying "database is locked" error. This typically happens when someone begins a transaction, and tries to write to a database while other person is reading from the database (in another transaction). You might be surprised but SQLite doesn't lock a database when you just begin a normal (deferred) transaction to maximize concurrency. It reserves a lock when you issue a statement to write, but until you actually try to write with a commit statement, it allows other people to read from the database. However, reading from the database also requires shared lock, and that prevents to give you the exclusive lock you reserved, thus you get the "database is locked" error, and other people will get the same error if they try to write afterwards, as you still have a pending lock. busy_timeout doesn't help in this case.
为避免这种情况，请显式设置事务类型.您可以为每个事务发出立即开始事务(或开始排他事务)，或将sqlite_use_immediate_transaction数据库句柄属性设置为true(从1.30_02开始)，以始终使用立即事务(即使仅使用begin_work或关闭AutoCommit也是如此).
To avoid this, set a transaction type explicitly. You can issue a begin immediate transaction (or begin exclusive transaction) for each transaction, or set sqlite_use_immediate_transaction database handle attribute to true (since 1.30_02) to always use an immediate transaction (even when you simply use begin_work or turn off the AutoCommit.).
my $dbh = DBI->connect("dbi:SQLite::memory:", "", "", {
sqlite_use_immediate_transaction => 1,
});
请注意，这仅在所有连接都使用相同(非延迟)事务时才有效.有关锁定的详细信息，请参见 http://sqlite.org/lockingv3.html .
这篇关于为什么SQLite给出“数据库已锁定"?在使用Perl的DBD :: SQLite时在事务中进行第二次查询?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
