php - MySQL 事务内的查询是否保证一起执行？

Question

我有一个进程根据某些条件从 MySQL InnoDB 表中选择要处理的下一个项目。当一行被选为下一个要处理的行时，它的 processing 字段设置为 1，而处理是在数据库外部进行的。我这样做是为了让许多处理器可以同时运行，并且它们不会处理同一行。

如果我使用事务来执行以下查询，是否保证它们会一起执行(例如，没有任何其他 MySQL 连接执行查询。)？如果不是，那么多个处理器可能会从 SELECT 查询中获得相同的 id，然后处理将是多余的。

---

伪代码示例

Prepare Transaction...

$id = SELECT id
FROM companies
WHERE processing = 0
ORDER BY last_crawled ASC
LIMIT 1;

UPDATE companies
SET processing = 1
WHERE id = $id;

Execute Transaction

我一直在努力使用单个更新查询 (see this question) 足够快地完成这项工作。假设这不是这个问题的一个选项。

Answer 1

即使您在单个事务中执行了 SELECT 和 UPDATE，您仍然有可能出现竞争条件。 SELECT 本身不会锁定任何东西，所以你可以有两个并发 session 都 SELECT 并获得相同的 id。然后两者都会尝试更新，但只有一个会“获胜”——另一个必须等​​待。

要解决这个问题，请使用 SELECT...FOR UPDATE 子句，它会在返回的行上创建一个锁。

Prepare Transaction...

$id = SELECT id
FROM companies
WHERE processing = 0
ORDER BY last_crawled ASC
LIMIT 1
FOR UPDATE;

这意味着在选择行时创建锁。这是原子，这意味着没有其他 session 可以潜入并锁定同一行。如果他们尝试，他们的事务将在 SELECT 上阻塞。

UPDATE companies
SET processing = 1
WHERE id = $id;

Commit Transaction

我将您的“执行交易”伪代码更改为“提交交易”。事务中的语句会立即执行，这意味着它们会创建锁等。然后当您 COMMIT 时，锁被释放并提交任何更改。已提交意味着它们无法回滚，并且它们对其他事务可见。

---

这是一个使用 mysqli 完成此操作的简单示例:

$mysqli = new mysqli(...);
$mysqli->report_mode = MYSQLI_REPORT_STRICT; /* throw exception on error */

$mysqli->begin_transaction();
$sql = "SELECT id
    FROM companies
    WHERE processing = 0
    ORDER BY last_crawled ASC
    LIMIT 1
    FOR UPDATE";
$result = $mysqli->query($sql);
while ($row = $result->fetch_array(MYSQLI_ASSOC)) {
    $id = $row["id"]; 
}

$sql = "UPDATE companies
    SET processing = 1
    WHERE id = ?";
$stmt = $mysqli->prepare($sql);
$stmt->bind_param("i", $id);
$stmt->execute();

$mysqli->commit();

---

回复你的评论:

我尝试了一个实验并创建了一个表 companies，用 512 行填充它，然后启动一个事务并发出上面的 SELECT...FOR UPDATE 语句。我在 mysql 客户端中完成了此操作，无需编写 PHP 代码。

然后，在提交我的事务之前，我检查了报告的锁:

mysql> show engine innodb status\G
=====================================
2013-12-04 16:01:28 7f6a00117700 INNODB MONITOR OUTPUT
=====================================
...
---TRANSACTION 30012, ACTIVE 2 sec
2 lock struct(s), heap size 376, 513 row lock(s)
...

尽管使用了 LIMIT 1，但此报告显示事务似乎锁定了表中的每一行(出于某种原因加 1)。

所以你是对的，如果你每秒有数百个请求，很可能交易正在排队。您应该能够通过观察 SHOW PROCESSLIST 并看到许多进程卡在 Locked 状态(即等待访问另一个线程已锁定的行)来验证这一点。

如果您每秒有数百个请求，您可能已经无法让 RDBMS 充当假消息队列。这不是 RDBMS 擅长的。

有多种与 PHP 集成良好的可扩展消息队列框架，如 RabbitMQ、STOMP、AMQP、Gearman、Beanstalk。

查看 http://www.slideshare.net/mwillbanks/message-queues-a-primer-international-php-conference-fall-2012