MySQL 更新查询 - 在竞争条件和行锁定情况下是否会尊重'where'条件？（php，PDO，MySQL，InnoDB）

Question

我正在尝试构建一个先到先得的销售页面模型。我们有n个相同类型的物品。我们想将这些n个物品分配给最先发出请求的n个用户。每个物品都对应一个数据库行。当用户按下购买按钮时，系统会尝试查找尚未出售的条目（reservationCompleted = FALSE），然后更新用户ID并将reservationCompleted设置为true。

由于我使用的数据库引擎是InnoDB，我知道有一种内部锁定机制，不允许两个进程同时在同一行上进行更新。

我的问题是，如果我使用的语句如下，是否会导致在同时到达两个请求时将不同的用户分配给同一行？

$query = "UPDATE available_items
    SET assignedPhone=".$user->phone.",
        reservationCompleted = TRUE,
        assignmentCreatedTimestamp =".time()."
    WHERE id=".$itemListing['id']."
    AND reservationCompleted=FALSE";
$stmt = $pdo->prepare($query);
$stmt->execute();

考虑以下情况。

两个不同的进程获取相同的行（例如id=5），并尝试更新DB条目。但其中一个获得锁定。它更新该项并释放锁定，下一个进程获得锁定。那么，在执行更新之前，它是否会再次验证where条件？

- Akhil Balakrishnan

---

你的 UPDATE 是否在自动提交模式下（非事务中）运行？ - Rick James

Answer 1

在比赛中，where条件将被尊重，但您必须小心如何检查谁赢得了比赛。

考虑以下演示，说明它是如何工作的以及为什么您必须小心。

首先，设置一些最小的表。

CREATE TABLE table1 (
`id` TINYINT UNSIGNED NOT NULL PRIMARY KEY,
`locked` TINYINT UNSIGNED NOT NULL,
`updated_by_connection_id` TINYINT UNSIGNED DEFAULT NULL
) ENGINE = InnoDB;

CREATE TABLE table2 (
`id` TINYINT UNSIGNED NOT NULL PRIMARY KEY
) ENGINE = InnoDB;

INSERT INTO table1
(`id`,`locked`)
VALUES
(1,0);

id在您的表中扮演id的角色，updated_by_connection_id则像assignedPhone一样，locked则像reservationCompleted。

现在让我们开始比赛测试。您应该打开2个命令行/终端窗口，连接到mysql并使用您创建这些表的数据库。

连接1

start transaction;

连接2

start transaction;

连接1

UPDATE table1
SET locked = 1,
updated_by_connection_id = 1
WHERE id = 1
AND locked = 0;

查询成功，1行受影响（0.00秒） 匹配的行数：1 改变的行数：1 警告数：0

连接2

UPDATE table1
SET locked = 1,
updated_by_connection_id = 2
WHERE id = 1
AND locked = 0;

连接2正在等待

连接1

SELECT * FROM table1 WHERE id = 1;

+----+--------+--------------------------+
| id | locked | updated_by_connection_id |
+----+--------+--------------------------+
|  1 |      1 |                        1 |
+----+--------+--------------------------+

commit;

此时，连接2被释放以继续执行，并输出以下内容：

连接2

查询成功，影响0行（23.25秒）匹配的行数：0，更改的行数：0，警告：0

SELECT * FROM table1 WHERE id = 1;

+----+--------+--------------------------+
| id | locked | updated_by_connection_id |
+----+--------+--------------------------+
|  1 |      1 |                        1 |
+----+--------+--------------------------+

commit;

一切看起来都很好。我们可以看到，在竞争情况下，WHERE子句确实被遵守了。

但是我说你必须小心的原因是，在实际应用中，事情并不总是这么简单。您可能会在事务中进行其他操作，并且这可能会改变结果。

让我们使用以下命令重置数据库：

delete from table1;
INSERT INTO table1
(`id`,`locked`)
VALUES
(1,0);

现在，考虑这种情况，在 UPDATE 之前执行了 SELECT。

连接 1

start transaction;

SELECT * FROM table2;

空集合 (0.00 秒)

连接 2

start transaction;

SELECT * FROM table2;

空集 (0.00 秒)

连接 1

UPDATE table1
SET locked = 1,
updated_by_connection_id = 1
WHERE id = 1
AND locked = 0;

