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

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

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 states: 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

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

113752 次浏览
小开

以下链接来自您发布的文档页面，提供了有关 锁定的更多信息

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

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

小开
最佳答案

我们为什么不试试呢？

建立数据库

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

连接1

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

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

还有积木！

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 UPDATE query can never be changed by another transaction (with an INSERT, UPDATE, or DELETE) while the lock is being held.

小开

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

表格结构:

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

12行插入 INSERT INTO t1 (notid) VALUES (1), (2),..., (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;

The strangest part is that SELECT * FROM t1 WHERE id>5 FOR UPDATE; is 没被封锁, nor are any of

...
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;
...

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

- 剪辑-

这是一个相当特殊的情况，但我能找到的唯一表现出这种行为的地方是:

连接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; is blocked;

SELECT * FROM t1 WHERE id=4 FOR UPDATE;是 没有阻塞。

小开

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

小开

这个帖子已经很老了，只是想和大家分享一下我对@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-当条件中使用的列没有被索引时(如上所示) :

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

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

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

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

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

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

小开

来自 mysql 官方文档:

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

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

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

看看这里最新的文件: https://dev.mysql.com/doc/refman/8.0/en/innodb-locks-set.html

小开

As others have mentioned, SELECT... FOR UPDATE locks all rows encountered in the default isolation level. Try setting the isolation for the session which runs this query to READ COMMITTED, for example precede the query with: set session transaction isolation level read committed;


提交答案