时间戳

首页 > 解决方案 > 当 AutoCommit 设置为 false 时，perl DBI SQLite 提交或分离失败

sqlite - 当 AutoCommit 设置为 false 时，perl DBI SQLite 提交或分离失败

问题描述

以下代码在运行 Strawberry Perl 5.24 的 Windows 10 机器上失败：

use DBI;

unlink glob("*.db3");
my $source = DBI->connect("dbi:SQLite:dbname=first.db3",q(),q(),{AutoCommit => 0, RaiseError =>1});

# populate source DB
$source->do("CREATE TABLE test(x integer)");
$source->do("INSERT INTO test(x) values (1)");
$source->commit();
$source->disconnect();

# copy source to dest
my $dest = DBI->connect("dbi:SQLite:dbname=second.db3",q(),q(),{AutoCommit => 0, RaiseError =>1});

$dest->do("CREATE TABLE test(x integer)");
$dest->do("ATTACH DATABASE 'first.db3' AS chunk_db");
$dest->do("INSERT INTO test(x) SELECT x FROM chunk_db.test");

# this statement will fail when AutoCommit => 0
$dest->commit;
$dest->do("DETACH DATABASE chunk_db");
$dest->disconnect();

为了使它工作，我必须AutoCommit => 1在创建$dest对象时连接。否则我会收到以下错误消息：

   DBD::SQLite::db do failed: database chunk_db is locked at test.pl line 21.

这是 DBI 中的错误还是我做错了什么？

标签： sqliteperl

解决方案

根据文档，AutoCommit关闭模式时：

The default transaction behavior of SQLite is deferred, that means, locks are not acquired until the first read or write operation, and thus it is possible that another thread or process could create a separate transaction and write to the database after the BEGIN on the current thread has executed, and eventually cause a "deadlock". To avoid this, DBD::SQLite internally issues a BEGIN IMMEDIATE if you begin a transaction by calling begin_work or by turning off AutoCommit (since 1.38_01).

If you really need to turn off this feature for some reasons, set sqlite_use_immediate_transaction database handle attribute to false, and the default deferred transaction will be used.

(This seems like undesirable behavior, and maybe it's because I'm tired but I don't see how you can get deadlocks; just errors when trying to lock a database that's already locked by another connection to it)

But anyways:

$ sqlite3 second.db3
sqlite> attach database 'first.db3' as chunk_db;
sqlite> begin immediate;
sqlite> detach database chunk_db;
Error: database chunk_db is locked

That looks familiar...

The default behavior when AutoCommit is off means you're always in a transaction with an acquired RESERVED lock. This has some unusual side-effects as you're seeing.

So, solutions in my order of preference:

  • Turn on AutoCommit mode and manually begin transactions (With $dbh->begin_work).
  • Skip the DETACH since you're closing the database connection anyways.
  • Leave AutoCommit disabled and set the sqlite_use_immediate_transaction option to 0 when connecting to the database file.

推荐阅读