为什么SQLite在使用Perl的DBD :: SQLite时为事务中的第二个查询提供“数据库被锁定”?

前端之家收集整理的这篇文章主要介绍了为什么SQLite在使用Perl的DBD :: SQLite时为事务中的第二个查询提供“数据库被锁定”?前端之家小编觉得挺不错的,现在分享给大家,也给大家做个参考。
当使用Perl DBD :: sqlite时,sqlite在单个事务中为第二个查询提供“数据库已锁定”错误是否存在已知问题?场景:Linux,Perl DBI,AutoCommit => 0,一个带有两个代码块的子程序(使用块来本地化变量名).在第一个代码块中,一个查询句柄由select()在select语句中创建,它被执行()并且块被关闭.第二个代码块是通过准备更新语句创建的另一个查询句柄,并且经常(30%的时间)sqlite / DBI在此阶段给出数据库锁定错误.我认为错误发生在prepare()期间而不是在execute()期间.

我的工作是在第一次查询后提交. (在第一个查询调用完成没有帮助).我不喜欢有几个与优雅和表现有关的理由. Postgres作为数据库,原始代码已经运行了很多年.我试过sqlite_use_immediate_transaction没有效果.

在所有其他情况下,我发现sqlite表现得非常好,所以我怀疑这是DBD驱动程序中的疏忽,而不是sqlite的问题.遗憾的是,我目前的代码是一大堆脚本和模块,所以我没有一个简短的单个文件测试用例.

无论如何与此无关: Transaction and Database Locking来自 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.

原文链接:https://www.f2er.com/sqlite/197862.html

猜你在找的Sqlite相关文章