Analysis and summarization of deadlock log caused by MySQL InnoDB table _mysql

Case description
During a timed script run, it is found that when the SQL statement of the backup table runs concurrently with the SQL statement that deletes the data in the table, MySQL detects the deadlock and prints out the log.
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 '
The table structure of the Teamuser table is as follows:
PRIMARY KEY (' uid ', ' Id '),
KEY ' K_id_titleweight_score ' (' id ', ' titleweight ', ' score '),
Engine=innodb
The use of the two statements on the Source_table table is as follows:

The point at which the deadlock log is printed indicates that a deadlock occurred during the operation of the statement (1) When the statement (2) was started.
When MySQL detects a deadlock, in addition to viewing the MySQL log, you can view the most recent deadlock record in the MySQL client with the show InnoDB STATUS \g statement. Because the printed statements can be messy, it is better to use the pager less command to view the results through the file content browsing method. (End with Nopager)
The resulting deadlock record is as follows:

Depending on the result of the deadlock record, it can be seen that the two statements were actually deadlocked and that the lock conflict occurred on the primary key index. So why is there a lock conflict in two SQL statements? Why is the conflict on the primary key index? The statement (2) Gets the primary key index lock, why is the lock applied again?
Lock Conflict Analysis
2.1 InnoDB transaction and ROW lock mechanism
MySQL transaction support is not tied to the MySQL server itself, but is related to the storage engine, MyISAM does not support transactions, uses table-level locks, and InnoDB supports acid transactions, row-level locks, concurrency. MySQL's default behavior is to execute a COMMIT statement after each SQL statement executes, effectively treating each statement as a separate transaction.
2.2 Two Statements lock case
Under the InnoDB default transaction isolation level, a normal select does not require a row lock, but lock in SHARE MODE, the for update, and the select in the high serialization level are locked. With one exception, in this case, the statement (1) INSERT into the teamuser_20110121 select * from Teamuser adds a table lock to the table teamuser_20110121 (engine= MyISAM). And a shared lock is added to the primary key index (that is, the clustered index) of all rows in the Teamuser table. The primary key index is used by default.
and the statement (2) deletes the from Teamuser WHERE teamid= $teamId and titleweight<32768 and jointime< ' $daysago _ 1week ' For the delete operation, the primary key index for the selected row is added with an exclusive lock. Because this statement also uses the prefix index of the nonclustered index key ' K_teamid_titleweight_score ' (' Teamid ', ' titleweight ', ' score '), it also adds exclusive locks to this nonclustered index of the related row.
2.3 The emergence of the lock conflict
Since the shared lock and exclusive lock are mutually exclusive, when one party has an exclusive lock of a row record, the other party cannot have a shared lock, and the other party cannot get its exclusive lock after the other party has its shared lock. Therefore, when the statement (1), (2) is running concurrently, the equivalent of two transactions will simultaneously request a lock resource for the same record row, resulting in a lock conflict. Because two transactions request a primary key index, the lock conflict only occurs on the primary key index.
Often see a sentence: in InnoDB, in addition to a single SQL composed of transactions, locks are gradually obtained. That means that a single SQL-composed transaction lock is acquired at once. In this case, the statement (2) has been given an exclusive lock on the primary key index, and why is the exclusive lock for the primary key index requested? Similarly, the statement (1) has been given a shared lock on the primary key index, and why is the shared lock for the primary key index requested?
In a deadlock record, the page no for a transaction waiting for a lock is the same as page no with transaction two holding a lock, and what does this mean?
Our guess is that the acquisition of row locks in the InnoDB storage engine is done on a line-by-row basis, not once. Here to prove.
analysis of deadlock generation process
The only way to know the InnoDB lock process is to run the debug version of MySQL and find the results from the GDB output. According to GDB results, a single SQL component of the transaction, from the macro point of view, the lock is the last time the statement was obtained, but from the bottom of the implementation, is a row-by-line query, to obtain a qualifying record that is the index of the row record lock.
The results of the GDB demo are as follows:

