The problem and solution of MySQL in concurrency scenario

1. Background

For the database system to improve concurrency under multi-user concurrency and to ensure data consistency is always the goal of database system, it is necessary to meet the needs of a large number of concurrent access and must ensure the security of the data under this condition, in order to meet this goal most of the database through lock and transaction mechanism to achieve, MySQL database is no exception. Despite this, we will still encounter a variety of problems in the business development process, this article will demonstrate the common concurrency problems in a case and analyze the solution ideas.

2. The problem of slow query caused by table lock

First we look at a simple case, based on the ID query a user information:

Mysql> SELECT * from user where id=6;

The total number of records for this table was 3, but it was executed for 13 seconds.

The first thing we think of when this happens is to look at the current MySQL process state:

From the process can see that the SELECT statement is waiting for a table lock, then what is the table lock query generated? This result does not show a direct correlation, but we can speculate that most of the UPDATE statement produced (because there is no other suspicious SQL in the process), in order to confirm our guess, first check the user table structure:

Sure enough, the user table uses the MyISAM storage engine, MyISAM will generate a table lock before performing the operation, and then automatically unlock the operation. If the operation is a write operation, the table lock type is a write lock, and if the operation is read, the table lock type is read lock. As you can understand, a write lock will block other operations (including read and write), which makes all operations serial, while read-write operations may be parallel in the case of read-lock, but read-writes are still serial. The following example shows the case where a table lock (read lock), read-read parallel, read-write serial is explicitly specified.

Open/Close the table lock explicitly, using the lock table user read/write; Unlock tables;

Session1:

Session2:

You can see that session 1 enables table locks (read locks) to perform read operations, and Session 2 can perform read operations in parallel, but write operations are blocked. Then look at:

Session1:

Session2:

When the Session1 is unlocked, Seesion2 immediately begins the write operation, read-write serial.

Summarize:

In this case we have a basic analysis of the cause of the problem, summed up the--myisam storage engine will produce a table lock, will affect the operation of other users on the table, if the table lock is a write lock, it will cause other user operation serial, if it is read lock other user's read operation can be parallel. So sometimes we come across a simple query that takes a long time to see if that's the case.

Workaround:

1), as far as possible without MyISAM storage engine, in the MySQL8.0 version has removed all the MyISAM storage engine tables, the recommended use of InnoDB storage engine.

2), if must use the MyISAM storage engine, reduces the write operation 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 directly modified online? Before answering this question, let's look at a case:

The above statement attempts to modify the name field length of the user table, and the statement is blocked. As a rule, let's check the current process:

From the process you can see that the ALTER statement is waiting for a metadata lock, which is most likely caused by this SELECT statement above, as is the case. When performing a DML (SELECT, Update, delete, insert) operation, a metadata lock is added to the table to ensure that the table structure is not modified during the query, so the ALTER statement above is blocked. So what if the execution order is reversed, the ALTER statement is executed before the DML statement is executed? Will DML statements be blocked? For example, I am modifying the table structure on an online environment, will the DML statements on the line be blocked? The answer is: not sure.

The online DDL feature has been provided at MySQL5.6, allowing some DDL statements and DML statements to be concurrent, and the online DDL has been enhanced in the current version 5.7, allowing most DDL operations to be performed online. See: https://dev.mysql.com/doc/refman/5.7/en/innodb-create-index-overview.html

So whether or not DML is blocked during DDL execution for a particular scenario depends on the scenario.

Summary: With this example, we have a basic understanding of metadata locking and online DDL, and if we have the need to modify the table structure online in the course of business development, we can refer to the following scenarios:

1, as far as possible in the time period of small business;

2, check the official documents, confirm that the table modification to be done can and DML concurrency, will not block the online business;

3. It is recommended to use Percona Company's Pt-online-schema-change tool, which is more powerful by the official online DDL, the basic principle is: Through the Insert ... select ... Statement to make a full-volume copy, through the trigger record table structure changes in the process of the increment generated, so as to achieve the purpose of table structure change.

For example, to make a change to the a table, the main steps are:

Create an empty table of the destination table structure, a_new;
Create a trigger on table A, including adding, deleting, and changing triggers;
Fragment copy data to the destination table by Insert...select...limit N statements
When copy is complete, rename the A_new table to table A.

4. Analysis of a deadlock problem

The problem with deadlocks in the online environment occasionally occurs when two or more transactions are waiting for each other to release the lock, causing the transaction to never terminate. To analyze the problem, we will simulate a simple deadlock situation, and then summarize some of the analysis ideas.

Demo Environment: MYSQL5.7.20 Transaction ISOLATION Level: RR

Table User:

