search
HomeDatabaseMysql TutorialLet'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.

Let's talk about MySQL global lock

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!

Statement
This article is reproduced at:CSDN. If there is any infringement, please contact admin@php.cn delete
Explain the InnoDB Buffer Pool and its importance for performance.Explain the InnoDB Buffer Pool and its importance for performance.Apr 19, 2025 am 12:24 AM

InnoDBBufferPool reduces disk I/O by caching data and indexing pages, improving database performance. Its working principle includes: 1. Data reading: Read data from BufferPool; 2. Data writing: After modifying the data, write to BufferPool and refresh it to disk regularly; 3. Cache management: Use the LRU algorithm to manage cache pages; 4. Reading mechanism: Load adjacent data pages in advance. By sizing the BufferPool and using multiple instances, database performance can be optimized.

MySQL vs. Other Programming Languages: A ComparisonMySQL vs. Other Programming Languages: A ComparisonApr 19, 2025 am 12:22 AM

Compared with other programming languages, MySQL is mainly used to store and manage data, while other languages ​​such as Python, Java, and C are used for logical processing and application development. MySQL is known for its high performance, scalability and cross-platform support, suitable for data management needs, while other languages ​​have advantages in their respective fields such as data analytics, enterprise applications, and system programming.

Learning MySQL: A Step-by-Step Guide for New UsersLearning MySQL: A Step-by-Step Guide for New UsersApr 19, 2025 am 12:19 AM

MySQL is worth learning because it is a powerful open source database management system suitable for data storage, management and analysis. 1) MySQL is a relational database that uses SQL to operate data and is suitable for structured data management. 2) The SQL language is the key to interacting with MySQL and supports CRUD operations. 3) The working principle of MySQL includes client/server architecture, storage engine and query optimizer. 4) Basic usage includes creating databases and tables, and advanced usage involves joining tables using JOIN. 5) Common errors include syntax errors and permission issues, and debugging skills include checking syntax and using EXPLAIN commands. 6) Performance optimization involves the use of indexes, optimization of SQL statements and regular maintenance of databases.

MySQL: Essential Skills for Beginners to MasterMySQL: Essential Skills for Beginners to MasterApr 18, 2025 am 12:24 AM

MySQL is suitable for beginners to learn database skills. 1. Install MySQL server and client tools. 2. Understand basic SQL queries, such as SELECT. 3. Master data operations: create tables, insert, update, and delete data. 4. Learn advanced skills: subquery and window functions. 5. Debugging and optimization: Check syntax, use indexes, avoid SELECT*, and use LIMIT.

MySQL: Structured Data and Relational DatabasesMySQL: Structured Data and Relational DatabasesApr 18, 2025 am 12:22 AM

MySQL efficiently manages structured data through table structure and SQL query, and implements inter-table relationships through foreign keys. 1. Define the data format and type when creating a table. 2. Use foreign keys to establish relationships between tables. 3. Improve performance through indexing and query optimization. 4. Regularly backup and monitor databases to ensure data security and performance optimization.

MySQL: Key Features and Capabilities ExplainedMySQL: Key Features and Capabilities ExplainedApr 18, 2025 am 12:17 AM

MySQL is an open source relational database management system that is widely used in Web development. Its key features include: 1. Supports multiple storage engines, such as InnoDB and MyISAM, suitable for different scenarios; 2. Provides master-slave replication functions to facilitate load balancing and data backup; 3. Improve query efficiency through query optimization and index use.

The Purpose of SQL: Interacting with MySQL DatabasesThe Purpose of SQL: Interacting with MySQL DatabasesApr 18, 2025 am 12:12 AM

SQL is used to interact with MySQL database to realize data addition, deletion, modification, inspection and database design. 1) SQL performs data operations through SELECT, INSERT, UPDATE, DELETE statements; 2) Use CREATE, ALTER, DROP statements for database design and management; 3) Complex queries and data analysis are implemented through SQL to improve business decision-making efficiency.

MySQL for Beginners: Getting Started with Database ManagementMySQL for Beginners: Getting Started with Database ManagementApr 18, 2025 am 12:10 AM

The basic operations of MySQL include creating databases, tables, and using SQL to perform CRUD operations on data. 1. Create a database: CREATEDATABASEmy_first_db; 2. Create a table: CREATETABLEbooks(idINTAUTO_INCREMENTPRIMARYKEY, titleVARCHAR(100)NOTNULL, authorVARCHAR(100)NOTNULL, published_yearINT); 3. Insert data: INSERTINTObooks(title, author, published_year)VA

See all articles

Hot AI Tools

Undresser.AI Undress

Undresser.AI Undress

AI-powered app for creating realistic nude photos

AI Clothes Remover

AI Clothes Remover

Online AI tool for removing clothes from photos.

Undress AI Tool

Undress AI Tool

Undress images for free

Clothoff.io

Clothoff.io

AI clothes remover

Video Face Swap

Video Face Swap

Swap faces in any video effortlessly with our completely free AI face swap tool!

Hot Tools

MantisBT

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.

Dreamweaver Mac version

Dreamweaver Mac version

Visual web development tools

SublimeText3 Mac version

SublimeText3 Mac version

God-level code editing software (SublimeText3)

PhpStorm Mac version

PhpStorm Mac version

The latest (2018.2.1) professional PHP integrated development tool

WebStorm Mac version

WebStorm Mac version

Useful JavaScript development tools