PHP / MySQL - 如何防止两个请求 *Update
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15026825/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
PHP / MySQL - how to prevent two requests *Update
提问by DjangoSi
I have some question ... example: a user will buy something for his USD
我有一些问题......例如：用户会用他的美元买东西
- Check his USD Balance
- Deduct the USD from his account
- Make an Order -> order queue
- user gets his item and the other one gets his USD
- 检查他的美元余额
- 从他的账户中扣除美元
- 下订单 -> 订单队列
- 用户得到他的物品，另一个得到他的美元
Lets say, the users makes 5 requests in the same second (very fast). So it is possible (and happen) that 5 requests are running. He has only money to buy only from 1 request. Now the requests are so fast, that the script checks his balance, but is not so fast, that it deduct the money from his account. So the requests will pass two times! How to solve it?
假设用户在同一秒内发出 5 个请求（非常快）。所以有可能（并且发生）有 5 个请求正在运行。他只有从 1 个请求购买的钱。现在请求如此之快，脚本会检查他的余额，但并没有那么快，以至于从他的帐户中扣除了钱。所以请求将通过两次！如何解决？
I use LOCK in mysql before I start the process:
在我开始这个过程之前，我在 mysql 中使用了 LOCK：
- IS_FREE_LOCK - check is there a lock for this user if not -> 2.
- GET_LOCK - sets the lock
- make the order / transaction
- RELEASE_LOCK - releases the lock
- IS_FREE_LOCK - 如果没有，请检查此用户是否有锁 -> 2。
- GET_LOCK - 设置锁
- 下单/交易
- RELEASE_LOCK - 释放锁
But this does not really work. Is there another way?
但这并没有真正起作用。还有其他方法吗？
function lock($id) {
mysql_query("SELECT GET_LOCK('$id', 60) AS 'GetLock'");
}
function is_free($id) {
$query = mysql_query("SELECT IS_FREE_LOCK('$id') AS 'free'");
$row = mysql_fetch_assoc($query);
if($row['free']) {
return true;
} else {
return false;
}
}
function release_lock($id) {
mysql_query("SELECT RELEASE_LOCK('$id')");
}
function account_balance($id) {
$stmt = $db->prepare("SELECT USD FROM bitcoin_user_n WHERE id = ?");
$stmt->execute(array($id));
$row = $stmt->fetch(PDO::FETCH_ASSOC);
return $row['USD'];
}
if(is_free(get_user_id())) {
lock(get_user_id());
if(account_balance(get_user_id()) < str2num($_POST['amount'])) {
echo "error, not enough money";
} else {
$stmt = $db->prepare("UPDATE user SET USD = USD - ? WHERE id = ?");
$stmt->execute(array(str2num($_POST['amount']), get_user_id()));
$stmt = $db->prepare("INSERT INTO offer (user_id, type, price, amount) VALUES (?, ?, ?, ?)");
$stmt->execute(array(get_user_id(), 2, str2num($_POST['amount']), 0));
}
UpdateTested the transaction function with SELECT ... FOR UPDATE
更新使用 SELECT ... FOR UPDATE 测试事务功能
$db->beginTransaction();
$stmt = $db->prepare("SELECT value, id2 FROM test WHERE id = ? FOR UPDATE");
$stmt->execute(array(1));
$row = $stmt->fetch(PDO::FETCH_ASSOC);
if($row['value'] > 1) {
sleep(5);
$stmt = $db->prepare('UPDATE test SET value = value - 5 WHERE id = 1');
$stmt->execute();
$stmt = $db->prepare('UPDATE test SET value = value + 5 WHERE id = 2');
$stmt->execute();
echo "did have enough money";
} else {
echo "no money";
}
$db->commit();
回答by
First off, you have to use transactions, but that's not enough. In your transaction, you can use SELECT FOR UPDATE.
首先，您必须使用事务，但这还不够。在您的交易中，您可以使用SELECT FOR UPDATE.
It's basically saying, "I'm going to update the records I'm selecting", so it's setting the same locks that an UPDATEwould set. But remember this has to happen inside a transaction with autocommit turned off.
它基本上是在说，“我要更新我选择的记录”，所以它设置了与 an 设置的相同的锁UPDATE。但请记住，这必须发生在关闭自动提交的事务中。
回答by Oden
Use TRANSACTIONand if it fails you can rollback.
使用TRANSACTION，如果失败，您可以回滚。
For example, assume the current balance is $20.
例如，假设当前余额为 20 美元。
Connection A Connection B
======================= ===========================
BEGIN TRANSACTION
BEGIN TRANSACTION
SELECT AccountBalance
SELECT AccountBalance
--returns
--sufficient balance,
--proceed with purchase
--returns
--sufficient balance,
--proceed with purchase
--update acquires exclusive lock
UPDATE SET AccountBalance
= AccountBalance - 20
--update blocked due
UPDATE SET AccountBalance
= AccountBalance - 20
--order complete
COMMIT TRANSACTION
--update proceeds
--database triggers
--constraint violation
--"AccountBalance >= 0"
ROLLBACK TRANSACTION
回答by Kols
This is how I used to do it many years ago..
这是我多年前的做法。。
results = query("UPDATE table SET value=value-5 WHERE value>=5 AND ID=1")
if (results == 1) YEY!
(Is this still a reliable method?)
（这仍然是一种可靠的方法吗？）
回答by Smoky McPot
you need to use TRANSACTION at the SERIALIZABLE isolation level.
您需要在 SERIALIZABLE 隔离级别使用 TRANSACTION。
回答by Ego Slayer
You need to use Data revision for MySQL UPDATE.
您需要为 MySQL UPDATE 使用数据修订版。
