CODEWITHSUNDEEP
mysqlsql-update
Jack
Jack

Reputation: 131

MySQL updating data by non-primary indexed column requires primary index locks on irrelevant record

MySQL updating data by non-primary indexed column requires primary index locks on irrelevant record.

The table information.

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

The index information.

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

The data information.

enter image description here

Why would step 2 update test set a = 400 where b = 4; require a lock on primary 1 ?

Why would step 1 update test set a = 300 where b = 3; hold a lock on primary 6 ?

Why would all of this happen ?

The problem

mysql> show engine innodb status;
------------
TRANSACTIONS
------------
Trx id counter 21001
Purge done for trx's n:o < 21000 undo n:o < 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

show engine innodb status full_result.txt

********** update after posted three days **********

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 update test set a = 300 where b = 3 and update test set a = 400 where b = 4 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.

enter image description here

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

Update 29 July: I haven't understood the Next-Key Lock for now. To intuitively display the locks, I used mysql 8 this time. Because of that mysql 8 has the table performance_schema.data_locks 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.

the data sql: test.sql

select * from performance_schema.data_locks data_locks.csv

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

Upvotes: 0

Views: 839

Answers (1)

Zhiyong
Zhiyong

Reputation: 488

From the following explain result we could see, this update query will use primary index, because using idx_b will return table and access primary index again. Therefore mysql choose to use primary index instead of idx_b.

mysql> explain update test set a = 300 where b = 3;
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
|  1 | UPDATE      | test  | NULL       | index | idx_b         | PRIMARY | 4       | NULL |   15 |   100.00 | Using where |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
1 row in set (0.00 sec)

That could be proved according to your show engine innodb status result, which show 16 row lock. With that said, all records had been locked.

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

The following from mysql offical doc show how innodb lock work:

UPDATE ... WHERE ... sets an exclusive next-key lock on every record the search encounters. However, only an index record lock is required for statements that lock rows using a unique index to search for a unique row.

The same reason for update test set a = 400 where b = 4 and explain update test set a = 400 where b = 4 and id > 5. Both queries will use primary index.

mysql> explain update test set a = 400 where b = 4;
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
|  1 | UPDATE      | test  | NULL       | index | idx_b         | PRIMARY | 4       | NULL |   15 |   100.00 | Using where |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
1 row in set (0.00 sec)

mysql> explain update test set a = 400 where b = 4 and id > 5;
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
|  1 | UPDATE      | test  | NULL       | range | PRIMARY,idx_b | PRIMARY | 4       | const |   10 |   100.00 | Using where |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
1 row in set (0.00 sec)

To sum up, mysql choose primay index instead of idx_b, that's why all of these happen.

Upvotes: 1

Related Questions