编程知识 cdmana.com

Problems and solutions of MySQL in concurrent scenarios

source : Li Ping

www.cnblogs.com/leefreeman/p/8286550.html

1、 background

For the database system, it is the goal of the database system to improve the concurrency under the condition of multi-user concurrency and ensure the consistency of data , To meet the needs of a large number of concurrent access, we must ensure the security of data under this condition , In order to meet this goal, most databases are implemented through lock and transaction mechanism ,MySQL Databases are no exception . However, we will still encounter a variety of difficult problems in the process of business development , This article will demonstrate common concurrency problems in the form of cases and analyze solutions .

2、 Slow query caused by table lock

First let's look at a simple case , according to ID Query a user's information :

mysql> select * from user where id=6;

The total number of records in this table is 3 strip , But it did 13 second .

The first thing we think about when this kind of problem arises is to look at the current MySQL Process status :

As can be seen from the process select Statement is waiting for a table lock , So what query generated this table lock ? There is no direct correlation in this result , But we can speculate that it's mostly that one update The statement produces ( Because there are no other suspects in the process SQL), To confirm our conjecture , Check first user Table structure :

Sure enough user The watch uses MyISAM Storage engine ,MyISAM Table lock will be generated before operation , After the operation is completed, it will be unlocked automatically . If the operation is a write operation , Then the table lock type is write lock , If the operation is read operation, the table lock type is read lock .

As you understand, writing locks will block other operations ( Including reading and writing ), This makes all operations serial ; And read in lock - Read operations can be parallel , But reading - Write operations are still serial . The following example demonstrates explicitly specifying a table lock ( Read the lock ), read - Read parallel , read - Write the serial situation .

Open... Explicitly / Close the watch lock , Use lock table user read/write; unlock tables;

session1:

session2:

You can see the conversation 1 Enable table lock ( Read the lock ) Perform read operations , Now the conversation 2 Read operations can be performed in parallel , But the write operation is blocked . Then look at :

session1:

session2:

When session1 After unlocking ,seesion2 Start writing immediately , Read - Write serial .

summary :

Let's make a basic analysis of the causes of the problem , To sum up ——MyISAM Table locks are generated when the storage engine performs operations , It will affect the operation of the table by other users , If the watch lock is a write lock , Will cause other users to operate serially , If it is a read lock, other users can read in parallel . So sometimes we come across a simple query that takes a long time , See if this is the case .

terms of settlement :

1) As far as possible need not MyISAM Storage engine , stay MySQL8.0 All of the MyISAM The table that stores the engine , Recommended InnoDB Storage engine .

2) If you must use MyISAM Storage engine , Reduce write time ;

3、 What are the risks of modifying the table structure online ?

If one day the business system needs to increase the length of a field , Can it be modified directly online ? Before I answer that question , Let's start with a case study :

The above statement attempts to modify user Tabular name Field length , The statement is blocked . By convention , Let's check the current process :

As can be seen from the process alter Statement is waiting for a metadata lock , And this metadata lock is probably the one above select Caused by a statement , That's exactly what happened . In execution DML(select、update、delete、insert) In operation , A metadata lock will be added to the table , This metadata lock is to ensure that the table structure will not be modified during the query , So the above alter The statement will be blocked .

So if the execution order is the opposite , Execute first alter sentence , Re execution DML Sentence? ?DML Will statements be blocked ? For example, I am modifying the table structure in the online environment , Online DML Will statements be blocked ? The answer is : Not sure .

stay MySQL5.6 Began to offer online ddl function , Allow some DDL Statement and DML Statement concurrency , At present 5.7 Version pair online ddl There is another enhancement , This makes most of DDL The operation can be carried out online . See :https://dev.mysql.com/doc/refman/5.7/en/innodb-create-index-overview.html

So execute... For a specific scenario DDL In the process ,DML Whether it will be blocked depends on the scene . Official account Java The technology stack can be searched for a copy of MySQL The development of specification .

summary : Through this example, we lock the metadata and online ddl With a basic understanding , If we need to modify the table structure online during the business development process , Please refer to the following plan :

1. Try to do it in a small amount of time ;

2. Check out the official documents , Confirm that the table changes to be made can be compared with DML Concurrent , It won't block online business ;

3. Recommended percona The company's pt-online-schema-change Tools , The tool is officially online ddl More powerful , Its basic principle is : adopt insert… select… Make a full copy of the statement , Use trigger to record the increment of table structure change , So as to achieve the purpose of table structure change .

For example, to be right A Table changes , The main steps are :

Create an empty table of the destination table structure ,A_new;
stay A Create trigger on table , Including increase 、 Delete 、 Change trigger ;
adopt insert…select…limit N Statement fragment copy data to destination table
Copy After completion , take A_new surface rename To A surface .


4、 Analysis of a deadlock problem

