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

This article mainly explains "what is the global lock in mysql". The content of the explanation 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 the global lock in mysql".

1. A global lock is to lock the entire database instance.

2. MySQL provides a method to add a global read lock, and the command is Flush tables with read lock. You can use this command when the entire database needs to be read-only.

Then the following sentences from other threads will be blocked: data update sentences (data additions and deletions), data definition sentences (including creating tables, modifying table structures, etc.) And update the transaction submission sentence.

A typical global lock usage scenario is to make a full library logical backup. That is, each table of the entire library is stored as text by select.

However, if the entire library is read-only, the following problems may occur:

If the backup is in the main database, it cannot be updated during the backup, and the business basically stops.

If the slave library is backed up, the binlog of master library synchronization cannot be performed during the backup, which will result in master-slave delay.

Open a transaction with a repeatable readable isolation level to get a consistent view.

Mysqldump is the official logical backup tool. When mysqldump uses the parameter-single-transaction, the transaction is started before the data is imported to ensure a consistent view. Due to the support of MVCC, the data can be updated normally during this process. Single-transaction applies only to all libraries where tables use the transaction engine.

Since the whole library is read-only, why not use setglobalreadonly=true?

In some systems, the value of readonly will be used as other logic, for example, to determine whether a library is primary or standby. Therefore, the method of modifying the global variable has a greater impact.

There are differences in exception handling mechanisms. If the client disconnects abnormally after executing the Flushtableswithreadlock command, MySQL will automatically release the entire lock and the entire library will return to its normally updated state. When the entire library is set to readonly, if there is an exception on the client, the database will always remain in the readonly state, which will cause the entire library to be unwritable for a long time and the risk will be higher.

Thank you for reading, the above is the content of "what is the global lock in mysql". After the study of this article, I believe you have a deeper understanding of what the global lock in mysql 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.