Shulou(Shulou.com)06/01 Report--

Data is more and more inseparable from our lives. Data has different pain points and needs as well as special scenarios at different stages of the life cycle.

CURD is the four basic requirements of data: write, update, read, and delete.

Today, let's talk about deadlock.

Deadlock is an unavoidable problem for MySQL under high concurrency.

This sentence can lead to four questions:

1. What is a deadlock?

When will 2.MySQL detect deadlocks?

3. How does the database system handle deadlocks?

4. What are the typical high concurrency deadlock scenarios?

1. Let's first see what a deadlock is.

This is how deadlocks are defined in Chapter 8, Section 2 of "Database system implementation"

Concurrently executed transactions reach a deadlock state due to competition for resources: each transaction of several transactions is waiting for resources occupied by other transactions, so each transaction cannot make progress.

This description seems to be a mouthful. Let's give two examples to visualize it:

1. Two carpenters nailed the floor, one only held an axe, while the other had a nail without a hammer

two。 Traffic jam phenomenon

After reading the definition description and visual understanding of deadlock, when will deadlock detection be carried out for MySQL?

Deadlock Detection and rollback of 2.MySQL

Here we talk about the deadlock detection of MySQL. At present, we only discuss the processing of InnoDB, but not the deadlock detection of MyRocks.

When an InnoDB transaction attempts to acquire (request) a lock and needs to wait, InnoDB performs deadlock detection.

The normal process is as follows:

1.InnoDB initializes a transaction, and when the transaction attempts to acquire (request) to add a lock and needs to wait (wait_lock), innodb starts deadlock detection (deadlock_mark).

two。 Enter lock_deadlock_check_and_resolve, the name is obvious, to detect deadlocks and resolve deadlocks

3. In the process of detecting deadlock, there is also a counter to limit it.

4. One of the logic of deadlock detection is to wait for the processing of the graph. If a graph is constructed from the information of the lock and the transaction waiting chain, if there is a loop in the graph, the deadlock is considered to have occurred.

5. For the rollback of deadlocks, one of the processing logic of internal code is to compare the number of undo

3. How to deal with deadlock in database system

Let's go back to the deadlock handling mentioned in Database system implementation.

1. Timeout deadlock detection: when there is a deadlock, it is usually impossible to expect all transactions to continue to execute at the same time, so at least one transaction must be aborted and restarted. Timeouts are the most direct way to limit and roll back transactions that are out of active time

two。 Waiting diagram: the implementation of the waiting graph can indicate which transactions are waiting for locks held by other transactions. This mechanism can be added to the deadlock detection of the database to detect the formation of rings.

3. Preventing deadlocks through element sorting: this is a beautiful idea, but the reality is cruel. It is usually found that deadlocks are found before trying to solve the causes of deadlocks

4. Deadlock detection through timestamp: each transaction is assigned a timestamp, and the rollback strategy is carried out according to the timestamp.

Here is an example of a waiting diagram.

4. What are the typical high concurrency deadlock scenarios?

1. In the second kill scene, each second kill is aimed at active transactions on the same line, and a steady stream of transactions find that the line they have locked has already been locked. At this time, InnoDB will enter an unnecessary deadlock detection that is painful. Later, I will tell you how to solve it.

two。 It's a mouthful to use secondary indexes to update secondary index records with high concurrency. MySQL's index plan is not 100% accurate. When case updates different records concurrently, because the index plan goes wrong, one transaction uses the secondary index to read the record, and another transaction uses the primary key to read the record, resulting in a deadlock. This case will also be sorted out later.

Finally, how does the source code of MySQL carry out deadlock detection and processing?

This question is the key to the follow-up, but before finishing, take a rest.

It is recommended to read the previous article "InnoDB transaction structure Code variable list", because deadlocks are detected only when active transactions are waiting for locks, and you need to know the trx_lock_t of InnoDB transaction structures first.

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.