Analysis and Induction of deadlock logs caused by MySQL Innodb tables

Case Description
During the running of the scheduled script, it is found that when the SQL statement for backing up the table and the SQL statement for deleting some data in the table run simultaneously, mysql will detect the deadlock and print the log.
The two SQL statements are as follows:
(1) insert into backup_table select * from source_table
(2) delete from source_table WHERE Id> 5 AND titleWeight <32768 AND joinTime <'$ daysago_1week'
TeamUserThe table structure is as follows:
Primary key ('uid', 'id '),
KEY 'K _ id_titleWeight_score '('id', 'titleweight', 'score '),
ENGINE = InnoDB
The usage of the two statements on the source_table table is as follows:

The time point at which the deadlock log is printed indicates that a deadlock occurs when Statement (1) starts running.
When mysql detects a deadlock, in addition to viewing mysql logs, you can also view the last deadlock record in the mysql client using the show InnoDB STATUS \ G statement. Since the printed statements will be messy, it is best to use the pager less command first to view the results through file content browsing, which will be clearer. (Ended with nopager)
The deadlock record is as follows:

According to the results of the deadlock record, we can see that the two statements are indeed deadlocked and the lock conflict occurs on the primary key index. So why are there lock conflicts between the two SQL statements? Why does the conflict occur on the primary key index? Statement (2) the primary key index lock is obtained. Why does the lock apply again?
Lock Conflict Analysis
2.1 innodb Transaction and row Lock Mechanism
MySQL transaction support is not bound to the MySQL server itself, but related to the storage engine. MyISAM does not support transactions and uses table-level locks. InnoDB supports ACID transactions, row-level locks, and concurrency. By default, MySQL runs a COMMIT statement after each SQL statement is executed to effectively process each statement as a separate transaction.
2.2 locking of two statements
IN the default transaction isolation level of innodb, normal SELECT statements do not require row locks, but lock in share mode, for update, and SELECT statements IN the high serialization level must be locked. In this case, the statement (1) insert into teamUser_20110121 select * from teamUser adds a table lock to teamUser_20110121 (ENGINE = MyISAM, add a shared lock to the primary key index (clustered index) of all rows in the teamUser table. The primary key index is used by default.
Statement (2) delete from teamUser WHERE teamId = $ teamId AND titleWeight <32768 AND joinTime <'$ daysago_1week' is the DELETE operation, which adds an exclusive lock to the primary key index of the selected row. Because this statement also uses the prefix index of the non-clustered index KEY 'K _ teamid_titleWeight_score '('teamid', 'titleweight', 'score, an exclusive lock will be applied to the non-clustered index of the relevant row.
2.3 generation of lock conflicts
Because shared locks and exclusive locks are mutually exclusive, when one party has an exclusive lock for a row of records, the other party cannot have a shared lock. Similarly, after one party has its share lock, the other side cannot obtain the exclusive lock. Therefore, when statements (1) and (2) run simultaneously, a lock conflict occurs because two transactions apply for a lock resource of the same record row at the same time. Because both transactions apply for primary key indexes, lock conflicts only occur on primary key indexes.
We often see one sentence: In InnoDB, except for transactions composed of a single SQL statement, locks are obtained gradually. It indicates that the transaction lock composed of a single SQL statement is obtained at a time. In this case, Statement (2) has obtained the primary key index exclusive lock. Why does it apply for the primary key index exclusive lock? Similarly, Statement (1) has obtained the shared lock for the primary key index. Why does it apply for a shared lock for the primary key index?
In the deadlock record, the page no of transaction wait lock is the same as the page no of transaction 2 holding the lock, both of which are 218436. What does this mean?
Our conjecture is that the innodb Storage engine does not obtain row-by-row locks once. The following is a proof.
Deadlock Generation Process Analysis
To understand the innodb locking process, the only way is to run the debug version of mysql and find the result from the output of gdb. According to the result of gdb, the lock is obtained last time in a single SQL statement, but from the underlying implementation, query records one by one. If a qualified record is obtained, the index of the row record is locked.
The result of Gdb is as follows:

Copy codeThe Code is as follows: (gdb) B lock_rec_lock
Breakpoint 1 at 0x867120: file lock/lock0lock. c, line 2070.
(Gdb) c
Continuing.
[Switching to Thread 1168550240 (LWP 5540)]
Breakpoint 1, lock_rec_lock (impl = 0, mode = 5, rec = 0x2aedbe01c1 "789 \ 200", index = 0x2aada734b8, thr = 0x2aada74c6) at lock/lock0lock. c: 2070
2070 {
Current language: auto; currently c
(Gdb) c
Continuing.
Breakpoint 1, lock_rec_lock (impl = 0, mode = 1029, rec = 0x2aedbc80ba "\ 200", index = 0x2aada730b8, thr = 0x2aada74c6) at lock/lock0lock. c: 2070
2070 {
(Gdb) c
Continuing.
Breakpoint 1, lock_rec_lock (impl = 0, mode = 5, rec = 0x2aedbe01cf "789 \ 200", index = 0x2aada734b8, thr = 0x2aada74c6) at lock/lock0lock. c: 2070
2070 {
(Gdb) c
Continuing.

(Description: "789 \ 200" is a non-clustered index, and "\ 200" is a primary key index)

The result of Gdb shows that the statement (1) (2) locks the obtained records into multiple rows, that is, the row-by-row locks are obtained, so that the statement (2) is interpreted) when the primary key index lock is obtained and the primary key index lock is applied again.
Because Statement (1) uses a primary key index, and statement (2) uses a non-clustered index, the order of the two transactions to Obtain Record rows is different, the locking process is to query and add and obtain data row by row. Therefore, the following situation occurs:

As a result, two transactions have partial locks and wait for the locks held by the other party. This kind of resource loop wait occurs, that is, the deadlock. In this case, a lock conflict occurs when no 218436 and 218103 are detected on the page.
InnoDB automatically detects deadlocks of a transaction and rolls back one or more transactions to prevent deadlocks. Innodb will select a transaction rollback with a low cost. The transaction (1) is unlocked and rolled back, and the statement (2) continues to run until the transaction ends.
Innodb deadlock form induction
Four elements of deadlock: mutex condition: A resource can only be used by one process at a time; request and persistence condition: when a process is blocked due to a resource request, keep the resources that have been obtained. Condition of no deprivation: the resources that have been obtained by the process cannot be forcibly deprived before they are used. Cyclic waiting conditions: several processes form a cyclic resource wait relationship that connects the beginning and the end.
Innodb detects deadlocks in two situations: one is to meet the loop wait condition, and the other is: when the lock structure exceeds the maximum number set in the mysql configuration or the traversal depth of the lock exceeds the maximum depth set, innodb will also judge as a deadlock (this is a performance improvement consideration, avoid a transaction occupying too many resources at a time ). Here, we only consider situations that meet the four elements of deadlock.
There are various deadlocks, but the bottom layer of innodb locking is analyzed. There are only four deadlocks due to loop wait conditions: the two tables apply for mutex locks for two rows of records. If the same table has a primary key index lock conflict, the primary key index lock conflicts with the non-clustered index lock, and the lock wait queue is blocked due to lock upgrade.
The following describes how to store innodb clustered indexes and non-clustered indexes, and then explains the four deadlocks in case.
4.1 Introduction to clustered index and non-clustered Index
A clustered index is a primary key index that reorganizes the actual data on the disk to sort the data by the values of one or more specified columns, the index page pointer of the clustered index points to the data page. Non-clustered indexes (that is, the second primary key index) do not re-organize the data in the table. The index order is irrelevant to the physical data arrangement order. The index is usually described by B-Tree data structure. Then, the leaf node of the clustered index is the data node, and the leaf node of the non-clustered index is still the index node, usually a pointer points to the corresponding data block.
The leaf node of the non-clustered index of innodb contains the primary key value as the pointer. (This is to reduce the index maintenance work when moving rows or data pages .) The structure is as follows:

When a non-clustered index is used, the clustered index is traversed Based on the obtained primary key value to obtain the corresponding record.
4.2 four deadlocks
In InnoDB, the row lock mechanism is used, so the locks are usually obtained gradually, which determines the possibility of a life-and-death lock in InnoDB.
The lock conflicts of the four deadlocks to be shared are: the same record row index lock conflicts, primary key index lock conflicts, primary key index locks conflict with non-clustered index locks, and lock upgrades cause lock queue blocking.
Same record row lock conflicts for different tables
Case: two tables and two rows of records, cross-obtain and apply for mutex lock

Condition:
Transactions A and two operate on the same row records of the two tables and the same table respectively.
B. The applied locks are mutually exclusive.
C. inconsistent application order

Primary Key index lock conflict
Case: The case in this article conflicts with the primary key index lock.
Condition:
A. Two SQL statements are used to operate the same table and use different indexes.
B. The applied locks are mutually exclusive.
C. Multi-row operation records
D. The order of records found is inconsistent.

Primary Key index lock conflicts with non-clustered index lock
Case: For the same row record, two transactions use different indexes for update operations

This case involves the TSK_TASK table. The related fields and indexes of this table are as follows:
ID: primary key;
MON_TIME: monitoring time;
STATUS_ID: Task status;
Index: KEY_TSKTASK_MONTIME2 (STATUS_ID, MON_TIME ).

Condition:
A. Two transactions use different indexes
B. The applied locks are mutually exclusive.
C. operate on the same row record

When the update and delete operations are performed, the data information in the table is modified. Because of the index data storage structure in the innodb Storage engine, different locking sequence is executed based on the index used by the modification statement and the modification information. When indexes are used to search for and modify records, the index lock is applied first. If the primary key information is modified, the primary key index lock and all non-clustered index locks are applied, this non-clustered index lock is applied when the value of the non-clustered index column is modified.
In this case, transaction 1 uses a non-clustered index to search for and modify the primary key value. Transaction 2 uses a primary key index to search for and modify the primary key value. The locking sequence is different, this leads to a resource loop wait during simultaneous running.
Lock upgrade causes lock queue Blocking
Case: The transaction locks are upgraded in the same row, and the lock queue is blocked with the other lock sending queue, resulting in a deadlock.

Condition:
A. Two transactions operate on the same row record
B. A transaction applies for a shared lock for a certain record before upgrading to an exclusive lock.
C. Apply for exclusive locks for this record in another transaction

How to avoid deadlocks
InnoDB provides MySQL with a transaction security (ACID-compatible) storage engine with the capabilities of commit, rollback, and crash recovery. InnoDB locks row-level and also provides non-locked read in SELECT statements. These features increase the deployment and performance of multiple users.
But its row lock mechanism also brings about the risk of deadlock, which needs to avoid deadlock during application design. For an implicit transaction composed of a single SQL statement, the recommended method to avoid deadlock is as follows:
1. If insert... The select statement backs up the table with a large amount of data. You can operate the table at a separate time point to avoid competing for resources with other SQL statements, or use select into outfile with load data infile instead of insert... Select, this is not only fast, but also does not require locking
2. for a transaction that locks the record set, the operation result set should be as short as possible to avoid occupying too much resources at a time and conflicting with the records processed by other transactions.
3. Update or delete table data. The where condition of the SQL statement is either a primary key or an index, avoiding crossover between the two conditions and causing a deadlock. When the where clause is complex, you can obtain it through SQL and then use it in the update statement.
4. Do not use too many nested tables for SQL statements. You can split the tables to avoid occupying resources and waiting for resources, resulting in conflicts with other transactions.
5. When you run scripts at a specified point, avoid running multiple scripts that read and write the same table at the same time point. Pay special attention to statements that lock and operate on a large amount of data.
6. Add a deadlock judgment in the application. If the transaction ends unexpectedly, re-run the transaction to reduce the impact on the function.