在比赛情况下,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
, and locked
like 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);
现在,考虑这种情况,其中 SELECT 在 UPDATE 之前执行。
连接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 and 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;
,创建快照。所有未来的非更新读取都将从该快照完成。
因此,如果你在更新后天真地选择,它将从原始快照中提取信息,即before该行已更新。通过执行 SELECT ... FOR UPDATE,您可以强制它获取正确的信息。
然而,在实际应用中这可能会成为一个问题。例如,您的请求被包装在事务中,并且在执行更新后您想要输出一些信息。收集和输出信息可以由单独的、可重用的代码处理,您不想“以防万一”用 FOR UPDATE 子句乱扔垃圾。由于不必要的锁定,这会导致很多挫败感。
相反,你会想走不同的路。您在这里有很多选择。
一是确保更新完成后提交事务。在大多数情况下,这可能是最好、最简单的选择。
另一种选择是不尝试使用 SELECT 来确定结果。相反,您可以读取受影响的行,并使用它(更新 1 行与更新 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 |
+----+--------+--------------------------+
连接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 从中读取的快照直到连接 1 提交后才创建。
所以,回到你原来的问题:是的,在所有情况下,WHERE 子句都会由 UPDATE 语句检查。但是,您必须小心可能执行的任何 SELECT,以避免错误地确定 UPDATE 的结果。
(是的,另一个选择是更改事务隔离级别。但是,我对此并没有真正的经验,也没有任何可能存在的问题,所以我不打算讨论它。)