php - 处理相同的函数同时运行和处理相同的数据

Question

我有一个 PHP 系统，允许客户使用电子钱包(商店信用)从我们的系统购买东西(下订单)。

这是数据库示例

**sales_order**
+--------+-------+----------+--------+--------------+-----------+
|order_id| price |product_id| status |already_refund|customer_id|
+--------+-------+----------+--------+--------------+-----------+
|   1    | 1000  |    1     |canceled|      1       |     2     |
|   2    | 2000  |    2     |pending |      0       |     2     |
|   3    | 3000  |    3     |complete|      0       |     1     | 
+--------+-------+----------+--------+--------------+-----------+

**ewallet**
+-----------+-------+
|customer_id|balance|
+-----------+-------+
|     1     | 43200 |
|     2     | 22500 |
|     3     | 78400 |
+-----------+-------+

表 sales_order 包含客户下的订单，列 has_refund 用于标记已取消订单已退款。

我每 5 分钟运行一次 cron 来检查状态待处理的订单是否可以取消，然后可以将钱退还给客户电子钱包

function checkPendingOrders(){
   $orders = $this->orderCollection->filter(['status'=>'pending']);
   foreach($orders as $order){
     //check if order is ready to be canceled
     $isCanceled = $this->isCanceled($order->getId());
     if($isCanceled === false) continue;
     if($order->getAlreadyRefund() == '0'){ // check if already refund
       $order->setAlredyRefund('1')->save();
       $this->refund($order->getId()); //refund the money to customer ewallet
     }
     $order->setStatus('canceled')->save();
   }
}

问题是，使用此功能，2个不同的cron计划可以同时处理相同的数据，这将使退款过程可以被调用两次，因此客户将收到双倍的退款金额。当两个相同的函数同时运行来处理相同的数据时，我该如何处理这种问题？我制作的 if 子句无法处理此类问题

更新

我尝试在 session 中使用 microtime 作为验证并锁定 MySQL 中的表行，因此一开始我将变量设置为包含 microtime ，而不是存储在由 order_id< 生成的唯一 session 中 ，然后在锁定表行并更新我的电子钱包表之前添加一个条件以将微时间值与 session 相匹配

function checkPendingOrders(){
   $orders = $this->orderCollection->filter(['status'=>'pending']);
   foreach($orders as $order){
     //assign unique microtime to session
     $mt = round(microtime(true) * 1000);
     if(!isset($_SESSION['cancel'.$order->getId()])) $_SESSION['cancel'.$order->getId()] = $mt;
     //check if order is ready to be canceled
     $isCanceled = $this->isCanceled($order->getId());
     if($isCanceled === false) continue;
     if($order->getAlreadyRefund() == '0'){ // check if already refund
       $order->setAlreadyRefund('1')->save();
       //check if microtime is the same as the first one that running
       if($_SESSION['cancel'.$order->getId()] == $mt){
        //update using lock row
        $this->_dbConnection->beginTransaction(); 
        $sqlRaws[] =  "SELECT * FROM ewallet WHERE customer_id = ".$order->getCustomerId()." FOR UPDATE;";
        $sqlRaws[] =  "UPDATE ewallet SET balance =(balance+".$order->getPrice().") WHERE customer_id = ".$order->getCustomerId().";";
        foreach ($sqlRaws as $sqlRaw) {
          $this->_dbConnection->query($sqlRaw);
        }
        $this->_dbConnection->commit(); 

       }
     }
     unset($_SESSION['cancel'.$order->getId()]);
     $order->setStatus('canceled')->save();
   }
}

但是当我进行压力测试时问题仍然存在，因为有一种情况是同一个函数在同一微时间处理相同的数据并在同一时间启动mysql事务

Answer 1

@Rick James 的回答一如既往的好，他只是没有告诉你需要锁定哪些数据。

首先让我评论一下你所说的内容

but the problem still persist when i'm doing a strees test,

