Tracking Unexplored MySQL Errors in Perl

Question

I have a single threaded Perl script running on a hosted shared server that basically runs 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"; } 

Several times (<1%) the code is run in case of rollback and does not work. 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 this error? Any help would be greatly appreciated.

Answer 1

Assuming your database is running with default settings, I am more surprised that your SELECT will ever return 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 first snapshot, such as a read in that transaction. You can get a more recent snapshot for your requests by completing the current transaction and then issuing new requests.

So, if the default isolation level REPEATABLE READ is valid, I expect all queries to return data that matches the state of the database at the time of the first query.

However, it seems that this may help.

With READ COMMITTED isolation level, each sequential 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 connecting, and see if this fixes something for you.

However, after this transaction, you must make sure that you either disconnect the database handler or return it to the previous isolation level. Otherwise, you will start to get conflicting results.