How many row should lock when using SELECT .. FOR UPDATE LIMIT 1

Question

I have a simple table with an index on the count column

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

I'm attempting this

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

The idea is to let MySQL skip the already locked row and give me back the next "non" locked row.

However with my testing it looks like all the rows with count >= 4 are locked even though I use a LIMIT 1

From what I understand MySQL will not lock the 'returned' row only but all the row it has scanned to arrived at this result.

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

So here is why the 3 rows are locked but I don't understand why.

However if instead of ordering by count I order by the PRIMARY KEY id I get 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 |
+----+-------------+--------+------------+-------+---------------+---------+---------+------+------+----------+-------------+

Isn't the fact that I have added an index on count and using order by count enough to have only one row scanned and thus locked ?

Is it my mysql docker that is acting strange ?

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

SELECT @@version == 8.0.33

Answer 1

So here is why the 3 rows are locked but I don't understand why.

However if instead of ordering by count I order by the PRIMARY KEY id I get 1

Using InnoDB Engine it locks every row it had to look at.

• UNIQUE index on the column (Primary Key is considered unique in MySQL)

  Only one row need , locked.

• A non-unique INDEX on the column
  It must lock all the rows with that value.

• No index on the column.
  If no index present it has to scan all table, so all rows are locked.