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!

本篇文章给大家带来了关于mysql的相关知识,其中主要介绍了关于架构原理的相关内容,MySQL Server架构自顶向下大致可以分网络连接层、服务层、存储引擎层和系统文件层,下面一起来看一下,希望对大家有帮助。

在mysql中,可以利用char()和REPLACE()函数来替换换行符;REPLACE()函数可以用新字符串替换列中的换行符,而换行符可使用“char(13)”来表示,语法为“replace(字段名,char(13),'新字符串') ”。

方法:1、利用right函数,语法为“update 表名 set 指定字段 = right(指定字段, length(指定字段)-1)...”;2、利用substring函数,语法为“select substring(指定字段,2)..”。

mysql的msi与zip版本的区别:1、zip包含的安装程序是一种主动安装,而msi包含的是被installer所用的安装文件以提交请求的方式安装;2、zip是一种数据压缩和文档存储的文件格式,msi是微软格式的安装包。

转换方法:1、利用cast函数,语法“select * from 表名 order by cast(字段名 as SIGNED)”;2、利用“select * from 表名 order by CONVERT(字段名,SIGNED)”语句。

本篇文章给大家带来了关于mysql的相关知识,其中主要介绍了关于MySQL复制技术的相关问题,包括了异步复制、半同步复制等等内容,下面一起来看一下,希望对大家有帮助。

本篇文章给大家带来了关于mysql的相关知识,其中主要介绍了mysql高级篇的一些问题,包括了索引是什么、索引底层实现等等问题,下面一起来看一下,希望对大家有帮助。

在mysql中,可以利用REGEXP运算符判断数据是否是数字类型,语法为“String REGEXP '[^0-9.]'”;该运算符是正则表达式的缩写,若数据字符中含有数字时,返回的结果是true,反之返回的结果是false。


Hot AI Tools

Undresser.AI Undress
AI-powered app for creating realistic nude photos

AI Clothes Remover
Online AI tool for removing clothes from photos.

Undress AI Tool
Undress images for free

Clothoff.io
AI clothes remover

AI Hentai Generator
Generate AI Hentai for free.

Hot Article

Hot Tools

SAP NetWeaver Server Adapter for Eclipse
Integrate Eclipse with SAP NetWeaver application server.

MinGW - Minimalist GNU for Windows
This project is in the process of being migrated to osdn.net/projects/mingw, you can continue to follow us there. MinGW: A native Windows port of the GNU Compiler Collection (GCC), freely distributable import libraries and header files for building native Windows applications; includes extensions to the MSVC runtime to support C99 functionality. All MinGW software can run on 64-bit Windows platforms.

VSCode Windows 64-bit Download
A free and powerful IDE editor launched by Microsoft

MantisBT
Mantis is an easy-to-deploy web-based defect tracking tool designed to aid in product defect tracking. It requires PHP, MySQL and a web server. Check out our demo and hosting services.

mPDF
mPDF is a PHP library that can generate PDF files from UTF-8 encoded HTML. The original author, Ian Back, wrote mPDF to output PDF files "on the fly" from his website and handle different languages. It is slower than original scripts like HTML2FPDF and produces larger files when using Unicode fonts, but supports CSS styles etc. and has a lot of enhancements. Supports almost all languages, including RTL (Arabic and Hebrew) and CJK (Chinese, Japanese and Korean). Supports nested block-level elements (such as P, DIV),
