If I have a script that decreases a users balance in a double entry book keeping system, and a malicious user decides to execute this script on his account on two different machines (or the same machine) at EXACTLY the same time, the whole thing would run twice right? SO heres my over simplified hypothetical scenario.
$balance = $user->ledger->getBalance(); // returns 5000
$amount = 3000;
if ($amount <= $balance) {
$user->ledger->decrease($amount);
}
echo $user->ledger->getBalance(); // echo's 2000
If the script is run one after the other, the second execution will fail because there is only 2000 left in the account and it tried to decrease by 3000.
If the scripts are run simultaneously at exactly the same time, won't both balances be 5000, and both script executions deduct 3000, leaving a negative value in the ledger?
How would you prevent such a thing happening? It is vitally important to maintain the data integrity in this database table.
You're talking about race conditions and they're extremely important to eliminate in financial code.
Anything that follows a get/test/set pattern is going to have huge problems. You can't do that.
Instead take the set/test/fail pattern. Try and deduct using an atomic SQL statement such as a single operation or a transaction block. If that pushes the balance negative roll it back.
For example, this is bad:
balance = query("SELECT balance FROM accounts WHERE account_id=?")
balance -= amount
balance = query("UPDATE accounts SET balance=?")
Anything could happen between the fetch and write.
Instead you could do this where this query either succeeds or fails, it can't be interrupted:
query("UPDATE accounts SET balance=balance-? WHERE account_id=? AND balance>?")
That query won't run unless there's enough balance remaining. You'll get zero rows modified as a result.
You can also do this with double-ledger style book-keeping by attempting to insert the required accounting transaction rows, then check the SUM() to ensure that a zero or positive balance results for the originating account. If it doesn't, abandon the transaction with a ROLLBACK. No changes are applied.
There's a lot of ways of structuring those INSERT statements to make it impossible for a negative balance to occur, like INSERT INTO x SELECT ... FROM y where you can apply conditions to the subquery to return zero rows in the case of an insufficient balance.