使用 MySQL 的 FOR UPDATE 锁定时，究竟锁定了什么？

When using MySQL's FOR UPDATE locking, what is exactly locked?

提问by Markus

This is not a full/correct MySQL query only pseudo-code:

这不是完整/正确的 MySQL 查询伪代码：

Select *
 from Notifications as n
 where n.date > (CurrentDate-10 days)
 limit by 1
 FOR UPDATE

http://dev.mysql.com/doc/refman/5.0/en/select.htmlstates: If you use FOR UPDATE with a storage engine that uses page or row locks, rows examined by the query are write-locked until the end of the current transaction

http://dev.mysql.com/doc/refman/5.0/en/select.html指出：如果您将 FOR UPDATE 与使用页锁或行锁的存储引擎一起使用，则查询检查的行将被写锁定，直到当前交易结束

Is here only the one record returned locked by MySQL or all records it has to scan to find the single record?

这里是只返回一条被 MySQL 锁定的记录还是它必须扫描的所有记录才能找到单条记录？

回答by Frans

Why don't we just try it?

我们为什么不试试呢？

Set up the database

设置数据库

CREATE DATABASE so1;
USE so1;
CREATE TABLE notification (`id` BIGINT(20), `date` DATE, `text` TEXT) ENGINE=InnoDB;
INSERT INTO notification(id, `date`, `text`) values (1, '2011-05-01', 'Notification 1');
INSERT INTO notification(id, `date`, `text`) values (2, '2011-05-02', 'Notification 2');
INSERT INTO notification(id, `date`, `text`) values (3, '2011-05-03', 'Notification 3');
INSERT INTO notification(id, `date`, `text`) values (4, '2011-05-04', 'Notification 4');
INSERT INTO notification(id, `date`, `text`) values (5, '2011-05-05', 'Notification 5');

Now, start two database connections

现在，启动两个数据库连接

Connection 1

连接 1

BEGIN;
SELECT * FROM notification WHERE `date` >= '2011-05-03' FOR UPDATE;

Connection 2

连接 2

BEGIN;

If MySQL locks all rows, the following statement would block. If it only locks the rows it returns, it shouldn't block.

如果 MySQL 锁定所有行，则以下语句将阻塞。如果它只锁定它返回的行，它不应该阻塞。

SELECT * FROM notification WHERE `date` = '2011-05-02' FOR UPDATE;

And indeed it does block.

它确实会阻塞。

Interestingly, we also cannot add records that would be read, i.e.

有趣的是，我们也不能添加将要读取的记录，即

INSERT INTO notification(id, `date`, `text`) values (6, '2011-05-06', 'Notification 6');

blocks as well!

块也是！

I can't be sure at this point whether MySQL just goes ahead and locks the entire table when a certain percentage of rows are locked, or where it's actually really intelligent in making sure the result of the SELECT ... FOR UPDATEquery can never be changed by another transaction (with an INSERT, UPDATE, or DELETE) while the lock is being held.

在这一点上，我无法确定 MySQL 是否会在一定比例的行被锁定时继续锁定整个表，或者它实际上真正智能地确保SELECT ... FOR UPDATE查询的结果永远不会被另一个事务更改（使用INSERT, UPDATE, 或DELETE) 持有锁。

回答by concat

I know this question is pretty old, but I've wanted to share the results of some relevant testing I've done with indexed columns which has yielded some pretty strange results.

我知道这个问题已经很老了，但我想分享我对索引列所做的一些相关测试的结果，这些测试产生了一些非常奇怪的结果。

Table structure:

表结构：

