Analysis of MySQL concurrency problems and solutions, mysql concurrency

Analysis of MySQL concurrency problems and solutions, mysql concurrency
1. Background

For database systems, improving concurrency while ensuring data consistency under multi-user concurrency conditions has always been the goal of the database system, to meet the needs of a large number of concurrent accesses and ensure data security under this condition, in order to meet this goal, most databases are implemented through locks and transaction mechanism, and MySQL databases are no exception. Despite this, we will still encounter various difficult problems in the business development process. This article will demonstrate common concurrency problems and analyze and solve the problem in case studies.

2. Slow query caused by table locks

First, let's look at a simple case and query a user information by ID:

Mysql> select * from user where id = 6;

The total number of records in this table is 3, but it takes 13 seconds.

When this problem occurs, we first think of the current MySQL process status:

From the process, we can see that the select statement is waiting for a table lock. So what query is generated for this table lock? This result does not show direct associations, but we can infer that most of the results are generated by the update Statement (because there are no other suspicious SQL statements in the process). To verify our speculation, first, check the structure of the user table:

Sure enough, the user table uses the MyISAM storage engine. MyISAM generates a table lock before executing the operation. After the operation is complete, it is automatically unlocked. If the operation is a write operation, the table lock type is a write lock. If the operation is a read operation, the table lock type is a read lock. As you understand, write locks block other operations (including read and write operations), making all operations serialized. Read-read operations can be parallel in the case of read locks, but the read-write operation is still serial. The following example shows how to explicitly specify a table lock (read lock), read-read parallel, and read-write serial.

Enable/disable table lock explicitly, and use lock table user read/write; unlock tables;

Session1:

Session2:

We can see that Session 1 enables the table lock (read lock) to execute the read operation. Then Session 2 can execute the read operation in parallel, but the write operation is blocked. Next, let's see:

Session1:

Session2:

When session1 is unlocked, seesion2 immediately starts the write operation, that is, the read-write serial.

Summary:

At this point, we can clearly analyze the cause of the problem. To sum up, the MyISAM storage engine will generate a table lock when performing operations, which will affect the operations of other users on the table, if the table lock is a write lock, other users may perform serial operations. If the table lock is a read lock, other users can perform read operations in parallel. So sometimes we encounter a simple query that takes a long time to see if this is the case.

Solution:

1) Try not to use the MyISAM storage engine. In MySQL8.0, all MyISAM storage engine tables have been removed. InnoDB Storage engine is recommended.

2) If you must use the MyISAM storage engine, reduce 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 take a look at the case:

The preceding statement attempts to modify the name field length of the user table, and the statement is blocked. By convention, let's check the current process:

From the process, we can see that the alter statement is waiting for a metadata lock, which may be caused by the preceding select statement. This is exactly the case. During DML (select, update, delete, insert) operations, a metadata lock is added to the table to ensure that the table structure is not modified during the query, therefore, the above alter statement will be blocked. If the execution order is the opposite, run the alter statement first and then the DML statement? Will DML statements be blocked? For example, if I modify the table structure in the online environment, will the online DML statements be blocked? The answer is: Not sure.

The online ddl function is available in MySQL 5.7 and allows some DDL statements and DML statements to run concurrently. in MySQL, online ddl is enhanced, which allows most DDL operations to run online. See: https://dev.mysql.com/doc/refman/5.7/en/innodb-create-index-overview.html

Therefore, whether or not DML is blocked during DDL execution in a specific scenario depends on the scenario.

Conclusion: In this example, we have a basic understanding of metadata locks and online ddl. If you need to modify the table structure online during business development, you can refer to the following solutions:

1. Try to do so in a time period with a small business volume;

2. Check the official documents and confirm that the table modification can be concurrent with DML without blocking online services;

3. We recommend that you use percona's pt-online-schema-change tool, which is more powerful by the official online ddl. Its basic principle is to use insert... Select... Statement to perform a full copy. The trigger records the incremental data generated during the table structure change process to achieve the Table Structure Change goal.

For example, to change table A, follow these steps:

Create an empty table with the target table structure, A_new;
Create A trigger on Table A, including the add, delete, and modify triggers;
Insert... Select... The limit N statement copies data to the target table in parts.
After the Copy operation is complete, rename the_new table to table.

4. Analysis of a deadlock

