MySQL InnoDB table results in deadlock log condition analysis and induction

found that when the SQL statement of the backup table runs at the same time as the SQL statement that deletes part of the data from the table, MySQL detects the deadlock and prints out the log

Case description
During a timed script run, MySQL detects a deadlock and prints out a log when the SQL statement that backs up the table runs concurrently with the SQL statement that deletes the data from the table.
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 '
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 two statements use the Source_table table as follows:

The deadlock log prints out a point in time indicating that a deadlock occurred while the statement (1) was running and when the statement (2) began to run.
When MySQL detects a deadlock, in addition to viewing the log of MySQL, you can view the last deadlock record in the MySQL client through the show InnoDB STATUS \g statement. Since the printed statement will be messy, it is better to use the pager less command to view the results through the file content browsing method. (ends with Nopager)
The resulting deadlock record is as follows:

Depending on the result of the deadlock record, it is clear that the two statements have been deadlocked and that the lock violation occurred on the primary key index. So why is there a lock conflict for two SQL statements? Why is the conflict on the primary key index? Statement (2) Gets the primary key index lock, why do you still request the lock again?
Lock Conflict Analysis
transaction and row lock mechanism of 2.1 InnoDB
MySQL's transactional 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, and concurrency. The default behavior of MySQL is to execute a COMMIT statement after each SQL statement executes, effectively processing each statement as a separate transaction.
2.2 Two-sentence lock case
Under the InnoDB default transaction isolation level, normal Select does not require a row lock, but lock in SHARE MODE, the for update, and select in the high serialization level are locked. With one exception, in this case, the statement (1) INSERT INTO teamuser_20110121 select * from Teamuser adds a table lock to the table teamuser_20110121 (engine= MyISAM). A shared lock is also added to the primary key index (that is, clustered index) for all rows of the Teamuser table. The primary key index is used by default.
While the statement (2) Delete from Teamuser WHERE teamid= $teamId and titleweight<32768 and jointime< ' $daysago _1week ' for the delete operation, An exclusive lock is added to the primary key index of the selected row. Since this statement also uses the prefix index of the non-clustered index key ' K_teamid_titleweight_score ' (' Teamid ', ' titleweight ', ' score '), the non-clustered index of the related row is also added to the exclusive lock.
2.3 Generation of lock conflicts
Since shared locks are mutually exclusive to exclusive locks, when a party has an exclusive lock on a row record, the other party cannot have a shared lock, and the other party cannot get its exclusive lock after having its shared lock. Therefore, when the statement (1), (2) runs concurrently, the equivalent of two transactions requests a lock resource for the same record row, resulting in a lock conflict. Because two transactions will request a primary key index, the lock conflict will only occur on the primary key index.
Often see a sentence: in InnoDB, in addition to a single SQL composed of transactions, the lock is gradually obtained. That means that a transaction lock consisting of a single SQL is obtained at one time. In this case, the statement (2) has been given an exclusive lock on the primary key index, why is it also requesting an exclusive lock on the primary key index? Similarly, if the statement (1) has acquired a shared lock on the primary key index, why does it request a shared lock for the primary key index?
In a deadlock record, a transaction that waits for a lock page no is the same as the page no with transaction two holding a lock, which is 218436, and what does that mean?
Our conjecture is that obtaining row locks in the InnoDB storage engine is obtained on a line-by-row basis and is not obtained at one time. below 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 the results of GDB, a single SQL composed of transactions, from the macro point of view, the lock is in this statement once obtained, but from the bottom of the implementation, is to record the row query, to obtain a qualifying record is the index of the row record lock.
The GDB results are shown below:

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=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.


(Description: "789\200″ is a nonclustered index," \200″ is the primary key index)

GDB results show that the statement (1) (2) locks the fetch record as multiple lines, that is, to get the lock line by row, thus explaining the case where the statement (2) obtains the primary key index lock and again requests the primary key index lock.
Since the statement (1) uses the primary key index, and the statement (2) uses the nonclustered index, two transactions get the record rows in different order, while the lock process is edge-checking plus, row-by-line access, and the following occurs:

