One, the background for the database system in multi-user concurrent conditions improve concurrency again at the same time to ensure the consistency of the data has always been the goal of the database system, to meet the needs of a large number of concurrent access and must ensure that under the condition of the security of the data, in order to satisfy this aim most database by locks and transaction mechanism, The MySQL database is no exception. However, we still encounter a variety of difficult problems in the process of business development. This article will demonstrate common concurrency problems and analyze solutions by way of case studies.
Table lock causes slow query problems
First let’s look at a simple example, query a user’s information by ID:
mysql> select * from user where id=6;
Copy the codeThis table had a total of three records, but it took 13 seconds to execute.
MySQL > select * from ‘MySQL’;
The select statement is waiting for a table lock. What query is creating the table lock? No direct correlation is shown in this result, but we can assume that the update statement is most likely the result (since there are no other suspicious SQL statements in the process). To verify our guess, let’s examine the user table structure:
Table user uses MyISAM storage engine. MyISAM locks the table before the operation, and then automatically unlocks the table. If the operation is a write operation, the table lock type is write lock; if the operation is a read operation, the table lock type is read lock. As you can understand, the write lock blocks other operations (including reads and writes), which makes all operations serial; In the read lock case, the read – read operation can be parallel, but the read – write operation is still serial. The following example demonstrates an explicit table lock (read lock), read-read parallel, and read-write serial.
To explicitly enable/disable table locking, run the lock table user read/write command. unlock tables; session1:
You can see that session 1 enables the table lock (read lock) to perform the read operation, while session 2 can perform the read operation in parallel, but the write operation is blocked. Session1:
After session1 is unlocked, Seesion2 immediately starts to perform write operations (read/write serial).
Conclusion:
If the table lock is a write lock, it will cause other users to operate the table in serial. If the table lock is a read lock, other users’ read operations can be parallel. So sometimes we run into a simple query that takes a long time to see if this is the case.
Solutions:
** * Use InnoDB storage engine instead of MyISAM storage engine. MySQL8.0 has removed all tables of MyISAM storage engine.
** If you must use MyISAM storage engine, reduce write operation time;
Iii. What are the risks of modifying table structure online?
If one day the business system needs to increase the length of a field, can it be directly modified online? Before we answer that question, let’s take a look at an example:
The above statement attempted to change the length of the name field in the user table, but blocked. As usual, let’s check the current process:
It can be seen from the process that the ALTER statement is waiting for a metadata lock, which is most likely caused by the select statement above, which it is. When performing DML (SELECT, UPDATE, DELETE, INSERT) operations, a metadata lock is added to the table to ensure that the table structure will not be modified during the query, so the above ALTER statement is blocked. What if the order of execution is reversed, with the ALTER statement executed first and the DML statement executed later? Can DML statements be blocked? For example, if I am modifying the table structure in the online environment, will the DML statement on the online environment be blocked? The answer is: not sure.
Online DDL functionality was introduced in MySQL5.6 to allow concurrent DDL and DML statements, and has been enhanced in the current 5.7 release to allow most DDL operations to take place online. See: dev.mysql.com/doc/refman/…
Therefore, whether DML will be blocked during DDL execution for a particular scenario depends on the scenario.
Conclusion:
This example gives us a basic understanding of metadata locking and online DDL. If we need to modify the table structure online during business development, we can refer to the following solutions:
**1, ** as far as possible in the time period of light business;
** Check the official document to make sure that the table modification can be concurrent with DML without blocking online services;
** pt-online-schema-change (pT-online-schema-change) is recommended by Percona. This tool is more powerful than the official ONLINE DDL. The select… The statement makes a full copy of the table and records the increments generated during the change of the table structure through triggers.
For example, to change table A, the main steps are as follows:
** create table A_new; ** create table A_new;
** create trigger on A table, including add, delete, change trigger;
**3, ** through insert… The select… The limit N statement copies data to the destination table in fragments
** rename table A_new to table A.
Analysis of a deadlock problem
Deadlocks occur occasionally in online environments. Deadlocks occur when two or more transactions wait for each other to release the lock, so that the transaction never terminates. To analyze the problem, we will simulate a simple deadlock situation and summarize some analysis ideas from it.
Demo environment: MySQL5.7.20 Transaction isolation level: RR
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
Copy the codeHere is how transactions 1 and 2 work:
InnoDB state has many indicators, here we intercept the deadlock related information, we can see InnoDB can output the latest deadlock information, in fact, many deadlock monitoring tools are based on this function.
WAITING FOR THIS LOCK TO BE GRANTED (blue) and holding THE LOCK(S) (green).
WAITING FOR THIS LOCK TO BE GRANTED Transaction 1 is WAITING FOR a row LOCK whose HEAP NO is 5 and transaction 2 is WAITING FOR a row LOCK whose heap NO is 7.
**HOLDS THE LOCK(S) : **HOLDS THE LOCK(S) of THE current transaction.
As you can see from the output, InnoDB finally rolled back transaction 2.
So how does InnoDB detect deadlocks?
The simplest approach we can think of is if a transaction is waiting for a lock, and if the wait time exceeds a set threshold, the transaction fails. This avoids the situation where multiple transactions are waiting for each other for a long time. The innodb_lock_WAIT_timeout parameter is used to set the lock wait time.
InnoDB storage engine provides a better algorithm to solve the deadlock problem, the wait-for graph algorithm. To put it simply, when multiple transactions start waiting for each other, the wait-for graph algorithm is enabled. The algorithm determines that one of the transactions is a deadlock and immediately rolls back the deadlock. The advantage of this method is that the inspection is more active and the waiting time is shorter.
Here are the basics of the wait-for Graph algorithm:
For the sake of understanding, consider a deadlock as a scenario where four cars are blocking each other:
Four vehicles act as four transactions, waiting for each other’s locks, resulting in deadlocks. The principle of wait-for graph algorithm is to regard transactions as nodes, and the lock-waiting relationship between transactions is represented by 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 A directed graph composed of A, B, C, and D forms A ring, it is judged to be deadlocked. This is the basic principle of the Wait-for Graph algorithm.
Conclusion:
**1, ** How to check if there is deadlock in our business development? By monitoring InnoDB state, you can create a widget that collects deadlock records for later viewing.
**2, ** If a deadlock occurs, how should the business system respond? InnoDB displays a Deadlock found when trying to get lock to the client. If the transaction information is restarted and the transaction is rolled back, the application ends need to restart the transaction based on the information and save onsite logs for further analysis to avoid the next deadlock.
Five, lock wait problem analysis
In business development, the occurrence probability of deadlock is small, but the occurrence probability of lock wait is large. Lock wait is because one transaction occupies lock resources for a long time, while other transactions wait for the previous transaction to release the lock.
Innodb_lock_wait_timeout: innodb_lock_WAIT_timeout: innodb_lock_wait_timeout: Innodb_lock_wait_timeout; If we encounter lock wait in business development, it will not only affect performance, but also pose a challenge to your business process, because your business side needs to adapt to the situation of lock wait logic processing, whether to retry operations or rollback transactions.
Transaction and lock waits information is collected in MySQL metadata tables such as INNODB_LOCKS, INNODB_TRX, and INNODB_LOCK_WAITS in the Information_SCHEMA database. You can use these tables to monitor lock waits in your business system. You can also easily query the relationship between transaction and lock wait by using the following statement:
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;
Copy the codeResults:
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
Copy the codeConclusion:
**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 program;
**2, ** Business systems should make appropriate logical judgment on lock wait timeout.
Six, the summary
This article introduces several common MySQL concurrency problems through a few simple examples, and tries to get our troubleshooting ideas for these problems. This article covers transactions, table locks, metadata locks, and row locks, but there are far more concurrency issues, such as transaction isolation levels, GAP locks, and so on. Real concurrency problems can be numerous and complex, but the troubleshooting ideas and methods are reusable. In this article, we used show ProcessList; show engine innodb status; And query metadata tables to troubleshoot problems. If problems involve replication, use master/slave monitoring to assist.
Long click the QR code to follow us