【Mysql】FLUSHTABLESWITHREADLOCK

原文地址：http://blog.csdn.net/zbszhangbosen/article/details/7434173

FLUSH TABLES WITH READ LOCK 作用

1. 对于myisam存储引擎，从数据库外部拷贝.frm，.fyd，.fyi文件后，执行FLUSH TABLES/FLUSH TABLES WITH READ LOCK后，就可以使用新表了，这个功能非常有用，备份变得格外简单
2. MySQLdump中从主库中拉出一个副本后通过change master后就可以快速搭建slave，为了获得一个一致性点，一般会使用参数–single-transaction和–master-data，它会使用FLUSH TABLES WITH READ LOCK阻塞事务commit写binlog来获得transaction开始时主库binlog位置
3. 与FLUSH TABLES相似，清空table_cache

创新互联是一家专业提供登封企业网站建设,专注与成都做网站、网站制作、成都外贸网站建设、成都h5网站建设、小程序制作等业务。10年已为登封众多企业、政府机构等服务。创新互联专业网络公司优惠进行中。

除了这几个重要作用外，目前还没有发现其他用处，而且对于InnoDB存储引擎，第一条就没用了

FLUSH TABLES WITH READ LOCK vs. FLUSH TABLES

1. 后面多了 WITH READ LOCK使得这两个SQL命令差很大，FLUSH TABLES WITH READ LOCK会在global read lock上加S锁，这会导致整个系统变得只读
2. FLUSH TABLES WITH READ LOCK后必须显示执行UNLOCK TABLES，否则整个系统会一直处于只读状态，这个问题非常隐晦，因为FLUSH TABLES WITH READ LOCK完成后在“show processlist”中无法被观察到，除非这个session自己执行unlock tables或者拥有root权限用户kill这个session，重复一次：FLUSH TABLES WITH READ LOCK后一定要UNLOCK TABLES，除非你想让系统只读！
3. FLUSH TABLES在以上三点都被用到，对于1和3很好理解，2中怎么会用到呢？查看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") ); }

解释很清楚：FLUSH TABLES WITH READ LOCK开始后整个系统就是read only状态了，为了使影响最小，先使用FLUSH TABLES “尝试一次”，假设在FLUSH TABLES和FLUSH TABLES WITH READ LOCK之间存在DML/DDL的可能性较小，FLUSH TABLES WITH READ LOCK执行时间会很短，若不幸一个大DML/DDL正好在这之间发生了，事情就会变得很糟糕了：FLUSH TABLES WITH READ LOCK需要等待DML/DDL执行完，之后开启的DML/DDL需要等待FLUSH TABLES WITH READ LOCK对应的UNLOCK TABLES被执行

FLUSH TABLES WITH READ LOCK实现细节

实现分三步：(函数：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只做这一步） 3. make_global_read_lock_block_commit --> mdl_request.init(MDL_key::COMMIT, "", "", MDL_SHARED, MDL_EXPLICIT);

注：
1. 对于任何DDL/DML，会首先申请MDL_INTENTION_EXCLUSIVE类型的global read lock：
–> mdl_request.init(MDL_key::GLOBAL, “”, “”, MDL_INTENTION_EXCLUSIVE, MDL_EXPLICIT); 然后申请对应表上的相应元数据锁
2. 对于任何DDL/DML，提交时会申请MDL_INTENTION_EXCLUSIVE类型的global commit lock:
–> mdl_request.init(MDL_key::COMMIT, “”, “”, MDL_INTENTION_EXCLUSIVE, MDL_EXPLICIT);(代码见：ha_commit_trans)
3. select操作不会申请任何类型的global read lock或者global commit lock

因此FLUSH TABLES WITH READ LOCK的三个步骤中：
1 会等待DDL/DML结束后才会开始，并且一旦开始后，整个系统变成只读
2 这个步骤可能很长，因为会等待所有的表被关闭，如果系统中有大查询，会一直等待…
3 会阻塞系统中已经开始的事务提交，从而保证binlog不增长，通过show master status就可以获取当前的binlog file和binglog pos

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

lock_global_read_lock被两处调用：reload_acl_and_cache 和 fix_read_only，fix_read_only做的事情和FLUSH TABLES WITH READ LOCK基本一样，lock_global_read_lock(同样的，MDL_SHARED类型的global read lock)–>close_cached_tables–>make_global_read_lock_block_commit,除此之外，设置全局变量read_only=1，由于它们在global read lock上加的都是MDL_SHARED锁，因此这两个命令是兼容的，只是set global read_only=1后，如果有DML会报错：the mysql server is running with the read-only option

一个线上案例

线上出现了一个问题，根据show processlist发现：
1. 所有的DML都无法执行，session状态为：Waiting for global read lock
2. set global read_only=1阻塞， session状态为：Waiting for table flush
出现1说明global read lock被某个session获取了或者被某个session等待，比如以下场景：

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

这时再开一个session，执行任何DML都会出现：Waiting for global read lock，但是此时session 2的状态也是：Waiting for global read lock，与2中现象不一致，出现2说明set global read_only=1的第一阶段已经通过，即MDL_SHARED类型的global read lock已经被grant，这样可以推测出系统中已经有session获取了MDL_SHARED类型的global read lock，而这只可能是FLUSH TABLES WITH READ LOCK造成的，因为如果其他session是也是通过set global read_only=1获取的MDL_SHARED类型的global read lock，那么DML执行就会报错，而不会出现1

故障总结

系统中的global read lock被其它线程获取了（被加上了S锁），因此DML在grl上面加IX锁时会出现Waiting for global read lock，而set read_only在grl上加的是S锁，因此此时是可以grant的，而此时系统中存在大查询，set global read_only=1需要等待表被关闭，因此状态是：Waiting for table flush

因此原因可以确定了：系统中存在flush tables with read lock但是没有unlock tables

小技巧

在手动做FLUSH TABLES WITH READ LOCK/set global read_only=1之前，先执行一下FLUSH TABLES，能够很大概率地减小系统变成只读的时间

参考：http://blog.csdn.net/zbszhangbosen/article/details/7434173

分享名称：【Mysql】FLUSHTABLESWITHREADLOCK
转载来于：http://cqcxhl.cn/article/pgseej.html

重庆分公司，新征程启航