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

How to solve the deadlock list in RR mode of MySQL. In view of this problem, this article introduces the corresponding analysis and solution in detail, hoping to help more partners who want to solve this problem to find a more simple and feasible method.

Environment: version 5.7.29 RR isolation level 1. Case simulation CREATE TABLE `t8` (`id` bigint (20) NOT NULL AUTO_INCREMENT, `did` varchar (40) NOT NULL DEFAULT'', `bid` varchar (40) NOT NULL DEFAULT'', `is_ dropped` tinyint (1) NOT NULL DEFAULT '0percent, `u_ c` varchar (10) NOT NULL DEFAULT', PRIMARY KEY (`id`), UNIQUE KEY `DealerAndBrokerAndDropped` (`did`, `bid`, `is_ dropped`) ENGINE=InnoDB Insert into T8 values; insert into T8 values); insert into T8 values (7 priors, 7 priors); insert into T8 values (8 priors, 8 priors; 8 values); insert into T8 values (9 miners, 9 miners); insert into T8 values Insert into T8 values (10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10

The execution statement is as follows:

| | S1 | S2 | |

| |-|-|

| | begin |

| | select upright c from T8 where dailided cards, 1 'and bounded idles, 1' and is_dropped=0 for update; |

| select Ubunc from T8 where dumped and is_dropped=0 for update; 1 'and blocked 1' and is_dropped=0 for update; is in a blocked state |

| | update T8 set upright clocked rollback b' where dwindled rollback'1' and bounded rollback;-this triggers a deadlock S2 rollback |

The deadlock is recorded as follows:

2. Deadlock analysis

After careful analysis, we will find that trx id 5679 is finally blocked and the lock that needs to be acquired is (lock_mode X waiting), which occurs on the index DealerAndBrokerAndDropped, that is, it is a next key lock and the mode to be acquired is LOCK_X, which is in a waiting state.

And let's take a look at what locks were acquired before trx id 5679? As you can obviously see as (lock_mode X locks rec but not gap), the fetch occurs on the index DealerAndBrokerAndDropped, that is, this is a key lock and the fetch mode is LOCK_X.

But we need to know that DealerAndBrokerAndDropped is a unique index, and it's easy for us to understand how to get key lock, but why do we also have to get next key lock? Let's put this question down for a moment, and first analyze the whole process of deadlock generation.

S1 (select operation)

The index data on the unique index DealerAndBrokerAndDropped is obtained through the unique index positioning index data.

LOCK_REC_NOT_GAP | LOCK_X. The data to obtain the success record is the data of "is_dropped=0" 1 'bounded idles ".

S1 (select operation)

Go back to the table to get all the data, which requires the corresponding row lock on the primary key. LOCK_REC_NOT_GAP | LOCK_X obtained successfully

S2 (select operation)

Locates the index data through the unique index in an attempt to obtain the

LOCK_REC_NOT_GAP | LOCK_X. The data to get the failure record is the data such as "is_dropped=0", which is waiting.

S1 (update operation)

Find the data through the index DealerAndBrokerAndDropped (note that this is no longer a unique positioning operation, which will be analyzed below). At this time, you first need to obtain the first piece of data that needs to be updated through the query conditions. In fact, this is also the data that needs to be updated at this time, and the lock to be acquired is LOCK_ order [next _ key_lock] | LOCK_X. At this time, I found that although S1 acquired the lock of this data before, the lock mode has changed (consistent will not be re-acquired, this behavior will be analyzed below), so it needs to be re-acquired here, but this is obviously not possible. Because S2 is still waiting, so there is also a wait.

Therefore, through this process, there is a deadlock, such as S2, S1, S1 and S2.

Third, about the change of lock mode

With regard to the function lock_rec_lock_fast, we refer to the function here. Quick locking will be performed here without row lock conflict verification. If the lock mode has not changed, it will also be locked here (that is, skipped directly). Of course, if there is no row lock in the block, it will be locked here. This is the judgment that every row locking operation must go through. If it cannot be locked quickly, enter the slow locking mode. Here's a look at the following code:

If (lock_rec_get_next_on_page (lock) | | lock- > trx! = trx | | lock- > type_mode! = (mode | LOCK_REC) | | lock_rec_get_n_bits (lock) trx! = trx determines whether this locked transaction and the last locked transaction are the same transaction, and lock- > type_mode! = (mode | LOCK_REC) determines whether the locking mode is the same. If the condition can not be met, it is determined to be LOCK_REC_FAIL, and enter the slow locking mode.

In our S1 lock, the first time is LOCK_REC_NOT_GAP | LOCK_X, and the second time is LOCK_ order [next _ key_lock] | LOCK_X, which has obviously changed, so when we enter the slow locking phase to verify conflicts, the results will be conflicted. This is one of the reasons for Ben's deadlock.

Fourth, about the origin of LOCK_ Ordinal [next _ key_lock]

This is one of the most important reasons for Ben's deadlock, and this case will be understood by knowing this reason. First, let's look at this update statement:

Update T8 set upright cymbals, where dudes, idlers, 1' and bundles, etc.

We found that at this time, the unique index still lacks a condition, that is, the is_dropped field. At this time, the location query will not be judged as a unique query, but a common secondary index location method. At this time, it is very natural for RR mode to appear LOCK_ order [next _ key_lock]. The following is the judgment process, and the code is located in row_search_mvcc.

(match_mode = = ROW_SEL_EXACT & & dict_index_is_unique (index) & & dtuple_get_n_fields (search_tuple) = = dict_index_get_n_unique (index) & & (dict_index_is_clust (index) | |! dtuple_contains_null (search_tuple)

To explain a little, the unique search criteria include at least the following three points:

The index is unique

The number of fields queried is the same as the number of index unique fields

Is the primary key or the query condition does not contain a null value

Note that the third point of the source code is as follows:

/ * Note above that a UNIQUE secondary index can contain many rows with the same key value if one of the columns is the SQL null. A clustered index under MySQL can never contain null columns because we demand that all the columns in primary key are non-null. , /

Only if the above four conditions are met can it be confirmed as a unique lookup. This query failed because Article 3 is not satisfied.

Not only that, if this data is successfully locked, then you will see the following results:

-TRANSACTION 25830, ACTIVE 2 sec4 lock struct (s), heap size 1160, 3 row lock (s), undo log entries 1MySQL thread id 5, OS thread handle 140737101231872, query id 4115 localhost root startingshow engine innodb statusTABLE LOCK table `test`.`t8` trx id 25830 lock mode IXRECORD LOCKS space id 1050 page no 4 n bits 80 index DealerAndBrokerAndDropped of table `test`.`t8` trx id 25830 lock_mode XRecord lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 00: len 1; hex 31; asc 1; 1: len 1; hex 31; asc 1; 2: len 1 Hex 80; asc;; 3: len 8; hex 80000000000001; asc; RECORD LOCKS space id 1050 page no 3 n bits 80 index PRIMARY of table `test`.`t8` trx id 25830 lock_mode X locks rec but not gapRecord lock, heap no 2 PHYSICAL RECORD: n_fields 7; compact format; info bits 00: len 8; hex 8000000000000001; asc;; 1: len 6; hex 00000064e6; asc d; 2: len 7; hex 5f000000430110; asc _ C; 3: len 1; hex 31; asc 1; len 1 Hex 31; asc 1 page no; 5: len 1; hex 80; asc; 6: len 1; hex 62; asc bscape LOCKS space id 1050 page no 4 n bits 80 index DealerAndBrokerAndDropped of table `test`.`t8` trx id 25830 lock_mode X locks gap before recRecord lock, heap no 11 PHYSICAL RECORD: n_fields 4; compact format; info bits 00: len 2; hex 3130; asc 10; 1: len 2; hex 3130; asc 10; 2: len 1; hex 80; asc; 3: len 8; hex 80000000000a; asc

We found that the shock record of the DealerAndBrokerAndDropped unique index also added gap lock, which is completely the locking behavior of the RR schema non-unique index.

Last

If we put the statement

Update T8 set upright cymbals, where dudes, idlers, 1' and bundles, etc.

Modify to

Update T8 set upright cymbals, where dudes, idlers, 1' and bundles, 1' and is_dropped=0

Then the deadlock will not be triggered. The reason is that as we said in the third part, the lock pattern here is exactly the same and will not lead to the locking operation.

This is the answer to a series of questions about how to solve the deadlock in MySQL's RR mode. I hope the above content can be of some help to you. If you still have a lot of doubts to be solved, you can follow the industry information channel for more related knowledge.

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.