CREATE TABLE `t1` (                       
  `id` int(11) NOT NULL AUTO_INCREMENT,                 
  `notid` int(11) DEFAULT NULL,                         
  PRIMARY KEY (`id`)                                    
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

12 rows inserted with INSERT INTO t1 (notid) VALUES (1), (2),..., (12). On connection 1:

12 行插入INSERT INTO t1 (notid) VALUES (1), (2),..., (12)。在连接 1 上：

BEGIN;    
SELECT * FROM t1 WHERE id=5 FOR UPDATE;

On connection 2, the following statements are blocked:

在连接 2 上，以下语句被阻塞：

SELECT * FROM t1 WHERE id!=5 FOR UPDATE;
SELECT * FROM t1 WHERE id<5 FOR UPDATE;
SELECT * FROM t1 WHERE notid!=5 FOR UPDATE;
SELECT * FROM t1 WHERE notid<5 FOR UPDATE;
SELECT * FROM t1 WHERE id<=4 FOR UPDATE;

The strangest part is that SELECT * FROM t1 WHERE id>5 FOR UPDATE;is not blocked, nor are any of

奇怪的部分是，SELECT * FROM t1 WHERE id>5 FOR UPDATE;在不堵塞，也不是任何的

...
SELECT * FROM t1 WHERE id=3 FOR UPDATE;
SELECT * FROM t1 WHERE id=4 FOR UPDATE;
SELECT * FROM t1 WHERE id=6 FOR UPDATE;
SELECT * FROM t1 WHERE id=7 FOR UPDATE;
...

I'd also like to point out that it seems the entire tableis locked when the WHEREcondition in the query from connection 1matches a non-indexed row. For example, when connection 1executes SELECT * FROM t1 WHERE notid=5 FOR UPDATE, all select queries with FOR UPDATEand UPDATEqueries from connection 2are blocked.

我还想指出，当来自连接 1的查询中的条件与非索引行匹配时，整个表似乎都被锁定了。例如，当连接 1执行时，所有来自连接 2 的选择查询和来自连接 2 的查询都会被阻止。WHERESELECT * FROM t1 WHERE notid=5 FOR UPDATEFOR UPDATEUPDATE

-EDIT-

-编辑-

This is a rather specific situation, but it was the only I could find that exhibits this behaviour:

这是一个相当具体的情况，但这是我发现的唯一表现出这种行为的情况：

Connection 1:

连接 1：

BEGIN;
SELECT *, @x:=@x+id AS counter FROM t1 CROSS JOIN (SELECT @x:=0) b HAVING counter>5 LIMIT 1 FOR UPDATE;
+----+-------+-------+---------+
| id | notid | @x:=0 | counter |
+----+-------+-------+---------+
|  3 |     3 |     0 |       9 |
+----+-------+-------+---------+
1 row in set (0.00 sec)

From connection 2:

从连接 2：

SELECT * FROM t1 WHERE id=2 FOR UPDATE;is blocked;

SELECT * FROM t1 WHERE id=2 FOR UPDATE;被封锁；

SELECT * FROM t1 WHERE id=4 FOR UPDATE;is notblocked.

SELECT * FROM t1 WHERE id=4 FOR UPDATE;是不是堵塞。

回答by El Yobo

Following links from the documentation page you posted gives more information about locking. In this page

您发布的文档页面中的以下链接提供了有关锁定的更多信息。在这个页面

A SELECT ... FOR UPDATE reads the latest available data, setting exclusive locks on each row it reads. Thus, it sets the same locks a searched SQL UPDATE would set on the rows.

A SELECT ... FOR UPDATE 读取最新的可用数据，在它读取的每一行上设置排他锁。因此，它设置的锁定与搜索的 SQL UPDATE 将在行上设置的锁定相同。

This seems pretty clear that it is all rows that it has to scan.

这似乎很清楚，它必须扫描所有行。

回答by flyingice

The thread is pretty old, just to share my two cents regarding the tests above performed by @Frans

该线程很旧，只是为了分享我关于@Frans 执行的上述测试的两分钱

Connection 1

连接 1

BEGIN;
SELECT * FROM notification WHERE `date` >= '2011-05-03' FOR UPDATE;

Connection 2

连接 2

BEGIN;

SELECT * FROM notification WHERE `date` = '2011-05-02' FOR UPDATE;

The concurrent transaction 2 will be blocked for sure, but the reason is NOTthat the transaction 1 is holding the lock on the whole table. The following explains what has happened behind the scene:

并发事务 2 肯定会被阻塞，但原因不是事务 1 持有整个表的锁。以下解释了幕后发生的事情：

First of all, the default isolation level of the InnoDB storage engine is Repeatable Read. In this case,

首先，InnoDB 存储引擎的默认隔离级别是Repeatable Read. 在这种情况下，

1- When the column used in where condition is not indexed (as the case above):

1- 当 where 条件中使用的列未编入索引时（如上例）：

The engine is obliged to perform a full table scan to filter out the records not matching the criteria. EVERY ROWthat have been scanned are locked in the first place. MySQL may release the locks on those records not matching the where clause later on. It is an optimization for the performance, however, such behavior violates the 2PL constraint.

引擎有义务执行全表扫描以过滤掉不符合条件的记录。每一行已经扫描锁定在首位。稍后，MySQL 可能会释放那些与 where 子句不匹配的记录上的锁。这是对性能的优化，但是，这种行为违反了 2PL 约束。

When transaction 2 starts, as explained, it needs to acquire the X lock for each row retrieved although there exists only a single record (id = 2) matching the where clause. Eventually the transaction 2 will be waiting for the X lock of the first row (id = 1) until the transaction 1 commits or rollbacks.

如上所述，当事务 2 开始时，它需要为检索到的每一行获取 X 锁，尽管只有一条记录 (id = 2) 与 where 子句匹配。最终事务 2 将等待第一行的 X 锁（id = 1），直到事务 1 提交或回滚。

2- When the column used in where condition is a primary index

2- 当 where 条件中使用的列是主索引时

Only the index entry satisfying the criteria is locked. That's why in the comments someone says that some tests are not blocked.

只有满足条件的索引条目才会被锁定。这就是为什么在评论中有人说某些测试没有被阻止。

3 - When the column used in where condition is an index but not unique

3 - 当 where 条件中使用的列是索引但不唯一时

This case is more complicated. 1) The index entry is locked. 2) One X lock is attached to the corresponding primary index. 3) Two gap locks are attached to the non-existing entries right before and after the record matching the search criteria.

