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)
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)