A deadlock occurs occasionally in an online environment. A deadlock occurs when two or more transactions wait for the other party to release the lock and the transaction will never be terminated. To analyze the problem, we will simulate a simple deadlock and summarize some analysis ideas.

Demo environment: MySQL5.7.20 transaction isolation level: RR

Table user:

CREATE TABLE `user` (`id` int(11) NOT NULL AUTO_INCREMENT,`name` varchar(300) DEFAULT NULL,`age` int(11) DEFAULT NULL,PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8

The following shows how transaction 1 and transaction 2 work:

Transaction 1

Transaction 2

Transaction monitoring

T1

Begin;

Query OK, 0 rows affected (0.00 sec)

Begin;

Query OK, 0 rows affected (0.00 sec)

T2

Select * from user where id = 3 for update;

+ ---- + ------ +
| Id | name | age |
+ ---- + ------ +
| 3 | sun | 20 |
+ ---- + ------ +
1 row in set (0.00 sec)

Select * from user where id = 4 for update;

+ ---- + ------ +
| Id | name | age |
+ ---- + ------ +
| 4 | zhou | 21 |
+ ---- + ------ +
1 row in set (0.00 sec)

Select * from information_schema.INNODB_TRX;

By querying the innodb Transaction table of the metabase, the number of currently running transactions is 2, that is, transaction 1 and transaction 2.

T3

Update user set name = 'hahaha' where id = 4;

Because the record with id = 4 has been added with a row lock by transaction 2, this statement will be blocked

The number of currently running transactions monitored is 2. T4 blocking status

Update user set name = 'hei' where id = 3;

ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

The record with id = 3 has been added with row lock by transaction 1, and this transaction holds the record row lock with id = 4. At this time, the InnoDB Storage engine checks the deadlock and the transaction is rolled back.

Transaction 2 is rolled back, transaction 1 is still running, and the number of currently running transactions monitored is 1. T5

Query OK, 1 row affected (20.91 sec)
Rows matched: 1 Changed: 1 Warnings: 0

Transaction 2 is rolled back, and the previously blocked update statement continues to be executed.

The number of currently running transactions monitored is 1. T6

Commit;

Query OK, 0 rows affected (0.00 sec)

Transaction 1 has been committed, transaction 2 has been rolled back, and the number of currently running transactions monitored is 0.

This is a simple deadlock scenario. Transaction 1 and transaction 2 wait for each other to release the lock. the InnoDB Storage engine detects the deadlock and rolls transaction 2 back, this makes transaction 1 no longer wait for the lock of transaction B, so that it can continue execution. How does the InnoDB Storage engine detect deadlocks? To understand this problem, we first check the InnoDB status:

Show engine innodb status \ G

------------------------LATEST DETECTED DEADLOCK------------------------2018-01-14 12:17:13 0x70000f1cc000*** (1) TRANSACTION:TRANSACTION 5120, ACTIVE 17 sec starting index readmysql tables in use 1, locked 1LOCK 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 updatingupdate 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 waitingRecord lock, heap no 5 PHYSICAL RECORD: n_fields 5; compact format; info bits 00: 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 readmysql tables in use 1, locked 13 lock struct(s), heap size 1136, 2 row lock(s)MySQL thread id 11, OS thread handle 123145555853312, query id 2765 localhost root updatingupdate 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 gapRecord lock, heap no 5 PHYSICAL RECORD: n_fields 5; compact format; info bits 00: 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 waitingRecord lock, heap no 7 PHYSICAL RECORD: n_fields 5; compact format; info bits 00: 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)

The InnoDB status has many indicators. Here we take the deadlock information and we can see that InnoDB can output the recent deadlock information. In fact, many deadlock monitoring tools are developed based on this function.

The deadlock information displays information about the two transaction wait locks (blue indicates transaction 1 and Green indicates transaction 2). Pay attention to the following: 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. From the output, we can see that transaction 1 is waiting for the row LOCK with heap no 5, transaction 2 is waiting for the row lock with heap no 7;

Holds the lock (S): indicates the lock information held by THE current transaction. From THE output, transaction 2 HOLDS five rows of heap no.

The output result shows that InnoDB rolled back transaction 2.

So how does InnoDB check for deadlocks?

The simplest way to think of this is to assume that a transaction is waiting for a lock. If the wait time exceeds the set threshold, the transaction fails, this avoids long waits for multiple transactions. The innodb_lock_wait_timeout parameter is used to set the lock wait time.

