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

This article introduces the relevant knowledge of "how to understand the MySQL wwwhj8828coml8o88O49999 deadlock problem". 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!

1 Application Log

1 how was the deadlock found?

1.1 the cause of deadlock & & Detection method

The storage engine used by our mysql is innodb, and from the log, innodb actively detects the deadlock and rolls back a long-awaited transaction. The question is, how does innodb detect deadlocks? The intuitive method is that when two transactions wait for each other, when one waiting time exceeds a set threshold, one transaction is rolled back and the other transaction can continue to execute. This method is simple and effective. In innodb, the parameter innodb_lock_wait_timeout is used to set the timeout.

Only using the above methods to detect deadlocks is too passive. Innodb also provides wait-for graph algorithm to actively carry out deadlock detection. Whenever the lock request can not immediately meet the needs and enter waiting, the wait-for graph algorithm will be triggered.

1.2 wait-for graph principle

How do we know that the four cars in the picture above are deadlocked? They wait for each other's resources and form a loop! We regard each car as a node, and when node 1 needs to wait for the resources of node 2, it generates a directed edge pointing to node 2, and finally forms a directed graph. We just need to check whether there is a loop in the digraph, and the loop is a deadlock! This is the wait-for graph algorithm.

Innodb regards each transaction as a node, and the resource is the lock occupied by each transaction. When transaction 1 needs to wait for the lock of transaction 2, it generates a directed edge from 1 to 2, and finally rows into a directed graph.

1.2 innodb isolation levels, indexes, and locks

Deadlock detection is a lifesaver given to us by innodb when deadlock occurs, we need it, but what we need more is the ability to avoid deadlock, how to avoid it as much as possible? This requires understanding the locks in innodb.

1.2.1 relationship between lock and index

Suppose we have a message table (msg) with three fields. Assume that id is the primary key, token is a non-unique index, and message has no index.

Id: biginttoken: varchar (30) message: varchar (4096)

Innodb uses a clustered index for the primary key, which is a way of data storage. The table data is stored with the primary key, and the leaf node of the primary key index stores row data. For a normal index, its leaf node stores the primary key value.

Let's analyze the relationship between indexes and locks.

1) delete from msg where id=2

Because id is the primary key, you can lock the entire row of records directly.

2) delete from msg where token=' cvs'

Because token is a secondary index, the secondary index (two rows) is locked first, and then the record corresponding to the corresponding primary key is locked

3) what is the order number of delete from msg where message='

Message does not have an index, so it does full table scan filtering. At this point, each record on the table will be added with an X lock.

1.2.2 relationship between lock and isolation level

University database principles have been learned, in order to ensure the correctness of concurrent operation data, the database will have the concept of transaction isolation level: 1) uncommitted read (Read uncommitted); 2) committed read (Read committed (RC)); 3) repeatable read (Repeatable read (RR)); 4) serializable (Serializable). We use RC and RR more often.

Commit read (RC): only committed data can be read.

RR: all queries within the same transaction are consistent at the beginning of the transaction, the default level of InnoDB.

What we are talking about in Section 1.2.1 is actually locks under the RC isolation level, which can prevent data conflicts when different transaction versions of data are modified and committed, but problems may occur when other transactions insert data.

As shown in the following figure, transaction A gets one record on the first query and two records on the second execution of the same query. Damn it from the point of view of transaction A! This is phantom reading, which cannot be avoided even though row locks are added at the RC level.

The RR isolation level of innodb can avoid phantom readings. How to achieve this? Of course we need the help of a lock!

In order to solve the problem of phantom reading, innodb introduces gap lock.

Execute in transaction A: update msg set message=' order 'where token='asd'

Innodb first adds an X lock to the records on the index, just like the RC level. In addition, it adds a lock between the non-unique index 'asd' and the adjacent two indexes.

In this way, when transaction B is executing insert into msg values (null,'asd','hello'); commit;, it will first check whether the interval is locked, if so, it cannot be executed immediately, and you need to wait for the gap lock to be released. This avoids the problem of phantom reading.

3 causes of deadlock

After understanding the basic principle of innodb lock, the cause of deadlock is analyzed below. As mentioned earlier, deadlocks are usually caused by transactions waiting for each other's resources and finally forming a loop. The following is a brief example of causing each other to wait for each other to form a loop.

3.1 Lock conflicts between rows of the same record in different tables

This situation is understandable. Transaction An and transaction B operate on two tables, but there is a loop waiting for a lock.

3.2 record row lock conflicts in the same table

This kind of situation is quite common. When two job were performing batch data updates, the id list processed by jobA was [1mem2, jobA 3], while the id list processed by job was [8, 9, 10, 10, and 4], resulting in a deadlock.

3.3 conflicts between different index locks

This situation is more obscure. When transaction An is executed, besides locking on the secondary index, it will also add locks on the clustered index. The order in which locking is added on the clustered index is [1Jing 4, 2, 2, 3, 5], while when transaction B is executed, only locks are added on the clustered index, and the locking order is [1, 2, 3, 4, 5], which results in the possibility of deadlock.

3.4 gap lock conflict

Innodb at the RR level, the following situations will also lead to deadlocks, which are more obscure. Students who are not clear can analyze by themselves according to the gap lock principle in the previous section.

4 how to avoid deadlock as much as possible

1) access tables and rows in a fixed order. For example, in the case of two job batch updates in section 2, the simple method is to sort the id list first and then execute it, so as to avoid cross-waiting for locks; for example, in the case of section 3.1, adjusting the sql order of the two transactions to the same order can also avoid deadlocks.

2) large transactions are divided into small ones. Large transactions tend to be deadlocks, and if the business allows, large transactions will be split into smaller ones.

3) in the same transaction, try to lock all the resources needed at once to reduce the probability of deadlock.

4) lower the isolation level. If the business allows, lowering the isolation level is also a better choice. For example, changing the isolation level from RR to RC can avoid many deadlocks caused by gap locks.

5) add reasonable indexes to the table. You can see that if you don't move the index, you will add a lock to each row of the table, which greatly increases the probability of deadlock.

5 how to locate the cause of deadlock

Taking the deadlock case at the beginning of this article as an example, let's talk about how to investigate the cause of deadlock.

1) locate the problem code by applying the business log, and find the sql corresponding to the corresponding transaction

Because the deadlock will be rolled back after being detected, the information will be reflected in the application's business log as an exception. Through these logs, we can locate the corresponding code and sort out the sql of the transaction.

Start tran

1 deleteHeartCheckDOByToken

2 updateSessionUser

...

Commit

In addition, based on the information of the log rollback, we found that the transaction was rolled back when a deadlock was detected.

2) determine the isolation level of the database.

Execute select @ @ global.tx_isolation to determine the isolation level of the database. The isolation level of our database is RC, which can eliminate the suspicion of deadlock caused by gap lock.

3) ask DBA to execute show InnoDB STATUS to check the log of the most recent deadlock.

This step is very critical. With the help of DBA, we can have more detailed deadlock information. From this detailed log, you can see that the transaction structure that conflicts with the previous transaction is as follows:

Start tran

1 updateSessionUser

2 deleteHeartCheckDOByToken

...

Commit

This is the end of the content of "how to understand MySQL wwwhj8828coml8o88O49999 deadlock". 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.