One of the most incredible MySQL deadlock analysis

One of the most incredible MySQL deadlock analysis

1. Deadlock problem Background

Do MySQL code in-depth analysis also some years, plus their own 10 years or so of database core research and development experience, since the mysql/innodb of the lock to realize the knowledge, because of this, the previous period of time, also dedicated to write a voluminous article, specifically analysis of MySQL lock implementation details: "MySQL Plus lock processing analysis".

However, yesterday "run clean" classmate in the "MySQL Plus lock processing analysis" In this blog post consulting a MySQL deadlock scene, or completely put me stumped. This deadlock, completely against my original lock knowledge system, let me baffled its solution. the spirit of the machine will not deceive, since the report of the deadlock, then there must be a deadlock principle, I re-deep analysis of the INNODB corresponding source code implementation, conducted several experiments, with just the right moment of inspiration, but really let me analyze the cause of this deadlock. The rest of this blog post, the content of the arrangement, the first is to give the "run-clean" students describe the deadlock scene, and then give my analysis. For the individual, this is a very necessary summary, for this blog post readers, hope to encounter a similar deadlock problem, can be clear about the cause of the deadlock.

1. An incredible deadlock.

"Run clean" classmate, give the deadlock scene as follows:

Table structure:

CREATE TABLE Dltask (

ID bigint unsigned not NULL auto_increment COMMENT ' AUTO id ',

A varchar (+) not NULL COMMENT ' uniq.a ',

b varchar (+) not NULL COMMENT ' uniq.b ',

C varchar (+) not NULL COMMENT ' uniq.c ',

x varchar (+) not NULL COMMENT ' data ',

PRIMARY KEY (ID),

UNIQUE KEY Uniq_a_b_c (A, B, c)

) Engine=innodb DEFAULT Charset=utf8 comment= ' deadlock test ';

a,b,c three columns, grouped into a unique index , and the primary key index is the ID column.

Transaction ISOLATION Level:

RR (repeatable Read)

There is only one SQL per transaction:

Delete from Dltask where a=? and b=? and c=?;

Execution plan for SQL:

Deadlock log:

1. Preliminary analysis

Concurrent transactions, with only one SQL statement per transaction: Deletes a record given a unique level two index key value. For each transaction, a maximum of one record is deleted, and why is a deadlock generated? This is absolutely impossible. But, in fact, the deadlock really happened. The two transactions that generate a deadlock are deleted by the same record, which should be a potential cause of the deadlock, but even if the same record is deleted, it should not, in principle, create a deadlock. Therefore, after a preliminary analysis, this deadlock is impossible to produce. This conclusion is far from enough!

1. How to read the deadlock log

Before we give a detailed explanation of the cause of this deadlock, let's take a look at how to read the deadlock log given by MySQL.

The above-printed deadlock log is generated by the lock0lock.c::lock_deadlock_recursive () function in the InnoDB engine. The transaction information in the deadlock is handled by calling the function Lock_deadlock_trx_print (), while each transaction holds and waits for the lock information generated by the Lock_deadlock_lock_print () function.

For example, more than one deadlock, there are two transactions. Transaction 1, currently working on a table (MySQL tables in use 1), holding two locks (2 lock structs, one table-level intent lock, one row lock (1 row locks)), this transaction, the statement currently being processed is a DELETE statement. At the same time, this only one row lock is in the waiting state (waiting for this lock to be granted).