Deadlock happens occasionally in online environment , Deadlock is because two or more transactions wait for each other to release the lock , A situation that causes a transaction to never terminate . To analyze the problem , We will simulate a simple deadlock situation , Then sum up some analysis ideas .

recommend :MySQL Database development 36 Rules !

Demo environment :MySQL5.7.20 Transaction isolation level :RR

surface user:

CREATE TABLE`USER`(

`ID`INT(11)NOTNULLAUTO_INCREMENT,

`NAME`VARCHAR(300)DEFAULTNULL,

`AGE`INT(11)DEFAULTNULL,

PRIMARY KEY(`ID`)

)ENGINE=INNODB AUTO_INCREMENT=5DEFAULTCHARSET=UTF8

Let's demonstrate the transaction 1、 Business 2 Work situation :

This is a simple deadlock scenario , Business 1、 Business 2 Wait for each other to release the lock ,InnoDB The storage engine has detected a deadlock , Make the transaction 2 Roll back , This makes things 1 No more waiting for transactions B Lock of , So that we can continue to execute . that InnoDB How does the storage engine detect deadlock ? In order to understand the problem , Let's check this first InnoDB The state of :

show engine innodb statusG

------------------------
LATEST DETECTED DEADLOCK
------------------------
2018-01-14 12:17:13 0x70000f1cc000
*** (1) TRANSACTION:
TRANSACTION 5120, ACTIVE 17 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1136, 2 row lock(s)
MySQL thread id 10, OS thread handle 123145556967424, query id 2764 localhost root updating
update user set name='haha' where id=4
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 94 page no 3 n bits 80 index PRIMARY of table `test`.`user` trx id 5120 lock_mode X locks rec but not gap waiting
Record lock, heap no 5 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
0: len 4; hex 80000004; asc ;;
1: len 6; hex 0000000013fa; asc ;;
2: len 7; hex 520000060129a6; asc R ) ;;
3: len 4; hex 68616861; asc haha;;
4: len 4; hex 80000015; asc ;;
















*** (2) TRANSACTION:
TRANSACTION 5121, ACTIVE 12 sec starting index read
mysql tables in use 1, locked 1
3 lock struct(s), heap size 1136, 2 row lock(s)
MySQL thread id 11, OS thread handle 123145555853312, query id 2765 localhost root updating
update user set name='hehe' where id=3
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 94 page no 3 n bits 80 index PRIMARY of table `test`.`user` trx id 5121 lock_mode X locks rec but not gap
Record lock, heap no 5 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
0: len 4; hex 80000004; asc ;;
1: len 6; hex 0000000013fa; asc ;;
2: len 7; hex 520000060129a6; asc R ) ;;
3: len 4; hex 68616861; asc haha;;
4: len 4; hex 80000015; asc ;;












*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 94 page no 3 n bits 80 index PRIMARY of table `test`.`user` trx id 5121 lock_mode X locks rec but not gap waiting
Record lock, heap no 7 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
0: len 4; hex 80000003; asc ;;
1: len 6; hex 0000000013fe; asc ;;
2: len 7; hex 5500000156012f; asc U V /;;
3: len 4; hex 68656865; asc hehe;;
4: len 4; hex 80000014; asc ;;






*** WE ROLL BACK TRANSACTION (2)

InnoDB There are many indicators of state , Here we intercept deadlock related information , It can be seen that InnoDB It can output the latest deadlock information , In fact, many deadlock monitoring tools are developed based on this function .

In the deadlock message , Shows information about two transaction wait locks ( Blue stands for business 1、 Green represents business 2), Focus on :WAITING FOR THIS LOCK TO BE GRANTED and HOLDS THE LOCK(S).

WAITING FOR THIS LOCK TO BE GRANTED Indicates the lock information that the current transaction is waiting for , The output shows that the transaction 1 Is waiting for heap no by 5 The row lock , Business 2 Is waiting for heap no by 7 The row lock ;

HOLDS THE LOCK(S): Represents the lock information held by the current transaction , The output shows that the transaction 2 hold heap no by 5 Row lock .

As you can see from the output , Last InnoDB Rolled back transaction 2.

that InnoDB How to check the deadlock ?

The easiest way to think about it is if a transaction is waiting for a lock , If the waiting time exceeds the set threshold , Then the transaction operation failed , This avoids multiple transactions waiting for each other . Parameters innodb_lock_wait_timeout It is used to set the waiting time of the lock .

If you follow this method , It takes time to solve the deadlock ( Wait for more than innodb_lock_wait_timeout Set threshold ), This method is slightly passive and affects system performance ,InnoDB Storage engine provides a better algorithm to solve deadlock problem ,wait-for graph Algorithm . To put it simply , When multiple transactions start waiting for each other , Enable wait-for graph Algorithm , The algorithm decides to roll back one of the transactions immediately after deadlock , The deadlock was unlocked . The advantage of this method is : Inspection is more active , Short waiting time .

