Mysql index merge queries cause deadlock problems

Even though distributed locks were added to the production environment, one transaction would not complete and the next transaction would come in to modify the data, which would result in a wait. Finally, the wait timed out and the transaction was rolled back. For the first time in several months, the deadlock occurred with two UPDATE statements. Using INTERSECT (index1,index2); mysql optimizer may merge two indexes in SQL statement where statement. Using INTERSECT (index1,index2); Index1 and index2 are merged to query. There are only two indexes index1 and index2 in this table.

UPDATE a SET a.data = a.data - 1  WHERE  index1_id = xxx AND index2_id = xxx
Copy the code

The first problem is that the first transaction has not released the occupied resources, so the second transaction cannot obtain resources, but in fact, there is no business relationship between the two transactions, and there should not be a conflict. Through explain analysis, index_merge appears in the type field, indicating that the whole table has been queried. It is possible that the index2 table was locked at the intersection of index1 and index2 indexes, causing other transactions to fail.

You can use show engine innodb status\G; To view information about the last deadlock.

As for why the mysql optimizer merges indexes, the optimizer chooses indexes to find an optimal execution plan and to execute statements with minimal cost. In a database, the number of rows scanned is one of the factors affecting the execution cost. Fewer rows scanned means less disk data is accessed and less CPU resources are consumed. The number of rows scanned is not the only judgment criterion. The optimizer also makes a comprehensive judgment based on whether to use temporary tables and whether to sort. We can look at the number of rows scanned by Explain.

Execute the original SQL statement with 1 row scan because the optimizer feels that the data can be uniquely determined through two indexes

+----+-------------+----------+------------+-------------+---------------------------------+---------------------------- -----+---------+------+------+----------+---------------------------------------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+----------+------------+-------------+---------------------------------+---------------------------- -----+---------+------+------+----------+---------------------------------------------------------------+ | 1 | UPDATE | A | NULL | index_merge | index1, index2 | index1, index2 4, 4 | | NULL | 1 | | 100.00 Using intersects (index1 index2); Using where | +----+-------------+----------+------------+-------------+---------------------------------+---------------------------- -----+---------+------+------+----------+---------------------------------------------------------------+Copy the code

Use force index() to specify indexes separately, starting with force index(index1) and scanning 3 rows

+----+-------------+----------+------------+-------+-----------------+-----------------+---------+-------+------+------- ---+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+----------+------------+-------+-----------------+-----------------+---------+-------+------+------- + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + | 1 | UPDATE | recharge | NULL | range | index1 | index1 | | 4 const | 3 | | 100.00 Using the where | +----+-------------+----------+------------+-------+-----------------+-----------------+---------+-------+------+------- ---+-------------+Copy the code

Using force index(index2), the number of scanned rows is 114

+----+-------------+----------+------------+-------+-----------------+-----------------+---------+-------+------+------- ---+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+----------+------------+-------+-----------------+-----------------+---------+-------+------+------- + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + | 1 | UPDATE | recharge | NULL | range | index2 | index2 | | 4 const | 114 | | 100.00 Using the where | +----+-------------+----------+------------+-------+-----------------+-----------------+---------+-------+------+------- ---+-------------+Copy the code

So it makes sense for the optimizer to merge the two indexes, but does not expect to block other transactions

Solution:

  1. Using force index() in SQL statements to specify the index to use is inconvenient in real development
  2. Turn off the index Merge optimization for the optimizer
  3. Removing index2 or index1 indexes can also solve the problem, leaving the optimizer to use only one index
  4. Add a federated index that contains index1+index2, and the optimizer will use that index directly

Adding a reasonable index is the most appropriate approach.