Transaction 1 waits for a row lock, and the locked object is a single row on page number 12713 on the unique index Uniq_a_b_c (note: Which line is not visible.) But what can be seen is that this row lock, a total of 96 bits can be used to lock 96 rows of records, n bits 96:lock_rec_print () method). At the same time, the waiting row lock mode is next key lock (Lock_mode X). (Note: For InnoDB's lock mode, refer to my earlier PPT: InnoDB transaction/Lock/multi-version implementation analysis.) To put it simply, the next key lock has two meanings, one is to add X locks to the current record, prevent the record from being modified concurrently, and lock the gap before the record, preventing new records from being inserted into this record. )

Similarly, transaction 2 can be analyzed. There are two row locks on transaction 2, and two row locks correspond to a single record on page number 12713 on a unique index uniq_a_b_c. A row lock is held and the lock mode is x lock with no gap (note: Record lock, lock record only, but do not lock the Gap,no Gap lock before recording). A row lock is in the wait state, and the lock mode is next key lock (note: The lock mode that is waiting for transaction 1 is the same. At the same time, it is important to note that the two lock modes of transaction 2 are not consistent and not fully compatible. The lock mode is x lock with no gap, and the waiting lock mode is next key lock X. Therefore, it is not possible to say that next key lock X can be added because it holds the X lock with no gap. )。

Analyze this deadlock log and you will find a deadlock. The next key lock X for transaction 1 is waiting for transaction 2 to hold the X lock with no gap (row lock x Conflict), while the next key lock X for transaction 2, while waiting for transaction 1 is waiting for the next key to be locked (note: Here, transaction 2 is waiting for transaction 1 because Fair competition, to eliminate the 1 hunger phenomenon. ), forming a cyclic wait, the deadlock is generated.

After a deadlock is generated, the weight of the transaction 1 is smaller, and is selected as the victim of the deadlock, and is rolled back, based on the weight of the two transactions.

Based on the analysis of the deadlock log, verify that the deadlock does exist. Also, the two transactions that generate a deadlock are actually running the same equivalent delete operation based on the unique index. Now that the deadlock does exist, the next step is to catch the cause of the deadlock.

1. Deep analysis of the cause of deadlock

1. Lock logic for delete operations

In the article "MySQL Plus lock processing analysis", I analyzed in detail the locking logic corresponding to various SQL statements. For example, the DELETE statement internally contains a current read (locking read) and then deletes it by invoking the delete operation with the record returned by the current read. In this article, combination six: ID unique index +RR, you can see that, under the RR isolation level, for the query record that satisfies the condition, the record is added to an exclusive lock (x Lock), but does not lock the gap before the record (no gap lock). Corresponding to the deadlock example above this article, the lock held by transaction 2 is an exclusive lock on a record, but does not lock the gap before recording (Lock_mode X locks Rec but not gap), consistent with my previous locking analysis.

In fact, in the "MySQL Lock processing Analysis" article in the combination of seven: ID non-unique index +RR part of the last, I also raised a question: if the combination of five, combination six, for the sql:select * from t1 where id = ten for update; first query , if no records are found to satisfy the query criteria, can the gap lock still be omitted? in response to this problem, the friends involved in the experiment, the correct answer is: The gap lock can not be omitted, the first will not meet the query conditions of the record on the gap lock, to prevent the new record inserted to meet the conditions.

In fact, the above two locking strategy, are correct. The above two strategies, respectively, correspond to: 1) The record that satisfies the query condition on the unique index exists and is valid; 2) a record that satisfies the query criteria on a unique index does not exist. However, in addition to these two, there is actually a third: 3) The unique index on the record that satisfies the query condition exists but is invalid. As is known to all, deleting a record on InnoDB is not a physical deletion in real sense, but instead identifies the record as a delete state. (Note: These identities are records of deletion status, which are subsequently reclaimed by purge operations in the background and physically deleted.) However, records that delete state are stored in the index for a period of time. Under the RR isolation level, the unique index satisfies the query condition, but deletes the record, how to lock it? InnoDB here the processing strategy is different from the first two strategies, or a combination of the first two strategies: for the deletion of the condition of the record, InnoDB will be added to the record on the next key lock X (the record itself with an X lock, while locking the gap before the record, to prevent the new record inserted to meet the conditions. ) Unique query, three cases, corresponding to three kinds of locking strategy, summarized as follows:

• find the record that satisfies the condition, and the record is valid , then add x lock to the record, No Gap Lock (Lock_mode X locks Rec but not gap);

• A record is found that satisfies the condition, but the record is invalid (identified as a deleted record), the record is added next key lock (the record itself is locked, and Gap:lock_mode X before recording);

• If no record is found to satisfy the condition, then the gap lock is added to the first record that does not satisfy the condition, and the record insertion (locks Gap before rec) is guaranteed.

Here, we see the next key lock, is it familiar? Yes, in front of the deadlock in transaction 1, transaction 2 is in the waiting state of the lock, are the next key lock. Understand the three locking strategy, in fact, the construction of a certain concurrency scene, the cause of the deadlock is already ready. However, there is a prerequisite strategy that needs to be introduced, which is the deadlock prevention strategy adopted within the INNODB.

