A MySQL online deadlock analysis practice

Time:2022-9-23

Keywords: MySQL Index Merge

foreword

I believe that everyone has a simple understanding of MySQL’s lock mechanism when learning MySQL. Since there is a lock, the problem of deadlock cannot be avoided. In fact, MySQL does not have deadlock problems in most scenarios (for example, the concurrency is not high, and the SQL is not written too much), but in high-concurrency business scenarios, it will cause death if you are not careful. lock, and this deadlock is also more troublesome to analyze.

I came across a comparison when I was an intern at the company some time ago.weird deadlock, I haven’t had time to sort it out well before, but I have reproduced it when I have time recently, which is considered to be accumulating a little experience.

Business scene

Briefly talk about the business background, the company is doing e-commerce live broadcast, and I am responsible for the business related to the anchor side. And this deadlock occurs when the anchor background updates the product information.

One of our products will have two associated IDs, and it is impossible to determine a unique product through any of these IDs (that is to say, this ID and the product areone-to-manyrelationship), only two IDs can be queried at the same time to determine a product. 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 (desensitized):

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

This SQL is very simple to update a field based on two equal conditions.

I don’t know if you will be confused when you see this SQL. According to common sense, there should be multiple SQLs in a transaction before a deadlock may occur. How can a deadlock occur in this SQL?

Yes, I also had such doubts at the time, and even wondered if the alarm system was making false reports (it turned out not to be…), I was really confused at the time. And because of database permissions, I couldn’t see the deadlock log even if I wanted to, and when it was time to get off work, it would be troublesome to find a DBA, so I went directly to the search engine… (keyword: update deadlock single sql) , it was 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 function introduced by MySQL in 5.0, mainly used to optimize a SQL using multiple indexes.

Let’s look at the SQL just now, assumingclass_idandteacher_idThey are two ordinary indexes:

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

Without Index Merge optimization, the steps for MySQL query data are as follows:

  • According to class_id or teacher_id (which index to use is determined by the optimizer according to the actual data situation, it is assumed that the use ofclass_idindex) to query the primary key ID of the corresponding data on the secondary index
  • Perform back-mark query according to the queried primary key ID (that is, query the clustered index), and get the corresponding data row
  • get from data rowteacher_id, judge whether it is equal to 8, and return if the condition is met

From this process, it is not difficult to see that,MySQL uses only one index, As for why not to use multiple indexes, simply put, because multiple indexes are on multiple trees, forcibly using them will reduce performance.

Let’s take a look at the introduction of Index Merge optimization, the steps for MySQL query data are as follows:

  • according toclass_idQuery the corresponding primary key, and then query the corresponding data row according to the primary key back to the table (recorded as result set A)
  • according toteacher_idQuery to the corresponding primary key, and then query the corresponding data row according to the primary key back to the table (recorded as result set B)
  • Combine result set A and result set Bperform intersectionOperation to obtain the final result set that satisfies the conditions

It can be seen here that with Index Merge, MySQL splits a SQL statement into two query steps,Use two indexes separately, and then use the intersection operation to optimize performance

Deadlock Analysis

After analyzing the steps of Index Merge, let’s go back and think about why there is a deadlock?

Remember that Index Merge splits an SQL query into two steps above, and that’s where the problem arises. we knowUPDATEstatement will add arow-level exclusive lockYes, before analyzing the locking step, we assume that there is a data table as follows:

A MySQL online deadlock analysis practice

The data in the above table meets the characteristics mentioned at the beginning of our article, according toclass_idandteacher_idA single field cannot uniquely determine a piece of data, only two fields can be combined to determine a piece of data, and setclass_idandteacher_idare two ordinary indexes, respectively.

Suppose the following two SQL statements are executed concurrently, and their parameters are completely different. The intuition tells us that there should be no deadlock, but the 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;

SoUnder the optimization of Index Merge, when the above SQL is executed concurrently, the locking steps of MySQL are as follows:

A MySQL online deadlock analysis practice

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

solution

Because this deadlock is essentially caused by the optimization of Index Merge, to solve the deadlock problem in this scenario, in essence, you only need to let MySQL not perform the optimization of Index Merge.

Option One

Manually split a single SQL into multiple SQLs, and perform the intersection operation at the logical layer to prevent MySQL’ssillyOptimize behavior, for example, here we can firstclass_idQuery to the corresponding primary key, and then according toteacher_idQuery the corresponding primary key, and finally query the data according to the primary key after the intersection.

Option II

Create a joint index, for example, here you canclass_idandteacher_idCreate a joint index, MySQL will not go Index Merge

third solution

Force a single index to be added after the table namefor index(class_id)You can specify that the statement only takes the class_id index

Option 4

Turn off Index Merge optimization:

  • Permanently close:SET [GLOBAL|SESSION] optimizer_switch='index_merge=off';
  • Temporarily closed:UPDATE /*+ NO_INDEX_MERGE(test_table) */ test_table SET name="zhangsan" WHERE class_id = 10 AND teacher_id = 8;

scene reproduction

data preparation

For the convenience of testing, here is an SQL script, which can be imported into Navicat to get the required test data:

download link:https://cdn.juzibiji.top/file/index_merge_student.sql

After importing, we will get 10000 pieces of test data in the following format:

A MySQL online deadlock analysis practice

test code

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

A MySQL online deadlock analysis practice

The above code mainly starts 100 threads to execute our data modification SQL statement 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 been generated

Deadlock Analysis

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

SHOW ENGINE INNODB STATUS;

A MySQL online deadlock analysis practice

In the log we findLATEST DETECTED DEADLOCKThis line, the beginning of this is the deadlock we generated last time, and then we start to analyze.

As you can see from line 29, the condition of the SQL executed by transaction 1 isclass_id = 6andteacher_id = 16 , it currently holds a row lock, row 34~39 is the row data, row 34 is the hexadecimal representation of the primary key, we convert it to decimal1616. Similarly, look at row 45, which is waiting for the lock to be the data of the primary key id 1517.

A MySQL online deadlock analysis practice

Next, analyze transaction 2 in the same way, we can see that transaction 2 holds 3 locks, the primary key id is1317、1417、1517row of data, waiting is1616

Seeing this, we have found 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 point, MySQL’s processing method is to roll back the transaction that holds the fewest locks, and JDBC will throw our previous MySQLTransactionRollbackException rollback exception.

Summarize

This deadlock is actually very difficult to troubleshoot. If you don’t know MySQL’s Index Merge, you will have no clue when you troubleshoot, because there is only a very simple SQL presented in front of you, even if you look at it. The deadlock log is also the same, so unknown.

Therefore, to deal with such problems, it is more to test your knowledge reserve and experience. As long as you have encountered it once, you should pay more attention when writing SQL later!

I’m Xiaoju, welcome to pay attention to my WeChat public account and show you more front-end and back-end knowledge.
A MySQL online deadlock analysis practice