Shulou(Shulou.com)06/02 Report--

This article mainly explains "what is a Mysql lock". The content in the article is simple and clear, and it is easy to learn and understand. Please follow the editor's train of thought to study and learn "what is a Mysql lock".

The original purpose of database lock design is to deal with concurrency problems. As a resource shared by multiple users, when concurrent access occurs, the database needs to reasonably control the access rules of the resources. The lock is an important data structure used to implement these access rules.

According to the scope of locking, locks in MySQL can be roughly divided into three categories: global locks, table-level locks and row locks.

Global lock

A global lock is a lock on the entire database instance. MySQL provides a way to add a global read lock, and the command is Flush tables with read lock (FTWRL). You can use this command when you need to make the entire library read-only, and then the following statements from other threads will be blocked: data update statements (addition, deletion and modification of data), data definition statements (including table creation, table structure modification, etc.), and commit statements for update transactions

A typical usage scenario of a global lock is to make a logical backup of the whole library.

But making the whole library read-only sounds dangerous:

If you back up on the master database, the update cannot be performed during the backup, and the business will basically have to stop; if you back up on the slave database, the binlog synchronized from the master library cannot be executed from the slave library during the backup, which will lead to master-slave delay.

The official logical backup tool is mysqldump. When mysqldump uses the parameter-single-transaction, a transaction is initiated before the import data to ensure that a consistent view is obtained. Due to the support of MVCC, the data can be updated normally in this process.

With this feature, why do you need FTWRL? Consistent reading is good, but only if the engine supports this isolation level. For example, for an engine that does not support transactions such as MyISAM, if there are updates during the backup, only the latest data can always be fetched, which breaks the consistency of the backup. At this point, we need to use the FTWRL command

Table level lock

There are two kinds of table level locks in MySQL: one is table lock, the other is metadata lock.

The syntax of a table lock is lock tables... Read/write . Similar to FTWRL, locks can be released actively with unlock tables or automatically when the client is disconnected. It should be noted that lock tables syntax not only restricts the reading and writing of other threads, but also defines the next operation objects of this thread.

Another type of table-level lock is MDL (metadata lock). MDL does not need to be used explicitly and is automatically added when a table is accessed. The function of MDL is to ensure the correctness of reading and writing. You can imagine that if a query is traversing the data in a table and another thread changes the table structure and deletes a column during execution, then the result obtained by the query thread does not match the table structure.

Therefore, MDL is introduced in MySQL 5.5.When adding, deleting, changing and querying a table, MDL read lock is added, and MDL write lock is added when the table structure is to be changed.

Read locks are not mutually exclusive, so you can have multiple threads to add, delete, change and query a table at the same time. Read-write locks and write locks are mutually exclusive, which is used to ensure the security of the operation of the change table structure. Therefore, if two threads want to add fields to a table at the same time, one of them will have to wait for the other to finish execution before it can start execution.

The MDL lock in a transaction is applied at the beginning of statement execution, but is not released immediately after the end of the statement, but is not released until the entire transaction is committed.

How to safely add fields to a table?

First of all, we have to solve the long transaction. If the transaction is not committed, it will always occupy the MDL lock.

The ideal mechanism is to set the waiting time in the alter table statement. If you can get the MDL write lock within this specified waiting time, it is best to give up if you can't get it and do not block the following business statements.

Row lock

MySQL's row locks are implemented by each engine itself at the engine layer. However, not all engines support row locks, such as MyISAM engines. Not supporting row locks means that concurrency control can only use table locks. For tables of this kind of engine, only one update can be performed on the same table at any time, which will affect the degree of business concurrency. InnoDB supports row locks, which is one of the important reasons why MyISAM is replaced by InnoDB.

Two-stage lock

In InnoDB transactions, row locks are added when needed, but not immediately when they are not needed, but not until the end of the transaction. This is the two-phase locking protocol.

If you need to lock multiple lines in your transaction, try to put back the locks that are most likely to cause lock conflicts and affect concurrency.

Deadlock and deadlock detection

When different threads in a concurrent system have circular resource dependencies, and the threads involved are waiting for other threads to release resources, it will cause these threads to enter a state of infinite waiting, which is called deadlock.

When a deadlock occurs, there are two strategies:

One strategy is to go straight into waiting until it times out. This timeout can be set by the parameter innodb_lock_wait_timeout. Another strategy is to initiate deadlock detection, and after the deadlock is found, actively roll back a transaction in the deadlock chain so that other transactions can continue. Set the parameter innodb_deadlock_detect to on, indicating that this logic is enabled

Normally we still have to adopt the second strategy, that is, active deadlock detection, and the default value of innodb_deadlock_detect itself is on. Active deadlock detection can be quickly detected and processed when a deadlock occurs, but it also has an additional burden.

Whenever a transaction is locked, it is necessary to see whether the thread it depends on is locked by others, so as to determine whether there is a loop waiting, that is, a deadlock.

What if all the transactions we mentioned above have to update the same line?

Each new blocked thread has to determine whether it has caused a deadlock due to its own joining, which is an operation with a time complexity of O (n). Assuming that there are 1000 concurrent threads updating the same row at the same time, the deadlock detection operation is of the order of 1 million. Although the final test result is that there is no deadlock, it consumes a lot of CPU resources during this period.

How to solve the performance problems caused by such hot row updates?

One idea is to control the degree of concurrency. Based on the above analysis, you will find that if concurrency can be controlled, compared with only 10 threads updating at the same time, then the cost of deadlock detection is very low, and this problem will not occur.

This concurrency control should be done on the database server. If you have middleware, you can consider implementing it in middleware; if your team has someone who can modify the MySQL source code, you can also do it in MySQL. The basic idea is that for peer updates, queue up before entering the engine

You can also consider reducing lock conflicts by changing one line to logical multiple lines.

Thank you for your reading, the above is the content of "what is the Mysql lock", after the study of this article, I believe you have a deeper understanding of what the Mysql lock is, and the specific use needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.