如何检测 2 个管理员是否在 php 中同时更新表行

Question

我正在开发的网站有一个管理页面，用于将产品添加到数据库或编辑数据库中的现有产品。如果两个管理员想要编辑同一个产品，第二个管理员应该收到产品已经/正在更新的警报。当 2 个管理员尝试编辑同一个产品时，我如何在 php/sql 中检测到？

我还没有真正尝试过任何东西，因为我不知道该尝试什么或从哪里开始。

这是我的函数如何查找更新数据库中的产品：

//I know this is a VERY unsafe function, this website will never go online!

FUNCTION updateProduct($data) {

  include_once 'dbconn.php';

  try {

    $conn = connectToDb();

    if ($data['imageChanged'] == false) {

      $query = "SELECT img_url FROM products WHERE product_id=".$data['productId'];

      $result = mysqli_query($conn, $query);

      if ($result == false) {

        throw new Exception('Failed to execute: '. $query . 'Error: '. mysqli_error($conn));

      }

      $imgUrl = mysqli_fetch_row($result)[0];

    } else {

      $imgUrl = $data['image'];

    }

    $query2 = "img_url='".$imgUrl."'";

    $query = "UPDATE products

              SET product_name='".$data['productName']."', product_desc='".$data['productDesc']."', price=".$data['price'].", ". $query2 . " 

              WHERE product_id=".$data['productId'];

    $result = mysqli_query($conn, $query);

    if ($result == false) {

      throw new Exception('Failed to execute: '. $query . 'Error: '. mysqli_error($conn));

    }

  } finally {

    mysqli_close($conn);

  }

}

编辑：不需要在更新后存储旧数据，也不需要存储正在进行的更新（关于这可能是重复的问题）。我想知道的是：如果 admin_1 正在更新一行，而 admin_2 正在尝试在 admin_1 的事务仍在进行时更新同一行，我的脚本如何检测到这种情况正在发生？

Answer 1

考虑以下几点 1) 您希望多个管理员编辑不同的产品 2) 您不希望多个管理员编辑同一产品3）在您的代码中，您将产品保存为单个表。在一个真实的电子商务网站产品信息被拆分成几个表格要实现这一点，您必须设置应用程序范围的 mysql 锁mysql> select get_lock('product_id_12345', 5);+---------------------------------+| get_lock('product_id_12345', 5) |+---------------------------------+|&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1 |+---------------------------------+1 row in set (0.00 sec)上面的代码使用 product_id 设置了一个锁，如果其他一些连接尝试使用相同的 product_id 获取锁，你将得到 0 作为响应 - 表明其他一些用户正在更新相同的 product_idmysql>&nbsp;mysql> select IS_FREE_LOCK('product_id_12345');+----------------------------------+| IS_FREE_LOCK('product_id_12345') |+----------------------------------+|&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 0 |+----------------------------------+1 row in set (0.00 sec)mysql>&nbsp;mysql> select IS_USED_LOCK('product_id_12345');+----------------------------------+| IS_USED_LOCK('product_id_12345') |+----------------------------------+|&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;46 |+----------------------------------+1 row in set (0.00 sec)mysql>&nbsp;mysql> select connection_id();+-----------------+| connection_id() |+-----------------+|&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 46 |+-----------------+1 row in set (0.00 sec)示例算法<?phpini_set('display_errors', 1);function updateProduct() {$user = 'root';$pass = 'xxx';$DB = 'test';$host = 'localhost';&nbsp; try&nbsp;&nbsp; {&nbsp; &nbsp; $conn = new mysqli($host, $user, $pass, $DB);&nbsp; &nbsp; $data['productId'] = 'product_id_12345';&nbsp; &nbsp; $data['productName'] = 'test';&nbsp; &nbsp; $data['productDesc'] = 'testing';&nbsp; &nbsp; $isLockFree = 'select IS_USED_LOCK("'.$data['productId'].'") ';&nbsp; &nbsp; $isLockFreeResult = mysqli_query($conn, $isLockFree);&nbsp; &nbsp; $row=mysqli_fetch_row($isLockFreeResult);&nbsp; &nbsp; if(empty($row[0]))&nbsp; &nbsp; {&nbsp; &nbsp; &nbsp; &nbsp; $lock = 'select get_lock("'.$data['productId'].'")';&nbsp; &nbsp; &nbsp; &nbsp; $result = mysqli_query($conn, $lock);&nbsp; &nbsp; &nbsp; &nbsp; echo 'lock established';&nbsp; &nbsp; &nbsp; &nbsp; $query = "UPDATE products&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; SET product_name='".$data['productName']."',&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; product_desc='".$data['productDesc']."',&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; price=".$data['price']."&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; WHERE&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; product_id=".$data['productId'];&nbsp; &nbsp; &nbsp; &nbsp; $result = mysqli_query($conn, $query);&nbsp; &nbsp; &nbsp; &nbsp; if ($result == false)&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; throw new Exception('Failed to execute: '. $query . 'Error: '. mysqli_error($conn));&nbsp; &nbsp; &nbsp; &nbsp; }&nbsp; &nbsp; }&nbsp; &nbsp; else&nbsp; &nbsp; {&nbsp; &nbsp; &nbsp; &nbsp; echo 'sorry! could not lock. somebody is updating the product info';&nbsp; &nbsp; }&nbsp; }&nbsp;&nbsp; finally&nbsp;&nbsp; {&nbsp; &nbsp; mysqli_close($conn);&nbsp; }}updateProduct();?>