CREATE TABLE`User' (' ID 'int( One) not NULLauto_increment, ' name 'varchar( -)DEFAULT NULL, ' age 'int( One)DEFAULT NULL,PRIMARY KEY(' id ')) ENGINE=InnoDB auto_increment=5 DEFAULTCHARSET=Utf8

Here's how transaction 1, Transaction 2 works:

This is a simple deadlock scenario where transaction 1, transaction 2 waits for each other to release the lock, the InnoDB storage engine detects that a deadlock occurs, and causes transaction 2 to roll back, which makes transaction 1 no longer waiting for the lock of transaction B to continue execution. So how does the InnoDB storage engine detect a deadlock? In order to understand this problem, we first check the state of InnoDB at this time:

Show Engine InnoDB Status\g

------------------------
LATEST detected DEADLOCK
------------------------
2018-01-14 12:17:13 0x70000f1cc000
* * * (1) TRANSACTION:
TRANSACTION 5120, ACTIVE 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, 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 index PRIMARY of table ' test '. ' User ' Trx ID 5120 lock_mode X LOCKS rec BU T 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 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 One, 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 index PRIMARY of table ' test '. ' User ' Trx ID 5121 lock_mode X LOCKS rec BU T 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 index PRIMARY of table ' test '. ' User ' Trx ID 5121 lock_mode X LOCKS Rec But no T 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 state There are many indicators, here we intercept the deadlock related information, we can see that InnoDB can output the recent deadlock information, in fact, many of the deadlock monitoring tool is based on this feature development.

In the deadlock information, the information about two transaction wait locks is displayed (blue for transaction 1, Green for Transaction 2), with a focus on: waiting for this lock to be granted and holds the lock (S).

Waiting for the lock to is granted indicates that the current transaction is waiting for the locking information, from the output shows that transaction 1 is waiting for the heap No 5 row lock, transaction 2 is waiting for the heap No 7 row lock;

Holds the Lock (S): Represents the lock information held by the current transaction, and from the output it is shown that transaction 2 holds a heap no of 5 row locks.

As seen from the output, the last InnoDB rollback of transaction 2.

So how did InnoDB check out the deadlock?

The simplest way to think of it is if a transaction is waiting for a lock, if the wait time exceeds the set threshold, then the transaction fails, which avoids the case of multiple transactions waiting for each other long. The parameter innodb_lock_wait_timeout is used to set the lock wait time.

If you follow this method, it takes time to resolve a deadlock (that is, waiting for a threshold that exceeds the Innodb_lock_wait_timeout setting), which is slightly passive and affects system performance, and the InnoDB storage engine provides a better algorithm to solve the deadlock problem, wait-for Graph algorithm. To put it simply, when multiple transactions start to wait for each other, the Wait-for graph algorithm is enabled, which determines that a deadlock is released immediately after one of the transactions is rolled back. The advantage of this method is that the check is more active and the waiting time is short.

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

For the sake of understanding, we see the deadlock as a scenario in which 4 cars block each other:

4 Cars as 4 transactions, waiting for each other's locks, causing deadlocks. The principle of wait-for graph algorithm is to use the transaction as a node, the lock wait relationship between transactions, with a forward edge representation, for example, transaction a waits for the lock of transaction B, from Node A to draw a forward edge to node B, so that if a, B, C, D composed of a forward graph, formed a ring, then judged as a deadlock. This is the basic principle of the wait-for graph algorithm.

Summarize:

1. How to check out deadlocks in our business development? Just now, you can tell by monitoring the INNODB state that you can do a little tool to collect the deadlock records for later viewing.

2. If a deadlock occurs, how should the business system respond? From the above we can see that when InnoDB checks out a deadlock, a deadlock found is reported to the client when trying to get lock; Try restarting transaction information, and roll back the transaction, the application side needs to do a transaction restart work for this information, and save the field log afterwards do further analysis, to avoid the next deadlock generation.

5. Analysis of lock waiting problem

In business development, the probability of deadlock appearing is small, but the probability of lock waiting is large, the lock waits because one transaction takes up the lock resource for a long time, while the other transaction waits for the previous transaction to release the lock.

From the above-mentioned transaction 1 long time hold id=3 of the row lock, transaction 2 generates a lock wait, the wait time exceeds innodb_lock_wait_timeout after the operation is interrupted, but the transaction does not roll back. If we encounter lock waits in our business development, it will not only affect performance, but also challenge your business process, because your business side needs to adapt to the situation of lock waiting, whether it is to retry the operation or roll back the transaction.

In the MySQL metadata table there is a collection of information about transactions, lock waits, such as Innodb_locks, Innodb_trx, innodb_lock_waits under the INFORMATION_SCHEMA database, You can watch your business system lock wait by using these tables. You can also use the statement to easily query the relationship between transaction and lock waiting:

Results:

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

Summarize:

1. Please monitor your business system for lock waiting, which will help you understand the current database lock situation and help you optimize your business process.

2, the business system should be the lock waiting time out of the situation to make appropriate logical judgment.

6. Summary

This article introduces several common MySQL concurrency problems through a few simple examples, and tries to get the idea of how to troubleshoot these problems. This article deals with transactions, table locks, metadata locks, row locks, but it can cause concurrency problems, such as transaction isolation levels, gap locks, and so on. The real concurrency problem may be many and complex, but the troubleshooting ideas and methods can be reused, in this article we use show Processlist;show engine InnoDB status, and query the metadata table method to troubleshoot the discovery problem, if the problem involves replication, It also needs to be assisted with master/slave monitoring.

This article transferred from: http://www.cnblogs.com/leefreeman/p/8286550.html

The problem and solution of MySQL in concurrency scenario