PHP/MySQL-如何防止两个请求*更新 [英] PHP / MySQL - how to prevent two requests *Update

问题描述

我有一个问题... 例如:用户将用他的美元买东西

I have some question ... example: a user will buy something for his USD

1. 检查他的美元余额
2. 从他的帐户中扣除美元
3. 下订单->订单队列
4. 用户获取他的商品，而另一个获取他的美元

可以说，用户在同一秒内发出5个请求(非常快). 因此有可能(并且发生)5个请求正在运行. 他只有钱，只能从1个请求中购买.现在的要求 速度如此之快，以至于脚本检查了他的余额，但又不是那么快，以至于扣除了 从他的帐户中赚钱.因此，请求将通过两次！ 该怎么解决?

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?

在启动进程之前，我在mysql中使用LOCK:

I use LOCK in mysql before I start the process:

1. IS_FREE_LOCK-检查该用户是否有锁-> 2.
2. GET_LOCK-设置锁定
3. 进行订单/交易
4. 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));
}

更新 使用SELECT ... FOR UPDATE测试了交易功能

Update Tested the transaction function with 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();

推荐答案

首先，您必须使用事务，但这还不够.在交易中，您可以使用 SELECT FOR UPDATE .

First off, you have to use transactions, but that's not enough. In your transaction, you can use SELECT FOR UPDATE.

基本上是说，我要更新选择的记录" ，因此它设置的锁与UPDATE设置的锁相同.但是请记住，这必须在自动提交关闭的事务中发生.

It's basically saying, "I'm going to update the records I'm selecting", so it's setting the same locks that an UPDATE would set. But remember this has to happen inside a transaction with autocommit turned off.

这篇关于PHP/MySQL-如何防止两个请求*更新的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！