我的工作是在第一次查询后提交. (在第一个查询上调用完成没有帮助).我不喜欢有几个与优雅和表现有关的理由. Postgres作为数据库,原始代码已经运行了很多年.我试过sqlite_use_immediate_transaction没有效果.
在所有其他情况下,我发现sqlite表现得非常好,所以我怀疑这是DBD驱动程序中的疏忽,而不是sqlite的问题.遗憾的是,我目前的代码是一大堆脚本和模块,所以我没有一个简短的单个文件测试用例.
DBD::SQLite perldoc？
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.
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,});
Note that this works only when all of the connections use the same (non-deferred) transaction. See 07002 for locking details.
