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

This article mainly explains "how deadlocks are produced". The content of the explanation in the article is simple and clear, and it is easy to learn and understand. please follow the editor's train of thought to study and learn how deadlocks are produced.

Part1 problem

Because innodb engine status records the details of the most recent deadlock, the information on the case site is fully available. In view of this deadlock problem, the user asked: the data is not updated by the same row and different indexes are used, so why does the deadlock occur? (the following details have been desensitized)

The two statements for deadlocks are as follows:

UPDATE tbl_deadlock SET col1 = 1, col2 = 1, update_time = 1603685523 WHERE (id1 = 6247476) AND (id2 = 74354) UPDATE tbl_deadlock SET col1 = 1, col2 = 1, update_time = 1603685523 WHERE (id1 = 6249219) AND (id2 = 74354)

The simplified MySQL deadlock information is as follows:

= 2020-10-26 12:14:30 7fd2642f5700 INNODB MONITOR OUTPUT = =. Omit...-LATEST DETECTED DEADLOCK-2020-10-26 12:12:03 7fd2846ed700 * (1) TRANSACTION: TRANSACTION 1795660514, ACTIVE 0 sec starting index read mysql tables in use 3, locked 3 LOCK WAIT 4 lock struct (s), heap size 1184, 3 row lock (s) MySQL thread id 21829887, OS thread handle 0x7fd28d14a700 Query id 178279444 172.21.0.15 username updating UPDATE tbl_deadlock SET col1= 1, col2 = 1, update_time = 1603685523 WHERE (id1 = 6247476) AND (id2 = 74354) * * (1) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 8575 page no 286947n bits 1048 index `id2`of table `lock`.`tbl_ lock`trx id 1795660514 lock_mode X waiting Record lock, heap no 429 PHYSICAL RECORD: n_fields 2 Compact format; info bits 00: len 4; hex 00012272; asc "r Ting"; 1: len 4; hex 00721f45; asc r E * * (2) TRANSACTION: TRANSACTION 1795660513, ACTIVE 0 sec fetching rows mysql tables in use 3, locked 3 20 lock struct (s), heap size 2936, 40 row lock (s) MySQL thread id 21905203, OS thread handle 0x7fd2846ed700, query id 178279443 172.21.0.15 username updating UPDATE tbl_deadlock SET col1 = 1, col2 = 1 Update_time = 1603685523 WHERE (id1 = 6249219) AND (id2 = 74354) * * (2) HOLDS THE LOCK (S): RECORD LOCKS space id 8575 page no 286947 n bits 1048index `id2`of table `lock`.`tbl _ lock`trx id 1795660513 lock_mode X Record lock, heap no 429 PHYSICAL RECORD: n_fields 2 Compact format; info bits 00: len 4; hex 00012272; asc "r politics; 1: len 4; hex 00721f45; asc r Eten; Record lock, heap no 430 PHYSICAL RECORD: n_fields 2; compact format; info bits 00: len 4; hex 00012272; asc" r politics; 1: len 4; hex 00721fe3; asc r machine; Record lock, heap no 431 PHYSICAL RECORD: n_fields 2; compact format; info bits 00: len 4; hex 00012272; asc "r politics; 1: len 4; hex 0072218f Asc r!;;... Omit a lot of Record lock... * (2) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 8575 page no 344554 n bits 120index `PRIMARY` of table `roomlock`.`tbl _ lock` trx id 1795660513 lock_mode X locks rec but not gap waiting Record lock, heap no 9 PHYSICAL RECORD: n_fields 44; compact format; info bits 00: len 4; hex 00722663; asc rustic; Omit two unrelated lines. 3: len 4; hex 005f5434; asc _ T4; 4: len 4; hex 00012272; asc "r". Many lines are omitted. * WE ROLL BACK TRANSACTION (1). Omit.

Cause Analysis of Part2

First, take a brief look at a few elements of deadlocks:

1. Mutex condition: a resource can only be occupied by one process at a time.

MySQL's locking mechanism naturally has this condition.

two。 Request and retention conditions: when a resource request is blocked, the resources already held will not be released.

This condition exists when MySQL does not trigger deadlock rollback and does not enter lockwait_timeout.

3. Non-deprivation conditions: resources that have been obtained cannot be forcibly deprived until they are used up.

MySQL's locking mechanism naturally has this condition.

