Supposing our accounts
table has a column called balance
, each transaction is logged in the transactions
table. Of course, we should verify there is sufficient funds prior to doing any transactions to sell products. Therefore, for performance purposes we should check the balance
column of the user, deduct the amount on a successful sale, and update his balance
.
However, if the user asynchronously bought 2 products, could not that potentially cause fraud? I’ve wrote a script that will deduct funds from an account and cloned it to another file. I executed both scripts at the same time and the results were surprising.
Deduct.php
<?php //database connection... $amount = 11; $deducted = 0; $blocked = 0; for($i = 0; $i < 5000; $i++){ $sql = $dbh->prepare('SELECT balance FROM accounts WHERE id = ?'); $sql->execute(array(1)); while($u = $sql->fetch()){ $balance = $u['balance']; $deduct = $balance - $amount; if($deduct >= 0){ $sql2 = $dbh->prepare('UPDATE accounts SET balance = ? WHERE id = ?'); $sql2->execute(array($deduct,1)); echo $balance . ' -> ' . $deduct . "n"; $deducted += $amount; } else { $blocked++; } } } echo 'Deducted: '.$deducted. "n"; echo 'Blocked: '.$blocked;
Before running the scripts my balance
was 1000000. I’ve executed two processes of this script with different $amount
values.
Here are the results:
- Any alternative solutions to overcome this? I understand that logging each transaction and calculating the final balance is precise but is also intensive.
Advertisement
Answer
If this is a school homework problem and you just want your script to work, do this:
$sql2 = $dbh->prepare('UPDATE accounts SET balance = balance - ? WHERE id = ?'); $sql2->execute(array($amount,1));
If you are doing anything real, you should be recording all the individual transactions. To speed things up, you can sum the transactions each night and update the account balance, then create a view which includes a query like this to get the current balance:
create or replace view current_accounts as select a.id as account_id , max(a.balance) + ifnull(sum(t.amount), 0) as current_balance from accounts a left join transactions t on t.account_id = a.id and t.transaction_at > a.updated_at group by a.id
When inserting each transaction, if you require that the balance never goes negative:
insert into transactions (transaction_at, amount, account_id) select now(), ?, v.account_id from current_accounts v where v.account_id = ? and v.current_balance + ? >= 0
When binding this, make sure that the amount being deducted is negative if you are withdrawing money and positive if you are depositing money into the account. You will need an index on transactions.transaction_date and accounts.updated_at for this to have any speed advantage.
The nightly update should look like:
drop table accounts_old if exists; create table accounts_new as select t.account_id as id , sum(t.amount) as balance , max(t.transaction_at) as updated_at from transactions t group by t.account_id; rename table accounts to accounts_old; rename table accounts_new to accounts;
Also, the primary key in the accounts table should be called account_id and you should have a primary key in the transactions table called transaction_id. Resist the convention to name anything “id” because it will confuse you eventually.