1. Deadlock prevention Strategy

Within the InnoDB engine (or inside all databases), there are various types of locks: Transaction locks (row locks, table locks), mutexes (shared variable operations that protect the internal), RWLock (also known as latch, which protect the internal page reads and modifications).

InnoDB each page is 16K, read a page, need to add s lock on the page, update a page, you need to add X lock page. In any case, the operation of a page, the page will be locked, page lock Plus, the page stored in the index records will not be modified concurrently.

Therefore, in order to modify a record, InnoDB internal how to handle:

1. According to the given query criteria, find the corresponding record page;

2. Add an X Lock (RWLock) to the page and look for a record within the page that satisfies the condition;

3. In the case of holding the page lock, the record plus transaction lock (Row lock: According to whether the record satisfies the query condition, whether the record has been deleted, corresponds to one of the 3 kinds of locking strategies mentioned above respectively);

4. Deadlock Prevention Policy : A page lock is a short-term hold lock relative to a transaction lock, while a transaction lock (row, table) is a long-held lock. Therefore, to prevent a deadlock between page locks and transaction locks. InnoDB did a deadlock prevention strategy: Holding transaction locks (row locks, table locks), you can wait for a page lock, but conversely, hold a page lock, cannot wait to hold a transaction lock.

5. Depending on the deadlock prevention strategy, when holding the page lock and the lock, the row lock needs to wait. Release the page lock, and then wait for the row lock. At this point, the row lock gets no lock protection, so after the row lock is added, the record may have been modified concurrently. Therefore, this time to re-add the page lock, re-judge the status of the record, re-under the protection of the page lock, the record lock. If the record is not concurrently modified, the second lock can be completed very quickly because it already has a lock in the same mode. However, if the record has been modified concurrently, it is possible to cause the deadlock problem mentioned earlier in this article.

1. The above InnoDB deadlock prevention processing logic, corresponding to the function, is Row0sel.c::row_search_for_mysql (). Interested friends, can follow the debugging of the function of the processing flow, very complex, but the essence of the focus of InnoDB.

1. Analyze the causes of deadlocks

Do so many of the bedding, with the delete operation of the 3 lock logic, InnoDB deadlock prevention strategy and other preparation knowledge, and then back to analyze the first mention of the deadlock problem, will hand to the turn, things half and twice.

First, suppose that there is only one record in Dltask: (1, ' A ', ' B ', ' C ', ' data '). Three concurrent transactions, while executing the following SQL:

Delete from Dltask where a= ' a ' and b= ' B ' and c= ' C ';

And the following concurrency execution logic is generated, resulting in a deadlock:

This concurrency process, which is analyzed above, fully demonstrates the cause of deadlocks in the deadlock log. In fact, according to the transaction 1 step 6, and the order between transaction 0 step 3/4, the deadlock log may also produce another situation, that is, transaction 1 waits for the lock mode of the record X Lock + No Gap Lock (Lock_mode x Locks Rec but not gap waiting). In this second case, it is also the cause of the deadlock generated by the "run-clean" students in the deadlock case, using the MySQL 5.6.15 release Test.

1. Summarize

At this point, the MySQL-based unique index of a single record of the deletion operation, but also the cause of the deadlock, has been analyzed. In fact, the difficulty of analyzing this deadlock is to understand the mysql/innodb of the lock mode, the difference of the locking mode in different situations, and the deadlock prevention strategy of INNODB handling page lock and transaction lock. With this understanding, the analysis of deadlocks will appear clear.

Finally, some prerequisites for this kind of deadlock are summarized:

• Delete operation, for the deletion of the equivalent query on a unique index, (the deletion in the range will also produce a deadlock, but the deadlock scenario is different from the scenario analyzed in this article)

• At least 3 concurrent delete operations (or more);

• Concurrent delete operation, it is possible to delete to the same record, and ensure that the deleted record must exist;

• The isolation level of the transaction is set to repeatable Read and the Innodb_locks_unsafe_for_binlog parameter is not set (this parameter defaults to false); (Read committed isolation level, because the gap lock is not added, There is no next key, so there is no deadlock)

• Using the InnoDB storage engine; (nonsense!) MyISAM engine does not have a row lock at all)

One of the most incredible MySQL deadlock analysis