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

Original address: http://blog.csdn.net/zbszhangbosen/article/details/7434173FLUSH TABLES WITH READ LOCK function

1. For the myisam storage engine, after copying .frm, .fyd, .fyi files from outside the database, and executing FLUSH TABLES/FLUSH TABLES WITH READ LOCK, you can use the new table, which is very useful and backup becomes very simple.

2. In mysqldump, you can quickly build a slave after pulling a copy from the main library through change master. In order to obtain a consistency point, you usually use the parameters-single-transaction and-master-data. It uses FLUSH TABLES WITH READ LOCK blocking transaction commit to write binlog to get the location of the main library binlog at the beginning of transaction.

3. Like FLUSH TABLES, clear table_cache

Apart from these important functions, no other uses have been found, and the first one is useless for the InnoDB storage engine.

FLUSH TABLES WITH READ LOCK vs. FLUSH TABLES

1. The addition of WITH READ LOCK makes a big difference between the two SQL commands. FLUSH TABLES WITH READ LOCK will add an S lock on the global read lock, which will cause the whole system to become read-only.

2. After FLUSH TABLES WITH READ LOCK, the execution UNLOCK TABLES must be displayed, otherwise the whole system will always be in a read-only state, this problem is very obscure, because after the FLUSH TABLES WITH READ LOCK is completed, it cannot be observed in the "show processlist", unless the session executes the unlock tables itself or has the root permission user kill this session, repeat: be sure to UNLOCK TABLES after FLUSH TABLES WITH READ LOCK, unless you want the system to be read-only!

3. FLUSH TABLES is used in all the above three points. It is easy to understand 1 and 3. How can it be used in 2? Check the code and comments for mysqldump.c:

Static int do_flush_tables_read_lock (MYSQL * mysql_con) {/ * We do first a FLUSH TABLES. If a long update is running, the FLUSH TABLES will wait but will not stall the whole mysqld, and when the long update is done the FLUSH TABLES WITH READ LOCK will start and succeed quickly. So, FLUSH TABLES is to lower the probability of a stage where both mysqldump and most client connections are stalled. Of course, if a second long update starts between the two FLUSHes, we have that bad stall. * / return (mysql_query_with_error_report (mysql_con, 0, ((opt_master_data! = 0)? "FLUSH / *! 40101 LOCAL * / TABLES": "FLUSH TABLES") | | mysql_query_with_error_report (mysql_con, 0, "FLUSH TABLES WITH READ LOCK");}

The explanation is clear: after FLUSH TABLES WITH READ LOCK starts, the whole system is in read only state. In order to minimize the impact, use FLUSH TABLES to "try" first. Assuming that there is little possibility of DML/DDL between FLUSH TABLES and FLUSH TABLES WITH READ LOCK, FLUSH TABLES WITH READ LOCK execution time will be very short. If unfortunately a big DML/DDL happens in between, things will become very bad: FLUSH TABLES WITH READ LOCK needs to wait for DML/DDL execution to finish. The DML/DDL that is enabled after that needs to wait for the UNLOCK TABLES corresponding to the FLUSH TABLES WITH READ LOCK to be executed.

FLUSH TABLES WITH READ LOCK implementation details

The implementation is divided into three steps: (function: reload_acl_and_cache)

1. Lock_global_read_lock-- > mdl_request.init (MDL_key::GLOBAL, ", MDL_SHARED, MDL_EXPLICIT); 2. Close_cached_tables (FLUSH TABLES only does this) 3. Make_global_read_lock_block_commit-- > mdl_request.init (MDL_key::COMMIT,", MDL_SHARED, MDL_EXPLICIT)

Note:

1. For any DDL/DML, you will first apply for a global read lock of type MDL_INTENTION_EXCLUSIVE:

-> mdl_request.init (MDL_key::GLOBAL, ", MDL_INTENTION_EXCLUSIVE, MDL_EXPLICIT); then apply for the corresponding metadata lock on the corresponding table

two。 For any DDL/DML, a global commit lock of type MDL_INTENTION_EXCLUSIVE is requested at the time of submission:

-> mdl_request.init (MDL_key::COMMIT, ", MDL_INTENTION_EXCLUSIVE, MDL_EXPLICIT); (for code, see ha_commit_trans)

3. Select operation will not apply for any type of global read lock or global commit lock

So of the three steps of FLUSH TABLES WITH READ LOCK:

1 waits for the end of DDL/DML before it starts, and once started, the entire system becomes read-only

2 this step may be long, because it will wait for all tables to be closed, and if there is a large query in the system, it will wait all the time.

3 will block the commits of transactions that have already started in the system, thus ensuring that binlog does not grow. The current binlog file and binglog pos can be obtained through show master status.

FLUSH TABLES WITH READ LOCK vs. Set global read_only=1

Lock_global_read_lock is called in two places: reload_acl_and_cache and fix_read_only,fix_read_only do basically the same thing as FLUSH TABLES WITH READ LOCK, lock_global_read_lock (also, global read lock of type MDL_SHARED)-> close_cached_tables- > make_global_read_lock_block_commit, and in addition, set the global variable read_only=1, because they all add MDL_ share locks on global read lock Therefore, the two commands are compatible, but after set global read_only=1, if there is a DML, an error will be reported: the mysql server is running with the read-only option

An online case

There is a problem online, according to show processlist:

1. All DML cannot be executed. The session status is: Waiting for global read lock.

2. Set global read_only=1 blocking. Session status is: Waiting for table flush

The appearance of 1 indicates that the global read lock has been acquired by a session or waited by a session, such as the following scenario:

Global read lock testSESSION 1SESSION 2start transaction;start transaction;select * from test.t1 for update; [select not finished...] FLUSH TABLES WITH READ LOCK/set global read_only=1

When another session is opened, any DML will appear: Waiting for global read lock, but at this time the status of session 2 is also: Waiting for global read lock, which is inconsistent with the phenomenon in 2. The appearance of 2 indicates that the first phase of set global read_only=1 has passed, that is, the global read lock of MDL_SHARED type has been grant, so it can be inferred that session in the system has acquired global read lock of MDL_SHARED type, and this can only be caused by FLUSH TABLES WITH READ LOCK. Because if the other session is a MDL_SHARED type global read lock also obtained through set global read_only=1, then the DML execution will report an error instead of 1

Fault summary

The global read lock in the system is acquired by other threads (S lock is added), so Waiting for global read lock appears when DML adds IX lock on grl, while set read_only adds S lock on grl, so it is possible to grant at this time, while there is a large query in the system, and set global read_only=1 needs to wait for the table to be closed, so the status is: Waiting for table flush

So the reason can be determined: there is flush tables with read lock in the system but there is no unlock tables

Tips

Before doing FLUSH TABLES WITH READ LOCK/set global read_only=1 manually, execute FLUSH TABLES first, which can greatly reduce the time for the system to become read-only

Reference: http://blog.csdn.net/zbszhangbosen/article/details/7434173

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.