Specific use of MySQL global locks and table-level locks

Preface

When using MySQL in a real enterprise development environment, I am definitely not the only one using MySQL. Instead, a team uses MySQL explicitly, or the business uses MySQL implicitly. So when multiple users or clients connect to use it, we should consider a question: How to ensure the consistency of concurrent data access? In this article, I will talk about MySQL locks, not MySQL transaction isolation levels.

Global Lock

MySQL's global lock closes all open tables and makes all tables read-only. Their commands are:

# Global lock, referred to as FTWRL
FLUSH TABLES WITH READ LOCK;

# Unlock command UNLOCK TABLES;

Experiments on FTWRL: (All the following experiments were completed in MySQL 8.0.22)

From the above experiments, we can conclude that after executing FTWRL, all tables become read-only and other update operations will be blocked.

The main function of the global lock is to make a logical backup of the entire database, that is, to select each table in the database and save it as text.

During the backup process, the entire database is in a read-only state, and the risk is extremely high. If the backup is done on the master database, all business tables will be unable to modify data; if the backup is done on the slave database, the slave database cannot execute the binlog sent from the master database, which will cause a delay between the master and the slave database.

Fortunately, the InnoDB storage engine supports transactions, and mysqldump has a parameter single-transaction, which can create a consistent snapshot in the transaction and then back up all tables. With this parameter, data can be modified during the backup, so it is recommended to use the InnoDB storage engine in normal development.

Table lock

There are two types of table-level locks: table lock and metadata lock.

Table Lock

Table locks are divided into table read locks and table write locks. The commands in MySQL are:

# Table read lock lock tables test read;

# Table write lock lock tables test write;

Next, let's see the difference between table read lock and table write lock through experiment.

Table read lock

A table read lock is added to the session1 session. At this time, both session1 and session2 can read data normally, but session1 will report an error when writing data, and session2 will be blocked when writing data. Session2 can only write data successfully after session1 is unlocked.

From this experiment, we can conclude that after the table is locked, this thread and other threads can read data, this thread will report an error when writing data, and other threads will be blocked when writing data.

Table write lock

From the above experiments, we can conclude that after the table is locked, the current thread can perform read and write operations, and the read and write operations of other threads will be blocked.

Metadata Locking (MDL lock)

In MySQL, the database DDL does not belong to the scope of transactions. If you select a row of data in session1, session2 will add a column xxx to this table. At this time, bugs such as transaction characteristics being destroyed and binlog order being disordered may occur (similar bugs have been announced on the MySQL official website, you can learn about them if you are interested).

In order to solve the above problems, metadata locks were introduced in MySQL 5.5.3. MDL locks do not need to be used explicitly, MySQL will add them by default. Their function is to ensure the correctness of database reading and writing. The following uses MDL to represent metadata lock.

When you add, delete, query or modify a table, an MDL read lock is added by default; when you change the table structure of a table, an MDL write lock is added by default.

When session1 queries test at the beginning, it obtains the MDL read lock and can query the data normally. Then session2 will also obtain the MDL read lock when querying data, so there is no conflict and the data can be queried normally.

However, when it comes to session 3, it needs to acquire the MDL write lock. At this time, it will be blocked because the MDL read lock of session 1 has not been released. Later, session 4 also needs an MDL read lock, but because session 3 is blocked, session 4 will also be blocked.

If this is an online business table, this scenario will invalidate any subsequent operations, and the table will become unreadable and unwritable. If the client configures the MySQL retry mechanism, it will re-establish a session and request again when the timeout occurs, and then MySQL will crash due to the continuous addition of new threads.

From the above example, we can know that the MDL lock is added by default when the statement is executed, but it will not be released after the statement is executed. The MDL lock will be released only after the entire transaction is committed.

Therefore, for us developers, we should try to avoid slow queries, ensure that transactions are submitted in a timely manner, avoid large transactions, etc. For DBAs, we should also try to avoid performing DDL operations during business peak hours.

Summarize

• A global lock will make all tables read-only and all update operations will be blocked.
• The table read lock means that both this thread and other threads can read it. If this thread writes, an error will be reported, and other threads will be blocked.
• The table write lock allows this thread to read and write, and other threads will be blocked from reading and writing.
• Introducing MDL locks to resolve bugs caused by simultaneous execution of transactions and DDL

References

• MySQL in Depth, Second Edition: 20.3.8 When to Use Table Locks
• "MySQL Practice 45 Lectures" by Lin Xiaobin
  

This concludes this article on the specific use of MySQL global locks and table-level locks. For more information about MySQL global locks and table-level locks, please search for previous articles on 123WORDPRESS.COM or continue to browse the following related articles. I hope you will support 123WORDPRESS.COM in the future!