Analysis of MySQL concurrency issues and solutions

Transaction 1

Transaction 2

Transaction Monitoring

begin;

Query OK, 0 rows affected (0.00 sec)

begin;

Query OK, 0 rows affected (0.00 sec)

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 metadata database, it is monitored that the number of currently running transactions is 2, namely transaction 1 and transaction 2.

update user set name='haha' where id=4;

Because the record with id=4 has been locked by transaction 2, the statement will be blocked.

The number of currently running transactions is monitored to be 2. T4 Blocked state

update user set name='hehe' where id=3;

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

The record with id=3 has been locked by transaction 1, and this transaction holds the row lock of the record with id=4. At this time, the InnoDB storage engine detects a deadlock and this transaction is rolled back.

Transaction 2 is rolled back, but transaction 1 is still running. The number of currently running transactions is 1. T5

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

Since transaction 2 is rolled back, the originally blocked update statement continues to be executed.

The number of monitored running transactions 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 is 0.

This is a simple deadlock scenario. Transaction 1 and Transaction 2 are waiting for each other to release the lock. The InnoDB storage engine detects the deadlock and rolls back Transaction 2, which means that Transaction 1 no longer waits for the lock of Transaction B and can continue to execute. So how does the InnoDB storage engine detect deadlock? To understand this problem, let's first check the status 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 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 UV /;;
3: len 4; hex 68656865; asc hehe;;
4: len 4; hex 80000014; asc ;;

*** WE ROLL BACK TRANSACTION (2)

There are many indicators for InnoDB status. Here we intercept deadlock-related information. It can be seen that InnoDB can output the recent deadlock information. In fact, many deadlock monitoring tools are also developed based on this function.

The deadlock information shows the information about two transactions waiting for locks (blue for transaction 1 and green for transaction 2). Pay special attention to 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 number 5, and transaction 2 is waiting for the row lock with heap number 7.

HOLDS THE LOCK(S): indicates the lock information held by the current transaction. From the output, we can see that transaction 2 holds a lock on heap no. 5.

From the output results, we can see that InnoDB finally rolled back transaction 2.

So how does InnoDB detect deadlock?

The simplest way we can think of is that if a transaction is waiting for a lock, if the waiting time exceeds the set threshold, the transaction operation fails, which avoids the situation where multiple transactions wait for each other for a long time. The parameter innodb_lock_wait_timeout is used to set the lock waiting time.

If this method is used, it takes time to resolve the deadlock (that is, the waiting time exceeds the threshold set by innodb_lock_wait_timeout). This method is slightly passive and affects system performance. The InnoDB storage engine provides a better algorithm to resolve the deadlock problem, the wait-for graph algorithm. Simply put, when multiple transactions start waiting for each other, the wait-for graph algorithm is enabled. After the algorithm determines that a deadlock has occurred, one of the transactions is rolled back immediately, and the deadlock is resolved. The advantages of this method are: more proactive inspection and shorter waiting time.

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

To make it easier to understand, let's think of the deadlock as a scenario where four cars are blocking each other:

The four cars are regarded as four transactions, waiting for each other's lock, causing deadlock. The principle of the wait-for graph algorithm is to treat transactions as nodes and represent the lock waiting relationship between transactions with directed edges. For example, if transaction A waits for the lock of transaction B, a directed edge is drawn from node A to node B. In this way, if the directed graph composed of A, B, C, and D forms a cycle, it is judged as a deadlock. This is the basic principle of the wait-for graph algorithm.

Summarize:

1. How to detect deadlock in our business development? As we have just introduced, by monitoring the InnoDB status, you can make a small tool to collect deadlock records for easy review later.

2. If a deadlock occurs, how should the business system respond? From the above, we can see that when InnoDB detects a deadlock, it reports a Deadlock found when trying to get lock; try restarting transaction message to the client and rolls back the transaction. The application needs to restart the transaction based on this message and save the on-site log for further analysis to avoid the next deadlock.

5. Analysis of lock waiting problem

In business development, the probability of deadlock is low, but the probability of lock waiting is high. Lock waiting is because a transaction occupies lock resources for a long time, while other transactions have been waiting for the previous transaction to release the lock.

Transaction 1

Transaction 2

Transaction Monitoring

begin;

Query OK, 0 rows affected (0.00 sec)

begin;

Query OK, 0 rows affected (0.00 sec)

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 metadata database, it is monitored that the number of currently running transactions is 2, namely transaction 1 and transaction 2.

T3 Other query operations

update user set name='hehe' where id=3;

Because the record with id=3 is locked by transaction 1, the statement will be blocked (i.e., lock waiting).

The number of currently running transactions is monitored to be 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 is monitored to be 2. T5 commit; Transaction 1 has been committed, but transaction 2 has not been committed. The number of currently running transactions is 1.

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

The MySQL metadata tables collect information about transactions and lock waits, such as INNODB_LOCKS, INNODB_TRX, and INNODB_LOCK_WAITS under the information_schema database. You can use these tables to observe the lock wait status of your business system. You can also use the following statement to conveniently query the relationship between transactions and lock waits:

SELECT r.trx_id waiting_trx_id, r.trx_mysql_thread_id waiting_thread, r.trx_query waiting_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
watching_query: update user set name='hehe' where id=3
blocking_trx_id: 5133
blocking_thread: 10
blocking_query: NULL

Summarize:

1. Please monitor the lock wait of your business system, which will help you understand the current database lock situation and help you optimize your business procedures;

2. Appropriate logical judgment should be made in the business system for lock waiting timeout situations.

6. Summary

