跟踪Perl中的非确定性MySQL错误

发布时间：2020-12-16 06:15:32 所属栏目：大数据来源：网络整理

导读：我有一个在托管共享服务器上运行的单线程Perl脚本,主要执行以下代码： my $O_dbh = DBI-connect("dbi:mysql:dbname=dbname","abc","xxx",{RaiseError = 1});$O_dbh-begin_work();my $O_sth1 = $O_dbh-prepare('SELECT COUNT(*) FROM mytable WHERE any = 5')

我有一个在托管共享服务器上运行的单线程Perl脚本,主要执行以下代码：

my $O_dbh = DBI->connect("dbi:mysql:dbname=dbname","abc","xxx",{RaiseError => 1});
$O_dbh->begin_work();

my $O_sth1 = $O_dbh->prepare('SELECT COUNT(*) FROM mytable WHERE any = 5');

$O_sth1->execute();
my @result1 = $O_sth1->fetchrow_array();
my $oldValue = $result1[0];

$O_sth1->finish();

my $O_sth2 = $O_dbh->prepare('INSERT INTO mytable (any) VALUES (5)');
$O_sth2->execute();

$O_sth1->execute();
my @result2 = $O_sth1->fetchrow_array();
my $newValue = $result2[0];

if ($oldValue + 1 == $newValue) {
  $O_dbh->commit();
} 
else {
  $O_dbh->rollback();
  die "why?! new = $newValue,old = $oldValue";
}

有时(<1％)代码进入回滚情况并失败.在我的本地系统上,我无法重现此错误.数据库是MySQL 5.

CREATE TABLE `mytable` (
  `id` int(11) NOT NULL auto_increment,`any` int(11) NOT NULL default '1',PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

如何追踪此错误？任何帮助将不胜感激.

解决方法

假设您的数据库使用默认设置运行,我会更惊讶您的SELECT返回两个不同的值.

The documentation说这个

If the transaction isolation level is REPEATABLE READ (the default level),all consistent reads within the same transaction read the snapshot established by the first such read in that transaction. You can get a fresher snapshot for your queries by committing the current transaction and after that issuing new queries.

因此,如果默认的REPEATABLE READ隔离级别生效,我希望所有查询都会返回与第一次查询时数据库状态一致的数据.

但是,听起来这听起来可能有所帮助

With READ COMMITTED isolation level,each consistent read within a transaction sets and reads its own fresh snapshot.

我想你应该试试

$O_dbh->do('SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED');

在连接之后立即查看是否能为您修复问题.

但是,您应确保在此事务之后断开数据库句柄或将其返回到先前的隔离级别.否则你将开始得到不一致的结果.

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!