MySQL online deadlock analysis practice

Preface

I believe that everyone has had a simple understanding of MySQL's lock mechanism when learning MySQL. Since there are locks, the deadlock problem is inevitable. In fact, MySQL will not have deadlock problems in most scenarios (for example, when the concurrency is not high and the SQL is not written too badly), but in high-concurrency business scenarios, deadlocks can occur if you are not careful, and this deadlock is more difficult to analyze.

Some time ago, when I was interning at a company, I encountered a rather strange deadlock. I had not had time to sort it out properly before. Recently, I had some time to reproduce it, so I could accumulate some experience.

Business Scenario

Let me briefly talk about the business background. The company is engaged in e-commerce live streaming, and I am responsible for the anchor-related business. This deadlock occurs when the anchor updates the product information in the background.

One of our products will have two associated IDs. It is impossible to identify a unique product through any one of the IDs (that is, the relationship between the ID and the product is one-to-many). Only by querying both IDs at the same time can a product be identified. Therefore, when updating product information, you need to specify two IDs in the where condition at the same time. The following is the structure of the deadlock SQL (anonymous):

UPDATE test_table SET `name`="zhangsan" WHERE class_id = 10 AND teacher_id = 8;

This SQL is very simple. It updates a field based on two equal conditions.

I wonder if you will be confused when you see this SQL. According to common sense, deadlock is likely to occur only when there are multiple SQLs in a transaction. How could deadlock occur in this one SQL?

Yes, I had the same doubts at the time, and even suspected that the alarm system was reporting something false (it turned out not to be true in the end...), I was really confused at the time. And because of database permissions, I couldn't see the deadlock log. It was almost time to get off work, and it would be too troublesome to find the DBA, so I just searched the search engine... (Keywords: update deadlock single sql), and finally found out that it was caused by MySQL's index merge optimization, namely Index Merge. The following will explain in detail and reproduce the deadlock scenario.

Index Merge

Index Merge is an optimization feature introduced in MySQL 5.0, which is mainly used to optimize the situation where a SQL statement uses multiple indexes.

Let's look at the SQL just now, assuming that class_id and teacher_id are two common indexes:

UPDATE test_table SET `name`="zhangsan" WHERE class_id = 10 AND teacher_id = 8;

If there is no Index Merge optimization, the steps for MySQL to query data are as follows:

• According to class_id or teacher_id (the optimizer determines which index to use based on the actual data situation, and it is assumed that the class_id index is used here), the primary key ID of the corresponding data is queried on the secondary index
• Perform a back-index query (i.e. query the clustered index) based on the queried primary key ID to obtain the corresponding data row
• Get teacher_id from the data row and check whether it is equal to 8. If it meets the condition, return

From this process, it is not difficult to see that MySQL only uses one index. As for why multiple indexes are not used, the simple reason is that multiple indexes are on multiple trees, and forcing them to be used will reduce performance.

Let's take a look at the steps of MySQL querying data after the Index Merge optimization is introduced:

• Query the corresponding primary key based on class_id , and then query the corresponding data row based on the primary key (recorded as result set A)
• Query the corresponding primary key based on teacher_id , and then query the corresponding data row based on the primary key (recorded as result set B)
• Perform an intersection operation on result set A and result set B to obtain the final result set that meets the conditions

It can be seen here that with Index Merge, MySQL splits a SQL statement into two query steps, using two indexes respectively, and then uses intersection operations to optimize performance.

Deadlock Analysis

After analyzing the steps of Index Merge, let's think back and think about why deadlock occurs?

Remember that Index Merge splits a SQL query into two steps? This is where the problem arises. We know that UPDATE statement will add a row-level exclusive lock. Before analyzing the locking steps, we assume that there is a data table as follows:

The data in the above table meets the characteristics mentioned at the beginning of our article. It is impossible to uniquely identify a piece of data based on the single fields of class_id and teacher_id . Only by combining the two fields can a piece of data be identified, and class_id and teacher_id are set as two common indexes respectively.

