Track non-deterministic MySQL errors in Perl

Question

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.

Why are you calling `finish` on your first statement handle? The [DBI docs](https://metacpan.org/module/TIMB/DBI-1.627/DBI.pm#finish) seem to suggest that you shouldn't. — innaM, Jun 22 '13 at 09:08
Have you tried to debug this? I would like to know the results of some experiments, such as: Does removing the `finish` call make any difference? Does it change anything if you put a `sleep 1` between the two `execute` calls? Do you get the expected result if you `execute` the `SELECT` a second time when it fails. Does this problem go away if you remove the `begin_work`? What is the effect of removing the `WHERE` clause on the `SELECT`? What *is* `$newvalue` if it isn't `$oldvalue + 1`? — Borodin, Jun 22 '13 at 11:38
@innM: Taking away the finish call, has no functional effect so far. I will observe this in the preproduction system. Unfortunately this will take some time. (The error above occured three times in the last month...) — Meise2000, Jun 22 '13 at 18:16
@Borodin: I guess retrying the transaction is worth thinking about. But only as a workaround before running in production. I would prefere to understand what happens and then draw a conclusion — Meise2000, Jun 22 '13 at 18:18

score 3 · Answer 1 · answered Jun 22 '13 at 12:08

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.

I am using the default isolation level (REPEATABLE READ). The documentation conceals, that the selected data is read from the first snapshot AND the called modifications (INSERT/UPDATE) of the current transaction. SET AUTOCOMMIT = 0; SELECT COUNT(*) FROM mytable; INSERT INTO mytable (any) VALUES (5); SELECT COUNT(*) FROM mytable; — Meise2000, Jun 22 '13 at 18:12

Track non-deterministic MySQL errors in Perl

1 Answers1

Linked