Talk about MySQL deadlock one

Data is increasingly inseparable from our lives, and data has different pain points and needs and special scenarios at all stages of the life cycle.

Curd is the four basic requirements of data: Write, update, read, delete.

Today, let's talk about the deadlock problem.

Deadlock is an unavoidable problem for MySQL under high concurrency.

This sentence can be extended to four questions:

1. What is a deadlock?

When does 2.MySQL detect deadlocks?

3. How does the database system handle deadlocks?

4. What are the typical high-concurrency deadlock scenarios?

1. Let's start by looking at what a deadlock is.

In the eighth chapter of the database system implementation, the deadlock is defined in the second section.

Concurrently executed transactions reach a deadlock state due to competing resources: each transaction in several transactions waits for resources that are consumed by other transactions, so that each transaction cannot progress.

This description seems very awkward, let's take two examples to visualize:

1. Two carpenters nailed the floor, one with only one axe, and the other without a hammer, but with a nail

2. The phenomenon of traffic jams

After reading the definition and visualization of deadlocks, when will deadlock detection be performed for MySQL?

2.MySQL Deadlock detection and rollback

Here we talk about the deadlock detection of MySQL, currently only discuss the processing of InnoDB, and do not involve myrocks deadlock detection processing.

When a INNODB transaction attempts to acquire (request) a lock and waits, InnoDB detects the deadlock.

The normal process is as follows:

1.InnoDB Initializes a transaction, and InnoDB begins deadlock detection (Deadlock_mark) when the transaction tries to acquire (request) a lock and waits (Wait_lock)

2. Enter into the Lock_deadlock_check_and_resolve, the name is obvious, to detect deadlocks and resolve deadlocks

3. During the detection of deadlocks, there are also counters to limit

4. One of the logic of deadlock detection is the process of waiting for graphs, if a graph is constructed through the lock information and the transaction wait chain, a deadlock is considered to occur if a loop is present in the diagram.

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

3. How the database system handles deadlocks

We'll go back to the deadlock treatment mentioned in database system implementation.

1. Timeout deadlock detection: when there is a deadlock, it is usually not possible to want all transactions to continue executing at the same time, so at least one transaction must be aborted and restarted. Timeouts are the most straightforward way to limit and rollback transactions that are out of active time

2. Wait diagram: The implementation of the wait graph is to indicate which transactions are waiting for the locks held by other transactions, which can be added to the deadlock detection in the database to detect the formation of loops.

3. Sorting through elements to prevent deadlocks: the idea is beautiful, but the reality is brutal, and it's usually the cause of finding a deadlock to find a way to solve the deadlock.

4. Detect deadlocks with timestamps: Each transaction is assigned a timestamp and the rollback policy is based on the timestamp.

Here's an example of a wait diagram.

4. What are the typical high-concurrency deadlock scenarios?

1. Seconds to kill the scene, each second is for the same row of active transactions, a steady stream of transactions found themselves locked the row has been locked, this time InnoDB will enter an egg pain of the unnecessary deadlock detection, follow-up to everyone talk about how to solve

2. Use a two-level index to update the two-level index record with high concurrency (is that a mouthful?) ), the MySQL indexing program is not 100% accurate, I have a case in the concurrency update different records, because the indexing plan went wrong, resulting in a transaction with a two-level index read records, another transaction with the primary key to read records, resulting in a deadlock, this example will be sorted out later.

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

This problem is the key to follow up, but not finishing, first rest ...

It is recommended to read the previous article "INNODB Transaction structure code variable list", because the deadlock is in the case of active transactions waiting for the lock to detect, first to understand the INNODB transaction structure of the trx_lock_t

Talk about MySQL deadlock one