带count索引的表执行SELECT...FOR UPDATE LIMIT1为何锁定多行？

表结构与测试数据 #

建表语句：

CREATE TABLE `Counts` (
  `id` bigint NOT NULL,
  `count` int NOT NULL,
  PRIMARY KEY (`id`),
  KEY `count_i` (`count`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

插入测试数据：

INSERT INTO Counts (id, count) VALUES (1, 4);
INSERT INTO Counts (id, count) VALUES (2, 4);
INSERT INTO Counts (id, count) VALUES (3, 4);
INSERT INTO Counts (id, count) VALUES (4, 2);
INSERT INTO Counts (id, count) VALUES (5, 2);

执行的SQL与问题现象 #

执行以下SQL：

SELECT * FROM Counts WHERE count >= 4 ORDER BY count LIMIT 1 FOR UPDATE of Counts SKIP LOCKED;

预期：跳过已锁定行，返回1条未锁定的符合条件的行。
实际：所有count >=4的3行都被锁定，即使使用了LIMIT 1。

执行计划分析 #

按count排序的执行计划 #

+----+-------------+--------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------+
| id | select_type | table  | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+--------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | Counts | NULL       | range | count_i       | count_i | 4       | NULL |    3 |   100.00 | Using where; Using index |
+----+-------------+--------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------+

该计划显示MySQL通过二级索引count_i进行range扫描，共扫描3行，这解释了为什么3行都被锁定。

按主键id排序的执行计划 #

mysql> EXPLAIN SELECT * FROM Counts WHERE count >= 4 ORDER BY id LIMIT 1;
+----+-------------+--------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table  | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
+----+-------------+--------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | Counts | NULL       | index | count_i       | PRIMARY | 8       | NULL |    1 |    60.00 | Using where |
+----+-------------+--------+------------+-------+---------------+---------+---------+------+------+----------+-------------+

该计划显示MySQL走主键索引，仅扫描1行，因此只会锁定1行。

环境信息 #

• 事务隔离级别：READ-COMMITTED
• MySQL版本：8.0.33

原因解析 #

1. 二级索引range扫描的锁定逻辑：
   当使用ORDER BY count LIMIT 1时，MySQL选择二级索引count_i执行查询，因为该索引既能满足count >=4的过滤条件，又能直接提供排序依据。但二级索引中所有count=4的条目是连续存储的，MySQL需要扫描所有这些条目（共3条）来确定排序后的结果（虽然count值相同，但InnoDB会按主键id作为排序的补充依据），之后再取LIMIT 1的结果。而InnoDB在执行FOR UPDATE时，会锁定扫描过程中访问到的所有索引条目对应的主键行，因此这3行都会被锁定。

2. 主键索引扫描的锁定逻辑：
   当改为ORDER BY id LIMIT 1时，MySQL选择走主键索引，按id顺序逐行扫描，每扫描一行就检查是否满足count >=4，找到第一个符合条件的行后立即停止扫描，因此仅锁定这1行。

这并非Docker环境的问题，是InnoDB处理二级索引range扫描结合FOR UPDATE时的锁定机制导致的。

内容的提问来源于stack exchange，提问作者Alexis