当使用MySQL的FOR UPDATE锁定时，到底锁定了什么？

Question

这不是完整/正确的 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.html 指出： 如果您将 FOR UPDATE 与使用页锁或行锁的存储引擎一起使用，则查询检查的行将被写入锁定，直到当前事务结束，

此处仅返回由 MySQL 锁定的一条记录，还是它必须扫描到的所有记录找到单个记录？

Answer 1

我们为什么不尝试一下呢？

设置数据库

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');

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

连接 1

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

连接 2

BEGIN;

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

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

事实上它确实会阻塞。

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

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

块！

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

Answer 2

该线程相当古老，只是为了分享我对 @Frans 执行的上述测试的两分钱

连接 1

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

连接 2

BEGIN;

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

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

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

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

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

正如所解释的，当事务 2 启动时，尽管只存在与 where 子句匹配的单个记录 (id = 2)，但它需要为检索的每一行获取 X 锁。最终事务2将等待第一行（id = 1）的X锁，直到事务1提交或回滚。

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

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

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

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

Answer 3

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

表结构：

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

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

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

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

最奇怪的部分是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;
...

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

-编辑-

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

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

来自连接2 :

SELECT * FROM t1 WHERE id=2 FOR UPDATE; 被阻止；

SELECT * FROM t1 WHERE id=4 FOR UPDATE; 不会被阻止。

Answer 4

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

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

这看起来很清楚，它必须扫描所有行。

Answer 5

来自mysql官方文档：

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

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

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

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

Answer 6

正如其他人提到的，SELECT...FOR UPDATE 锁定在默认隔离级别中遇到的所有行。尝试将运行此查询的会话的隔离设置为 READ COMMITTED，例如在查询之前添加：set session transactionisolation level read commit;

Answer 7

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