查询成功，1行受影响（0.00秒）。匹配行数：1，更改行数：1，警告数：0。

连接2

UPDATE table1
SET locked = 1,
updated_by_connection_id = 2
WHERE id = 1
AND locked = 0;

连接2现在正在等待

连接1

SELECT * FROM table1 WHERE id = 1;

+----+--------+--------------------------+
| id | locked | updated_by_connection_id |
+----+--------+--------------------------+
|  1 |      1 |                        1 |
+----+--------+--------------------------+
1 row in set (0.00 sec)

SELECT * FROM table1 WHERE id = 1 FOR UPDATE;

+----+--------+--------------------------+
| id | locked | updated_by_connection_id |
+----+--------+--------------------------+
|  1 |      1 |                        1 |
+----+--------+--------------------------+
1 row in set (0.00 sec)

commit;

此时，连接2被释放并继续输出以下内容:

查询成功，0行受影响 (20.47秒) 行数匹配: 0 改变: 0 警告: 0

好的，让我们看看谁赢了:

连接2

SELECT * FROM table1 WHERE id = 1;

+----+--------+--------------------------+
| id | locked | updated_by_connection_id |
+----+--------+--------------------------+
|  1 |      0 |                     NULL |
+----+--------+--------------------------+

等等，什么？为什么locked是0，updated_by_connection_id又是NULL？

这就是我提到的小心谨慎。罪魁祸首实际上是因为我们在开头做了一个选择。为了得到正确的结果，我们可以运行以下命令：

SELECT * FROM table1 WHERE id = 1 FOR UPDATE;

+----+--------+--------------------------+
| id | locked | updated_by_connection_id |
+----+--------+--------------------------+
|  1 |      1 |                        1 |
+----+--------+--------------------------+

commit;

使用SELECT ... FOR UPDATE可以得到正确的结果。这可能会非常令人困惑（就像最初对我一样），因为SELECT和SELECT ... FOR UPDATE会产生两个不同的结果。

这种情况发生的原因是默认的隔离级别 READ-REPEATABLE。当进行第一个SELECT时，在start transaction;之后立即创建了一个快照。所有未来的非更新读取将从该快照中完成。

因此，如果你只是简单地在更新后SELECT，它将从那个初始快照中拉取信息，这个快照是在行被更新之前的。通过使用SELECT ... FOR UPDATE，您强制它获取正确的信息。

然而，在一个真实的应用程序中，这可能是一个问题。例如，假设您的请求被包装在事务中，在执行更新后，您希望输出一些信息。收集和输出这些信息可能由单独的，可重用的代码处理，您不希望“仅仅为了防备”而将其放置在FOR UPDATE子句中。这将导致由于不必要的锁定而引起许多挫败感。

相反，您需要采取不同的方法。您有很多选择。

其中之一是在UPDATE完成后确保提交事务。在大多数情况下，这可能是最好、最简单的选择。

另一种选择是不尝试使用SELECT来确定结果。相反，您可以读取受影响的行，并使用它（1行更新 vs 0行更新）来确定UPDATE是否成功。

另一种选择，也是我经常使用的选择，因为我喜欢将单个请求（如HTTP请求）完全封装在一个事务中，是确保在事务中执行的第一个语句要么是UPDATE，要么是SELECT ... FOR UPDATE。这将导致快照在连接被允许继续之前不会被拍摄。

让我们重新设置我们的测试数据库，看看它是如何工作的。

delete from table1;
INSERT INTO table1
(`id`,`locked`)
VALUES
(1,0);

连接1

start transaction;

SELECT * FROM table1 WHERE id = 1 FOR UPDATE;

+----+--------+--------------------------+
| id | locked | updated_by_connection_id |
+----+--------+--------------------------+
|  1 |      0 |                     NULL |
+----+--------+--------------------------+

Connection 2

start transaction;

SELECT * FROM table1 WHERE id = 1 FOR UPDATE;

现在，连接2正在等待。

连接1

UPDATE table1
SET locked = 1,
updated_by_connection_id = 1
WHERE id = 1
AND locked = 0;

查询完成，1行受到影响（0.01秒）。匹配行数：1，修改行数：1，警告：0。

SELECT * FROM table1 WHERE id = 1;