并发感知应用程序不通过压力测试进行测试，只是因为您无法控制将要发生的事情，并且您可能不幸并且测试结果良好，而您仍然有一个应用程序中的偷偷摸摸的错误-相信我，并发错误是最糟糕的:(-

您需要打开2个客户端(数据库 session )并手动模拟竞争条件，在MySQL工作台中打开2个连接就足够了。

让我们这样做，在您的客户端(MySQL Workbench 或 phpMyAdmin)中打开 2 个连接并按此顺序执行这些语句，将它们视为同时运行的 PHP 脚本。

**sales_order**
+--------+-------+----------+--------+--------------+-----------+
|order_id| price |product_id| status |already_refund|customer_id|
+--------+-------+----------+--------+--------------+-----------+
|   1    | 1000  |    1     |canceled|      1       |     2     |
|   2    | 2000  |    2     |pending |      0       |     2     |
|   3    | 3000  |    3     |complete|      0       |     1     | 
+--------+-------+----------+--------+--------------+-----------+


(SESSION 1) > select * from sales_order where status = 'pending';
-- result 1 row (order_id 2)
(SESSION 2) > select * from sales_order where status = 'pending';
-- result 1 row (order_id 2)
/*
 >> BUG: Both sessions are reading that order 2 is pending and already_refund is 0

 your session 1 script is going to see that this guy needs to cancel
 and his already_refund column is 0 so it will increase his wallet with 2000
*/
(SESSION 1) > update sales_order set  status = 'canceled' , already_refund = 1
              where  order_id = 2
(SESSION 1) > update ewallet set balance = balance + 2000 where customer_id = 2
/*
 same with your session 2 script : it is going to see that this guy needs
 to cancel and his already_refund column is 0 so it will increase his 
 wallet with 2000
*/
(SESSION 2) > update sales_order set  status = 'canceled' , already_refund = 1
              where  order_id = 2
(SESSION 2) > update ewallet set balance = balance + 2000 where customer_id = 2

现在客户 2 会因此而高兴，而这个案例就是您提出问题的原因(想象一下，如果 5 个 session 可以在 already_refund 被其中一个 session 更新为 1 之前读取该订单，那么客户 2 会非常高兴，因为他得到了 5 * 2000 )

我:现在请花点时间考虑一下这种情况，您认为如何才能保护自己免受这种情况的影响？ ..？

你:按照@Rick所说锁定

我:完全正确!

你:好的，现在我去锁定 ewallet表

我:不，你需要锁定sales_order因此，在 SESSION1 完成其工作之前，SESSION 2 无法读取数据，现在让我们通过应用锁来更改场景。

(SESSION 1) > START TRANSACTION;
-- MySQL > OK;
(SESSION 2) > START TRANSACTION;
-- MySQL > OK;
(SESSION 1) > select * from sales_order where status = 'pending' FOR UPDATE;
-- MySQL > OK result 1 row (order_id 2)
(SESSION 2) > select * from sales_order where status = 'pending' FOR UPDATE;
-- MySQL > WAAAAAAAAAAAAAAAIT ...... THE DATA IS LOCKED
/*
 now session 2 is waiting for the result of the select query .....

 and session 1 is going to see that this guy needs to cancel and his
 already_refund column is 0 so it will increase his  wallet with 2000
*/
(SESSION 1) > update sales_order set  status = 'canceled' , already_refund = 1
          where  order_id = 2
(SESSION 1) > update ewallet set balance = balance + 2000 where customer_id = 2;
(SESSION 2) >  :/  I am still waiting for the result of the select .....
(SESSION 1) > COMMIT;
-- MySQL > OK , now I will release the lock so any other session can read the data
-- MySQL > I will now execute the select statement of session 2
-- MySQL > the result of the select statement of session 2 is 0 rows
(SESSION 2) >  /* 0 rows ! no pending orders ! 
               Ok just end the transaction, there is nothing to do*/

现在你很高兴，而不是客户 2!

注1:

SELECT * from sales_order where status = 'pending' FOR UPDATE在此代码中应用可能不会仅锁定 pending订单，因为它使用 status 上的搜索条件列并且不使用唯一索引

MySQL manual声明

For locking reads (SELECT with FOR UPDATE or FOR SHARE), UPDATE, and DELETE statements, the locks that are taken depend on whether the statement uses a unique index with a unique search condition, or a range-type search condition.
.......

For other search conditions, and for non-unique indexes, InnoDB locks the index range scanned ...

(这是我最讨厌 MySQL 的地方之一。我希望只锁定 select 语句返回的行:( )

注释2

我不知道你的应用程序，但如果这个 cron 任务只是取消挂起的订单，那么就摆脱它，并在用户取消订单时启动取消流程。

此外，如果already_refund列始终更新为 1，状态列更新为 canceled然后“取消订单意味着他也退款了”，并摆脱 already_refund列，额外的数据=额外的工作和额外的问题

<小时/>

MySQL documentation examples of locking reads 向下滚动到“锁定读取示例”