PHP/MySQL - 如何防止两个请求 *Update

2022-01-06 00:00:00 transactions php

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

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.

相关文章