| Bug #100646 | select ... skip locked work uncorrectly with secondary index | ||
|---|---|---|---|
| Submitted: | 26 Aug 2020 15:26 | Modified: | 28 Aug 2020 12:41 |
| Reporter: | Ye Jinrong | Email Updates: | |
| Status: | Not a Bug | Impact on me: | |
| Category: | MySQL Server: InnoDB storage engine | Severity: | S3 (Non-critical) |
| Version: | 8.0.21 | OS: | Any (10.15.6 (19G73)) |
| Assigned to: | CPU Architecture: | Any | |
[27 Aug 2020 12:24]
MySQL Verification Team
Hi Mr. Jinrong, Thank you for your bug report. However, your report is not clear enough for us to repeat it. First of all, we do not see which of the transactions are executed simultaneously. You need to provide of what transactions are active and which commands are executed in session1, session2, session3 and so on ... Also, this does not seem to be a bug at all. If rows with values of 20 and 30 are already locked and you look for rows where the same value is greater than 30, then the returned error message is correct. Simply, there are no rows that are not locked and that do satisfy the filtering criteria. Also, provide us with innodb status with locks enabled, as this is much more detailed than the output from Performance Schema. Most likely, this is not a bug, but we need your feedback to confirm our opinion.
[28 Aug 2020 2:58]
Ye Jinrong
# 1. case1(correctly) ## session1: [root@yejr.run] [dblocks]>select * from t where c1=10 for update; +----+------+------+------+ | c1 | c2 | c3 | c4 | +----+------+------+------+ | 10 | 10 | 10 | 10 | +----+------+------+------+ 1 row in set (0.00 sec) ## session2: [root@yejr.run] [dblocks]>select * from t where c1>=10 for update skip locked; +----+------+------+------+ | c1 | c2 | c3 | c4 | +----+------+------+------+ | 20 | 20 | 20 | 20 | | 30 | 30 | 30 | 30 | +----+------+------+------+ 2 rows in set (0.00 sec) # 2. case2(uncorrectly) ## session1: [root@yejr.run] [dblocks]>select * from t where c1=10 for update; +----+------+------+------+ | c1 | c2 | c3 | c4 | +----+------+------+------+ | 10 | 10 | 10 | 10 | +----+------+------+------+ 1 row in set (0.00 sec) ## session2: [root@yejr.run] [dblocks]>select * from t where c2>=10 for update skip locked; ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction ## session3: [root@yejr.run] [dblocks]>show engine innodb status\G ... ---TRANSACTION 57648, ACTIVE 14 sec 2 lock struct(s), heap size 1136, 1 row lock(s) MySQL thread id 34, OS thread handle 123145389621248, query id 592 localhost root TABLE LOCK table `dblocks`.`t` trx id 57648 lock mode IX RECORD LOCKS space id 345 page no 4 n bits 72 index PRIMARY of table `dblocks`.`t` trx id 57648 lock_mode X locks rec but not gap Record lock, heap no 3 PHYSICAL RECORD: n_fields 6; compact format; info bits 0 0: len 4; hex 8000000a; asc ;; 1: len 6; hex 00000000dc4d; asc M;; 2: len 7; hex 810000020e011d; asc ;; 3: len 4; hex 8000000a; asc ;; 4: len 4; hex 8000000a; asc ;; 5: len 4; hex 8000000a; asc ;; ---TRANSACTION 57647, ACTIVE 189 sec starting index read mysql tables in use 2, locked 2 LOCK WAIT 4 lock struct(s), heap size 1136, 5 row lock(s) MySQL thread id 33, OS thread handle 123145390227456, query id 593 localhost root executing select * from t where c2>=10 for update skip locked ------- TRX HAS BEEN WAITING 8 SEC FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 345 page no 4 n bits 72 index PRIMARY of table `dblocks`.`t` trx id 57647 lock_mode X locks rec but not gap waiting Record lock, heap no 3 PHYSICAL RECORD: n_fields 6; compact format; info bits 0 0: len 4; hex 8000000a; asc ;; 1: len 6; hex 00000000dc4d; asc M;; 2: len 7; hex 810000020e011d; asc ;; 3: len 4; hex 8000000a; asc ;; 4: len 4; hex 8000000a; asc ;; 5: len 4; hex 8000000a; asc ;; ------------------ TABLE LOCK table `dblocks`.`t` trx id 57647 lock mode IX RECORD LOCKS space id 345 page no 4 n bits 72 index PRIMARY of table `dblocks`.`t` trx id 57647 lock_mode X Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0 0: len 8; hex 73757072656d756d; asc supremum;; Record lock, heap no 4 PHYSICAL RECORD: n_fields 6; compact format; info bits 0 0: len 4; hex 80000014; asc ;; 1: len 6; hex 00000000dc4d; asc M;; 2: len 7; hex 810000020e012a; asc *;; 3: len 4; hex 80000014; asc ;; 4: len 4; hex 80000014; asc ;; 5: len 4; hex 80000014; asc ;; Record lock, heap no 5 PHYSICAL RECORD: n_fields 6; compact format; info bits 0 0: len 4; hex 8000001e; asc ;; 1: len 6; hex 00000000dc4d; asc M;; 2: len 7; hex 810000020e0137; asc 7;; 3: len 4; hex 8000001e; asc ;; 4: len 4; hex 8000001e; asc ;; 5: len 4; hex 8000001e; asc ;; RECORD LOCKS space id 345 page no 5 n bits 72 index c2 of table `dblocks`.`t` trx id 57647 lock_mode X Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 0 0: len 4; hex 8000000a; asc ;; 1: len 4; hex 8000000a; asc ;; RECORD LOCKS space id 345 page no 4 n bits 72 index PRIMARY of table `dblocks`.`t` trx id 57647 lock_mode X locks rec but not gap waiting Record lock, heap no 3 PHYSICAL RECORD: n_fields 6; compact format; info bits 0 0: len 4; hex 8000000a; asc ;; 1: len 6; hex 00000000dc4d; asc M;; 2: len 7; hex 810000020e011d; asc ;; 3: len 4; hex 8000000a; asc ;; 4: len 4; hex 8000000a; asc ;; 5: len 4; hex 8000000a; asc ;; ... # 3. case3(correctly) ## session1: [root@yejr.run] [dblocks]>select * from t where c2=10 for update; +----+------+------+------+ | c1 | c2 | c3 | c4 | +----+------+------+------+ | 10 | 10 | 10 | 10 | +----+------+------+------+ 1 row in set (0.00 sec) ## session2: [root@yejr.run] [dblocks]>select * from t where c1>=10 for update skip locked; +----+------+------+------+ | c1 | c2 | c3 | c4 | +----+------+------+------+ | 20 | 20 | 20 | 20 | | 30 | 30 | 30 | 30 | +----+------+------+------+ 2 rows in set (0.01 sec) # 4. case4(uncorrectly) ## session1: [root@yejr.run] [dblocks]>select * from t where c2=10 for update; +----+------+------+------+ | c1 | c2 | c3 | c4 | +----+------+------+------+ | 10 | 10 | 10 | 10 | +----+------+------+------+ 1 row in set (0.00 sec) ## session2: [root@yejr.run] [dblocks]>select * from t where c2>=10 for update skip locked; ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction ## session3: [root@yejr.run] [dblocks]>show engine innodb status\G ... ---TRANSACTION 57653, ACTIVE 2 sec starting index read mysql tables in use 2, locked 2 LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s) MySQL thread id 33, OS thread handle 123145390227456, query id 606 localhost root executing select * from t where c2>=10 for update skip locked ------- TRX HAS BEEN WAITING 2 SEC FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 345 page no 5 n bits 72 index c2 of table `dblocks`.`t` trx id 57653 lock_mode X waiting Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 0 0: len 4; hex 8000000a; asc ;; 1: len 4; hex 8000000a; asc ;; ------------------ TABLE LOCK table `dblocks`.`t` trx id 57653 lock mode IX RECORD LOCKS space id 345 page no 5 n bits 72 index c2 of table `dblocks`.`t` trx id 57653 lock_mode X waiting Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 0 0: len 4; hex 8000000a; asc ;; 1: len 4; hex 8000000a; asc ;; ---TRANSACTION 57652, ACTIVE 11 sec 3 lock struct(s), heap size 1136, 2 row lock(s) MySQL thread id 34, OS thread handle 123145389621248, query id 604 localhost root TABLE LOCK table `dblocks`.`t` trx id 57652 lock mode IX RECORD LOCKS space id 345 page no 5 n bits 72 index c2 of table `dblocks`.`t` trx id 57652 lock_mode X locks rec but not gap Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 0 0: len 4; hex 8000000a; asc ;; 1: len 4; hex 8000000a; asc ;; RECORD LOCKS space id 345 page no 4 n bits 72 index PRIMARY of table `dblocks`.`t` trx id 57652 lock_mode X locks rec but not gap Record lock, heap no 3 PHYSICAL RECORD: n_fields 6; compact format; info bits 0 0: len 4; hex 8000000a; asc ;; 1: len 6; hex 00000000dc4d; asc M;; 2: len 7; hex 810000020e011d; asc ;; 3: len 4; hex 8000000a; asc ;; 4: len 4; hex 8000000a; asc ;; 5: len 4; hex 8000000a; asc ;; ...
[28 Aug 2020 12:41]
MySQL Verification Team
Hi Mr. Jinrong, Thank you for your feedback. Your feedback confirmed out opinion.Namely, this is not a bug. All the locks are record locks, so that a query, that tries to return records while skipping the locked ones, it can't return a single record, since all those that are greater or equal to 20 are already locked. Hence, it returns the error message. Not a bug.
Description: select ... skip locked work well with primary key, but it work uncorrectly with secondary index. How to repeat: [root@yejr.run] [dblocks]>select version(); +-----------+ | version() | +-----------+ | 8.0.21 | +-----------+ [root@yejr.run] [dblocks]>show create table t\G *************************** 1. row *************************** Table: t Create Table: CREATE TABLE `t` ( `c1` int NOT NULL, `c2` int DEFAULT NULL, `c3` int DEFAULT NULL, `c4` int DEFAULT NULL, PRIMARY KEY (`c1`), UNIQUE KEY `c2` (`c2`), KEY `c3` (`c3`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; [root@yejr.run] [dblocks]>select * from t; +----+------+------+------+ | c1 | c2 | c3 | c4 | +----+------+------+------+ | 1 | 1 | 1 | 1 | | 10 | 10 | 10 | 10 | | 20 | 20 | 20 | 20 | | 30 | 30 | 30 | 30 | +----+------+------+------+ session1: [root@yejr.run] [dblocks]>begin; select * from t where c1=30 for update; Query OK, 0 rows affected (0.00 sec) +----+------+------+------+ | c1 | c2 | c3 | c4 | +----+------+------+------+ | 30 | 30 | 30 | 30 | +----+------+------+------+ case1: with primary key, it work correctly, skip the row data(c1=30) session2: [root@yejr.run] [dblocks]>begin; select * from t where c1 >= 20 for update skip locked; +----+------+------+------+ | c1 | c2 | c3 | c4 | +----+------+------+------+ | 20 | 20 | 20 | 20 | +----+------+------+------+ case2: with secondary key(c2 is an unique key), it work uncorrectly, can not return the other rows [root@yejr.run] [dblocks]>begin; select * from t where c2 >= 20 for update skip locked; ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction and the row locks status are: [root@yejr.run] [dblocks]>select THREAD_ID, concat(OBJECT_SCHEMA,'.',OBJECT_NAME,'.',ifnull(INDEX_NAME,'')) AS LOCK_OBJ,LOCK_TYPE,LOCK_MODE,LOCK_STATUS,LOCK_DATA from performance_schema.data_locks; +-----------+-------------------+-----------+---------------+-------------+-----------+ | THREAD_ID | LOCK_OBJ | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA | +-----------+-------------------+-----------+---------------+-------------+-----------+ | 77 | dblocks.t. | TABLE | IX | GRANTED | NULL | | 77 | dblocks.t.c2 | RECORD | X | GRANTED | 20, 20 | | 77 | dblocks.t.c2 | RECORD | X | GRANTED | 30, 30 | | 77 | dblocks.t.PRIMARY | RECORD | X,REC_NOT_GAP | GRANTED | 20 | | 77 | dblocks.t.PRIMARY | RECORD | X,REC_NOT_GAP | WAITING | 30 | | 80 | dblocks.t. | TABLE | IX | GRANTED | NULL | | 80 | dblocks.t.PRIMARY | RECORD | X,REC_NOT_GAP | GRANTED | 30 | +-----------+-------------------+-----------+---------------+-------------+-----------+ case3: with secondary key(c3 is an non-unique key), it work uncorrectly, can not return the other rows [root@yejr.run] [dblocks]>begin; select * from t where c3 >= 20 for update skip locked; ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction and the row locks status are: [root@yejr.run] [dblocks]>select THREAD_ID, concat(OBJECT_SCHEMA,'.',OBJECT_NAME,'.',ifnull(INDEX_NAME,'')) AS LOCK_OBJ,LOCK_TYPE,LOCK_MODE,LOCK_STATUS,LOCK_DATA from performance_schema.data_locks; +-----------+-------------------+-----------+---------------+-------------+-----------+ | THREAD_ID | LOCK_OBJ | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA | +-----------+-------------------+-----------+---------------+-------------+-----------+ | 77 | dblocks.t. | TABLE | IX | GRANTED | NULL | | 77 | dblocks.t.c3 | RECORD | X | GRANTED | 20, 20 | | 77 | dblocks.t.c3 | RECORD | X | GRANTED | 30, 30 | | 77 | dblocks.t.PRIMARY | RECORD | X,REC_NOT_GAP | GRANTED | 20 | | 77 | dblocks.t.PRIMARY | RECORD | X,REC_NOT_GAP | WAITING | 30 | | 80 | dblocks.t. | TABLE | IX | GRANTED | NULL | | 80 | dblocks.t.PRIMARY | RECORD | X,REC_NOT_GAP | GRANTED | 30 | +-----------+-------------------+-----------+---------------+-------------+-----------+ case4: when session1 lock with secondary key, no more rows return in session2 unless it lock with primary key session1: [root@yejr.run] [dblocks]>begin; select * from t where c2=30 for update; Query OK, 0 rows affected (0.00 sec) +----+------+------+------+ | c1 | c2 | c3 | c4 | +----+------+------+------+ | 30 | 30 | 30 | 30 | +----+------+------+------+ session2: lock with primary key, correctly [root@yejr.run] [dblocks]>begin; select * from t where c1 >= 20 for update skip locked; Query OK, 0 rows affected (0.00 sec) +----+------+------+------+ | c1 | c2 | c3 | c4 | +----+------+------+------+ | 20 | 20 | 20 | 20 | +----+------+------+------+ but when change the command as lock with secondary key(c2 or c3 is the same result), no more rows return session2: lock with secondary key, uncorrectly [root@yejr.run] [dblocks]>begin; select * from t where c2 >= 20 for update skip locked; ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction Suggested fix: skip the locked rows, return all other non-locked rows.