title: MySQL updating data by non-primary indexed column requires primary index locks on irrelevant record
body: <p><strong>MySQL updating data by non-primary indexed column requires primary index locks on irrelevant record.</strong></p>
  <p><strong>The table information.</strong></p>
  <pre><code>mysql&gt; SHOW TABLE STATUS WHERE Name = 'test'\G
  *************************** 1. row ***************************
             Name: test
           Engine: InnoDB
          Version: 10
       Row_format: Compact
             Rows: 15
   Avg_row_length: 1092
      Data_length: 16384
  Max_data_length: 0
     Index_length: 32768
        Data_free: 6291456
   Auto_increment: 18058
      Create_time: 2020-07-23 13:09:52
      Update_time: NULL
       Check_time: NULL
        Collation: utf8_general_ci
         Checksum: NULL
   Create_options: row_format=DYNAMIC
          Comment: 
  1 row in set (0.00 sec)
  
  </code></pre>
  <p><strong>The index information.</strong></p>
  <pre><code>mysql&gt; show index from test;
  +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
  | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
  +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
  | test  |          0 | PRIMARY  |            1 | id          | A         |          15 |     NULL | NULL   |      | BTREE      |         |               |
  | test  |          1 | index_a  |            1 | a           | A         |           7 |     NULL | NULL   |      | BTREE      |         |               |
  | test  |          1 | index_b  |            1 | b           | A         |           7 |     NULL | NULL   |      | BTREE      |         |               |
  +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
  3 rows in set (0.00 sec)
  </code></pre>
  <p><strong>The data information.</strong></p>
  <p><a href="https://i.stack.imgur.com/1M9wE.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/1M9wE.png" alt="enter image description here" /></a></p>
  <p><strong>Why would <em>step 2</em> <code>update test set a = 400 where b = 4;</code> require a lock on primary 1 ?</strong></p>
  <p><strong>Why would <em>step 1</em> <code>update test set a = 300 where b = 3;</code> hold a lock on primary 6 ?</strong></p>
  <p><strong>Why would all of this happen ?</strong></p>
  <p><a href="https://i.stack.imgur.com/ZMAEk.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/ZMAEk.png" alt="The problem" /></a></p>
  <pre><code>mysql&gt; show engine innodb status;
  ------------
  TRANSACTIONS
  ------------
  Trx id counter 21001
  Purge done for trx's n:o &lt; 21000 undo n:o &lt; 0 state: running but idle
  History list length 82
  LIST OF TRANSACTIONS FOR EACH SESSION:
  ---TRANSACTION 0, not started
  MySQL thread id 3, OS thread handle 0x7fa0292a6700, query id 65 192.168.79.1 root
  ---TRANSACTION 0, not started
  MySQL thread id 4, OS thread handle 0x7fa029264700, query id 33 192.168.79.1 root
  ---TRANSACTION 21000, ACTIVE 13749 sec starting index read
  mysql tables in use 1, locked 1
  LOCK WAIT 2 lock struct(s), heap size 1184, 1 row lock(s)
  MySQL thread id 2, OS thread handle 0x7fa0292e8700, query id 67 localhost root updating
  update test set a = 400 where b = 4
  ------- TRX HAS BEEN WAITING 5 SEC FOR THIS LOCK TO BE GRANTED:
  RECORD LOCKS space id 6 page no 3 n bits 592 index `PRIMARY` of table `test`.`test` trx id 21000 lock_mode X waiting
  Record lock, heap no 2 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
   0: len 4; hex 80000001; asc     ;;
   1: len 6; hex 000000005203; asc     R ;;
   2: len 7; hex 03000002910110; asc        ;;
   3: len 4; hex 8000012c; asc    ,;;
   4: len 4; hex 80000003; asc     ;;
  
  ------------------
  ---TRANSACTION 20995, ACTIVE 13764 sec
  2 lock struct(s), heap size 360, 16 row lock(s), undo log entries 5
  MySQL thread id 1, OS thread handle 0x7fa02932a700, query id 68 localhost root init
  show engine innodb status
  </code></pre>
  <p><a href="https://drive.google.com/file/d/1NKVDgJknAYkHfzOqUgBkyFSCPHyjcl_A/view?usp=sharing" rel="nofollow noreferrer">show engine innodb status full_result.txt</a></p>
  <p>********** <strong>update after posted three days</strong> **********</p>
  <p><strong>After the gentleman @Zhiyong answered my question, I did another test. In the test, I modified the data in column b, that I changed the original two distinct numbers '3' and '4' to more distinct numbers '3 4 5 6 7'. At that time, the locks just worked as I had expected before, that <code>update test set a = 300 where b = 3</code> and <code>update test set a = 400 where b = 4</code> can be executed at the same time in two transactions. It seems like the innodb doesn't build the index completely when there are too small amount of data in the column, I guess. Sorry for your time, maybe everyone else will never have the problem in real life. It's a little bit weird for me.</strong></p>
  <p><a href="https://i.stack.imgur.com/9rbxX.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/9rbxX.png" alt="enter image description here" /></a></p>
  <pre><code>mysql&gt; show create table test;
  +-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  | Table | Create Table                                                                                                                                                                                                                                                                                   |
  +-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  | test  | CREATE TABLE `test` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `a` int(11) NOT NULL,
    `b` int(11) NOT NULL,
    PRIMARY KEY (`id`) USING BTREE,
    KEY `index_a` (`a`) USING BTREE,
    KEY `index_b` (`b`) USING BTREE
  ) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC |
  +-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  1 row in set (0.00 sec)
  </code></pre>
  <p><strong>Update 29 July:</strong> I haven't understood the <code>Next-Key Lock</code> for now. To intuitively display the locks, I used mysql 8 this time. Because of that mysql 8 has the table <code>performance_schema.data_locks</code> from which we can see all the locks not only the waiting locks. I did another test use the same data on both mysql 5.6 and mysql 8. The update query (where b = 3) and (b = 4) can be executed simultaneously in different transaction on mysql 8 but they cannot on mysql 5.6. I'm so confused.</p>
  <p>the data sql: <a href="https://drive.google.com/file/d/19JxrMPwQ6JEUsRotfWTBewLBcCqng7zI/view?usp=sharing" rel="nofollow noreferrer">test.sql </a></p>
  <p><code>select * from performance_schema.data_locks</code> <a href="https://drive.google.com/file/d/1qkaS1WL56GZ-IbaLd8_AWZplXGFXu8ka/view?usp=sharing" rel="nofollow noreferrer">data_locks.csv</a></p>
  <pre><code>null    140235916803776 TABLE   IX  GRANTED null
  idx_b   140235916800864 RECORD  X   GRANTED supremum pseudo-record
  idx_b   140235916800864 RECORD  X   GRANTED 4, 7
  idx_b   140235916800864 RECORD  X   GRANTED 4, 8
  idx_b   140235916800864 RECORD  X   GRANTED 4, 9
  idx_b   140235916800864 RECORD  X   GRANTED 4, 10
  idx_b   140235916800864 RECORD  X   GRANTED 4, 11
  idx_b   140235916800864 RECORD  X   GRANTED 4, 12
  idx_b   140235916800864 RECORD  X   GRANTED 4, 13
  idx_b   140235916800864 RECORD  X   GRANTED 4, 14
  idx_b   140235916800864 RECORD  X   GRANTED 4, 15
  idx_b   140235916800864 RECORD  X   GRANTED 4, 16
  PRIMARY 140235916801208 RECORD  X,REC_NOT_GAP   GRANTED 7
  PRIMARY 140235916801208 RECORD  X,REC_NOT_GAP   GRANTED 8
  PRIMARY 140235916801208 RECORD  X,REC_NOT_GAP   GRANTED 9
  PRIMARY 140235916801208 RECORD  X,REC_NOT_GAP   GRANTED 10
  PRIMARY 140235916801208 RECORD  X,REC_NOT_GAP   GRANTED 11
  PRIMARY 140235916801208 RECORD  X,REC_NOT_GAP   GRANTED 12
  PRIMARY 140235916801208 RECORD  X,REC_NOT_GAP   GRANTED 13
  PRIMARY 140235916801208 RECORD  X,REC_NOT_GAP   GRANTED 14
  PRIMARY 140235916801208 RECORD  X,REC_NOT_GAP   GRANTED 15
  PRIMARY 140235916801208 RECORD  X,REC_NOT_GAP   GRANTED 16
      140235916797744 TABLE   IX  GRANTED 
  idx_b   140235916794832 RECORD  X   GRANTED 3, 1
  idx_b   140235916794832 RECORD  X   GRANTED 3, 2
  idx_b   140235916794832 RECORD  X   GRANTED 3, 3
  idx_b   140235916794832 RECORD  X   GRANTED 3, 4
  idx_b   140235916794832 RECORD  X   GRANTED 3, 5
  idx_b   140235916794832 RECORD  X   GRANTED 3, 6
  PRIMARY 140235916795176 RECORD  X,REC_NOT_GAP   GRANTED 1
  PRIMARY 140235916795176 RECORD  X,REC_NOT_GAP   GRANTED 2
  PRIMARY 140235916795176 RECORD  X,REC_NOT_GAP   GRANTED 3
  PRIMARY 140235916795176 RECORD  X,REC_NOT_GAP   GRANTED 4
  PRIMARY 140235916795176 RECORD  X,REC_NOT_GAP   GRANTED 5
  PRIMARY 140235916795176 RECORD  X,REC_NOT_GAP   GRANTED 6
  idx_b   140235916795520 RECORD  X,GAP   GRANTED 4, 7
  </code></pre>