假设我们的帐户表有一个称为余额的列.每笔交易都记录在交易表中.当然,在进行任何交易以出售任何产品之前,我们应该验证是否有足够的资金.因此,出于性能目的,我们应检查用户的余额列并扣除成功销售的金额并更新其余额
如果用户异步购买了2种产品怎么办,这可能会导致欺诈.我编写了一个脚本,该脚本将从一个帐户中扣除资金,并将其克隆到另一个文件中.我同时执行了两个脚本,结果令人惊讶.
Deduct.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;
在运行脚本之前,我的余额为1000000,我已经用不同的$amount值执行了该脚本的两个进程.结果如下:
>是否有其他解决方案可以克服这一问题?我了解,如果记录每笔交易并计算最终余额是准确的但很费力的.
解决方法:
如果这是学校的家庭作业问题,而您只希望脚本能正常工作,请执行以下操作:
$sql2 = $dbh->prepare('UPDATE accounts SET balance = balance - ? WHERE id = ?');
$sql2->execute(array($amount,1));
如果您做的是真实的事情,则应该记录所有的单个交易.为了加快进度,您可以每晚汇总交易并更新帐户余额,然后创建一个包含如下查询的视图以获取当前余额:
create or replace view current_accounts
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
插入每笔交易时,如果您要求余额永远不会为负:
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
绑定时,请确保在提取资金时扣除的金额为负,在帐户中存入资金的金额为正.您需要在transactions.transaction_date和accounts.updated_at上具有索引,以使其具有任何速度优势.
每晚更新应如下所示:
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;
另外,accounts表中的主键应称为account_id,而transactions表中的主键应称为transaction_id.禁止使用约定来命名任何“ id”,因为它最终会使您感到困惑.
标签:sql,mysql,php,pdo
来源: https://codeday.me/bug/20191027/1943862.html