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.

Answer 2

@Alexis, You might want to check this - https://dev.mysql.com/doc/refman/8.0/en/limit-optimization.html

Especially this:

If you combine LIMIT row_count with ORDER BY, MySQL stops sorting as soon as it has found the first row_count rows of the sorted result, rather than sorting the entire result. If ordering is done by using an index, this is very fast. If a filesort must be done, all rows that match the query without the LIMIT clause are selected, and most or all of them are sorted, before the first row_count are found. After the initial rows have been found, MySQL does not sort any remainder of the result set.

Other points are also important...

Answer 3

InnoDB locks all records it encounters while scanning the range, not just the ones that match the condition (even if you use LIMIT 1). To avoid locking all rows, you can try use nested subquery.

SELECT * FROM Counts WHERE id = (
      SELECT id FROM Counts WHERE count >= 4 ORDER BY count LIMIT 1
    ) FOR UPDATE;

Keep in mind that this approach might have performance implications, as it involves two queries instead of one