Hello and welcome to our community! Is this your first visit?
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 2 of 2
  1. #1
    Regular Coder
    Join Date
    Mar 2005
    Spokane, WA
    Thanked 4 Times in 4 Posts

    Preventing two PHP scripts from working on the same record

    I realize its not likely but is two instances of the script run an update query at or near enough to the same time it is possible for them to update the same record, invalidated the change made by the first instance to update it. Does either PHP or MySQL have a way to guard against this?
    Last edited by RyanB88; 08-24-2013 at 08:52 PM.

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Thanked 4,323 Times in 4,289 Posts
    Short answer: Yes.

    Longer answer: You quite possibly don't need to worry about it.

    An UPDATE query runs as a single unit, so two people can't really change the same record at the same time. One *will* complete before the next one can take place.

    Now... If your query is too simple, it *is* quite possible that the second one will overwrite important changes made by the first one.

    Let's say, for example, that you have two UPDATEs, each of which represents the purchase of one item, and so you want to end up subtracting 2 from the quantity in inventory. If you naively do
    UPDATE inventory 
    SET quantityOnHand = quantityOnHand - 1 
    WHERE itemid = 7788
    and there was only 1 item on hand before both updates, you will end up with a quantityOnHand of -1 !! ** OOPS ** No good!

    But if you are just a tiny bit more clever, you will write:
    UPDATE inventory 
    SET quantityOnHand = quantityOnHand - 1 
    WHERE quantityOnHand >= 1 
    AND itemid = 7788
    Or, more generally in (say) PHP code:
    $sql = "UPDATE inventory 
            SET quantityOnHand = quantityOnHand - $quantityPurchased 
            WHERE quantityOnHand >= $quantityPurchased 
            AND itemid = 7788";
    And then, after doing the update, check how many records were updated. By using mysqi_affected_rows(), for example. If the number of affected rows was zero, you know your UPDATE did not succeed, presumably because there were not enough items left in inventory.

    And so on.


    But granted, there are some circumstances where you have to perform multiple steps in MySQL to achieve a final result. And so you want all the steps to succeed or you want to not do any of them. And for that, PHP supports this:

    Your code still needs to test to make sure a given INSERT or UPDATE or DELETE succeeded. But now, if one or more does not, you can call rollback() and all of the operations since the last commit() will indeed be wiped out.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.


    Posting Permissions

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