Shulou(Shulou.com)05/31 Report--

This article introduces the relevant knowledge of "how to solve the deadlock caused by MySQL optimization index merge". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

Background

There is a deadlock pipeline in the production environment. By looking at the deadlock log, we can see that the deadlock is caused by two identical update statements (only the values in the where condition are different)

As follows:

UPDATE test_table SET `status` = 1 WHERE `statusid` = 'xxx1' AND `status` = 0 absolutely update test_table SET `status` = 1 WHERE `statusid` =' xxx2' AND `status` = 0

At first, it is difficult to understand, through a large number of queries and learning, analyzed the specific principle of deadlock formation, specially shared with you, hoping to help friends who encounter the same problems.

Because there are many MySQL knowledge points, many nouns are not introduced too much here. Friends who are interested can carry out special in-depth study later.

Deadlock log * (1) TRANSACTION:TRANSACTION 791913819, ACTIVE 0 sec starting index read, thread declared inside InnoDB 4999mysql tables in use 3, locked 3LOCK WAIT 4 lock struct (s), heap size 1184, 3 row lock (s) MySQL thread id 462005230, OS thread handle 0x7f55d5da3700, query id 2621313306 x.x.x.x test_user Searching rows for updateUPDATE test_table SET `status` = 1 WHERE `status` = 'xxx1' AND `status` = 0 * * (1) WAITING FOR THIS LOCK TO BE GRANTED:RECORD LOCKS space id 110 page no 39167 n bits 1056 index `idx_ status` of table `test`.`test _ table` trx id 791913819 lock_mode X waitingRecord lock, heap no 495PHYSICAL RECORD: n_fields 2; compact format Info bits 0status` * (2) TRANSACTION:TRANSACTION 791913818, ACTIVE 0 sec starting index read, thread declared inside InnoDB 4999mysql tables in use 3, locked 35 lock struct (s), heap size 1184, 4 row lock (s) MySQL thread id 462005231, OS thread handle 0x7f55cee63700, query id 2621313305 x.x.x.x test_user Searching rows for updateUPDATE test_table SET `status` = 1 WHERE `statusid` = 'xxx2' AND `status` = 0 * (2) HOLDS THE LOCK (S): RECORD LOCKS space id page no 39167 n bits 1056 index `idx_ status` of table `test`.`test _ table` trx id 791913818 lock_mode XRecord lock, heap no 495PHYSICAL RECORD: n_fields 2; compact format; info bits 0standards * (2) WAITING FOR THIS LOCK TO BE GRANTED:RECORD LOCKS space id 110page no 41569 n bits 88 index `PRIMARY`of table `test`.`test _ table` trx id 791913818 lock_mode X locks rec but not gap waitingRecord lock, heap no 14 PHYSICAL RECORD: n_fields 30 compact format; Info bits 0 customers * WE ROLL BACK TRANSACTION (1)

Briefly analyze the deadlock log above:

1. In the first block of content (lines 1 to 9), behavior 6 is the SQL statement executed by transaction (1). Lines 7 and 8 mean that transaction (1) is waiting for the X lock on the idx_status index.

2. In the second block (lines 11 to 19), the SQL statement executed by behavior 16 transaction (2), lines 17 and 18 mean that the transaction (2) holds the X lock on the idx_status index

3. The third piece of content (lines 21 to 23) means that transaction (2) is waiting for an X lock on the PRIMARY index. (but not gap means not a gap lock)

4. The last sentence means that MySQL rolls back the transaction (1).

Table structure CREATE TABLE `idx_ Table` (`id` int (11) NOT NULL AUTO_INCREMENT, `trans_ id` varchar (21) NOT NULL, `status` int (11) NOT NULL,PRIMARY KEY (`id`), UNIQUE KEY `idx_ id` (`statusid`) USING BTREE,KEY `idx_ status` (`status`) USING BTREE) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8

You can see from the table structure that there is a unique index uniq_trans_id on the trans_id column, a normal index idx_status on the status column, and id as the primary key index PRIMARY.

There are two indexes in the InnoDB engine:

Clustered index: the data storage is put together with the index, and the leaf nodes of the index structure hold the row data.

Secondary index: the secondary index leaf node stores the primary key value, that is, the key value of the clustered index.

The primary key index PRIMARY is a clustered index, and row data is stored in the leaf node. The uniq_trans_id index and the idx_status index are secondary indexes, and the leaf node holds the primary key value, that is, the id column value.

When we look for row data through the secondary index, we first find the primary key id through the secondary index, and then do a secondary search (also known as back to the table) through the primary key index, and finally find the row data.

Carry out the plan

By looking at the execution plan, we can see that the update statement uses index merging, that is, this statement uses both uniq_trans_id index and idx_status index. Using intersect (uniq_trans_id,idx_status) means to obtain the intersection through two indexes.

Why index_merge (Index merge) is used

Before MySQL5.0, a table could only use one index at a time, and multiple indexes could not be used for conditional scanning at the same time. However, index merge optimization technology has been introduced since 5.1. multiple indexes can be used for conditional scanning on the same table.

Such as executing the statements in the plan:

UPDATE test_table SET `status` = 1 WHERE `status` = '38' AND `status` = 0

According to the condition of trans_id = '38', MySQL will use the uniq_trans_id index to find the id value stored in the leaf node; at the same time, according to the condition of status = 0, the idx_status index will be used to find the id value stored in the leaf node; then the two sets of id values found will be intersected, and finally returned to the table through the intersected id, that is, the row data stored in the leaf node can be found through the PRIMARY index.

Many people may have doubts here. Uniq_trans_id is already a unique index, and you can only find at most one piece of data through this index, so why does the MySQL optimizer use two indexes to take the intersection and then return to the table for query? this is not an extra process of idx_status index lookup. Let's analyze the implementation process of these two situations.

The first uses only the uniq_trans_id index:

According to the query condition of trans_id = '38', the id value saved in the leaf node is found by using the uniq_trans_id index.

Through the id value found, the row data saved in the leaf node is found by using the PRIMARY index.

Then the found row data is filtered by the status = 0 condition.

The second uses index merge Using intersect (uniq_trans_id,idx_status):

According to the query condition of trans_id = '38', the id value saved in the leaf node is found by using the uniq_trans_id index.

According to the query condition of status = 0, the id value saved in the leaf node is found by using the idx_status index.

Intersect the id values found in 1ap2, and then use the PRIMARY index to find the row data stored in the leaf node.

The main difference between the above two cases is that the first is to find the data through an index, and then filter it with other query conditions; the second is to take the intersection of the id values found by the two indexes, and if there is still an id value after the intersection, then go back to the table to get the data.

Index merging occurs when the optimizer believes that the execution cost of the second case is less than that of the first. (there is very little data with status = 0 in the production environment flow table, which is one of the reasons why the optimizer considers the second case.)

Why is it deadlocked when using index_merge?

The above briefly depicts the locking process of two update transactions, and you can see that there is a coincident intersection on both the idx_status index and the PRIMARY (clustered index), which creates conditions for deadlocks.

For example, a deadlock occurs when the following timing is encountered:

Transaction 1 waits for transaction 2 to release the lock, and transaction 2 waits for transaction 1 to release the lock, resulting in a deadlock.

When MySQL detects a deadlock, it automatically rolls back the lower-cost transaction. For example, in the sequence diagram above, transaction 1 holds fewer locks than transaction 2, then MySQL rolls back transaction 1.

Solution one, from the code level

In the where query condition, only trans_id is passed. After querying the data, determine whether the status status is 0 at the code level.

Use force index (uniq_trans_id) to force query statements to use uniq_trans_id indexes

The where query condition is directly followed by the id field, which is updated by the primary key.

Second, from the MySQL level

Delete the idx_status index or create a federated index that contains the two columns

Turn off index merge optimization for the MySQL optimizer.

This is the end of the content of "how to solve the deadlock caused by MySQL optimized index merge". Thank you for reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.