Copy Code code 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=0x2aada74c18) 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=0x2aada74c18) 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=0x2aada74c18) at lock/ lock0lock.c:2070
2070 {
 (GDB) c
continuing.

(Note: "789\200″ is not a clustered index," \200″ is the primary key index)

The GDB result shows that the statement (1) (2) locks the fetch record as multiple lines, that is, to obtain the lock on a line-by-row basis, thus explaining that the statement (2) obtains the primary key index lock and then requests the primary key index lock again.
Since the statement (1) uses a primary key index, and the statement (2) uses a nonclustered index, two transactions get the order of the record rows, while the lock process is obtained by side-by-side addition and row by line, so the following will occur:

Thus, two transactions have a partial lock and wait for the other to hold the lock, which occurs when the resource cycle waits, that is, a deadlock. A lock conflict detected at the time of this case found a lock on page no 218436 and 218103. The
InnoDB automatically detects a deadlock on a transaction and rolls back one or more transactions to prevent deadlocks. InnoDB will select a less costly transaction rollback, this transaction (1) is unlocked and rolled back, and the statement (2) continues to run until the transaction ends. The
InnoDB deadlock form induces the four elements of a
deadlock: A resource that can only be used by one process at a time; request and hold conditions: a process that is blocked by requesting resources, keeps the acquired resources in place; not deprived of conditions: process The resources that have been obtained should not be forcibly deprived until the end of use; cyclic wait condition: a cyclic wait resource relationship is formed between several processes. There are two scenarios in which the
InnoDB detects deadlocks. One is to satisfy the cyclic wait condition, there is another strategy: the lock structure exceeds the maximum number set in the MySQL configuration or the lock traversal depth exceeds the maximum depth of the setting, and InnoDB is also judged to be a deadlock (this is a performance-enhancing consideration, Avoid transactions that consume too many resources at once. Here, we only consider situations that satisfy the four-factor deadlock. The form of a
deadlock is diverse, but it is analyzed to the very bottom of the innodb lock situation, the deadlock caused by a cyclic wait condition can only be four forms: two tables and two rows record cross request mutexes, the same table exists primary key index lock conflict, primary key index lock and nonclustered index lock conflict, Lock wait queue blocking caused by lock escalation.
The following first introduces the data storage form of the InnoDB clustered index and the nonclustered index, and then explains the four deadlock cases by case.
4.1 Clustered Index and nonclustered index introduction
a clustered index, which is a primary key index, is a type that organizes the actual data on the disk to be sorted by the value of one or more of the specified columns, and 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 rearrange the data in the table, and the order of the indexes is independent of the physical ordering of the data. Indexes are usually described by b-tree data structures, so the leaf node of the clustered index is the data node, and the leaf node of the nonclustered index is still the index node, usually a pointer to the corresponding block of data. The
and the InnoDB index leaves the node contains the primary key value as a pointer. (This is to reduce the maintenance of indexes while moving rows or data paging.) The chart is as follows:

When the nonclustered index is used, the clustered index is traversed according to the resulting primary key value, and the corresponding records are obtained.
4.2 Four kinds of deadlock conditions
In InnoDB, the row lock mechanism is used, so the lock is usually obtained progressively, which determines that deadlocks are possible in InnoDB.
The four deadlock conflicts that will be shared are: The same record row index lock conflict, primary key index lock conflict, primary key index lock and nonclustered index lock conflict, lock escalation causing lock queue blocking in different tables.
Same record row lock conflict for different tables
Case: Two tables, two rows of records, cross access and request mutexes

Conditions:
A, two transactions to operate two tables, the same table of the same row Records
B, the application of the lock mutex
C, the order of the application is inconsistent

Primary key index lock conflict
Case: The case of this article, which creates a conflict in the primary key index lock
Conditions:
A, two SQL statements that two transactions operate the same table, using different indexes
B, the application of the lock mutex
C, the operation of multiple lines of records
D, inconsistencies in the order in which records are found

Primary key index lock conflict with nonclustered index lock
Case: Same row record, two transactions using different indexes for update operations

This case involves the Tsk_task table, which has related fields and indexes as follows:
ID: Primary key;
Mon_time: monitoring time;
status_id: Task status;
Index: Key_tsktask_montime2 (status_id, Mon_time).

Conditions:
A, two transactions using different indexes
B, the application of the lock mutex
C, operation of the same line of records

When you perform an update, delete operation, the data information in the table is modified. Because of the data storage structure indexed in the InnoDB storage engine, different locking sequences are performed based on the indexes used by the modified statements and the different changes to the information. When the index is used to find and modify records, the index lock is used first, and if the primary key information is modified, the primary key index lock and all nonclustered index locks are added, and the nonclustered indexed column values are added to the nonclustered index lock.
In this case, a transaction uses a nonclustered index to find and modify the primary key value, and transaction two uses the primary key index to find and modify primary key values, with different lock orders, resulting in a resource loop waiting while the runtime is running.
Lock escalation causes lock queue blocking
Case: Same line record, lock escalation within a transaction, blocking with another waiting lock send lock queue, resulting in deadlock

Conditions:
A, two transaction operations the same row record
B, a transaction to a record to apply for a shared lock, and then upgraded to exclusive locks
C, another transaction requests the exclusive lock of this record in the process

methods to avoid deadlocks
InnoDB provides MySQL with transaction security (acid-compatible) storage engine with Commit, rollback, and crash recovery capabilities. InnoDB locks the row level and also provides unlocked reads in the SELECT statement. These features add to multi-user deployment and performance.
But the mechanism of its row locks also poses a risk of deadlock, which requires avoiding deadlocks when the application is designed. For an implicit transaction consisting of a single SQL statement, the recommended way to avoid deadlocks is as follows:
1. If you use the Insert...select statement to back up the table and have a large amount of data, at a single point in time, avoid competing with other SQL statements, or use SELECT INTO OutFile plus the load data infile instead of Insert...select, this is not only fast, it does not require locking the
2. A transaction that locks a recordset should be as brief as possible so that it does not consume too many resources at once and conflicts with records processed by other transactions.
3. Update or delete table data, the SQL statement where conditions are primary keys or are indexed, to avoid two cases of intersection, resulting in deadlock. For a more complex where clause, it is used separately in the UPDATE statement after it is obtained by SQL.
4. SQL statements do not have too many nested tables to split, to avoid occupying resources while waiting for resources, resulting in conflicts with other transactions.
5. To run a script on a fixed-point, avoid running multiple scripts to read and write to the same table at the same point of time, paying special attention to statements that lock and manipulate data volumes.
6. Increase the judgment of deadlocks in the application and, if the transaction ends unexpectedly, rerun the transaction, reducing the impact on the functionality.