Here is wait-for graph The basic principle of the algorithm :

For the sake of understanding , We think of deadlocks as 4 The scene of cars blocking each other :

4 A car is regarded as 4 One transaction , Waiting for each other's locks , Cause a deadlock .wait-for graph The principle of the algorithm is to treat transactions as nodes , Lock wait relationship between transactions , Use the directed edge to show , Such as transaction A Wait for business B Lock of , From the node A Draw a directed edge to node B, So if A、B、C、D A directed graph of composition , Formed a ring , It's a deadlock . This is it. wait-for graph The basic principle of the algorithm .

summary :

1. If deadlock occurs in our business development, how to check out ? Just now I've introduced how to monitor InnoDB The state can be concluded , You can make a gadget to collect deadlock records , Easy to check after the fact .

2. If there is a deadlock , How should business systems respond to ? From the above we can see when InnoDB After checking out the deadlock , Report a... To the client Deadlock found when trying to get lock; try restarting transaction Information , And roll back the transaction , The application side needs to target this information , Do the work of transaction restart , And save the site log for further analysis afterwards , Avoid the next deadlock .

5、 Analysis of lock waiting problem

In business development, the probability of deadlock is small , But the probability of lock waiting is high , Lock waiting is because a transaction takes up lock resources for a long time , Other transactions wait for the previous transaction to release the lock .

From the above we can see that 1 Long term holding id=3 The row lock , Business 2 Generate lock wait , Waiting time exceeds innodb_lock_wait_timeout Post operation interrupt , But the transaction did not roll back . If we encounter lock waiting in business development , Not only will it affect performance , It also challenges your business processes , Because your business side needs to do logical processing for lock waiting , Retry operation or rollback transaction .MySQL How transactions are implemented , It's recommended to have a look at .

stay MySQL There are pairs of transactions in the metadata table 、 Lock waiting information for collection , for example information_schema Database based INNODB_LOCKS、INNODB_TRX、INNODB_LOCK_WAITS, You can use these tables to observe your business system lock waiting . You can also use the following statement to conveniently query the relationship between transactions and lock waiting :

SELECT  R.TRX_ID WAITING_TRX_ID,

           R.TRX_MYSQL_THREAD_ID WAITING_THREAD,

           R.TRX_QUERY WATING_QUERY,

           B.TRX_ID BLOCKING_TRX_ID,

           B.TRX_MYSQL_THREAD_ID BLOCKING_THREAD,

           B.TRX_QUERY BLOCKING_QUERY

FROM  INFORMATION_SCHEMA.INNODB_LOCK_WAITSW

INNER JOIN  INFORMATION_SCHEMA.INNODB_TRXBONB.TRX_ID = W.BLOCKING_TRX_ID

INNER JOIN  INFORMATION_SCHEMA.INNODB_TRXRONR.TRX_ID = W.REQUESTING_TRX_ID;


result :

waiting_trx_id: 5132
waiting_thread: 11
wating_query: update user set name='hehe' where id=3
blocking_trx_id: 5133
blocking_thread: 10
blocking_query: NULL




summary :

1. Please monitor your business system for lock waiting , This will help you understand the current database lock situation , And help you optimize your business processes ;

2. In the business system, we should make appropriate logical judgment for the timeout of lock waiting .

6、 Summary

This article introduces some of the common ones through several simple examples MySQL Concurrency issues , And try to come up with the idea of troubleshooting for these problems . The article deals with affairs 、 Table locks 、 Metadata lock 、 Row lock , But there's more to the concurrency problem , For example, there are transaction isolation levels 、GAP Locks, etc. .

Real concurrency problems can be many and complex , But troubleshooting ideas and methods can be reused , In this article we used show processlist;show engine innodb status; And query the metadata table to find problems , If the problem involves copying , You need help master/slave Monitor to assist .

 

Reference material :

  • Jiang Cheng Yao 《InnoDB Storage engine 》

  • Li Hongzhe Yang Ting 《MySQL Troubleshooting guide 》

  • He Dengcheng  http://hedengcheng.com

 Recommended reading 


 Code comparison tool , I'll use this 6 individual 

 Share my favorite 5 A free online  SQL  Database environment , It's so convenient !

Spring Boot  A combination of three moves , Hand in hand to teach you to play elegant back-end interface 

MySQL 5.7 vs 8.0, You choose the one ? Net friend : I'm going to stay where I am ~


 Last , I recommend you an interesting and interesting official account : The guy who wrote the code ,7 Old programmers teach you to write bug, reply   interview | resources   Send you a complete set of Development Notes   There's a surprise 

版权声明
本文为[osc_ qheq8wav]所创,转载请带上原文链接,感谢

Scroll to Top