Transactions Question

Databases
1

This issue was raised from the current blog post on transactions

<?php
  $dbConn = new mysqli("localhost", "test", "test", "test");

  $dbConn->query("START TRANSACTION;");

  $dbConn->query("UPDATE accounts SET amount = amount – 10 WHERE account_holder = 1;");

  $dbResults = $dbConn->query("SELECT amount FROM accounts WHERE account_holder = 1;");

  $arrResult = $dbResults->fetch_assoc();

  if ($arrResult['amount'] < 0) {
    $dbConn->query("ROLLBACK;");
  } else {
    $dbConn->query("UPDATE accounts SET amount = amount + 10 WHERE account_holder = 2;");
    $dbConn->query("COMMIT;");
  }//if
?>

This code simulates a hypothetical transfer from account 1 to account 2.

Why does this not work as expected? I would expect it to take 10 off the value for account 1, check if the balance is negative, if it is then roll back the transaction, if not then add the money to account 2 and commit.

What actually happens in this script (with an appropriate test database set up) is that account 2’s value increases by 10, but account 1’s value stays the same. Surely this is completely against the whole reason for using transactions?

Can you SELECT in the middle of a transaction? If not, how would you achieve this without data integrity issues?

2

at first i thought it might be due to autocommit which is on by default

but da manual says you can leave it on if you use START TRANSACTION with either COMMIT or ROLLBACK

are you sure you were testing on innodb tables?

3

Definitely

CREATE TABLE IF NOT EXISTS `accounts` (
  `account_holder` int(11) NOT NULL,
  `amount` int(11) NOT NULL,
  PRIMARY KEY (`account_holder`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

is the table I was using to test on

4

Anyone have any ideas on where the error is here?

5

I found this issue intriguing so I took a look and tested it locally.

Your problem is that the minus (-) in the first query, is not an actual minus sign. But some other character looking the same.

I removed the minus in your code and entered a new one and the code worked at once.

6

Ah ok, interesting. I think it’s because I originally posted the code as a comment on a blog post on sitepoint, then copied and pasted into a php file and edited to make it work when the code I pasted got corrupted. Sitepoint must have converted it into a different type of hyphen.

Well spotted!

7

Well spotted TheRedDevil! I’d been a little concerned and was about to test it myself.

The SitePoint blogs are powered by WordPress which often converts characters to more readable alternatives. It’s a nasty bug to find though!

8

very well spotted :award: