A few days ago, I received an online alarm email. There was a deadlock in MySQL operation in the production environment. The SQL extracted from the email alarm is roughly as follows.

update pe_order_product_info_test
        set  end_time = 'the 2021-04-30 23:59:59'
        where order_no = '111111111'
        and product_id = 123456
        and status in (1.2);
update pe_order_product_info_test
        set  end_time = 'the 2021-04-30 23:59:59'
        where order_no = '222222222'
        and product_id = 123456
        and status in (1.2);      
Copy the code

SQL > Update (‘ Update ‘); SQL > Update (‘ Update ‘); SQL > Update (‘ Update ‘);

There are several possible scenarios where other business parties call the select-related interface at the time of the Update operation, but a review of the call chain of the life-lock application at that point in time shows that there are no other calls that would affect the Update.

In order to further understand the situation at that time, I contacted the DBA teacher, asked for the log when the deadlock occurred, and planned to get the log to do a thorough analysis of the problem.

Index_merge index merge

1. What is index merge

This is an optimization that MySQL introduced in 5.1. Before this, only one index could be used for a table, but with the introduction of index merge, multiple indexes can be used for conditional scans of the same table.

If you want to compare index_Merge with using only one index, use the update statement above as an example.

update pe_order_product_info_test
        set end_time = 'the 2021-04-30 23:59:59'
        where order_no = '111111111'
        and product_id = 123456
        and status in (1.2);
Copy the code

MySQL select an optimal index (index_order_no, order_no = 111111111); MySQL select an optimal index (index_order_NO, order_NO = 111111111); Finally, the remaining product_ID and status criteria are filtered in memory.

However, if the MySQL optimizer feels that this second step may not be efficient if you just use one index, pull out a large number of records, and then filter them in memory using product_id and status (and there are very few record values that match this condition), it will use index merges for optimization.

If an index merge is used to determine where conditions, it will first find the ID of the PRIMARY index through the index_ORDER_NO index, then find the ID of the PRIMARY index through the index_product_id index, and finally find the intersection of the two ID sets, and then go back to the table to find the row data. (The order in which index merges use indexes is uncertain)

2. Scene repetition

There is also a Bug #77209 documented in the MySQL Bug feedback document, which notes that index merges cause deadlocks. However, I cannot reproduce the scene of index merging according to the repeat given by it. In its instance, there are 6 million random numbers earlier. I guess MySQL may raise the condition of index merging and increase the amount of data to 10 million.

I’m going to take you through what happened.

Environment: MySQL 5.6.24

  1. Create a test table

    CREATE TABLE `a` (
  `ID` int  AUTO_INCREMENT PRIMARY KEY,
  `NAME` varchar(21),
  `STATUS` int,
  KEY `NAME` (`NAME`),
  KEY `STATUS` (`STATUS`)
) engine = innodb;
Copy the code

  2. To import random data, you need to enable compatibility configuration.

    set global show_compatibility_56=on;  
Copy the code

    Start importing random data.

    set @N=0;
insert into a(ID,NAME,STATUS)
select
	@N:=@N+1.@N%1600000.floor(rand()*4)
 from information_schema.global_variables a, information_schema.global_variables b, information_schema.global_variables c 
LIMIT 10000000;
Copy the code

  3. test

    update a set status=5 where rand() < 0.005 limit 1;
explain UPDATE a SET STATUS = 2 WHERE NAME =  '1000000' AND STATUS = 5;
Copy the code

3. Why is the deadlock happening

Go straight to the previous figure and the locking process for the two UPDATE transactions.

As you can see, in the order and product model, Update transaction 1 and Update transaction 2 overlap on the product_ID index and primary index, resulting in a deadlock.

They count A transaction Transaction 2
1 Lock theindex_order_noIndex entry order_no is 2222 on the index tree
2 Lock theindex_order_noOrder_no is the index entry of 3333 on the index tree
3 Back to the table lockPRIMARYThe index entry whose ID is 11
4 Back to the table lockPRIMARYThe index entry whose ID is 12
5 Lock theindex_product_idFour index entries in the index tree with product_id 2000
6 Try to lock itindex_product_idSQL > select * from index tree where product_id = 2000;Wait until the transaction is releasedinindex_product_idThe lock on the
7 Try to lock the table backPRIMARYIn the index, index entry 12 whose id is 10,11,12,13 is found inStep 4Has been locked by transaction two,Wait for transaction two to releasein

This is where this deadlock comes in, and there are many solutions, depending on the scenario.

  1. Dropping an index is certainly not a good idea
  2. Disable the Index_Merge optimization
  3. Add joint indexes to the query criteria, in this case product_id and order_no.

4. The last

Finally, of course, these are my personal analysis, DBA teacher give advice is directly on the joint index, online information about index combined are few, in addition to the official documentation simply pulled off, the rest is reproduced to reprint blog, content is the same, the DBA teacher also don’t write blog, so I can only according to the above this line of thought, I understand, If the net friend has what question welcome to point out ~