I am running an SMS sending website. When a user sends a message, 1 credit needs to be deducted from their account.
However, when a user submits requests rapidly, the account is not decremented correctly. For example, user starts off with 10 credits, submits 3 requests rapidly but the database stores the balance as 9 credits instead of 7!
Here's how I update the database:
$sql5="update sms set sms='$remaining_sms' WHERE username='$session_username'";
$result5=mysql_query($sql5);
You've got several requests all racing to put what they think is the correct value into the database. So, two requests arrive at the same time, they both read the credit level as '10', send a message, then both write '9' as the new credit level.
Instead, have the database decrement it for you
$sql5="update sms set sms=sms-1 WHERE username='$session_username'";
$result5=mysqli_query($link, $sql5);
This will maintain the correct credit level, but under heavy load this could go negative, so a user can send a few more messages than they have credit for!
To guard against that, you need a query which can both decrement the account and tell whether they had any credit. Something as simple as this would do the trick:
UPDATE sms SET sms=sms-1 WHERE sms>0 AND username=?
Then check that a row was updated using mysqli_affected_rows or similar technique - if no row was updated, then they are out of credit. If it was, then they had credit and its been successfully deducted.
For more techniques on avoiding these sorts of race conditions, see this question
Finally, please note that using the mysql_ PHP functions is deprecated
