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
2
Answers
@Alexis, You might want to check this – https://dev.mysql.com/doc/refman/8.0/en/limit-optimization.html
Especially this:
Other points are also important…
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.
Keep in mind that this approach might have performance implications, as it involves two queries instead of one