Bug #113472 when use skip locked syntax, different query plan return different result
Submitted: 20 Dec 2023 10:58 Modified: 22 Dec 2023 2:31
Reporter: Demon Chen Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: Locking Severity:S3 (Non-critical)
Version:8.0.24 OS:Any
Assigned to: CPU Architecture:Any

[20 Dec 2023 10:58] Demon Chen
Description:
when I use skip locked to get result sets, I found different query plan will return different results.  

How to repeat:
#table structure
CREATE TABLE `t1` (
  `a` int NOT NULL,
  `b` int DEFAULT NULL,
  PRIMARY KEY (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

insert into t1 values(1,2),(2,2),(3,5),(4,10);
# version
mysql 8.0.24
# transaction_isolation RR

1. index scan 

	s1	s2
t1	begin;	begin;
t2	select * from t1 where b=2 order by a limit 1 for update skip locked;	
t3		select * from t1 where b=2 order by a limit 1 for update skip locked;

query plan:
explain select * from t1 where b=2 order by a limit 1 for update;
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | t1    | NULL       | index | NULL          | PRIMARY | 4       | NULL |    1 |    12.50 | Using where |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
session 1:
+---+------+
| a | b    |
+---+------+
| 1 |    2 |
+---+------+
1 row in set (0.00 sec)
session 2:
+---+------+
| a | b    |
+---+------+
| 2 |    2 |
+---+------+
1 row in set (0.00 sec)

2. full table scan

query plan :
explain select * from t1 ignore index(primary) where b=2 order by a limit 1 for update;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-----------------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-----------------------------+
|  1 | SIMPLE      | t1    | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    8 |    12.50 | Using where; Using filesort |

session 1:
+---+------+
| a | b    |
+---+------+
| 1 |    2 |
+---+------+

session 2:
empty set

Suggested fix:
I  dont know
[20 Dec 2023 13:20] MySQL Verification Team
Hi Mr. Chen,

Thank you for your bug report.

We have tested your test case with latest 8.0 release and discovered that it behaves properly.

Namely, only the locked records as well as the records defining the locked gap are skipped.

Not a bug.
[20 Dec 2023 13:35] Demon Chen
Hi,

If I changed transaction_isolation from rr to rc, it still happend like before.
at this situation, it has no gap lock.
[20 Dec 2023 13:37] MySQL Verification Team
Hi,

That is OK ...... record locks are sufficient.

However, even with READ COMMITTED there ARE gap locks.

Please, analyse carefully status of the InnoDB.
[22 Dec 2023 2:31] Demon Chen
Hi,
I got the root cause.It's nothing to do with the table scan or index scan, the key factor is whether use filesort or not.

if the query plan need use sort buffer or file to do the sort, mysql can not unlock the rows which in the buffer. 
But if you just use limit not use order by , mysql will just lock the rows it needed.
[8 Jan 10:07] MySQL Verification Team
Hi,

Your conclusion is quite correct.