3

I have an issue where I'm running a web service which is performing a transaction involving two tables Users and Transactions. The trouble is that when I select from Transactions it sometimes is unable to find the latest row, which I'm able to see exists in the database.

I'm using Perl/Dancer as the web framework, although I think the issue I'm having is at the database level (I am using MySQL/InnoDB). The pseudocode looks like this:

my $dbh = DBI->connect_cached(("DBI:mysql:Database;host=localhost;mysql_ssl=1",
                 "user", "password",
                 {RaiseError => 0, AutoCommit => 0});
my $sth_transact = $dbh->prepare("select balance from Users ".
                  "where id=? for update");

... store result in local variable ...

my $sth_inner = $dbh->prepare("select deposit from Transactions ".
                   "where id=?");

$sth_inner->execute();
if (my $deposit = $sth_inner->fetch_row()) {
     $deposit *= $bonus;

     $sth_inner = $dbh->prepare("update Transactions set deposit=?".
                                "where id=?");
     $sth_inner->bind_param(1, $deposit);
     $sth_inner->bind_param(2, $transaction_id);
     $sth_inner->execute();

     ... update balance in Users table ...
}

On the first few requests the select on the Transactions table returns the most recent row. However, on the third request, it can no longer find the most recent row. And when I do:

my $sth_inner = $dbh->prepare("select id from Transactions where id=(SELECT max(id) from Transactions)");

It returns the id of 3 rows older than the most recent row. E.g., if there were 87 rows in the Transactions table, it would return 84.

I'm not sure if I'm handling the locking incorrectly. I'm protecting the Users table with a select ... for update lock, but I'm not doing that for the Transactions table. I'm not sure if that's important. Since I nested the update to the Transactions table in the select ... for update of the Users table I thought it would be protected in the transaction.

Any help on why the select on the Transactions table isn't returning the most recent rows is appreciated.

Vijay Boyapati
  • 7,632
  • 7
  • 31
  • 48
  • Can you show the code you've omitted? – ThisSuitIsBlackNot Mar 18 '14 at 21:59
  • It appears if I just add a "... for update" to the "select deposit from Transactions" query it solves the problem. Although I'm not sure I understand why that's the case, and why I need the lock on both tables. – Vijay Boyapati Mar 18 '14 at 21:59
  • Added some of the omitted code. The main problem is it never enters the if statement because the row for the given id does not appear available to it (even though it's in the database). – Vijay Boyapati Mar 18 '14 at 22:12

1 Answers1

3

This problem is usually caused by the default isolation level for InnoDB, which is "repeatable read." This means a connection can't see anything added to the database after the current transaction was started.

You can avoid this problem by changing the isolation level (possibly to "read committed" which is the default for Oracle) or by issuing a commit just before your select to begin a new transaction.

Perrin Harkins
  • 221
  • 2
  • 4
  • Intriguing. So in Perl: $dbh->do("SET TRANSACTION ISOLATION LEVEL READ COMMITTED"); – Vijay Boyapati Mar 19 '14 at 22:56
  • Here is a relevant SO question: http://stackoverflow.com/questions/17248553/track-non-deterministic-mysql-errors-in-perl Thank you for bringing this to my attention! – Vijay Boyapati Mar 19 '14 at 22:58