4. Loop waiting condition: a loop waiting resource relationship is formed between several processes, which usually appears as a directed loop.

Because of MySQL's locking mechanism, it is only necessary to determine that there is a loop waiting for the locks of two SQL statements, and the deadlock condition will be established.

Next, the deadlock information recorded by MySQL is analyzed in detail. First, observe the transaction details of the deadlock:

LOCK WAIT 4 lock struct (s), heap size 1184, 3 row lock (s). . 20 lock struct (s), heap size 2936, 40 row lock (s)

It is obvious that these two statements involve a large number of data rows, and the user's question: the data is not updated in the same row, which is actually a misunderstanding. Then in theory, the typical deadlock scenario of "loop waiting: holding the lock needed by each other" may exist.

Next, focus on more detailed information:

* (1) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 8575 page no 286947 n bits 1048 index `id2`of table `lock`.`tbl _ lock` trx id 1795660514 lock_mode X waiting Record lock, heap no 429 PHYSICAL RECORD: n_fields 2; compact format; info bits 00: len 4; hex 00012272; asc "r politics; 1: len 4; hex 00721f45; asc r Escape;. * (2) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 8575 page no 344554 n bits 120index `PRIMARY` of table `roomlock`.`tbl _ lock` trx id 1795660513 lock_mode X locks rec but not gap waiting Record lock, heap no 9 PHYSICAL RECORD: n_fields 44; compact format; info bits 00: len 4; hex 00722663; asc rustic; Omit two unrelated lines. 3: len 4; hex 005f5434; asc _ T4; 4: len 4; hex 00012272; asc "r *; Omit a lot of lines.

The question raised by the user: why is a deadlock sent when different indexes are used? In fact, the record lock on the secondary index will eventually be added to the primary key.

This is easy to understand, if on the secondary index, search for "iphone12" by searching the commodity name index (secondary index) of the commodity table, and lock this row of data, locking the detailed data row of "iphone12". If other transactions can modify this row of data by searching the primary key, it is obviously not possible.

Therefore, in this case, although the index name recorded in the deadlock information is different, the condition of lock contention is true, that is, trx1 performs a lock operation on the primary key through the secondary index, while trx2 gets the lock on the other secondary index, but the primary key lock cannot be obtained, so it enters the waiting state. So you only need to locate the data of the specific lock and find the logical relationship of circular waiting, and you can complete the whole case analysis.

Referring to the information cited above, the information about the specific lines where the deadlock occurred is recorded in information such as 0: len 4; hex 00722663; asc r deadlock;.

The lock wait information recorded by trx1 is the secondary index id2, because id2 is a single-row index, so there are only 0 and 1 rows of information, and 0 represents the specific row id2,1 that is the primary key. Through the hexadecimal conversion tool and converting to decimal, you can find the corresponding data as follows:

Competition = 7479109 and id2 = 74354

Then take a look at the information recorded by trx2. In terms of lock waiting, the recorded information is the primary key, so there will be complete table data in this place. After filtering out the invalid data, three rows are left: 0 as the primary key and 3 as id1,4 for id2. After converting the base, the corresponding data is as follows:

Competition = 7480931 and id1 = 6247476 and id2 = 74354

As you can see, the locks that trx2 is waiting for, id1 and id2 just meet the query criteria of trx1. The first lock information held by trx2 happens to be what trx1 is waiting for:

Locks held by trx2

Then the specific scenario of this deadlock case can be explained by the legend of the directed loop:

Deadlock legend

So far, the case study of the deadlock has been completed, from the initial deadlock establishment condition analysis to the interpretation of the specific lock content, and finally completed the directed loop legend of the deadlock.

In fact, if you look at the directed loop legend of the deadlock, you will find that these two statements use two single-column indexes, so if you think about it further, if these two columns build a joint index, is it possible that the case of deadlock would not have happened?

Part3 summary

For the problem of deadlock, we only need to filter and analyze step by step according to four conditions. By interpreting the details of the deadlock scene, we can accurately restore the cause of the whole deadlock and the data rows involved. Of course, in the actual business environment, there may be more complex and hidden deadlock cases, but no matter how hidden and complex, the ideas and steps of deadlock analysis are similar.

Thank you for your reading, the above is the content of "how deadlocks are produced". After the study of this article, I believe you have a deeper understanding of how deadlocks are produced, and the specific use needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

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.