Mysql 使用 SELECT 时应锁定多少行 .. FOR UPDATE LIMIT 1

我有一个简单的表，在count列上有一个索引

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

我正在try 这样做

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

这样做的 idea 是让MySQL跳过已经锁定的行，并返回给我下一个"非"锁定行.

然而，根据我的测试，尽管我使用的是LIMIT 1，但看起来所有包含count >= 4的行都是锁定的

据我所知，MySQL不会只锁定‘返回’行，还会锁定它扫描到的所有行，以得出这个结果.

EXPLAIN SELECT * FROM Counts WHERE count >= 4 ORDER BY count LIMIT 1个

+----+-------------+--------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------+
| 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 |
+----+-------------+--------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------+

这就是为什么这3行被锁定了，但我不明白为什么.

然而，如果不是按计数I排序，而是按主键id排序，我得到1

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 |
+----+-------------+--------+------------+-------+---------------+---------+---------+------+------+----------+-------------+

我在count上添加了一个索引并使用order by count，这难道不足以只扫描一行从而锁定它吗？

是我的MySQL docker表现得很奇怪吗？

SELECT @@global.transaction_ISOLATION; == READ-COMMITTED

SELECT @@version == 8.0.33