+----+--------+--------------------------+
| id | locked | updated_by_connection_id |
+----+--------+--------------------------+
|  1 |      1 |                        1 |
+----+--------+--------------------------+

SELECT * FROM table1 WHERE id = 1 FOR UPDATE;

+----+--------+--------------------------+
| id | locked | updated_by_connection_id |
+----+--------+--------------------------+
|  1 |      1 |                        1 |
+----+--------+--------------------------+

commit;

现在已释放连接2。

连接2

+----+--------+--------------------------+
| id | locked | updated_by_connection_id |
+----+--------+--------------------------+
|  1 |      1 |                        1 |
+----+--------+--------------------------+

在这里，您实际上可以让您的服务器端代码检查此SELECT的结果，并知道它是准确的，甚至不需要继续进行下一步。但为了完整起见，我将像以前一样完成。

UPDATE table1
SET locked = 1,
updated_by_connection_id = 2
WHERE id = 1
AND locked = 0;

查询成功，0行受影响 (0.00秒) 匹配行数: 0 改变行数: 0 警告数量: 0。

SELECT * FROM table1 WHERE id = 1;

+----+--------+--------------------------+
| id | locked | updated_by_connection_id |
+----+--------+--------------------------+
|  1 |      1 |                        1 |
+----+--------+--------------------------+

SELECT * FROM table1 WHERE id = 1 FOR UPDATE;

+----+--------+--------------------------+
| id | locked | updated_by_connection_id |
+----+--------+--------------------------+
|  1 |      1 |                        1 |
+----+--------+--------------------------+

commit;

现在你可以看到，在连接2中SELECT和SELECT ... FOR UPDATE的结果是相同的。这是因为SELECT从快照中读取，而该快照直到Connection 1提交后才创建。

所以，回到你最初的问题：是的，在所有情况下，WHERE子句都会被UPDATE语句检查。但是，你必须小心任何可能进行的SELECT操作，以避免错误地确定该UPDATE的结果。

(是的，另一个选择是更改事务隔离级别。然而，我对此没有太多经验，也不知道可能存在的任何陷阱，因此我不打算深入探讨。)

- Willem Renzema

---

优秀的 SELECT .. FOR UPDATE 演示。 - Rick James

Answer 2

答案是：在更新数据之前，它将验证WHERE条件。

---

好的，我必须说这是一个非常有趣的问题。以前我从来没有想过这样的问题，这迫使我更好地了解MySQL内部工作方式。谢谢！

我是如何得出答案的：

我首先对这种情况进行了测试。即使在测试之前我就知道它应该像这样工作，但我只是不明白为什么。

为什么：

最后，我在Index Condition Pushdown部分找到了一些有用的东西。

这就是MySQL内部的工作方式：

MySQL Server
   ↑  ↑
   ↓  ↓
Storage Engine(InnoDB here)

1. MySQL服务器解析来自外部应用程序的SQL语句。
2. MySQL服务器告诉InnoDB为其检索行。
3. InnoDB定位行（索引、锁定），然后将它们返回给MySQL服务器。
4. MySQL服务器评估行的WHERE条件。
5. 其他一些事情...

可以看到，锁定发生在InnoDB内部，在获取行之后，MySQL服务器评估WHERE条件。对于您的情况，第一个UPDATE锁定了行（id = 5），当尝试获取相同的行时，第二个UPDATE会被阻塞。第二个UPDATE的WHERE条件的评估发生在获取行的锁之后。

此外，如果您在id上创建了索引，则查询将进行索引条件下推。

- walter

---

ICP是一项新的“优化”功能。它通过避免处理程序和引擎之间的某些来回操作来提高性能。在第二步中，它将“附加条件”发送到引擎（请参见EXPLAIN FORMAT=JSON）。在可能的情况下，由于InnoDB执行了更多第四步的工作，因此在第三步中发送的行数较少。在实施ICP之前，InnoDB避免了竞争条件。 - Rick James

Answer 3

由于reservationCompleted已设置为true，因此不要忘记在每个成功的事务中COMMIT。下一个进程当然会获取锁，但不会满足WHERE条件并释放锁。如果您希望下一个进程查找另一个可用项，则可以将您的更新语句包装在一个子例程过程中，以检查reservationCompleted是否为FALSE。

- GNETO DOMINIQUE