| Bug #96437 | multi_range select for update with a partition table may lock other rec | ||
|---|---|---|---|
| Submitted: | 6 Aug 2019 11:58 | Modified: | 6 Mar 2020 0:46 |
| Reporter: | yayun zhou | Email Updates: | |
| Status: | Not a Bug | Impact on me: | |
| Category: | MySQL Server: Partitions | Severity: | S3 (Non-critical) |
| Version: | 5.7.22 | OS: | Red Hat (6.5) |
| Assigned to: | CPU Architecture: | x86 | |
| Tags: | multi_range partition rec_lock | ||
[7 Aug 2019 12:34]
MySQL Verification Team
Hi Mr. yoga, Thank you for your bug report. I ran your test case and these are the locks that are held. Please, let us know what is wrong with them: ------------ TRANSACTIONS ------------ Trx id counter 8005 Purge done for trx's n:o < 7998 undo n:o < 0 state: running but idle History list length 10 Total number of lock structs in row lock hash table 3 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 281479383576864, not started 0 lock struct(s), heap size 1160, 0 row lock(s) ---TRANSACTION 8004, ACTIVE 67 sec starting index read mysql tables in use 1, locked 1 LOCK WAIT 2 lock struct(s), heap size 1160, 1 row lock(s) MySQL thread id 3, OS thread handle 123145532420096, query id 40 localhost sinisa Sending data SELECT khh,gtid FROM t1 where KHH = 100010000234 for update ------- TRX HAS BEEN WAITING 67 SEC FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 137 page no 3 n bits 72 index PRIMARY of table `test`.`t1` /* Partition `part_5` */ trx id 8004 lock_mode X waiting Record lock, heap no 2 PHYSICAL RECORD: n_fields 7; compact format; info bits 0 0: len 8; hex 80000017490f7f6a; asc I j;; 1: len 8; hex 3130303030323334; asc 10000234;; 2: len 6; hex 000000001f1f; asc ;; 3: len 7; hex bf000001a20110; asc ;; 4: len 8; hex 80000017490f7f6a; asc I j;; 5: len 4; hex 7fffffff; asc ;; 6: len 8; hex 8000000000000005; asc ;; ------------------ TABLE LOCK table `test`.`t1` /* Partition `part_5` */ trx id 8004 lock mode IX RECORD LOCKS space id 137 page no 3 n bits 72 index PRIMARY of table `test`.`t1` /* Partition `part_5` */ trx id 8004 lock_mode X waiting Record lock, heap no 2 PHYSICAL RECORD: n_fields 7; compact format; info bits 0 0: len 8; hex 80000017490f7f6a; asc I j;; 1: len 8; hex 3130303030323334; asc 10000234;; 2: len 6; hex 000000001f1f; asc ;; 3: len 7; hex bf000001a20110; asc ;; 4: len 8; hex 80000017490f7f6a; asc I j;; 5: len 4; hex 7fffffff; asc ;; 6: len 8; hex 8000000000000005; asc ;; ---TRANSACTION 7999, ACTIVE 250 sec 4 lock struct(s), heap size 1160, 5 row lock(s) MySQL thread id 4, OS thread handle 123145548259328, query id 34 localhost sinisa TABLE LOCK table `test`.`t1` /* Partition `part_1` */ trx id 7999 lock mode IX RECORD LOCKS space id 133 page no 3 n bits 72 index PRIMARY of table `test`.`t1` /* Partition `part_1` */ trx id 7999 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 3 PHYSICAL RECORD: n_fields 7; compact format; info bits 0 0: len 8; hex 800000174876e9c8; asc Hv ;; 1: len 8; hex 3030303030343536; asc 00000456;; 2: len 6; hex 000000001f20; asc ;; 3: len 7; hex c0000001370110; asc 7 ;; 4: len 8; hex 800000174876e9c8; asc Hv ;; 5: len 4; hex 7fffffff; asc ;; 6: len 8; hex 8000000000000006; asc ;; Record lock, heap no 4 PHYSICAL RECORD: n_fields 7; compact format; info bits 0 0: len 8; hex 800000174876e9c9; asc Hv ;; 1: len 8; hex 3030303030343537; asc 00000457;; 2: len 6; hex 000000001f28; asc (;; 3: len 7; hex a7000001120110; asc ;; 4: len 8; hex 800000174876e9c9; asc Hv ;; 5: len 4; hex 7fffffff; asc ;; 6: len 8; hex 800000000000000b; asc ;; TABLE LOCK table `test`.`t1` /* Partition `part_5` */ trx id 7999 lock mode IX RECORD LOCKS space id 137 page no 3 n bits 72 index PRIMARY of table `test`.`t1` /* Partition `part_5` */ trx id 7999 lock_mode X Record lock, heap no 2 PHYSICAL RECORD: n_fields 7; compact format; info bits 0 0: len 8; hex 80000017490f7f6a; asc I j;; 1: len 8; hex 3130303030323334; asc 10000234;; 2: len 6; hex 000000001f1f; asc ;; 3: len 7; hex bf000001a20110; asc ;; 4: len 8; hex 80000017490f7f6a; asc I j;; 5: len 4; hex 7fffffff; asc ;; 6: len 8; hex 8000000000000005; asc ;; Record lock, heap no 3 PHYSICAL RECORD: n_fields 7; compact format; info bits 0 0: len 8; hex 80000017490f80b7; asc I ;; 1: len 8; hex 3130303030353637; asc 10000567;; 2: len 6; hex 000000001f27; asc ';; 3: len 7; hex a6000001180110; asc ;; 4: len 8; hex 80000017490f80b7; asc I ;; 5: len 4; hex 7fffffff; asc ;; 6: len 8; hex 800000000000000a; asc ;;
[7 Aug 2019 12:34]
MySQL Verification Team
We are eagerly waiting on your feedback.
[9 Aug 2019 2:57]
yayun zhou
If you commit the trx 8005, the trx 7999 will also acquire the rec lock(space id 137 page no 3 heap no 2) and until the trx end release the rec lock. I think if the trx 7999 select sql finished ,the trx 7999 only acquire two rec locks(space id 133 page no 3 heap no 3 , space id 133 page no 3 heap no 4). So I think it is a bug.
[9 Aug 2019 12:33]
MySQL Verification Team
Hi, Transactions hold locks until they are committed, or until some other condition occurs, like lock timeout, deadlock and similar. Hence, transaction 8005 MUST hold the lock, as it is not committed yet. Transaction 7999 is not committed yet, so it has to hold all these locks until that command occurs. Hence, this is a behaviour as designed, and fully described in our Reference Manual, chapter on the InnoDB storage engine. Hence, not a bug.
[10 Aug 2019 2:08]
yayun zhou
I'm not agree!!!
1. The trx 7999 holds the rec lock, the KHH = 100010000234, and PHYSICAL RECORD is space id 137 page no 3 heap no 2
2. The sql in session 2(trx 7999) is: SELECT khh,gtid FROM edf_dormancy_acct where ((KHH between (100000000000 + (100 * 4)) and ((100000000000 + (100 * 4)) + 99)) or (KHH between (100010000000 + (100 * 4)) and ((100010000000 + (100 * 4)) + 99)) and (MSG_CODE = -(1)) ) FOR UPDATE;
3. the rec KHH = 100010000234 is not in the where condition.
As a comparison:
--Session 1;
Use abczyy_part;
start transaction;
SELECT khh,gtid FROM edf_dormancy_acct where ((KHH between (100000000000 + (100 * 4)) and ((100000000000 + (100 * 4)) + 99)) or (KHH between (100010000000 + (100 * 4)) and ((100010000000 + (100 * 4)) + 99)) and (MSG_CODE = -(1)) ) FOR UPDATE;
--Session 2;
Use abczyy_part;
SELECT khh,gtid FROM edf_dormancy_acct where KHH = 100010000234 for update;
(session 2 will acquire the rec 100010000234 immediately rather then wait lock_wait_timeout.)
commit;
other information:
mysql> select trx_mysql_thread_id,trx_rows_locked from information_schema.innodb_trx\G
*************************** 1. row ***************************
trx_mysql_thread_id: 16
trx_rows_locked: 2 (only 2 rec_locks, but int the How to repeat has 3 rec_locks)
1 row in set (0.00 sec)
[12 Aug 2019 12:22]
MySQL Verification Team
Hi, You have not send us the optimiser plan for that particular query, which would have been very useful.
[5 Mar 2020 7:43]
yayun zhou
(you should open 3 clients and do the follow cases in order. Thanks!) set global innodb_lock_wait_timeout = 500; --Session 1, first order: Use abczyy_part; start transaction; SELECT khh,gtid FROM edf_dormancy_acct where KHH = 100010000234 for update; --Session 2, second order; Use abczyy_part; start transaction; SELECT khh,gtid FROM edf_dormancy_acct where ((KHH between 100000000400 and 100000000499) or (KHH between 100010000400 and 100010000499) and (MSG_CODE = -(1)) ) FOR UPDATE; --(lock wait until the session 1 commit, its ok.) --Session 1, third order: Commit; --Session 3, fourth order: Use abczyy_part; SELECT khh,gtid FROM edf_dormancy_acct where KHH = 100010000234 for update; --(lock wait until the session 2 commit, why? I think this has a problem) --Session 1, fifth order: show engine innodb status\G ------------ TRANSACTIONS ------------ Trx id counter 6120 Purge done for trx's n:o < 6109 undo n:o < 0 state: running but idle History list length 63 Total number of lock structs in row lock hash table 3 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 281475205089472, not started 0 lock struct(s), heap size 1160, 0 row lock(s) ---TRANSACTION 6119, ACTIVE 4 sec starting index read (session 3 trx) mysql tables in use 1, locked 1 LOCK WAIT 2 lock struct(s), heap size 1160, 1 row lock(s) MySQL thread id 19, OS thread handle 140301251852032, query id 197 localhost root Sending data SELECT khh,gtid FROM edf_dormancy_acct where KHH = 100010000234 for update ------- TRX HAS BEEN WAITING 4 SEC FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 56 page no 3 n bits 72 index PRIMARY of table `abczyy_part`.`edf_dormancy_acct` /* Partition `part_5` */ trx id 6119 lock_mode X locks rec but not gap waiting Record lock, heap no 2 PHYSICAL RECORD: n_fields 7; compact format; info bits 0 0: len 8; hex 80000017490f7f6a; asc I j;; 1: len 8; hex 3130303030323334; asc 10000234;; 2: len 6; hex 0000000017bd; asc ;; 3: len 7; hex a80000001b0110; asc ;; 4: len 8; hex 80000017490f7f6a; asc I j;; 5: len 4; hex 7fffffff; asc ;; 6: len 8; hex 8000000000000005; asc ;; ------------------ TABLE LOCK table `abczyy_part`.`edf_dormancy_acct` /* Partition `part_5` */ trx id 6119 lock mode IX RECORD LOCKS space id 56 page no 3 n bits 72 index PRIMARY of table `abczyy_part`.`edf_dormancy_acct` /* Partition `part_5` */ trx id 6119 lock_mode X locks rec but not gap waiting Record lock, heap no 2 PHYSICAL RECORD: n_fields 7; compact format; info bits 0 0: len 8; hex 80000017490f7f6a; asc I j;; 1: len 8; hex 3130303030323334; asc 10000234;; 2: len 6; hex 0000000017bd; asc ;; 3: len 7; hex a80000001b0110; asc ;; 4: len 8; hex 80000017490f7f6a; asc I j;; 5: len 4; hex 7fffffff; asc ;; 6: len 8; hex 8000000000000005; asc ;; ---TRANSACTION 6114, ACTIVE 34 sec (session 2 trx) 4 lock struct(s), heap size 1160, 3 row lock(s) MySQL thread id 18, OS thread handle 140301252118272, query id 188 localhost root TABLE LOCK table `abczyy_part`.`edf_dormancy_acct` /* Partition `part_1` */ trx id 6114 lock mode IX RECORD LOCKS space id 52 page no 3 n bits 72 index PRIMARY of table `abczyy_part`.`edf_dormancy_acct` /* Partition `part_1` */ trx id 6114 lock_mode X locks rec but not gap Record lock, heap no 3 PHYSICAL RECORD: n_fields 7; compact format; info bits 0 0: len 8; hex 800000174876e9c8; asc Hv ;; 1: len 8; hex 3030303030343536; asc 00000456;; 2: len 6; hex 0000000017be; asc ;; 3: len 7; hex a90000001d0110; asc ;; 4: len 8; hex 800000174876e9c8; asc Hv ;; 5: len 4; hex 7fffffff; asc ;; 6: len 8; hex 8000000000000006; asc ;; Record lock, heap no 4 PHYSICAL RECORD: n_fields 7; compact format; info bits 0 0: len 8; hex 800000174876e9c9; asc Hv ;; 1: len 8; hex 3030303030343537; asc 00000457;; 2: len 6; hex 0000000017ca; asc ;; 3: len 7; hex b20000001f0110; asc ;; 4: len 8; hex 800000174876e9c9; asc Hv ;; 5: len 4; hex 7fffffff; asc ;; 6: len 8; hex 800000000000000b; asc ;; TABLE LOCK table `abczyy_part`.`edf_dormancy_acct` /* Partition `part_5` */ trx id 6114 lock mode IX RECORD LOCKS space id 56 page no 3 n bits 72 index PRIMARY of table `abczyy_part`.`edf_dormancy_acct` /* Partition `part_5` */ trx id 6114 lock_mode X locks rec but not gap Record lock, heap no 2 PHYSICAL RECORD: n_fields 7; compact format; info bits 0 0: len 8; hex 80000017490f7f6a; asc I j;; 1: len 8; hex 3130303030323334; asc 10000234;; 2: len 6; hex 0000000017bd; asc ;; 3: len 7; hex a80000001b0110; asc ;; 4: len 8; hex 80000017490f7f6a; asc I j;; 5: len 4; hex 7fffffff; asc ;; 6: len 8; hex 8000000000000005; asc ;; At the end. I think the session 2 query should not has the record lock(100010000234) after the session 1 trx commit, because the select query range is [100000000400,100000000499] and [100010000400, 100010000499].
[5 Mar 2020 13:41]
MySQL Verification Team
Hi Mr. yoga, Session 3 is locked because session 2 is still active. Not a bug.
[6 Mar 2020 0:46]
yayun zhou
The important problem is why the session 2 obtain the record lock(100010000234)? can you tell me why? At the end. I think the session 2 query should not has the record lock(100010000234) after the session 1 trx commit, because the select query range is [100000000400,100000000499] and [100010000400, 100010000499].
[6 Mar 2020 13:07]
MySQL Verification Team
Session 2 had a condition : KHH = 100010000234 and as that record was not locked, it got locked. Anyway, this is not a forum for explanations on the functioning of MySQL server. In future, ask your question on the forum mentioned in the comment below.
[6 Mar 2020 13:07]
MySQL Verification Team
We're sorry, but the bug system is not the appropriate forum for asking help on using MySQL products. Your problem is not the result of a bug. For details on getting support for MySQL products see http://www.mysql.com/support/ You can also check our forums (free) at http://forums.mysql.com/ Thank you for your interest in MySQL.
Description: 1. When I do a select for update sql for a partitions table with multi_range, I find it will obtain the first rec_lock of a partition and the rec not in the where conditions. the sql example is: select a,b from tb1 where (between ... and ...) or (between ... and ...) or (between ... and ...) for update. Assume all the first rec of partitions is not in the where conditions, there are two prossibles. 1) If the sql does not have lock conflict in the first rec of partitions , the trx will not have the rec. 2) If the sql have lock conflicts in the first rec of partitions, after the trx obtain the locks, it will not release it until the trx end. 2. sometimes because this problem, we can find deadlock in two multi_range for update query. How to repeat: Drop database if exists abczyy_part; Create database abczyy_part; Use abczyy_part; CREATE TABLE `edf_dormancy_acct` ( `SERIAL_NO` bigint(20) NOT NULL DEFAULT '0', `KHH` bigint(20) NOT NULL DEFAULT '0', `ZQZH` varchar(20) COLLATE utf8_bin NOT NULL DEFAULT '', `MSG_CODE` int(11) NOT NULL DEFAULT '0', `GTID` bigint(20) DEFAULT NULL, PRIMARY KEY (`KHH`,`ZQZH`), KEY `EDF_DORMANCY_ACCT_IDX1` (`KHH`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin /*!50100 PARTITION BY RANGE (KHH) (PARTITION part_0 VALUES LESS THAN (100000000000) ENGINE = InnoDB, PARTITION part_1 VALUES LESS THAN (100002500000) ENGINE = InnoDB, PARTITION part_2 VALUES LESS THAN (100005000000) ENGINE = InnoDB, PARTITION part_3 VALUES LESS THAN (100007500000) ENGINE = InnoDB, PARTITION part_4 VALUES LESS THAN (100010000000) ENGINE = InnoDB, PARTITION part_5 VALUES LESS THAN (100012500000) ENGINE = InnoDB)*/; insert into edf_dormancy_acct values(100000000123,100000000123,'00000123',-1,1); insert into edf_dormancy_acct values(100002500123,100002500123,'02500123',10,2); insert into edf_dormancy_acct values(100005000123,100005000123,'05000123',-1,3); insert into edf_dormancy_acct values(100007500123,100007500123,'07500123',10,4); insert into edf_dormancy_acct values(100010000234,100010000234,'10000234',-1,5); insert into edf_dormancy_acct values(100000000456,100000000456,'00000456',-1,6); insert into edf_dormancy_acct values(100002500456,100002500456,'02500456',10,7); insert into edf_dormancy_acct values(100005000456,100005000456,'05000456',-1,8); insert into edf_dormancy_acct values(100007500456,100007500456,'07500456',10,9); insert into edf_dormancy_acct values(100010000567,100010000567,'10000567',-1,10); insert into edf_dormancy_acct values(100000000457,100000000457,'00000457',-1,11); insert into edf_dormancy_acct values(100002500457,100002500457,'02500457',10,12); insert into edf_dormancy_acct values(100005000457,100005000457,'05000457',-1,13); insert into edf_dormancy_acct values(100007500457,100007500457,'07500457',10,14); insert into edf_dormancy_acct values(100010000568,100010000568,'10000568',-1,15); --Session 1: Use abczyy_part; start transaction; SELECT khh,gtid FROM edf_dormancy_acct where KHH = 100010000234 for update; --Session 2; Use abczyy_part; start transaction; SELECT khh,gtid FROM edf_dormancy_acct where ((KHH between (100000000000 + (100 * 4)) and ((100000000000 + (100 * 4)) + 99)) or (KHH between (100010000000 + (100 * 4)) and ((100010000000 + (100 * 4)) + 99)) and (MSG_CODE = -(1)) ) FOR UPDATE; --Session 1: Commit; --Session 3: Use abczyy_part; SELECT khh,gtid FROM edf_dormancy_acct where KHH = 100000000123 for update; SELECT khh,gtid FROM edf_dormancy_acct where KHH = 100002500123 for update; SELECT khh,gtid FROM edf_dormancy_acct where KHH = 100005000123 for update; SELECT khh,gtid FROM edf_dormancy_acct where KHH = 100007500123 for update; Session 3: Use abczyy_part; SELECT khh,gtid FROM edf_dormancy_acct where KHH = 100010000234 for update; (This sql will be blocked by the trx in session 2...) other information: mysql> select trx_mysql_thread_id,trx_rows_locked from information_schema.innodb_trx\G *************************** 1. row *************************** trx_mysql_thread_id: 16 trx_rows_locked: 3 1 row in set (0.00 sec) (I think the trx in session 2 will have 2 row_locks, but it obtain 3 row_locks, and the 100010000234 is not in the where condition in session 2 trx.)