在比赛情况下将遵守where条件，但是您必须谨慎检查谁在比赛中获胜.

The where condition will be respected during a race situation, but you must be careful how you check to see who won the race.

请考虑以下示例，以了解其工作原理以及为什么要小心.

Consider the following demonstration of how this works and why you have to be careful.

首先，设置一些最小表.

First, setup some minimal tables.

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.

id plays the role of id in your table, updated_by_connection_id acts like assignedPhone, and locked like reservationCompleted.

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

Now lets start the race test. You should have 2 commandline/terminal windows open, connected to mysql and using the database where you have created these tables.

连接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

Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 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以继续并输出以下内容:

At this point, connection 2 is released to continue and outputs the following:

连接2

查询确定，受影响的0行(23.25秒)匹配的行:0已更改:0 警告:0

Query OK, 0 rows affected (23.25 sec) Rows matched: 0 Changed: 0 Warnings: 0

SELECT * FROM table1 WHERE id = 1;

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

commit;

一切都很好.我们看到是的，在比赛情况下WHERE子句得到了尊重.

Everything looks fine. We see that yes, the WHERE clause was respected in a race situation.

我说你必须要小心的原因是，在实际的应用程序中，事情并不总是那么简单.您可能还会在事务中进行其他操作，而这些操作实际上可以改变结果.

The reason I said you had to be careful though, is because in a real application things are not always this simple. You MAY have other actions going on within the transaction, and that can actually change the results.

让我们使用以下内容重置数据库:

Let's reset the database with the following:

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

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

And now, consider this situation, where a SELECT is performed before the UPDATE.

连接1

start transaction;

SELECT * FROM table2;

空集(0.00秒)

Empty set (0.00 sec)

连接2

start transaction;

SELECT * FROM table2;

空集(0.00秒)

Empty set (0.00 sec)

连接1

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

查询确定，受影响的1行(0.00秒)匹配的行:1已更改:1 警告:0

Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 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以继续并输出以下内容:

At this point, connection 2 is released to continue and outputs the following:

查询确定，受影响的0行(20.47秒)匹配的行:0已更改:0 警告:0

Query OK, 0 rows affected (20.47 sec) Rows matched: 0 Changed: 0 Warnings: 0

好，让我们看看谁赢了:

Ok, let's see who won:

连接2

SELECT * FROM table1 WHERE id = 1;

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

等等，什么?为什么locked 0和updated_by_connection_id NULL ??

Wait, what? Why is locked 0 and updated_by_connection_id NULL??

这是我提到的注意事项.罪魁祸首实际上是由于我们在一开始就进行了选择.为了获得正确的结果，我们可以运行以下命令:

This is the being careful I mentioned. The culprit is actually due to the fact that we did a select at the beginning. To get the correct result, we could run the following:

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给出两个不同的结果.

By using SELECT ... FOR UPDATE we can get the right result. This can be very confusing (as it was for me, originally), as a SELECT and a SELECT ... FOR UPDATE are giving two different results.

发生这种情况的原因是由于默认的隔离级别READ-REPEATABLE.在start transaction;之后紧接第一个SELECT时，将创建一个快照.将来所有非更新的读取都将从该快照中完成.

The reason this happens is because of the default isolation level READ-REPEATABLE. When the first SELECT is made, right after the start transaction;, a snapshot is created. All future non-updating reads will be done from that snapshot.

因此，如果您只是在更新后天真地执行SELECT，它将从该原始快照中提取信息，该快照位于之前行已更新.通过执行SELECT ... FOR UPDATE，可以强制其获取正确的信息.

Therefore, if you just naively SELECT after you do the update, it will pull the information from that original snapshot, which is before the row has been updated. By doing a SELECT ... FOR UPDATE you force it to get the correct information.

但是，在实际应用中，这又可能是一个问题.例如，假设您的请求被包装在一个事务中，并且在执行更新后您想输出一些信息.收集和输出该信息可能由单独的可重用代码处理，您不希望使用FOR UPDATE子句以防万一".由于不必要的锁定，这将导致很多挫败感.

However, again, in a real application this could be an issue. Say, for example, your request is wrapped in a transaction, and after performing the update you want to output some information. Collecting and output that information may be handled by separate, reusable code, that you DON'T want to litter with FOR UPDATE clauses "just in case." That would lead to lots of frustration due to unnecessary locking.

相反，您需要改头换面.您在这里有很多选择.

Instead, you'll want to take a different track. You have many options here.

一个是要确保您在UPDATE完成后提交事务.在大多数情况下，这可能是***，最简单的选择.

One, is to make sure you commit the transaction after the UPDATE has completed. In most case, this is probably the best, simplest choice.

另一个选择是不尝试使用SELECT来确定结果.相反，您也许可以读取受影响的行，并使用该行(更新1行与更新0行)来确定UPDATE是否成功.

Another option is to not try using SELECT to determine the result. Instead, you may be able to read the rows affected, and use that (1 row updated vs 0 rows update) to determine if the UPDATE was a success.

另一个选项，也是我经常使用的选项，因为我想将单个请求(如HTTP请求)完全包装在单个事务中，所以是确保在事务中执行的第一条语句是UPDATE或SELECT ... FOR UPDATE .这将导致在允许连接继续进行之前无法拍摄快照.

Another option, and one that I use frequently, as I like to keep a single request (like an HTTP request) fully wrapped in a single transaction, is to make sure that the first statement executed in a transaction is either the UPDATE or a SELECT ... FOR UPDATE. That will cause the snapshot to NOT be taken until the connection is allowed to proceed.

让我们再次重置测试数据库，看看它如何工作.

Let's reset our test database again and see how this works.

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

Query OK, 1 row affected (0.01 sec) Rows matched: 1 Changed: 1 Warnings: 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的结果并知道它是准确的，甚至不继续进行下一步.但是，为了完整起见，我将像以前一样完成.

Here you could actually have your server side code check the results of this SELECT and know it is accurate, and not even continue with the next steps. But, for completeness, I'll finish as before.

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

查询确定，受影响的0行(0.00秒)匹配的行:0已更改:0 警告:0

Query OK, 0 rows affected (0.00 sec) Rows matched: 0 Changed: 0 Warnings: 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;

现在，您可以看到在Connection 2中SELECT和SELECT ... FOR UPDATE给出相同的结果.这是因为在提交连接1之后才创建SELECT读取的快照.

Now you can see that in Connection 2 the SELECT and SELECT ... FOR UPDATE give the same result. This is because the snapshot that the SELECT reads from was not created until after Connection 1 had been committed.

因此，回到您的原始问题:是的，在所有情况下，UPDATE语句都会检查WHERE子句.但是，您必须谨慎对待可能执行的所有SELECT，以免错误地确定该UPDATE的结果.

So, back to your original question: Yes, the WHERE clause is checked by the UPDATE statement, in all cases. However, you have to be careful with any SELECTs you may be doing, to avoid incorrectly determining the result of that UPDATE.

(是的另一种选择是更改事务隔离级别.但是，我对此并没有真正的经验，并且可能存在任何问题，所以我不再赘述.)

(Yes another option is to change the transaction isolation level. However, I don't really have experience with that and any gotchya's that might exist, so I'm not going to go into it.)