This issue was raised from the current blog post on transactions

PHP Code:
<?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?