According to this method, it takes time to solve the deadlock (that is, wait until the threshold value set by innodb_lock_wait_timeout). This method is slightly passive and affects system performance, the InnoDB Storage engine provides a better algorithm to solve the deadlock problem, the wait-for graph algorithm. Simply put, when multiple transactions start to wait for each other, the wait-for graph algorithm is enabled. The algorithm determines that a deadlock is used to roll back one of the transactions immediately and the deadlock is terminated. The advantage of this method is that the check is more active and the wait time is short.

The basic principles of the wait-for graph algorithm are as follows:

For ease of understanding, we consider deadlocks as a scenario where four cars are congested with each other:

Four vehicles are regarded as four transactions, waiting for each other's locks, resulting in deadlocks. The wait-for graph algorithm uses transactions as nodes and the lock wait relationships between transactions are represented by directed edges. for example, transaction A waits for the lock of transaction B, draw A directed edge from node A to Node B. If A, B, C, and D form A directed graph and form A ring, the result is A deadlock. This is the basic principle of the wait-for graph algorithm.

Summary:

1. How can we check if a deadlock occurs during business development? We have already introduced how to monitor the InnoDB status. You can use a small tool to collect deadlock records for later viewing.

2. What should the business system do if a deadlock occurs? From the above we can see that after InnoDB checks the Deadlock, a Deadlock found when trying to get lock is reported to the client; try restarting transaction information and roll back the transaction, the application end needs to restart the transaction for this information and save the field logs for further analysis to avoid the next deadlock.

5. Analysis of lock wait Problems

In business development, the probability of deadlock occurrence is small, but the probability of lock wait appears is large. The lock wait is because a transaction occupies lock resources for a long time, other transactions keep waiting for the previous transaction to release the lock.

Transaction 1

Transaction 2

Transaction monitoring

T1

Begin;

Query OK, 0 rows affected (0.00 sec)

Begin;

Query OK, 0 rows affected (0.00 sec)

T2

Select * from user where id = 3 for update;

+ ---- + ------ +
| Id | name | age |
+ ---- + ------ +
| 3 | sun | 20 |
+ ---- + ------ +
1 row in set (0.00 sec)

Other query operations

Select * from information_schema.INNODB_TRX;

By querying the innodb Transaction table of the metabase, the number of currently running transactions is 2, that is, transaction 1 and transaction 2.

T3 other query operations

Update user set name = 'hei' where id = 3;

Because the record id = 3 is added with a row lock by transaction 1, this statement will be blocked (that is, lock wait)

The number of currently running transactions monitored is 2. T4 other query operations

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

The lock wait time exceeds the threshold and the operation fails. Note: Transaction 2 is not rolled back at this time.

The number of currently running transactions monitored is 2. T5 commit; transaction 1 is committed, transaction 2 is not committed, and the number of currently running transactions is 1 monitored.

From the above, we can see that transaction 1 holds the row lock with id = 3 for a long time, and transaction 2 generates the lock wait. The operation is interrupted after the wait time exceeds innodb_lock_wait_timeout, but the transaction is not rolled back. If we encounter lock wait in business development, it will not only affect the performance, but also challenge your business process, because your business end needs to adapt to the lock wait situation for logical processing, whether to retry or roll back the transaction.

The MySQL metadata table collects transaction and lock wait information, such as INNODB_LOCKS, INNODB_TRX, and INNODB_LOCK_WAITS in the information_schema database, you can use these tables to observe the lock wait situation of your business system. You can also use the following statement to easily query the association between transactions and lock WAITS:

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_waits w inner join information_schema.innodb_trx B ON B. trx_id = w. blocking_trx_id inner join information_schema.innodb_trx r ON r. 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 lock wait for your business system, which helps you understand the current database lock situation and help you optimize your business programs;

2. Make appropriate logical judgments on lock wait timeout in the business system.

6. Summary

This article introduces several common MySQL concurrency problems through a few simple examples, and tries to come up with our troubleshooting ideas for these problems. Transactions, table locks, metadata locks, and row locks are involved in this article, but there are more and more concurrent problems, such as transaction isolation levels and GAP locks. Real concurrency problems may be complicated, but the troubleshooting methods and methods can be reused. In this article, we use show processlist; show engine innodb status; and how to query the metadata table to locate the problem. If the problem involves replication, you also need to use master/slave monitoring to assist.