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

This article mainly talks about "the deadlock mechanism of MySQL and the ways to avoid deadlock". Interested friends may wish to take a look. The method introduced in this paper is simple, fast and practical. Now let the editor take you to learn "the deadlock mechanism of MySQL and the ways to avoid deadlock".

A preface

Deadlock is a technical problem that every MySQL DBA will encounter. This paper makes a summary of deadlock learning to understand what deadlock is, how MySQL detects deadlock, deals with deadlock, deadlock cases, and how to avoid deadlock.

Second deadlock

Deadlock is a common problem in concurrent systems, and it also appears in Innodb systems. A "deadlock" occurs when two or more transactions are waiting for each other to release the lock they already hold, or when a loop waits for lock resources due to inconsistent locking order. For example, A transaction holds x1 lock, applies for x2 lock, B transaction holds x2 lock, applies for x1 lock. An and B transactions hold the lock and apply for the lock held by the other party to enter the loop waiting, resulting in a deadlock.

From the definition of deadlock, there are several elements of deadlock in MySQL:

A two or more transactions.

B each transaction already holds a lock and applies for a new lock.

C lock resources can only be held by the same transaction or incompatible at the same time.

D transactions cause a loop wait because of holding the lock and applying for the lock.

Deadlock mechanism of three MySQL

The deadlock mechanism consists of two parts: detection and processing.

The transaction waiting list and the lock waiting information list are checked by wait-for graph through the transaction information, and if a closed loop is found, the transaction with a small amount of undo log is rolled back; deadlock detection itself will also calculate the cost of the detection itself, in order to deal with the unexpected situation caused by the detection timeout.

3.1 deadlock detection

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 apply for a lock and needs to wait (wait_lock), innodb starts deadlock detection (deadlock_mark)

two。 Go to the lock_deadlock_check_and_resolve () function to detect and resolve deadlocks.

3. In the process of detecting deadlock, there is a counter to limit it. If you encounter a timeout or exceed the threshold while waiting for wait-for graph detection, you will stop the detection.

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 and roll back transactions with a small number of undo.

3.2 how to handle deadlocks

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.

Lock type of four Innodb

First of all, we need to know that there are two regular lock modes for MySQL.

LOCK_S (read lock, shared lock)

LOCK_X (write lock, exclusive lock)

The easiest lock mode to understand is read plus shared lock (in share mode), write plus exclusive lock.

There are several properties of locks

LOCK_REC_NOT_GAP (lock record)

LOCK_GAP (GAP before lock record)

LOCK_ORDINARY (simultaneously lock the record + GAP before the record, that is, Next key lock)

LOCK_INSERT_INTENTION (insert intention lock, which is actually a special gap lock)

The properties of the lock can be arbitrarily combined with the lock mode. For example.

Lock- > type_mode can be Lock_X or Lock_S

Locks gap before rec is represented as gap lock: lock- > type_mode & LOCK_GAP

Locks rec but not gap is represented as a record lock, non-gap lock: lock- > type_mode & LOCK_REC_NOT_GAP

Insert intention is expressed as insert intention lock: lock- > type_mode & LOCK_INSERT_INTENTION

Waiting means lock waiting: lock- > type_mode & LOCK_WAIT

Note for more information on Innodb locks, you can go to the official documentation or MySQL engine features InnoDB transaction locking system introduction.

5 Innodb different transaction locking types

Instance update tab set Xero1 where id= 1

1 Index column is the primary key, RC isolation level

Add X locks to records

2 Index column is the second-level unique index, RC isolation level

If the id column is a unique column, it has an unique index on it. Then SQL needs to add two X locks, one corresponding to the record of id=10 on the id unique index and the other corresponding to the record of [name='d',id=10] on the clustered index.

3 Index column is a secondary non-unique index, RC isolation level

If there is a non-unique index on the id column, then all corresponding records that meet the SQL query criteria are locked. At the same time, these records on the primary key index will also be locked.

4 there is no index on the index column, RC isolation level

If there is no index on the id column, SQL will take a full scan of the clustered index to filter, because the filtering is done at the MySQL Server level. So every record, whether it meets the condition or not, is locked with an X. However, for the sake of efficiency, MySQL optimizes that records that do not meet the conditions will be locked after judgment, and finally hold the locks on records that meet the conditions, but the locking / unlocking actions on records that do not meet the conditions will not be omitted. At the same time, optimization violates the constraints of 2PL.

5 Index column is the primary key, RR isolation level

Add X locks to records

6 Index column is a secondary unique index, RR isolation level

Add two X locks to the table, one on the record that the unique index meets the criteria, and one record on the corresponding clustered index.

7 Index columns are secondary non-unique indexes, RR isolation level

