Let's talk about MySQL global lock

This article brings you relevant knowledge about mysql, which mainly introduces related issues about global locks. Global locks are to lock the entire database. After we add a read lock to the database, no other requests can add a write lock to the database. Let's take a look at it together. I hope it will be helpful to everyone.

Recommended learning: mysql video tutorial

The original intention of database design is to deal with concurrency issues, as a resource shared by multiple users. When concurrent access occurs, the database needs to reasonably control resource access rules. The lock is an important data structure used to implement this access rule.

Let’s first post a diagram of the general classification of locks

According to the scope of locking, the locks in MySQL can be roughly divided into global locks, table locks and table locks. Lock, row lock. We will mainly learn these types of locks first. In this article, we will learn about global locks.

Global lock

Global lock is to lock the entire database. After we add a read lock to the database, no other requests can add write locks to the database. When we add a write lock to the database, no other subsequent requests can add read or write locks to the database.

FTWRL

MySQL provides a method to add a global read lock, Flush tables with read lock (FTWRL). When we need to make the entire library in a read-only state, we can use this command. Then the following statements of other threads will be blocked: data update statements (add, delete, modify), data definition statements (including creating tables, modifying table structures, etc.) and update A commit statement for a class transaction.

Usage scenarios of global locks

Usage scenarios of global locks: Make logical backup of the entire database. Logical backup means selecting every table in the entire database and saving it as text. That is to say, the global lock is only used when performing master-slave backup data or importing and exporting data.

So why do we need a global lock?

Because when we are doing data backup or importing and exporting data, if data can be added, deleted, and modified at the same time during this period, data inconsistency will occur.

In the past, there was a way to use the FTWRL mentioned above to ensure that no other thread would update the database during the backup. Note: During the backup process, the entire library is completely in a read-only state.

Because the global lock is oriented to this database, adding a global lock sounds very dangerous:

• If we back up on the main database, updates cannot be performed during the backup period, and Basically all business is suspended.
• If we back up on the slave database, the binlog synchronized from the master database during the backup period cannot be executed, which will lead to master-slave delay and data inconsistency.

How to avoid locking

Since adding global lock has such a big impact, can we avoid locking?

Through the above introduction, we know that locking is to solve the problem of data inconsistency. So as long as we can solve the problem of data inconsistency, we don't need to add a global lock. There is such an idea: if we record an operation log when we start data backup, additions, deletions, modifications and queries to the database are allowed without locking during the backup process, and during the backup process, the operation records of additions, deletions, modifications and queries are recorded in one In the log file, after our backup is completed, we will execute all the operations in the log file during this period. This ensures the consistency of data before and after backup.

Summary, without locking, the backup data and the main data are not at a logical time point, and this view is logically inconsistent. If we ensure that the logical time points are consistent, that is, the logical views are consistent, we can ensure data consistency. From this, we think of the transaction isolation level we learned before. Opening a transaction under the repeatable isolation level is a consistent view.

There is a mechanism in InnoDB, the default engine of MySQL, to ensure data consistency. The InnoDB engine has a data snapshot version function. This function is called MVCC because MVCC retains snapshots of historical versions. Each snapshot corresponds to a transaction version number. When we back up data, we will apply for a transaction version number. When reading When fetching data, you only need to read data with a smaller transaction version number than your own.

–single-transaction command locking

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

--The role of the single-transaction parameter is to set the isolation level of the transaction to repeatable read, that is, REPEATABLE READ. This ensures that all the same queries in a transaction read the same data, which roughly guarantees that the During the dump, if other InnoDB engine threads modify the table data and submit it, it will have no impact on the data of the dump thread.

And set WITH CONSISTENT SNAPSHOT to the snapshot level. Imagine that if it is only repeatable reading, then before the data is dumped at the beginning of the transaction, other threads modify and submit the data, then the result of the first query at this time is the result submitted by other threads, and WITH CONSISTENT SNAPSHOT can ensure that when a transaction is started, the result of the first query is the data A at the beginning of the transaction. Even if other threads modify its data to B at this time, the result of the query is still A.

The single-transaction method only applies to libraries where all tables use transaction engines. In the mysqldump process, adding --single-transaction can ensure that the InnoDB data is completely consistent. For engines like MyISAM that do not support transactions, if there are updates during the backup process, only the latest data can be obtained. Then This destroys the consistency of the backup. At this time, a global lock is still needed, so we still need to use the FTWRL command.

Read-only setting

We may also have this question, since the whole library is read-only, why don't we use set global readonly = true?

It is true that the readonly method can also put the entire library into a read-only state, but it is still recommended to use the FTWRL method, mainly for two reasons:

• In some systems, the value of readonly will It is used for other logic, such as determining whether a database is the main database or the standby database. Therefore, modifying global variables has a greater impact.
• There are differences in the exception handling mechanism. If the client disconnects abnormally after executing the FTWRL command, MySQL will automatically release the global lock and the entire library will return to a state where it can be updated normally.

After setting the entire library to readonly, if an exception occurs on the client, the database will remain in the readonly state, which will cause the entire library to be in an unwritable state for a long time, with a high risk.

Recommended learning: mysql video tutorial

The above is the detailed content of Let's talk about MySQL global lock. For more information, please follow other related articles on the PHP Chinese website!