SitePoint Sponsor

User Tag List

Results 1 to 8 of 8
  1. #1
    SitePoint Wizard silver trophybronze trophy Stormrider's Avatar
    Join Date
    Sep 2006
    Location
    Nottingham, UK
    Posts
    3,133
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Transactions Question

    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?

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    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?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Wizard silver trophybronze trophy Stormrider's Avatar
    Join Date
    Sep 2006
    Location
    Nottingham, UK
    Posts
    3,133
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Definitely

    Code:
    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. #4
    SitePoint Wizard silver trophybronze trophy Stormrider's Avatar
    Join Date
    Sep 2006
    Location
    Nottingham, UK
    Posts
    3,133
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Anyone have any ideas on where the error is here?

  5. #5
    SitePoint Wizard TheRedDevil's Avatar
    Join Date
    Sep 2004
    Location
    Norway
    Posts
    1,196
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Stormrider View Post
    Anyone have any ideas on where the error is here?
    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. #6
    SitePoint Wizard silver trophybronze trophy Stormrider's Avatar
    Join Date
    Sep 2006
    Location
    Nottingham, UK
    Posts
    3,133
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    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. #7
    SitePoint Addict
    Join Date
    Apr 2001
    Location
    Devon, UK
    Posts
    333
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    very well spotted
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •