Bug #67745 Too much row locks when using SELECT for UPDATE, LIMIT and ORDER BY
Description:
Hello,
I think i meet a bug when I try to run a "SELECT FOR UPDATE" statement with a "LIMIT" clause and a complex "ORDER BY" clause (i.e using a filesort).
My statement has to use the ORDER BY clause with one column ASC and another DESC. As told in the documentation, the explain plan shows a filesort.
I also use a "LIMIT 1" clause to handle only the first row (many of this process can run in parallel).
I expect row locks on my concerned row, the problem is that all the rows of the request are locked ...
Of course, if i don't use filesort by reducing the order by clause, i only lock the concerned rows.
Is this a bug or not ?
Thank you for your feedback.
How to repeat:
Here is a simple test case :
DROP TABLE TEST;
create table TEST(col1 int, col2 varchar(10), primary key (col1));
insert into TEST values (1,'aaa');
insert into TEST values (2,'bbb');
insert into TEST values (3,'ccc');
insert into TEST values (4,'ddd');
insert into TEST values (5,'eee');
insert into TEST values (6,'fff');
insert into TEST values (7,'aaa');
insert into TEST values (8,'aaa');
insert into TEST values (9,'aaa');
insert into TEST values (10,'aaa');
insert into TEST values (11,'bbb');
commit;
create index IDX1 on TEST (col2);
drop table innodb_lock_monitor;
create table innodb_lock_monitor( i int not null ) engine = innodb;
1) the test that works fine
session #1
----------
start transaction;
select col1,col2 from TEST where col2 = 'aaa' or col2 = 'bbb'
order by col1 desc
LIMIT 1 for update;
+------+------+
| col1 | col2 |
+------+------+
| 11 | bbb |
+------+------+
1 row in set (0.00 sec)
The "show engine innodb status\G" command indicates 1 row lock :
#####################################################################################################################################
---TRANSACTION 13750DF, ACTIVE 12 sec
2 lock struct(s), heap size 376, 1 row lock(s)
MySQL thread id 9, OS thread handle 0x7fbc9b487700, query id 409 localhost root
show engine innodb status
TABLE LOCK table `otest`.`TEST` trx id 13750DF lock mode IX
RECORD LOCKS space id 31 page no 3 n bits 80 index `PRIMARY` of table `otest`.`TEST` trx id 13750DF lock_mode X locks rec but not gap
Record lock, heap no 12 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
0: len 4; hex 8000000b; asc ;;
1: len 6; hex 0000013750d1; asc 7P ;;
2: len 7; hex b3000001d40110; asc ;;
3: len 3; hex 626262; asc bbb;;
#####################################################################################################################################
mysql> explain select col1,col2 from TEST where col2 = 'aaa' or col2 = 'bbb'
-> order by col1 desc
-> LIMIT 1 ;
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
| 1 | SIMPLE | TEST | index | IDX1 | PRIMARY | 4 | NULL | 1 | Using where |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
1 row in set (0.00 sec)
2) Now, the test that doesn't work :
session #1
----------
start transaction;
select col1,col2 from TEST where col2 = 'aaa' or col2 = 'bbb'
order by col1 desc, col2 asc
LIMIT 1 for update;
+------+------+
| col1 | col2 |
+------+------+
| 11 | bbb |
+------+------+
The "show engine innodb status\G" command indicates 14 row locks :
#####################################################################################################################################
---TRANSACTION 137505C, ACTIVE 0 sec
3 lock struct(s), heap size 376, 14 row lock(s)
MySQL thread id 7, OS thread handle 0x7fbd2c60d700, query id 273 localhost root
show engine innodb status
TABLE LOCK table `otest`.`TEST` trx id 137505C lock mode IX
RECORD LOCKS space id 21 page no 4 n bits 80 index `IDX1` of table `otest`.`TEST` trx id 137505C lock_mode X locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 3; hex 616161; asc aaa;;
1: len 4; hex 80000001; asc ;;
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 3; hex 616161; asc aaa;;
1: len 4; hex 80000007; asc ;;
[ ... 5 lines "Record lock" more ... ]
RECORD LOCKS space id 21 page no 3 n bits 80 index `PRIMARY` of table `otest`.`TEST` trx id 137505C lock_mode X locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
0: len 4; hex 80000001; asc ;;
1: len 6; hex 0000013750c7; asc 7P ;;
2: len 7; hex a9000002030110; asc ;;
3: len 3; hex 616161; asc aaa;;
Record lock, heap no 3 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
0: len 4; hex 80000002; asc ;;
1: len 6; hex 0000013750c8; asc 7P ;;
2: len 7; hex aa000002250110; asc % ;;
3: len 3; hex 626262; asc bbb;;
[ ... 5 lines "Record lock" more ... ]
#####################################################################################################################################
Here is the explain plan :
mysql> explain select col1,col2 from TEST where col2 = 'aaa' or col2 = 'bbb'
-> order by col1 desc, col2 asc
-> LIMIT 1;
+----+-------------+-------+-------+---------------+------+---------+------+------+------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+------+---------+------+------+------------------------------------------+
| 1 | SIMPLE | TEST | range | IDX1 | IDX1 | 13 | NULL | 7 | Using where; Using index; Using filesort |
+----+-------------+-------+-------+---------------+------+---------+------+------+------------------------------------------+
1 row in set (0.00 sec)
Portions of this website are copyright © 2001, 2002 The PHP Group
Page generated in 0.051 sec. using MySQL 8.4.7-u6-cloud
Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the
respective copyright holders.
It is not reviewed in advance
by Oracle and does not necessarily represent the opinion of
Oracle or any other party.
Description: Hello, I think i meet a bug when I try to run a "SELECT FOR UPDATE" statement with a "LIMIT" clause and a complex "ORDER BY" clause (i.e using a filesort). My statement has to use the ORDER BY clause with one column ASC and another DESC. As told in the documentation, the explain plan shows a filesort. I also use a "LIMIT 1" clause to handle only the first row (many of this process can run in parallel). I expect row locks on my concerned row, the problem is that all the rows of the request are locked ... Of course, if i don't use filesort by reducing the order by clause, i only lock the concerned rows. Is this a bug or not ? Thank you for your feedback. How to repeat: Here is a simple test case : DROP TABLE TEST; create table TEST(col1 int, col2 varchar(10), primary key (col1)); insert into TEST values (1,'aaa'); insert into TEST values (2,'bbb'); insert into TEST values (3,'ccc'); insert into TEST values (4,'ddd'); insert into TEST values (5,'eee'); insert into TEST values (6,'fff'); insert into TEST values (7,'aaa'); insert into TEST values (8,'aaa'); insert into TEST values (9,'aaa'); insert into TEST values (10,'aaa'); insert into TEST values (11,'bbb'); commit; create index IDX1 on TEST (col2); drop table innodb_lock_monitor; create table innodb_lock_monitor( i int not null ) engine = innodb; 1) the test that works fine session #1 ---------- start transaction; select col1,col2 from TEST where col2 = 'aaa' or col2 = 'bbb' order by col1 desc LIMIT 1 for update; +------+------+ | col1 | col2 | +------+------+ | 11 | bbb | +------+------+ 1 row in set (0.00 sec) The "show engine innodb status\G" command indicates 1 row lock : ##################################################################################################################################### ---TRANSACTION 13750DF, ACTIVE 12 sec 2 lock struct(s), heap size 376, 1 row lock(s) MySQL thread id 9, OS thread handle 0x7fbc9b487700, query id 409 localhost root show engine innodb status TABLE LOCK table `otest`.`TEST` trx id 13750DF lock mode IX RECORD LOCKS space id 31 page no 3 n bits 80 index `PRIMARY` of table `otest`.`TEST` trx id 13750DF lock_mode X locks rec but not gap Record lock, heap no 12 PHYSICAL RECORD: n_fields 4; compact format; info bits 0 0: len 4; hex 8000000b; asc ;; 1: len 6; hex 0000013750d1; asc 7P ;; 2: len 7; hex b3000001d40110; asc ;; 3: len 3; hex 626262; asc bbb;; ##################################################################################################################################### mysql> explain select col1,col2 from TEST where col2 = 'aaa' or col2 = 'bbb' -> order by col1 desc -> LIMIT 1 ; +----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+ | 1 | SIMPLE | TEST | index | IDX1 | PRIMARY | 4 | NULL | 1 | Using where | +----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+ 1 row in set (0.00 sec) 2) Now, the test that doesn't work : session #1 ---------- start transaction; select col1,col2 from TEST where col2 = 'aaa' or col2 = 'bbb' order by col1 desc, col2 asc LIMIT 1 for update; +------+------+ | col1 | col2 | +------+------+ | 11 | bbb | +------+------+ The "show engine innodb status\G" command indicates 14 row locks : ##################################################################################################################################### ---TRANSACTION 137505C, ACTIVE 0 sec 3 lock struct(s), heap size 376, 14 row lock(s) MySQL thread id 7, OS thread handle 0x7fbd2c60d700, query id 273 localhost root show engine innodb status TABLE LOCK table `otest`.`TEST` trx id 137505C lock mode IX RECORD LOCKS space id 21 page no 4 n bits 80 index `IDX1` of table `otest`.`TEST` trx id 137505C lock_mode X locks rec but not gap Record lock, heap no 2 PHYSICAL RECORD: n_fields 2; compact format; info bits 0 0: len 3; hex 616161; asc aaa;; 1: len 4; hex 80000001; asc ;; Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 0 0: len 3; hex 616161; asc aaa;; 1: len 4; hex 80000007; asc ;; [ ... 5 lines "Record lock" more ... ] RECORD LOCKS space id 21 page no 3 n bits 80 index `PRIMARY` of table `otest`.`TEST` trx id 137505C lock_mode X locks rec but not gap Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 0 0: len 4; hex 80000001; asc ;; 1: len 6; hex 0000013750c7; asc 7P ;; 2: len 7; hex a9000002030110; asc ;; 3: len 3; hex 616161; asc aaa;; Record lock, heap no 3 PHYSICAL RECORD: n_fields 4; compact format; info bits 0 0: len 4; hex 80000002; asc ;; 1: len 6; hex 0000013750c8; asc 7P ;; 2: len 7; hex aa000002250110; asc % ;; 3: len 3; hex 626262; asc bbb;; [ ... 5 lines "Record lock" more ... ] ##################################################################################################################################### Here is the explain plan : mysql> explain select col1,col2 from TEST where col2 = 'aaa' or col2 = 'bbb' -> order by col1 desc, col2 asc -> LIMIT 1; +----+-------------+-------+-------+---------------+------+---------+------+------+------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+---------------+------+---------+------+------+------------------------------------------+ | 1 | SIMPLE | TEST | range | IDX1 | IDX1 | 13 | NULL | 7 | Using where; Using index; Using filesort | +----+-------------+-------+-------+---------------+------+---------+------+------+------------------------------------------+ 1 row in set (0.00 sec)