Thus, two transactions each have a partial lock and wait for the other party to hold the lock, there is such a resource cycle waiting, that is, deadlock. Lock collisions in this case are detected when the page no is 218436 and 218103 locked.
InnoDB automatically detects the deadlock of a transaction and rolls back one or more transactions to prevent deadlocks. InnoDB will choose a less expensive transaction rollback, this transaction (1) unlocks and rolls back, and the statement (2) continues to run until the end of the transaction.
InnoDB The deadlock form induces the four elements of the
deadlock: Mutual exclusion: A resource can only be used by one process at a time; request and hold condition: When a process is blocked by a request for resources, the resources that have been obtained are held up; non-deprivation conditions: process The resources that have been obtained cannot be forcibly deprived until the end of use; cyclic wait conditions: a cyclic waiting resource relationship is formed between several processes.
InnoDB detects deadlocks in two cases, one that satisfies the cyclic wait condition, and another policy: if the lock structure exceeds the maximum number set in the MySQL configuration or the lock's traverse depth exceeds the set maximum depth, InnoDB will also be judged as a deadlock (this is an improvement in performance considerations, Avoid a transaction that takes up too many resources at a time). Here, we only consider the case where the four elements of the deadlock are met. The
deadlock form is diverse, but analysis to the bottom of the InnoDB lock situation, the deadlock caused by the cyclic wait conditions can only be four forms: two tables two rows of records cross-request mutex, the same table there is a primary key index lock conflict, primary key index lock and non-clustered index lock conflict, Lock escalation caused by lock wait queue blocking.
The following first describes the data storage form of InnoDB clustered index and nonclustered index, and then interprets these four deadlock cases as a case.
4.1 Clustered Index and nonclustered index introduction
clustered index, or primary key index, is a sort of re-organization of actual data on disk to be sorted by the value of one or more columns specified, and the index page pointer to the clustered index points to the data page. Nonclustered indexes (that is, the second primary key index) do not reorganize the data in the table, and the index order is independent of the physical order of the data. The index is usually described by the B-tree data structure, so the leaf node of the clustered index is the data node, and the leaf node of the non-clustered index is still an index node, usually a pointer to the corresponding data block. The
InnoDB the leaf node in the nonclustered index contains the primary key value as a pointer. (This is to reduce the maintenance work of indexing when moving rows or data paging.) The chart is as follows:

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

Conditions:
A, two transactions operate two tables, the same row record of the same table
B, the application of the lock mutex
C. Inconsistent order of application

Primary key index lock conflict
Case: The case of this article, creating a conflict at the primary key index lock
Conditions:
A, two SQL statements that are two transactions operating on the same table, using different indexes
B, the application of the lock mutex
C, operation of multi-line records
D, the order in which records are found is inconsistent

Primary key index lock conflicts with non-clustered index locks
Case: Same row of records, two transactions using different indexes for update operations

This case relates to the Tsk_task table, and the table related fields and indexes are 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, the 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 lock 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 the record, the index lock is used first, and then, if the primary key information is modified, the primary key index lock and all nonclustered index locks are added, and the nonclustered index column values are modified to add the nonclustered index lock.
In this case, transaction one uses a nonclustered index to find and modify primary key values, and transaction two uses the primary key index to find and modify primary key values, with different lock orders resulting in resource cycle waits at the same time.
Lock escalation causes lock queue blocking
Case: Same row record, lock escalation within transaction, blocking with another waiting lock send lock queue, resulting in deadlock

Conditions:
A, two transaction operations on the same row of records
B. A transaction applies a shared lock to a record before it is upgraded to an exclusive lock
C, another transaction in the process of requesting an exclusive lock on this record

Ways to avoid deadlocks
InnoDB provides MySQL with a transaction-safe (acid-compatible) storage engine with Commit, rollback, and crash resiliency. InnoDB locks the row-level and also provides non-lock reads in the SELECT statement. These features add to multi-user deployment and performance.
However, its locking mechanism also introduces the risk of deadlocks, which requires avoiding deadlocks when the application is designed. For implicit transactions consisting of a single SQL statement, the recommended method for avoiding deadlocks is as follows:
1. If you use the Insert...select statement to back up the table with a large amount of data, operate at a separate point in time, avoid contention with other SQL statements, or use SELECT INTO outfile plus load data infile instead of Insert...select. It's not just fast, and it doesn't require locking.
2. A transaction that locks a recordset should be as brief as possible to avoid consuming too many resources at a time and conflict with records from other transactions.
3. To update or delete tabular data, the Where condition of the SQL statement is either a primary key or an index, preventing the two cases from crossing and causing a deadlock. For cases where the WHERE clause is more complex, it is passed through SQL alone and then used in the UPDATE statement.
4. SQL statements do not have too many nested tables, can be split to split, to avoid the possession of resources while waiting for resources, resulting in conflict with other transactions.
5. To run a script on a fixed point, avoid running multiple scripts that read and write to the same table at the same point in time, paying special attention to statements that lock and manipulate large amounts of data.
6. The application increases the decision to deadlock, and if the transaction ends unexpectedly, rerun the transaction, reducing the impact on functionality.

Reprinted from: http://www.jb51.net/article/32651.htm

MySQL InnoDB table results in deadlock log condition analysis and induction