Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 2 of 2
08-24-2013, 08:09 PM #1
- 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.
08-24-2013, 09:32 PM #2
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 doand there was only 1 item on hand before both updates, you will end up with a quantityOnHand of -1 !! ** OOPS ** No good!Code:UPDATE inventory SET quantityOnHand = quantityOnHand - 1 WHERE itemid = 7788
But if you are just a tiny bit more clever, you will write:Or, more generally in (say) PHP code:Code:UPDATE inventory SET quantityOnHand = quantityOnHand - 1 WHERE quantityOnHand >= 1 AND itemid = 7788And 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.Code:$sql = "UPDATE inventory SET quantityOnHand = quantityOnHand - $quantityPurchased WHERE quantityOnHand >= $quantityPurchased AND itemid = 7788";
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.