Suppose the following two SQL statements are executed concurrently, and their parameters are completely different. Intuition tells us that deadlock should not occur, but intuition is often wrong:

// Thread A executes UPDATE test_table SET `name`="zhangsan" WHERE class_id = 2 AND teacher_id = 1;

// Thread B executes UPDATE test_table SET `name`="zhangsan" WHERE class_id = 1 AND teacher_id = 2;

Then, under the optimization of Index Merge, when the above SQL is executed concurrently, MySQL's locking steps are as follows:

Eventually, the two transactions wait for each other, resulting in a deadlock.

Solution

Because this deadlock is essentially caused by the Index Merge optimization, to solve the deadlock problem in this scenario, you just need to prevent MySQL from performing the Index Merge optimization.

Solution 1

Manually split a SQL statement into multiple SQL statements, and perform intersection operations at the logical layer to prevent MySQL's stupid optimization behavior. For example, here we can first query the corresponding primary key based on class_id , then query the corresponding primary key based on teacher_id , and finally query the data based on the primary key after intersection.

Solution 2

Create a joint index. For example, you can create a joint index class_id and teacher_id . Then MySQL will not use Index Merge.

Option 3

To force a single index, add for index(class_id) after the table name to specify that the statement only uses the class_id index.

Option 4

Turn off Index Merge optimization:

• To disable permanently: SET [GLOBAL|SESSION] optimizer_switch='index_merge=off';
• Temporary closure: UPDATE /*+ NO_INDEX_MERGE(test_table) */ test_table SET name ="zhangsan" WHERE class_id = 10 AND teacher_id = 8;

Scene Reproduction

Data preparation

To facilitate testing, here is a SQL script that can be imported using Navicat to get the required test data:

Download address: https://cdn.juzibiji.top/file/index_merge_student.sql

After importing, we will get 10,000 test data in the following format:

Test code

Due to space limitations, only the code Gist link is given here: https://gist.github.com/juzi214032/17c0f7a51bd8d1c0ab39fa203f930c60

The above code mainly starts 100 threads to execute our data modification SQL statements to simulate online concurrency. After running for a few seconds, we will get the following error:

com.mysql.cj.jdbc.exceptions.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction

This means that a deadlock exception has occurred.

Deadlock Analysis

We have constructed a deadlock using the code above. Next, we enter MySQL to view the deadlock log. Execute the following command in MySQL to view the deadlock log:

SHOW ENGINE INNODB STATUS; 

In the log, we find the line LATEST DETECTED DEADLOCK , which is the last deadlock we generated. Next, we start analyzing it.

From line 29, we can see that the SQL executed by transaction 1 has the conditions of class_id = 6 and teacher_id = 16 It currently holds a row lock. Lines 34 to 39 are the data of this row. Line 34 is the hexadecimal representation of the primary key, which is 1616 when converted to decimal. Similarly, look at row 45, the data waiting to be locked is the data of primary key id 1517.

Next, we use the same method to analyze transaction 2. We can see that transaction 2 holds three locks, which are the data rows with primary key IDs 1317, 1417, and 1517, and is waiting for 1616.

At this point we have discovered that transaction 1 holds 1616 and waits for 1517, and transaction 2 holds 1517 and waits for 1616, so a deadlock is formed. At this time, MySQL's processing method is to roll back the transaction with the least locks, and JDBC will throw the MySQLTransactionRollbackException rollback exception we mentioned above.

Summarize

This deadlock is actually very difficult to troubleshoot. If you don't know MySQL's Index Merge, you will have no idea what to do when troubleshooting, because there is only a very simple SQL statement in front of you. Even if you look at the deadlock log, you still don't understand.

Therefore, dealing with this type of problem is more about testing your knowledge and experience. Once you encounter it, just pay more attention when writing SQL in the future!

This is the end of this article about the practical online deadlock analysis of MySQL. For more relevant online deadlock analysis content of MySQL, please search for previous articles on 123WORDPRESS.COM or continue to browse the related articles below. I hope you will support 123WORDPRESS.COM in the future!