php - 限制表的行一次由一个脚本使用

Question

我有一个表tableName，其中包含一些要使用的名称:

id    name    in_use
1     name1   0
2     name2   0

我的脚本使用 in_use = 0 从这个表中获取名字:

SELECT name FROM tableName WHERE in_use = 0 ORDER BY id

然后立即将获得的行设置为 in_use = 1:

UPDATE tableName set in_use = 1 WHERE id = '$idObtained'

当脚本完成时，它设置 in_use = 0:

UPDATE tableName set in_use = 0 WHERE id = '$idObtained'

问题是当脚本将在同一时刻执行多次时，两个或多个选择可能会从该表中获得相同的名称。

我的目标是避免两个脚本在执行时获得同一行。

有没有办法用 PHP/MySQL 做到这一点？

是否在选择之前锁定表并在更新之后解锁它？

脚本(代码只是一个草稿。只有当我需要的东西在 PHP 和 MySQL 中可行时，我才会开发它):

<?php
    // ...

    // TODO: lock the raw, if found
    $query = "SELECT id, name
    FROM tableName
    WHERE in_use = 0
    ORDER BY id";
    $stmt = $connection->prepare($query);
    $stmt->execute();
    $data = $stmt->fetch();

    if(!empty($data['id'])) {

        $query = "UPDATE tableName
        SET in_use = 1
        WHERE id = '$data[id]'";
        $stmt = $connection->prepare($query);
        $stmt->execute();

        // do some with $data['name]

        $query = "UPDATE tableName
        SET in_use = 0
        WHERE id = '$data[id]'";
        $stmt = $connection->prepare($query);
        $stmt->execute();

        // TODO: unlock the raw
    }
?>

非常感谢。

Answer 1

修正你的方法

BEGIN;
SELECT id, name FROM tableName WHERE in_use = 0 ORDER BY id
                  FOR UPDATE;   -- Add this 
$idObtained = ...
UPDATE tableName set in_use = 1 WHERE id = '$idObtained';
COMMIT;

稍后，完成该行的工作后:

UPDATE tableName set in_use = 0 WHERE id = '$idObtained';

讨论:

FOR UPDATE 防止另一个连接在 SELECT 和 UPDATE 之间偷偷摸摸，并且也捕获它行。

如果这一行的操作只需要几秒钟，那么这种技术就太过分了。因此，我假设这将花费很长时间，因此有两个单独的事务——一个“抓取”该行，另一个“释放”它。

请注意，我没有在“发布”UPDATE 周围放置BEGIN 和COMMIT。任何独立查询本身就是一个事务(假设 autocommit=ON)。因此，只需一个查询即可轻松完成发布。

替代方法

“简化”“抓取”可能是可能的，如果我们可以设计一条既查找“空闲”行又“抓取”它的指令。一种方法包括一个额外的列，说明哪个连接“拥有”每个抓取的行。但是，让我们简单地改变 set_in_use 的工作方式。假设您不会为每个连接获取超过一行，CONNECTION_ID() 很方便:

$conn = `SELECT CONNECTION_ID()`

UPDATE tbl SET in_use_by = $conn
    WHERE in_use_by IS NULL
    ORDER BY id
    LIMIT 1;      -- grab only 1 row
SELECT ... FROM tbl WHERE in_use_by = $conn;   -- Get the data to process

... process ...

UPDATE tbl SET in_use_by = NULL WHERE in_use_by = $conn;

使用这种技术，不需要显式事务。 autocommit=ON 工作得很好。

此技术使您能够查看谁拥有什么元素。 (并不是说您可以利用这些知识做很多事情。)

(可能还有其他方法。)