This article introduces several common MySQL concurrency problems through several simple examples, and tries to come up with ideas for troubleshooting these problems. The article involves transactions, table locks, metadata locks, and row locks, but there are far more concurrency issues that may arise than these, such as transaction isolation levels, GAP locks, and so on. Real concurrency problems may be numerous and complex, but the troubleshooting ideas and methods can be reused. In this article, we used show processlist; show engine innodb status; and query metadata tables to troubleshoot and discover problems. If the problem involves replication, master/slave monitoring is also needed to assist.

You may also be interested in:

How to handle concurrent updates of MySQL data
Tomcat+Mysql high concurrency configuration optimization explanation
PHP uses Mysql lock to solve high concurrency
Solution to PHP+MySQL high-concurrency locked transaction processing problem
Yii+MYSQL lock table to prevent duplicate data in concurrent situations
Implementing high-performance and high-concurrency counter solutions in MySQL (such as article click counts)
Sharing solutions to the problem of concurrent updates in SELECT+UPDATE in MySQL
Example of using MySQL transaction features to implement concurrent and safe auto-increment ID
In-depth explanation of MySQL concurrent insert optimization
Mysql transaction concurrency problem solution

1. Background
2. Slow query caused by table locks
3. What are the risks of modifying the table structure online?
4. Analysis of a deadlock problem
5. Analysis of lock waiting problem
6. Summary

1. Background

Improving concurrency while ensuring data consistency under multi-user concurrent conditions has always been the goal pursued by database systems. It is necessary to meet the needs of a large number of concurrent accesses while ensuring the security of data under such conditions. In order to meet this goal, most databases use locks and transaction mechanisms to achieve this, and MySQL database is no exception. Despite this, we will still encounter various difficult problems during business development. This article will use cases to demonstrate common concurrency problems and analyze solutions.

2. Slow query caused by table locks

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

mysql> select * from user where id=6;

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

When this problem occurs, the first thing we think of is to check the current MySQL process status:

From the process, we can see that the select statement is waiting for a table lock, so what query generates this table lock? This result does not show a direct correlation, but we can infer that it is most likely generated by the update statement (because there is no other suspicious SQL in the process). To confirm our guess, first check the user table structure:

As expected, the user table uses the MyISAM storage engine. MyISAM will generate a table lock before performing an operation and automatically unlock it after the operation is completed. 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 will block other operations (including reads and writes), which makes all operations serial; while read locks can be used in parallel for read-read operations, but read-write operations are still serial. The following example demonstrates the case where a table lock (read lock) is explicitly specified, read-read parallelism, and read-write serialism.

To explicitly open/close table locks, use lock table user read/write; unlock tables;

session1:

session2:

You can see that session 1 enables table lock (read lock) to perform read operations. At this time, session 2 can perform read operations in parallel, but write operations are blocked. Next:

session1:

session2:

When session1 is unlocked, session2 immediately starts writing, i.e., read-write serially.

Summarize:

At this point, we have basically analyzed the cause of the problem. To summarize, the MyISAM storage engine will generate a table lock when performing an operation, which will affect other users' operations on the table. If the table lock is a write lock, it will cause other users to operate serially. If it is a read lock, other users' read operations can be parallel. So sometimes we encounter a simple query that takes a long time, see if this is the case.

Solution:

1) Try not to use the MyISAM storage engine. All MyISAM storage engine tables have been removed in MySQL 8.0. It is recommended to use the InnoDB storage engine.

2) If you must use the MyISAM storage engine, reduce the time of write operations;

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 answering this question, let’s look at a case:

The above statement attempts to modify the length of the name field in the user table, and the statement is blocked. As usual, we check the current process:

From the process, we can see that the alter statement is waiting for a metadata lock, and this metadata lock is likely caused by the select statement above, which is indeed the case. When executing DML (select, update, delete, insert) operations, 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 statement will be blocked. So what if the execution order is reversed, executing the alter statement first and then the DML statement? Will DML statements be blocked? For example, if I am modifying the table structure in an online environment, will the online DML statements be blocked? The answer is: uncertain.

The online DDL function was provided in MySQL 5.6, allowing some DDL statements and DML statements to be executed concurrently. In the current 5.7 version, online DDL has been enhanced, which allows most DDL operations to be performed online. For details, see: https://dev.mysql.com/doc/refman/5.7/en/innodb-create-index-overview.html

Therefore, whether DML will be blocked during DDL execution in a specific scenario depends on the scenario.

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

1. Try to conduct it during the time when the business volume is low;

2. Check the official documentation to confirm that the table modification to be done can be concurrent with DML and will not block online business;

3. It is recommended to use Percona's pt-online-schema-change tool, which is more powerful than the official online DDL. Its basic principle is: perform a full copy through the insert... select... statement, and use triggers to record the increments generated during the table structure change process, so as to achieve the purpose of table structure change.

For example, to change table A, the main steps are:

Create an empty table of the destination table structure, A_new;
Create triggers on table A, including add, delete, and modify triggers;
Use the insert...select...limit N statement to copy data to the destination table in fragments.
After the copy is completed, rename the A_new table to the A table.

4. Analysis of a deadlock problem

Deadlock problems occasionally occur in online environments. Deadlock is caused by two or more transactions waiting for each other to release locks, resulting in the transaction never being terminated. In order to analyze the problem, we will simulate a simple deadlock situation and then summarize some analysis ideas from it.

Demonstration 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 demonstrates the working of transaction 1 and transaction 2:

<<: How to authorize all the contents of a folder to a certain user in Linux?

>>: Command to view binlog file creation time in Linux