在Perl中跟踪不确定的MySQL错误 [英] Track non-deterministic MySQL errors in Perl

问题描述

我在托管的共享服务器上运行一个单线程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 ever 返回两个不同的值./p>

文档这样说

如果事务隔离级别为REPEATABLE READ(默认级别)，则同一事务中的所有一致读取将读取该事务中第一个此类读取所建立的快照.通过提交当前事务并在此之后发出新查询，可以为查询获取更新鲜的快照.

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

但是，听起来确实有帮助

使用READ COMMITTED隔离级别，事务中的每个一致读取都会设置并读取其自己的新快照.

我认为您应该尝试

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

连接后立即进行，看看是否能为您解决问题.

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

I have a single-thread Perl script running on a hosted shared server that mainly executes the following code:

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";
}

Some times (<1%) the code runs into the rollback case and fails. On my local system I cannot reproduce this error. The database is 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;

How can I track down this error? Any help would be greatly appreciated.

解决方案

Assuming your database is running with the default settings, I am more surprised that your SELECT ever returns two different values.

The documentation says this

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.

So, if the default REPEATABLE READ isolation level is in effect, I would expect that all queries would return data consistent with the state of the database at the moment of the first query.

However, it does sound like this may help

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

I think you should try

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

immediately after the connect, and see if that fixes things for you.

However, you should make sure to either disconnect the database handle after this transaction or return it to the previous isolation level. Otherwise you will start to get inconsistent results.

这篇关于在Perl中跟踪不确定的MySQL错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！