这个案子比较复杂。1) 索引条目被锁定。2) 一个 X 锁附加到相应的主索引。3) 在与搜索条件匹配的记录之前和之后的不存在的条目上附加两个间隙锁。

回答by yxc

From mysql official doc:

来自 mysql 官方文档：

A locking read, an UPDATE, or a DELETE generally set record locks on every index record that is scanned in the processing of the SQL statement. It does not matter whether there are WHERE conditions in the statement that would exclude the row.

锁定读取、UPDATE 或 DELETE 通常在 SQL 语句处理中扫描的每个索引记录上设置记录锁定。语句中是否存在排除该行的 WHERE 条件并不重要。

For the case discussed in Frans' answer, all rows are locked because there's a table scan during sql processing:

对于 Frans 的回答中讨论的情况，所有行都被锁定，因为在 sql 处理期间存在表扫描：

If you have no indexes suitable for your statement and MySQL must scan the entire table to process the statement, every row of the table becomes locked, which in turn blocks all inserts by other users to the table. It is important to create good indexes so that your queries do not unnecessarily scan many rows.

如果您没有适合您的语句的索引，并且 MySQL 必须扫描整个表来处理该语句，则该表的每一行都会被锁定，从而阻止其他用户对该表的所有插入。创建好的索引很重要，这样您的查询就不会不必要地扫描很多行。

Check the latest doc here: https://dev.mysql.com/doc/refman/8.0/en/innodb-locks-set.html

在此处查看最新文档：https: //dev.mysql.com/doc/refman/8.0/en/innodb-locks-set.html

回答by r.bhardwaj

It locks all the rows selected by query.

它锁定查询选择的所有行。