MySQL Bugs: #113472: when use skip locked syntax, different query plan return different result

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

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

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.

Hi,

If I changed transaction_isolation from rr to rc, it still happend like before.
at this situation, it has no gap lock.

Hi,

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

However, even with READ COMMITTED there ARE gap locks.

Please, analyse carefully status of the InnoDB.

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.

Hi,

Your conclusion is quite correct.