preface

MySQL deadlock exception is an online exception category that we often encounter. Once the online business is complicated, lock conflicts often occur between various business operations, some of which may lead to deadlock exceptions. This kind of deadlock exception usually reappears at a specific time for specific data and specific business operations. In addition, it is necessary to understand the knowledge of MySQL lock conflict when analyzing and solving these deadlock exceptions. Therefore, it is often difficult to deal with these deadlock exceptions occasionally.

This article will explain how to troubleshoot and handle deadlock exceptions if they occur online. In addition to the principle of locking and lock conflicts explained in the previous series, we also need to analyze MySQl deadlock logs and binlog logs.

The body of the

** In daily work, we should have our own SOP (standard operation process) ** to deal with all kinds of online anomalies, which can not only improve our efficiency in dealing with problems, but also help to promote good processing process to the team and improve the team’s overall ability to deal with anomalies.

Therefore, in the face of the occasional MySQL deadlock problem, my troubleshooting process is as follows:

  1. Deadlock exception found in online error log alarm

  2. View the error log stack information

  3. View logs about MySQL deadlocks

  4. View the execution content of deadlock-related transactions based on binlog

  5. Based on the above information, find out the SQL operations performed by two mutually deadlocked transactions, and analyze and infer the cause of the deadlock according to the theoretical knowledge about locking introduced in this series

  6. Modifying business code

You can follow steps 1,2 to find the specific business that rolled back the transaction when the deadlock exception occurred, and thus the SQL statement executed by the transaction. Then we need to go through steps 3 and 4 to find the SQL statement executed by the other transaction at the time of the deadlock exception, that is, the transaction that finally acquired the lock, and then perform lock conflict-related analysis.

The first and second steps of the online error log and stack information is generally easy to obtain, the fifth step of the analysis of the cause of SQL lock conflict involved in the lock related theory are introduced in the series of articles, students who do not understand the following can read.

Let’s focus on the third and fourth steps, which are how to look at deadlock logs and binlog logs to find deadlock related SQL operations.

Deadlock log retrieval

After a deadlock exception occurs, you can run the show engine Innodb status command to obtain the deadlock information. However, this command can obtain only the latest deadlock information. So, we can get real-time deadlock information by enabling InnoDB’s monitoring mechanism, which periodically (every 15 seconds) prints InnoDB’s running status to the error log file of the mysqld service.

The Standard InnoDb Monitor and InnoDb Lock Monitor are important monitoring methods. They can be enabled by corresponding system parameters.

Set GLOBAL innodb_status_output=ON; Set GLOBAL innodb_status_output=OFF; Set GLOBAL innodb_status_output_locks=ON; Set GLOBAL innodb_status_output_locks=OFF;Copy the code

In addition, MySQL provides a system parameter innodb_print_all_deadlocks specifically for logging deadlocks. When a deadlock occurs, the deadlock log is logged to MySQL’s error log file.

set GLOBAL innodb_print_all_deadlocks=ON;
Copy the code

Deadlock log analysis

Using the above method, we can get the deadlock log, which is shown below when I do my experiment to trigger the deadlock exception (some information is omitted).

The log lists the time when the deadlock occurred, the deadlock-related transactions, and shows the SQL statements executed at the time of the deadlock, the locks held or waiting information, and the transactions that were eventually rolled back for both transactions (unfortunately, only two transactions are displayed when multiple transactions are deadlocked).

Now, let’s interpret the information in the log paragraph by paragraph, in the order indicated in the figure:

TRANSACTION 2078, ACTIVE 74 sec starting index read// -1 Basic information about transaction 1, including transaction ID, active time, and current running statusCopy the code

74 SEC indicates the transaction activity time, starting Index read indicates the current transaction status, the possible transaction states are: Inserting, inserting, inserting, starting index read and so on.

mysql tables inLOCK WAIT 3 LOCK struct(s), heap size 1136, 2 row LOCK (s), undologEntries 1 // -3 lock structure and memory size involvedCopy the code

Tables in Use 1 indicates that a table is in use, and locked 1 indicates that a table is locked. 3 LOCK struct(s) indicates that the length of the chain table of the transaction is 3. Each linked list node represents a LOCK structure held by the transaction, including table LOCK, record LOCK or autoinc LOCK, etc. Heap Size 1136 Specifies the size of the locked heap memory allocated for transactions.

2 Row lock(s) indicates the number of row locks held by the current transaction. Find the number of records whose type is LOCK_REC by iterating through the 11 lock structures mentioned above. Undo log entries 1 indicates that the current transaction has one Undo log record, indicating that the transaction has updated one record.

The following is the most important information about locks held or pending in the deadlock log, as shown in lines -5 and -6 in Figure 5. This information can be used to analyze the specific types of locks and the tables involved. This information can help you analyze SQL lock conflicts in accordance with the knowledge of locks in the series.

RECORD LOCKS space id 2 page no 4 n bits 80 index PRIMARY of table `test`.`t` trx id 2078 lock_mode X locks rec but not RECORD LOCKS SPACE ID 2 Page No 4 N bits 80 index PRIMARY of table 'test'. 't' TRX ID 2078 lock_mode X Locks rec but not gap waiting // -6 Waiting for information about locksCopy the code

As mentioned in Lock Types and Locking Principles, there are four types of row locks: record locks, gap locks, next-key locks, and insert intent locks. The deadlock logs corresponding to the four locks are different as follows:

  • LOCK_REC_NOT_GAP: lock_mode X locks rec but not gap
  • Gap lock (LOCK_GAP) : lock_mode X locks gap before REc
  • Next-key lock (LOCK_ORNIDARY) : lock_mode X
  • LOCK_INSERT_INTENTION: lock_mode X locks gap before rec insert intention

So, according to the deadlock log, we find that transaction 1 holds a record lock on the test.t table and is waiting for another record lock.

The deadlock log can be used to find the last SQL statement executed by the transaction to obtain the lock, but if the transaction executed multiple SQL statements, this information may not be sufficient, we need to fully understand all the SQL statements executed by the transaction. In this case, we need to get it from the binlog.

Binlog acquisition and analysis

The **binlog log is a complete record of all THE SQL executed by the transaction. With this log, we can find all the SQL executed by the transaction that ultimately acquired the lock. ** Then perform specific lock conflict analysis.

You can use the MySQL command line tool Mysqlbinlog to obtain the binlog logs of the online database remotely. The specific command is as follows:

Mysqlbinlog -h127.0.0.1 -u root -p --read-from-remote-server binlog.000001 --base64-output=decode-rows -v
Copy the code

–base64-output=decode-rows indicates binlog logs in row mode, so this method is only applicable to binlog logs in row mode. So this limitation doesn’t really matter. -V indicates that the row events are reconstituted into annotated pseudo SQL statements.

We can find the corresponding information of the transaction in binlog by the specific event of the deadlock in the deadlock log and finally obtain the parameter information of the SQL that the lock transaction is executing. For example, we can directly find the corresponding location in binlog through the specific time 10:57 in the deadlock log screenshot and the specific data information of SQL such as Tom1 and Teddy2, as shown in the figure below.

According to the specific information of binlog, we can clearly find all THE SQL statements executed by the final acquisition lock transaction, which can also find the corresponding business code, and then we can carry out specific lock conflict analysis.

section

The deadlock series has come to an end. If you have any questions or errors, feel free to leave a comment below. I hope you will continue to pay attention.

Personal blog, welcome to play