Conclusion: under the Repeatable Read isolation level, there is a non-unique index on the id column, corresponding to SQL:delete from T1 where id = 10

First, locate the first record that meets the query conditions through the id index, add the X lock on the record, add the GAP lock on the GAP, then add the record X lock on the primary key clustered index, and then return; then read the next entry and repeat. Until the first record that does not meet the condition [115F], there is no need to add record X lock, but still need to add GAP lock, and finally return to the end.

8 there is no index on the index column, RR isolation level

Then lock the whole table.

Here we need to focus on the locking methods of insert and delete, because most of the cases encountered so far or some of the cases that are difficult to analyze are related to delete,insert operations.

Locking mode of insert

The locking strategy for insert when a concurrent insert causes a unique key conflict is

In the first stage, apply for LOCK_S + LOCK_ORDINARY for uniqueness constraint checking.

After the second stage acquires the lock of stage 1 and the insert is successful, the location inserted is the Gap lock: LOCK_INSERT_INTENTION, in order to prevent other insert unique key conflicts.

New data insertion: LOCK_X + LOCK_REC_NOT_GAP

For insert operations, if a unique constraint conflict occurs, the conflicting unique index needs to be appended with S Next-key Lock. From here, you will find that even at the RC transaction isolation level, there are also Next-Key Lock locks that block concurrency. However, what the document does not state is that for the only index that detected a conflict, the waiting thread needs to lock the next record after acquiring the S Lock, which is judged by the function row_ins_scan_sec_index_for_duplicate in the source code.

Quoted from the third case of deadlock

Locking mode of delete

1 in the case of a non-unique index, deleting an existing record has a gap lock, locking the record itself and the gap before the record.

2 delete an existing record in the case of a unique index and primary key, because it is a unique value, when deleting, there will be no gap.

3 non-unique index, unique index and primary key will add gap lock in this interval when deleting a record that does not exist.

4 when a record marked for deletion is deleted through a non-unique index and a unique index, the row lock of the record is requested and the gap before the record is locked.

There is no gap lock in the case of 5 RC, except in the case of a unique key conflict, such as inserting a unique key conflict.

Analysis of locking the deletion sentence in MySQL DELETE quoted from the article

Six deadlock cases

The case of deadlock is not analyzed in detail in this article. Here are several examples I have done (other cases will be shared later). The four cases are basically related to gap locks in RR mode.

One of the deadlock cases delete applied for gap lock conflict with insert gap lock resulting in deadlock

Deadlock case 2 concurrent delete there is no record to apply for gap lock resulting in deadlock

Deadlock case III deadlock case caused by two transactions concurrent insert unique key conflict and gap lock

Deadlock caused by three concurrent insert statements in deadlock case 4

7 how to view deadlocks

1. Check the waiting status of transaction locks

Select * from information_schema.innodb_locks

Select * from information_schema.innodb_lock_waits

Select * from information_schema.innodb_trx

The following query can get the database waiting in the current situation: via "innodb Technology Insider"

Select r.trx_id wait_trx_id

R.trx_mysql_thread_id wait_thr_id

R.trx_query wait_query

B.trx_id block_trx_id

B.trx_mysql_thread_id block_thrd_id

B.trx_query block_query

From information_schema.innodb_lock_waits w

Inner join information_schema.innodb_trx b on b.trx_id = w.blocking_trx_id

Inner join information_schema.innodb_trx r on r.trx_id = w.requesting_trx_id

two。 Open the following parameters for more detailed transaction and deadlock information.

Innodb_print_all_deadlocks = ON

Innodb_status_output = ON

Innodb_status_output_locks = ON

3. View innodb status (including recent deadlock logs)

Show engine innodb status

8 how to avoid deadlocks as much as possible

1 the transaction isolation level uses read committed and binlog_format=row to avoid gap lock competition caused by RR mode.

(2) the index should be designed reasonably, and the columns with high differentiation should be placed in front of the composite index, so that the business sql can locate fewer rows through the index as much as possible, and reduce lock competition.

3 adjust the execution order of the business logic SQL to prevent update/delete from holding the lock sql in front of the transaction for a long time (this optimization depends on the situation)

(4) choose a reasonable transaction size, and small transactions are less likely to have lock conflicts.

5 when accessing the same table, try to agree to access the table in the same order, and for a table, access the rows in the table in a fixed order as far as possible. This will greatly reduce the chances of deadlocks.

After version 65.7.15, a new function innodb_deadlock_detect parameter is provided, which can turn off deadlock detection and improve concurrent TPS.

At this point, I believe you have a deeper understanding of "the deadlock mechanism of MySQL and the ways to avoid deadlock". You might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

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.