启用事务时Perl DBI SQLite多数据库句柄遇数据库锁定错误

问题背景 #

基于Perl DBI模块和SQLite驱动的应用，需将主库（pipeline.sdb）的部分数据归档至归档库（archive.sdb）。启用归档库的事务后，数据迁移流程触发database is locked错误；禁用事务则脚本可正常执行，且两个数据库的数据状态符合预期（frames 1-9存入archive.sdb，frames 10-20保留在pipeline.sdb）。

最小复现代码 #

use strict;
use warnings;
use DBI;

unlink foreach glob('*.sdb');

#create the table in the pipeline db
my $dbname_pipe = 'pipeline.sdb';
my $dbh_pipe = DBI->connect("dbi:SQLite:dbname=$dbname_pipe","","",{RaiseError=>1,AutoCommit=>1}) or die $DBI::errstr;
my $sth_pipe = $dbh_pipe->prepare('CREATE TABLE files (filename varchar(128) NOT NULL, frame int, PRIMARY KEY(filename))') ;
$sth_pipe->execute();

#add some data to the pipeline table
$sth_pipe = $dbh_pipe->prepare('INSERT INTO files (filename, frame) VALUES (?, ?)');
$sth_pipe->execute(sprintf("file_%04d.png",$_),$_) foreach (1..20);

#create the table in the archive db
my $dbname_arch = 'archive.sdb';
my $dbh_arch = DBI->connect("dbi:SQLite:dbname=$dbname_arch","","",{RaiseError=>1,AutoCommit=>1}) or die $DBI::errstr;
my $sth_arch = $dbh_arch->prepare('CREATE TABLE files (filename varchar(128) NOT NULL, frame int, PRIMARY KEY(filename))') ;
$sth_arch->execute();

#move some entries from the pipeline db to the archive db
$dbh_arch->do(qq{ATTACH DATABASE "$dbname_pipe" AS pipeline});

$dbh_pipe->begin_work;
$dbh_arch->begin_work;
eval {
    $sth_arch = $dbh_arch->prepare('INSERT INTO files(filename, frame) SELECT filename, frame FROM pipeline.files WHERE frame < ?');
    $sth_pipe = $dbh_pipe->prepare('DELETE FROM files WHERE frame < ?');
    $sth_arch->execute(10);
    $sth_pipe->execute(10);
    $dbh_arch->commit;
    $dbh_pipe->commit;
};
if ($@) {
    warn "archiving transaction aborted because of $@";
    eval { $dbh_pipe->rollback };
    eval { $dbh_arch->rollback };
}

$dbh_arch->do(qq{DETACH DATABASE pipeline});
#disconnect
$dbh_arch->disconnect;
$dbh_pipe->disconnect;

#done
1;

报错信息 #

DBD::SQLite::st execute failed: database is locked at dbi_lock_mwe.pl line 32. archiving transaction aborted because of DBD::SQLite::st execute failed: database is locked at dbi_lock_mwe.pl line 32.

解决方案 #

问题根源是同时通过两个独立数据库句柄对同一个SQLite数据库（pipeline.sdb）开启事务：一个是直接连接的$dbh_pipe，另一个是通过归档库$dbh_arch附加（ATTACH）后的引用。SQLite的文件级锁机制无法处理这种冲突，导致锁等待超时触发错误。

推荐采用以下简洁方案解决：

仅使用归档库句柄操作两个数据库 #

既然已经通过$dbh_arch附加了pipeline库，无需再保留$dbh_pipe的事务，直接用$dbh_arch执行两个库的操作，并仅维护一个事务，确保所有操作在同一事务上下文内完成，避免锁冲突。

修改后的核心代码如下：

#move some entries from the pipeline db to the archive db
$dbh_arch->do(qq{ATTACH DATABASE "$dbname_pipe" AS pipeline});

# 仅开启归档库的事务，覆盖两个附加数据库
$dbh_arch->begin_work;
eval {
    # 插入归档库数据
    $sth_arch = $dbh_arch->prepare('INSERT INTO files(filename, frame) SELECT filename, frame FROM pipeline.files WHERE frame < ?');
    # 通过附加库引用删除pipeline的数据
    $sth_arch = $dbh_arch->prepare('DELETE FROM pipeline.files WHERE frame < ?');
    
    $sth_arch->execute(10); # 执行归档插入
    $sth_arch->execute(10); # 执行主库删除
    
    $dbh_arch->commit;
};
if ($@) {
    warn "archiving transaction aborted because of $@";
    eval { $dbh_arch->rollback };
}

$dbh_arch->do(qq{DETACH DATABASE pipeline});
#disconnect
$dbh_arch->disconnect;
$dbh_pipe->disconnect;

方案优势 #

• 确保操作原子性：归档插入和主库删除要么全部成功，要么全部回滚，符合事务要求。
• 避免锁冲突：所有操作通过同一个数据库句柄执行，不存在跨句柄的锁竞争。
• 代码更简洁，减少冗余的事务管理逻辑。

验证结果 #

修改后执行脚本，既能保留事务的原子性保障，又不会触发数据库锁定错误，数据状态完全符合预期：frames 1-9存入archive.sdb，frames 10-20保留在pipeline.sdb。

内容的提问来源于stack exchange，提问作者Derek