Detailed explanation of MySQL deadlock and database and table sharding issues

Record the problem points of MySQL production.

Business scenario and problem description

When requesting an external interface, the number of requests per day is around 9 million.

It is divided into two items: request item and receipt item. Request is used to call external interface, and receipt is the interface for receiving and sending.

The data is inserted into the database before sending the request.

After the request, if the interface returns a call failure, the database status will be updated to failure.

If the sending is successful, it will wait for the upstream to give a receipt message and then update the database status.

During the production operation, the problem of MQ consumer accumulation caused by MySQL occurred twice in half a year.

Problem Analysis

Record two production problems caused by different reasons and cause analysis.

mysql deadlock problem

View the TPS of the mq aggregation platform
In production, it is found that mq data has been accumulating and increasing. However, the TPS is only around 30 and it has not gone up.

This will slow down mq consumption and cause continuous accumulation. The specific reason why MQ is constantly accumulating needs to be further investigated.

View production server logs

Checking the production server log, I found a dead lock error.

error response from MySQLConnection [node=24, id=277499, threadId=2735941, state=borrowed, closed=false, autocommit=true, host=10.1.10.74, port=3306, database=sep_4, localPort=27744, isClose:false, toBeClose:false, MySQLVersion:5.7.25], err: Deadlock found when trying to get lock; try restarting transaction, code: 1213

The specific sql is as follows:

update stage set status = 'success',reply_time = '2021-03-07 10:40:11' where code = '000123' and create_time > '2021-03-03 00:00:00';

That is to say, a deadlock occurred when executing the service.

It is not intuitive to see the exact number of entries and the time taken on the production server, so we asked the DBA to find out the slow SQL statements and the time taken.

After checking, it was found that the longest slow SQL took as long as 7780ms.

If you look closely, you will find that sql will find that the same id is in execution and the other is in Lock Wait state.

And there are a lot of Lock Wait states in this slow SQL.

What causes deadlock?

The database engine used by mysql is InnoDB. First understand what a deadlock is:

Deadlock: It means that two or more processes are in the process of execution.
A phenomenon of waiting for each other caused by competing for resources. Without external force, they will not be able to move forward.
At this time, the system is said to be in a deadlock state or the system has a deadlock, and these processes that are always waiting for each other are called deadlock processes.

From the above investigation, we can see that the deadlock problem is:

When executing SQL to update a row of data, the row of data will be locked. After the execution is completed, the row lock will be released, and the SQL that has not been executed is in the Lock Wait state.

The reason for this in the program is that the database is frequently operated before and after sending and when receiving a receipt, and the same data may be operated at the same time.

Therefore, lock waiting occurs during execution.

The sharding key is not included in the sharding table

The first warning is that the CPU of the stage_prod library has soared to 85%.

Is the number of database threads full?

After checking the database connection status, we can see that the number of database connections is not full.

Detect slow SQL and time consumption

Detected problem sql:

update stage set status = 'success',reply_time = '2021-03-07 10:40:11' where create_time > '2021-03-03 00:00:00';

If you check the SQL, you will find that this SQL does not contain the shard key code field. This SQL statement is executed when the receipt is received.

Check production server logs

There is a judgment in the code. If the code value is not empty, sql will carry the code value. If you didn't bring it with you, you need to check why you didn't bring it with you.

Looking at the code, you will find that the code is obtained from redis and is set to redis when it is sent. But it's weird that it's not set.

We initially suspected that it was a redis problem, and then we communicated with the redis maintenance platform and found that it was indeed a problem caused by a redis failure.

Why does the CPU soar without a shard key?

First of all, the company uses hotdb to divide the database and table, because the daily input volume is about 9 million, and one table contains hundreds of millions of data.

If you just use the index, it will not meet the requirements.

The hotdb database and table were divided into different parts, and hash sharding was performed based on the code value, with 64 shards in total. That is to say, 64 databases are distributed in 16 instances on 8 servers.

This can avoid uneven data distribution among shards and theoretically avoid over-concentration on a certain shard.

If the SQL does not contain the shard key code, all DML operations are sent to all underlying libraries for execution, which is equivalent to traversing the library once.

This may cause the CPU to soar to 99%, or even cause the server to crash, which is very scary.

Solution

Emergency treatment: Stop several services first to reduce database operations

The continuous accumulation of data will affect the data processing speed. Then, we must first reduce the speed of the operation. The fastest way is to stop the service and reduce the frequency of database operations.

Reduce database operations to avoid database deadlock

Deadlock usually occurs because the submission of DML operations is not properly controlled by the program and is not submitted in time.

Reduce repeated operations on the same data. Reduce the number of DMLs in each batch during batch operations to ensure fast submission, avoid long transactions, and avoid repeated submission of DMLs.

So how to reduce operations?

Merge SQL

Merge the insert before sending and the update when sending fails into one SQL statement, thus avoiding multiple operations on the same data.

Reduce long transactions and number of entries during batch execution

During execution, it was found that executing 20 SQL statements in batches each time was more efficient than executing 200 SQL statements at a time.

So try to avoid this problem as much as possible.

Each SQL statement must carry a sharding key for each database or table.

The principle is that the operation speed of the entire database should not be slowed down because of one piece of data.

The shard key must be included. If it is not included, an error will be thrown.

Increase the time interval opening and closing interval

Use code as the shard key and createTime as the partition. Then, while ensuring the existence of the code, you can write the opening and closing intervals to improve execution efficiency.

Better solution: SQL sequential execution

This solution can ensure that the SQL statements to be executed are executed sequentially, thus avoiding deadlock.

But this needs to be differentiated according to business scenarios.

Review

To solve the MySQL deadlock problem, try to avoid frequent operations on the same data and avoid long transactions;
For the problem of sharding, be sure to include the sharding key;
Monitoring mechanisms are essential;

Summarize

This is the end of this article about MySQL deadlock and sharding. For more information about MySQL deadlock and sharding, please search previous articles on 123WORDPRESS.COM or continue to browse the following related articles. I hope you will support 123WORDPRESS.COM in the future!