| Bug #73369 | Tail of secondary index may cause gap lock in read-committed | ||
|---|---|---|---|
| Submitted: | 24 Jul 2014 6:52 | Modified: | 31 Aug 2014 9:49 |
| Reporter: | Peiran Song | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: InnoDB storage engine | Severity: | S3 (Non-critical) |
| Version: | 5.6.19 | OS: | Linux |
| Assigned to: | CPU Architecture: | Any | |
[24 Jul 2014 6:52]
Peiran Song
[24 Jul 2014 7:26]
Peiran Song
During testing, found un-repeatable locking behavior as below: 0) Change the INSERT in session 1 to a single row insert insert into test_txn_schd_queue_tbl (txn_id, col1, col2, override_routing_no_upd, schld_id, schld_row_id, outbound_file_id, outbound_file_type, txn_status_code, txn_sub_status_code, partner_id, txn_schld_date) values (5133633, 'one', 'two', 'Y', 9974687, 1, 10, 10, 10, 10, 2, sysdate()); 1) Run single row insert in session1, run update in session2, the update doesn't lock wait. 2) Run the insert..select from in session1, run update in session2, the update lock wait. 3) Repeat (1), now the update lock wait !! ------------------ No lock wait with single row insert -------------------------- mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> insert into test_txn_schd_queue_tbl (txn_id, col1, col2, override_routing_no_upd, schld_id, schld_row_id, outbound_file_id, outbound_file_type, txn_status_code, txn_sub_status_code, partner_id, txn_schld_date) values (5133633, 'one', 'two', 'Y', 9974687, 1, 10, 10, 10, 10, 2, sysdate()); Query OK, 1 row affected (0.00 sec) mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> UPDATE test_txn_schd_queue_tbl SET col1 = 'Updated' WHERE schld_id = 9974590 and schld_row_id between 1 and 3; Query OK, 3 rows affected (0.00 sec) ---TRANSACTION 700132, ACTIVE 42 sec 2 lock struct(s), heap size 360, 3 row lock(s), undo log entries 3 MySQL thread id 49, OS thread handle 0x7f8ca805e700, query id 1089 localhost root cleaning up TABLE LOCK table `test`.`test_txn_schd_queue_tbl` trx id 700132 lock mode IX RECORD LOCKS space id 182 page no 3 n bits 88 index `PRIMARY` of table `test`.`test_txn_schd_queue_tbl` trx id 700132 lock_mode X locks rec but not gap Record lock, heap no 20 PHYSICAL RECORD: n_fields 14; compact format; info bits 0 0: len 8; hex 80000000004e4d6e; asc NMn;; 1: len 6; hex 0000000aaee4; asc ;; 2: len 7; hex 4b000001562433; asc K V$3;; 3: len 7; hex 55706461746564; asc Updated;; 4: len 3; hex 74776f; asc two;; 5: len 1; hex 59; asc Y;; 6: len 8; hex 800000000098333e; asc 3>;; 7: len 8; hex 8000000000000001; asc ;; 8: len 8; hex 8000000000011170; asc p;; 9: len 4; hex 800003ec; asc ;; 10: len 4; hex 80000011; asc ;; 11: len 4; hex 8000022f; asc /;; 12: len 8; hex 80000000000000c3; asc ;; 13: len 5; hex 9993655db8; asc e] ;; Record lock, heap no 21 PHYSICAL RECORD: n_fields 14; compact format; info bits 0 0: len 8; hex 80000000004e553f; asc NU?;; 1: len 6; hex 0000000aaee4; asc ;; 2: len 7; hex 4b000001562458; asc K V$X;; 3: len 7; hex 55706461746564; asc Updated;; 4: len 3; hex 74776f; asc two;; 5: len 1; hex 59; asc Y;; 6: len 8; hex 800000000098333e; asc 3>;; 7: len 8; hex 8000000000000002; asc ;; 8: SQL NULL; 9: len 4; hex 800001fe; asc ;; 10: len 4; hex 80000011; asc ;; 11: len 4; hex 8000022f; asc /;; 12: len 8; hex 800000000000007c; asc |;; 13: len 5; hex 9993655db8; asc e] ;; Record lock, heap no 22 PHYSICAL RECORD: n_fields 14; compact format; info bits 0 0: len 8; hex 80000000004e5540; asc NU@;; 1: len 6; hex 0000000aaee4; asc ;; 2: len 7; hex 4b00000156247d; asc K V$};; 3: len 7; hex 55706461746564; asc Updated;; 4: len 3; hex 74776f; asc two;; 5: len 1; hex 59; asc Y;; 6: len 8; hex 800000000098333e; asc 3>;; 7: len 8; hex 8000000000000003; asc ;; 8: SQL NULL; 9: len 4; hex 800001fe; asc ;; 10: len 4; hex 80000011; asc ;; 11: len 4; hex 8000022f; asc /;; 12: len 8; hex 800000000000007c; asc |;; 13: len 5; hex 9993655db8; asc e] ;; ---TRANSACTION 700125, ACTIVE 68 sec 2 lock struct(s), heap size 360, 1 row lock(s), undo log entries 1 MySQL thread id 44, OS thread handle 0x7f8ca809f700, query id 1084 localhost root cleaning up TABLE LOCK table `test`.`test_txn_schd_queue_tbl` trx id 700125 lock mode IX RECORD LOCKS space id 182 page no 3 n bits 88 index `PRIMARY` of table `test`.`test_txn_schd_queue_tbl` trx id 700125 lock_mode X locks rec but not gap Record lock, heap no 16 PHYSICAL RECORD: n_fields 14; compact format; info bits 0 0: len 8; hex 80000000004e5541; asc NUA;; 1: len 6; hex 0000000aaedd; asc ;; 2: len 7; hex c700000d810110; asc ;; 3: len 3; hex 6f6e65; asc one;; 4: len 3; hex 74776f; asc two;; 5: len 1; hex 59; asc Y;; 6: len 8; hex 800000000098339f; asc 3 ;; 7: len 8; hex 8000000000000001; asc ;; 8: len 8; hex 800000000000000a; asc ;; 9: len 4; hex 8000000a; asc ;; 10: len 4; hex 8000000a; asc ;; 11: len 4; hex 8000000a; asc ;; 12: len 8; hex 8000000000000002; asc ;; 13: len 5; hex 99936628ec; asc f( ;;
[24 Jul 2014 7:27]
Peiran Song
Cont. from last comment ----- After run the original INSERT..SELECT, rollback everything, Repeat 1) again ------------ session1: mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> insert into test_txn_schd_queue_tbl (txn_id, col1, col2, override_routing_no_upd, schld_id, schld_row_id, outbound_file_id, outbound_file_type, txn_status_code, txn_sub_status_code, partner_id, txn_schld_date) values (5131653, 'one', 'two', 'Y', 9974592, 2, 10, 10, 10, 10, 2, sysdate()); Query OK, 1 row affected (0.00 sec) session2: mysql> begin; Query OK, 0 rows affected (0.02 sec) mysql> UPDATE test_txn_schd_queue_tbl SET col1 = 'Updated' WHERE schld_id = 9974590 and schld_row_id between 1 and 3; ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction ---TRANSACTION 700106, ACTIVE 9 sec fetching rows mysql tables in use 1, locked 1 LOCK WAIT 4 lock struct(s), heap size 1184, 7 row lock(s), undo log entries 3 MySQL thread id 45, OS thread handle 0x7f8ca80e0700, query id 1014 localhost root updating UPDATE test_txn_schd_queue_tbl SET col1 = 'Updated' WHERE schld_id = 9974590 and schld_row_id between 1 and 3 ------- TRX HAS BEEN WAITING 9 SEC FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 182 page no 8 n bits 80 index `test_txn_schd_queue_tbl_idx1` of table `test`.`test_txn_schd_queue_tbl` trx id 700106 lock_mode X locks rec but not gap waiting Record lock, heap no 5 PHYSICAL RECORD: n_fields 3; compact format; info bits 0 0: len 8; hex 800000000098333f; asc 3?;; 1: len 8; hex 8000000000000002; asc ;; 2: len 8; hex 80000000004e5555; asc NUU;; *********** Lock is X locks, not gap ********** schld_id : 9974591 schld_row_id: 2 txn_id: 5133653 ------------------ TABLE LOCK table `test`.`test_txn_schd_queue_tbl` trx id 700106 lock mode IX RECORD LOCKS space id 182 page no 8 n bits 80 index `test_txn_schd_queue_tbl_idx1` of table `test`.`test_txn_schd_queue_tbl` trx id 700106 lock_mode X locks rec but not gap Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 0 0: len 8; hex 800000000098333e; asc 3>;; 1: len 8; hex 8000000000000001; asc ;; 2: len 8; hex 80000000004e4d6e; asc NMn;; Record lock, heap no 3 PHYSICAL RECORD: n_fields 3; compact format; info bits 0 0: len 8; hex 800000000098333e; asc 3>;; 1: len 8; hex 8000000000000002; asc ;; 2: len 8; hex 80000000004e553f; asc NU?;; Record lock, heap no 4 PHYSICAL RECORD: n_fields 3; compact format; info bits 0 0: len 8; hex 800000000098333e; asc 3>;; 1: len 8; hex 8000000000000003; asc ;; 2: len 8; hex 80000000004e5540; asc NU@;; RECORD LOCKS space id 182 page no 3 n bits 88 index `PRIMARY` of table `test`.`test_txn_schd_queue_tbl` trx id 700106 lock_mode X locks rec but not gap Record lock, heap no 17 PHYSICAL RECORD: n_fields 14; compact format; info bits 0 0: len 8; hex 80000000004e4d6e; asc NMn;; 1: len 6; hex 0000000aaeca; asc ;; 2: len 7; hex 3b0000016819b6; asc ; h ;; 3: len 7; hex 55706461746564; asc Updated;; 4: len 3; hex 74776f; asc two;; 5: len 1; hex 59; asc Y;; 6: len 8; hex 800000000098333e; asc 3>;; 7: len 8; hex 8000000000000001; asc ;; 8: len 8; hex 8000000000011170; asc p;; 9: len 4; hex 800003ec; asc ;; 10: len 4; hex 80000011; asc ;; 11: len 4; hex 8000022f; asc /;; 12: len 8; hex 80000000000000c3; asc ;; 13: len 5; hex 9993655db8; asc e] ;; Record lock, heap no 18 PHYSICAL RECORD: n_fields 14; compact format; info bits 0 0: len 8; hex 80000000004e553f; asc NU?;; 1: len 6; hex 0000000aaeca; asc ;; 2: len 7; hex 3b0000016819db; asc ; h ;; 3: len 7; hex 55706461746564; asc Updated;; 4: len 3; hex 74776f; asc two;; 5: len 1; hex 59; asc Y;; 6: len 8; hex 800000000098333e; asc 3>;; 7: len 8; hex 8000000000000002; asc ;; 8: SQL NULL; 9: len 4; hex 800001fe; asc ;; 10: len 4; hex 80000011; asc ;; 11: len 4; hex 8000022f; asc /;; 12: len 8; hex 800000000000007c; asc |;; 13: len 5; hex 9993655db8; asc e] ;; Record lock, heap no 19 PHYSICAL RECORD: n_fields 14; compact format; info bits 0 0: len 8; hex 80000000004e5540; asc NU@;; 1: len 6; hex 0000000aaeca; asc ;; 2: len 7; hex 3b000001681a00; asc ; h ;; 3: len 7; hex 55706461746564; asc Updated;; 4: len 3; hex 74776f; asc two;; 5: len 1; hex 59; asc Y;; 6: len 8; hex 800000000098333e; asc 3>;; 7: len 8; hex 8000000000000003; asc ;; 8: SQL NULL; 9: len 4; hex 800001fe; asc ;; 10: len 4; hex 80000011; asc ;; 11: len 4; hex 8000022f; asc /;; 12: len 8; hex 800000000000007c; asc |;; 13: len 5; hex 9993655db8; asc e] ;; RECORD LOCKS space id 182 page no 8 n bits 80 index `test_txn_schd_queue_tbl_idx1` of table `test`.`test_txn_schd_queue_tbl` trx id 700106 lock_mode X locks rec but not gap waiting Record lock, heap no 5 PHYSICAL RECORD: n_fields 3; compact format; info bits 0 0: len 8; hex 800000000098333f; asc 3?;; 1: len 8; hex 8000000000000002; asc ;; 2: len 8; hex 80000000004e5555; asc NUU;; ---TRANSACTION 700104, ACTIVE 19 sec 2 lock struct(s), heap size 360, 1 row lock(s), undo log entries 1 MySQL thread id 44, OS thread handle 0x7f8ca809f700, query id 1012 localhost root cleaning up TABLE LOCK table `test`.`test_txn_schd_queue_tbl` trx id 700104 lock mode IX RECORD LOCKS space id 182 page no 8 n bits 80 index `test_txn_schd_queue_tbl_idx1` of table `test`.`test_txn_schd_queue_tbl` trx id 700104 lock_mode X locks rec but not gap Record lock, heap no 5 PHYSICAL RECORD: n_fields 3; compact format; info bits 0 0: len 8; hex 800000000098333f; asc 3?;; 1: len 8; hex 8000000000000002; asc ;; 2: len 8; hex 80000000004e5555; asc NUU;;
[24 Jul 2014 7:45]
Peiran Song
A similar schema and test data I used below couldn't reliably reproduce the lock wait, but I did finally capture a couple lock wait as below: CREATE TABLE `t1` ( `txn_id` bigint(16) NOT NULL, `col1` varchar(100) DEFAULT NULL, `schld_id` bigint(16) DEFAULT NULL, `schld_row_id` bigint(16) DEFAULT NULL, `file_id` int(11) DEFAULT NULL, `upd_date` datetime DEFAULT NULL, PRIMARY KEY (`txn_id`), KEY `t1_idx1` (`schld_id`,`schld_row_id`), KEY `t1_idx2` (`file_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; CREATE TABLE `t2` ( `txn_id` int(11) NOT NULL DEFAULT '0', `col1` varchar(100) CHARACTER SET latin1 DEFAULT NULL, PRIMARY KEY (`txn_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; mysql> insert into t1 (txn_id, col1, schld_id, schld_row_id, file_id) values (5131630, 'one', 9974590, 1, 10), (5133631, 'one', 9974590, 2, 10), (5133632, 'one', 9974590, 3, 10); mysql> insert into t2 values (5131630, 'one'), (5133631, 'one'), (5133632, 'one'),(5133633, 'one'), (5133634,'one'),(5133635,'one'); First reproduced the lock wait with this INSERT..SELECT : session1: mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> insert into t1 (txn_id, col1, schld_id, schld_row_id) select txn_id, col1, 9974687, (@ROWNUM:=@ROWNUM+1) FROM t2,(SELECT @ROWNUM:=0) a where txn_id NOT IN (5131630, 5133631, 5133632); session 2: mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> update t1 set col1 = 'Updated' where schld_id = 9974590 and schld_row_id between 1 and 3; ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction ---TRANSACTION 700408, ACTIVE 17 sec starting index read mysql tables in use 1, locked 1 LOCK WAIT 2 lock struct(s), heap size 360, 1 row lock(s) MySQL thread id 63, OS thread handle 0x7f8c7be78700, query id 1467 localhost root updating update t1 set col1 = 'Updated' where schld_id = 9974590 and schld_row_id between 1 and 3 ------- TRX HAS BEEN WAITING 17 SEC FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 189 page no 4 n bits 72 index `t1_idx1` of table `test`.`t1` trx id 700408 lock_mode X locks rec but not gap waiting Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 0 0: len 8; hex 800000000098339f; asc 3 ;; 1: len 8; hex 8000000000000001; asc ;; 2: len 8; hex 80000000004e5541; asc NUA;; ------------------ TABLE LOCK table `test`.`t1` trx id 700408 lock mode IX RECORD LOCKS space id 189 page no 4 n bits 72 index `t1_idx1` of table `test`.`t1` trx id 700408 lock_mode X locks rec but not gap waiting Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 0 0: len 8; hex 800000000098339f; asc 3 ;; 1: len 8; hex 8000000000000001; asc ;; 2: len 8; hex 80000000004e5541; asc NUA;; ---TRANSACTION 700403, ACTIVE 28 sec 2 lock struct(s), heap size 360, 1 row lock(s), undo log entries 3 MySQL thread id 64, OS thread handle 0x7f8ca809f700, query id 1465 localhost root cleaning up TABLE LOCK table `test`.`t1` trx id 700403 lock mode IX RECORD LOCKS space id 189 page no 4 n bits 72 index `t1_idx1` of table `test`.`t1` trx id 700403 lock_mode X locks rec but not gap Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 0 0: len 8; hex 800000000098339f; asc 3 ;; 1: len 8; hex 8000000000000001; asc ;; 2: len 8; hex 80000000004e5541; asc NUA;; Then I could produce the lock wait with different INSERT statements, even this simplest one. However, if I wipe out everything and recreate the tables again, and try the simple INSERT, no lock wait. session1 : mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> insert into t1 (txn_id, col1, schld_id, schld_row_id) values (5133633, 'one', 9974687, 1); Query OK, 1 row affected (0.00 sec) session2: mysql> update t1 set col1 = 'Updated' where schld_id = 9974590 and schld_row_id between 1 and 3; ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction ---TRANSACTION 700440, ACTIVE 7 sec starting index read mysql tables in use 1, locked 1 LOCK WAIT 2 lock struct(s), heap size 360, 1 row lock(s) MySQL thread id 74, OS thread handle 0x7f8ca80e0700, query id 1670 localhost root updating update t1 set col1 = 'Updated' where schld_id = 9974590 and schld_row_id between 1 and 3 ------- TRX HAS BEEN WAITING 7 SEC FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 189 page no 4 n bits 72 index `t1_idx1` of table `test`.`t1` trx id 700440 lock_mode X locks rec but not gap waiting Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 0 0: len 8; hex 800000000098339f; asc 3 ;; 1: len 8; hex 8000000000000001; asc ;; 2: len 8; hex 80000000004e5541; asc NUA;; ------------------ TABLE LOCK table `test`.`t1` trx id 700440 lock mode IX RECORD LOCKS space id 189 page no 4 n bits 72 index `t1_idx1` of table `test`.`t1` trx id 700440 lock_mode X locks rec but not gap waiting Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 0 0: len 8; hex 800000000098339f; asc 3 ;; 1: len 8; hex 8000000000000001; asc ;; 2: len 8; hex 80000000004e5541; asc NUA;; ---TRANSACTION 700439, ACTIVE 12 sec 2 lock struct(s), heap size 360, 1 row lock(s), undo log entries 1 MySQL thread id 75, OS thread handle 0x7f8ca805e700, query id 1668 localhost root cleaning up TABLE LOCK table `test`.`t1` trx id 700439 lock mode IX RECORD LOCKS space id 189 page no 4 n bits 72 index `t1_idx1` of table `test`.`t1` trx id 700439 lock_mode X locks rec but not gap Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 0 0: len 8; hex 800000000098339f; asc 3 ;; 1: len 8; hex 8000000000000001; asc ;; 2: len 8; hex 80000000004e5541; asc NUA;;
[24 Jul 2014 19:32]
Sveta Smirnova
Thank you for taking the time to write to us, but this is not a bug. Please double-check the documentation available at http://dev.mysql.com/doc/ and the instructions on how to report a bug at http://bugs.mysql.com/how-to-report.php Thank you for the report. schld_id and schld_row_id are not unique, therefore InnoDB cannot know how many rows to lock. This is not a bug.
[28 Jul 2014 2:48]
Peiran Song
Sveta, First of all, please check the lock information in the initial bug description again. The lock wait is on a record that is not yet committed by the insert statement. And it conflicts with the documentation: "For UPDATE statements, InnoDB does a “semi-consistent” read, such that it returns the latest committed version to MySQL so that MySQL can determine whether the row matches the WHERE condition of the UPDATE", so the UPDATE statement should not see and attempt lock on the new record. " Secondly, if you run the last test case I posted with similar Non-unique secondary index, it doesn't always show lock waits. That contradicts with the behavior in the first test case. Lastly, in comments, I show two scenarios where locking behavior only happens conditionally(unreliably) with the same queries. This by itself is buggy. Please review again.
[26 Aug 2014 12:53]
Andrii Nikitin
I agree with Sveta that the test case doesn't fit basic requirements for simplified test cases. Additionally - of course Update will lock those rows which match search criteria, and it is not clear from the test case whether any locked rows match WHERE condition. E.g. if you add some FALSE condition like below to UPDATE - will lock wait occur: AND (schld_id/3 = schld_row_id) So please try to create simplified minimal possible testcase and explain why you think that UPDATE shouldn't lock the rows.
[27 Aug 2014 6:46]
Peiran Song
I spent considerable amount of time trying to reproduce the same buggy behavior with a simple test case, but could not. I did provide a simplified test case where I could see the bug under CERTAIN condition, which reassured that something is unstable and buggy here. Despite the test case being a bit lengthier, copy and paste should only take 10 minutes or so. If that doesn't meet the "basic requirements for simplified test case", please kindly inform how simple is simple enough. And with respect to the QA team, we shouldn't expect every bug to be easily reproduced with an intuitive and "simple" test case, should we? The main problem here is the UPDATE statement waits for a row lock on an UNCOMMITTED row. Without trying the test case, the simple fact might not be obvious, let me list the data here and try one more time. Please bare with me for a few minutes: The target table has an index on columns (schld_id, schld_row_id), and the table has three rows with (schld_id, schld_row_id) values being: 9974590, 1 9974590, 2 9974590, 3 Now, an insert statement tries to insert multiple rows with the first row in the index order being: 9974687, 1 Before the insert statement commits, and in READ COMMITTED isolation level, should the following update statement wait on the row lock for the uncommitted row (9974687, 1)? UPDATE test_txn_schd_queue_tbl SET col1 = 'Updated' WHERE schld_id = 9974590 and schld_row_id between 1 and 3; ------- TRX HAS BEEN WAITING 6 SEC FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 182 page no 8 n bits 80 index `test_txn_schd_queue_tbl_idx1` of table `test`.`test_txn_schd_queue_tbl` trx id 700372 lock_mode X locks rec but not gap waiting Record lock, heap no 5 PHYSICAL RECORD: n_fields 3; compact format; info bits 0 0: len 8; hex 800000000098339f; asc 3 ;; 1: len 8; hex 8000000000000001; asc ;; 2: len 8; hex 80000000004e5541; asc NUA;; ************ X lock, no gap ********* schld_id: 9974687 schld_row_id : 1 txn_id: 5133633 Do you think this conflicts with the manual below? "For UPDATE statements, InnoDB does a “semi-consistent” read, such that it returns the latest committed version to MySQL so that MySQL can determine whether the row matches the WHERE condition of the UPDATE", so the UPDATE statement should not see and attempt lock on the new record. "
[27 Aug 2014 7:48]
Andrii Nikitin
Before trying testcase, a verifier must understand what test case tries to achieve. It is expected that UPDATE will wait for a row lock on an UNCOMMITTED row if that row fully matches search condition. Isolation level only matters for SELECT statements, UPDATE will always lock all rows it actually modifies (even uncommitted).
[28 Aug 2014 11:30]
Peiran Song
Test cases are prepared, and you don't care to run. Test case is explained, and you still don't care to read closely, otherwise, you would not say "that row fully matches search condition". A blurb from the manual is included twice, and you just don't care to read it. You are obviously conflicting with the manual.
[28 Aug 2014 14:48]
Andrii Nikitin
It isn't even clear that you complain about gap locking, bugreport really looks that you complain that UPDATE locks rows. (which isn't worth investigating) I wasn't able to repeat the problem in 5.6.20, do you want me to try in 5.6.19 ?
[28 Aug 2014 14:58]
Andrii Nikitin
OK, I can repeat in 5.6.20 , will update shortly
[28 Aug 2014 15:43]
Andrii Nikitin
Verified in 5.6.20 as described. Following condition doesn't lock original test case: instead of: and schld_row_id between 1 and 3 use and (schld_row_id+0) between 1 and 3 That means on certain condition tail of secondary index may create gap locks. Simpler test cases don't demonstrate the problem.
[29 Aug 2014 6:30]
Andrii Nikitin
Simplified testcase : read-committed : drop table t; create table t(a int primary key, b int, c int, d int, index(b,c)) engine = innodb; insert into t values(1,1,1,0); insert into t values(2,1,2,0); Session #1 start transaction; insert into t values(3,2,1,0); Session #2 - hangs on gap lock update t set d=0 where b=1 and c between 1 and 2; Session #3 - doesn't hang (the same is expected for session #2) update t set d=0 where b=1 and (c+0) between 1 and 2;
[31 Aug 2014 9:49]
Peiran Song
Interesting. I certainly tried a similar simple case but didn't produce the lock wait. The difference turned out to be that I seeded with three rows! With your test case, if you seed more than 2 rows, there would not be lock wait for the UPDATE. read-committed : drop table t; create table t(a int primary key, b int, c int, d int, index(b,c)) engine = innodb; insert into t values(1,1,1,0); insert into t values(2,1,2,0); insert into t values(3,1,3,0); Session #1 start transaction; insert into t values(4,2,1,0); Session #2 - no lock wait update t set d=1 where b=1 and c between 1 and 3;
[7 Apr 2015 17:32]
Valeriy Kravchuk
I wonder why do you state that there is a wait on gap lock specifically. With READ COMMITTED in both sessions and Andrii's test case I do see wait: LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 74815, ACTIVE 4 sec fetching rows mysql tables in use 1, locked 1 LOCK WAIT 4 lock struct(s), heap size 1184, 5 row lock(s), undo log entries 2 MySQL thread id 41, OS thread handle 0x3ee0, query id 5545 localhost ::1 root up dating update t set d=0 where b=1 and c between 1 and 2 ------- TRX HAS BEEN WAITING 4 SEC FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 516 page no 4 n bits 72 index `b` of table `test`.`t` trx id 74815 lock_mode X locks rec but not gap waiting Record lock, heap no 4 PHYSICAL RECORD: n_fields 3; compact format; info bits 0 0: len 4; hex 80000002; asc ;; 1: len 4; hex 80000001; asc ;; 2: len 4; hex 80000003; asc ;; ------------------ TABLE LOCK table `test`.`t` trx id 74815 lock mode IX RECORD LOCKS space id 516 page no 4 n bits 72 index `b` of table `test`.`t` trx id 74815 lock_mode X locks rec but not gap Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 0 0: len 4; hex 80000001; asc ;; 1: len 4; hex 80000001; asc ;; 2: len 4; hex 80000001; asc ;; Record lock, heap no 3 PHYSICAL RECORD: n_fields 3; compact format; info bits 0 0: len 4; hex 80000001; asc ;; 1: len 4; hex 80000002; asc ;; 2: len 4; hex 80000002; asc ;; RECORD LOCKS space id 516 page no 3 n bits 72 index `PRIMARY` of table `test`.`t ` trx id 74815 lock_mode X locks rec but not gap Record lock, heap no 2 PHYSICAL RECORD: n_fields 6; compact format; info bits 0 0: len 4; hex 80000001; asc ;; 1: len 6; hex 00000001243f; asc $?;; 2: len 7; hex 00000002340bd2; asc 4 ;; 3: len 4; hex 80000001; asc ;; 4: len 4; hex 80000001; asc ;; 5: len 4; hex 80000000; asc ;; Record lock, heap no 3 PHYSICAL RECORD: n_fields 6; compact format; info bits 0 0: len 4; hex 80000002; asc ;; 1: len 6; hex 00000001243f; asc $?;; 2: len 7; hex 00000002340bee; asc 4 ;; 3: len 4; hex 80000001; asc ;; 4: len 4; hex 80000002; asc ;; 5: len 4; hex 80000000; asc ;; RECORD LOCKS space id 516 page no 4 n bits 72 index `b` of table `test`.`t` trx id 74815 lock_mode X locks rec but not gap waiting Record lock, heap no 4 PHYSICAL RECORD: n_fields 3; compact format; info bits 0 0: len 4; hex 80000002; asc ;; 1: len 4; hex 80000001; asc ;; 2: len 4; hex 80000003; asc ;; ---TRANSACTION 74811, ACTIVE 211 sec 2 lock struct(s), heap size 360, 1 row lock(s), undo log entries 1 MySQL thread id 40, OS thread handle 0x34fc, query id 5546 localhost ::1 root in it show engine innodb status TABLE LOCK table `test`.`t` trx id 74811 lock mode IX RECORD LOCKS space id 516 page no 4 n bits 72 index `b` of table `test`.`t` trx id 74811 lock_mode X locks rec but not gap Record lock, heap no 4 PHYSICAL RECORD: n_fields 3; compact format; info bits 0 0: len 4; hex 80000002; asc ;; 1: len 4; hex 80000001; asc ;; 2: len 4; hex 80000003; asc ;; -------- But none of the locks involved is a gap lock. See "rec but not gap" everywhere. Do I miss something?
