这篇文章主要介绍“MySQL FLTWL的堵塞和被堵塞分析”，在日常操作中，相信很多人在MySQL FLTWL的堵塞和被堵塞分析问题上存在疑惑，小编查阅了各式资料，整理出简单好用的操作方法，希望对大家解答”MySQL FLTWL的堵塞和被堵塞分析”的疑惑有所帮助！接下来，请跟着小编一起来学习吧！

一、FTWRL的作用

总的说来flush tables with read lock多用于备份的时候对全局表进行锁定来获取binlog信息。虽然持有时间很短，单容易被堵塞造成备份不能完成。那么flush tables with read lock到底做了什么事情呢如下：

获取MDL GLOBAL的S锁，直到unlock tables释放。

获取MDL COMMIT的S锁，直到unlock tables释放。

关闭所有的表重新打开，先释放table cache(包含TABLE_SHARED)，然后重新加载生成table cache。

（下面的信息是在源码函数 acquire_lock和release_lock增加输出达到的）
下面的信息可以看到flush tables with read lock获取MDL LOCK的信息：

2018-08-07T08:03:59.272547Z7[Note][CallAcquire_lock]THISMDLLOCKacquire[OK]:2018-08-07T08:03:59.272583Z7[Note](>MDLPRINT)|Threadidis7|Current_state:starting|2018-08-07T08:03:59.272599Z7[Note](--->MDLPRINT)Namespaceis:GLOBAL2018-08-07T08:03:59.272613Z7[Note](----->MDLPRINT)Mdltypeis:MDL_SHARED(S)2018-08-07T08:03:59.272627Z7[Note](------>MDLPRINT)Mdldurationis:MDL_EXPLICIT2018-08-07T08:03:59.272642Z7[Note](------->MDLPRINT)Mdlstatusis:EMPTY2018-08-07T08:03:59.292471Z7[Note][CallAcquire_lock]THISMDLLOCKacquire[OK]:2018-08-07T08:03:59.292522Z7[Note](>MDLPRINT)|Threadidis7|Current_state:starting|2018-08-07T08:03:59.292538Z7[Note](--->MDLPRINT)Namespaceis:COMMIT2018-08-07T08:03:59.292551Z7[Note](----->MDLPRINT)Mdltypeis:MDL_SHARED(S)2018-08-07T08:03:59.292564Z7[Note](------>MDLPRINT)Mdldurationis:MDL_EXPLICIT2018-08-07T08:03:59.292580Z7[Note](------->MDLPRINT)Mdlstatusis:EMPTY

下面是unlock tables释放MDL LOCK的信息：

2018-08-07T08:05:43.520540Z7[Note][Callrelease_lock]thisMDLLOCKwill[RELEASE]:2018-08-07T08:05:43.520571Z7[Note](>MDLPRINT)|Threadidis7|Current_state:starting|2018-08-07T08:05:43.520597Z7[Note](--->MDLPRINT)Namespaceis:COMMIT2018-08-07T08:05:43.520609Z7[Note](----->MDLPRINT)Mdltypeis:MDL_SHARED(S)2018-08-07T08:05:43.520620Z7[Note](------>MDLPRINT)Mdldurationis:MDL_EXPLICIT2018-08-07T08:05:43.520634Z7[Note](------->MDLPRINT)Mdlstatusis:EMPTY2018-08-07T08:05:43.520658Z7[Note][Callrelease_lock]thisMDLLOCKwill[RELEASE]:2018-08-07T08:05:43.520671Z7[Note](>MDLPRINT)|Threadidis7|Current_state:starting|2018-08-07T08:05:43.520682Z7[Note](--->MDLPRINT)Namespaceis:GLOBAL2018-08-07T08:05:43.520693Z7[Note](----->MDLPRINT)Mdltypeis:MDL_SHARED(S)2018-08-07T08:05:43.520704Z7[Note](------>MDLPRINT)Mdldurationis:MDL_EXPLICIT2018-08-07T08:05:43.520727Z7[Note](------->MDLPRINT)Mdlstatusis:EMPTY

如下是关于范围 MDL LOCK的兼容性：

|Typeofactive|Request|scopedlock|type|IS(*)IXSX|---------+------------------+IS|++++|IX|++--|S|+-+-|X|+---|二、常见操作关于和FTWRL MDL 相关锁。

对于DML\FOR UPDATE：需要获取GLOBAL的IX锁持有到语句结束，但是TABLE MDL 持有到事物结束一般为(MDL_SHARED_WRITE(SW) )，DML提交的时候会持有COMMIT的IX锁。

SELECT： 不需要GLOBAL的IX锁，但是TABLE级别的MDL 需要持有到事物事物结束一般为(MDL_SHARED_READ(SR))。

DDL： 需要获取GLOBAL的IX锁到语句结束，TABLE MDL多变。

三、FTWRL的被什么堵塞1、长时间的DDL\DML\FOR UPDATE堵塞FTWRL，因为FTWRL需要获取 GLOBAL的S锁，而这些语句都会对GLOBAL持有IX（MDL_INTENTION_EXCLUSIVE）锁，根据兼容矩阵不兼容。

等待为：Waiting for global read lock
堵塞栈帧：

#00x0000003f7480ba5einpthread_cond_timedwait@@GLIBC_2.3.2()from/lib64/libpthread.so.0#10x00000000018e7f33innative_cond_timedwait(cond=0x7fff28009688,mutex=0x7fff28009640,abstime=0x7ffff0318a00)at/root/mysql5.7.14/percona-server-5.7.14-7/include/thr_cond.h:129#20x00000000018e82a2insafe_cond_timedwait(cond=0x7fff28009688,mp=0x7fff28009618,abstime=0x7ffff0318a00,file=0x20013b8"/root/mysql5.7.14/percona-server-5.7.14-7/sql/mdl.cc",line=1899)at/root/mysql5.7.14/percona-server-5.7.14-7/mysys/thr_cond.c:88#30x00000000014a0149inmy_cond_timedwait(cond=0x7fff28009688,mp=0x7fff28009618,abstime=0x7ffff0318a00,file=0x20013b8"/root/mysql5.7.14/percona-server-5.7.14-7/sql/mdl.cc",line=1899)at/root/mysql5.7.14/percona-server-5.7.14-7/include/thr_cond.h:180#40x00000000014a06acininline_mysql_cond_timedwait(that=0x7fff28009688,mutex=0x7fff28009618,abstime=0x7ffff0318a00,src_file=0x20013b8"/root/mysql5.7.14/percona-server-5.7.14-7/sql/mdl.cc",src_line=1899)at/root/mysql5.7.14/percona-server-5.7.14-7/include/mysql/psi/mysql_thread.h:1229#50x00000000014a192binMDL_wait::timed_wait(this=0x7fff28009618,owner=0x7fff28009510,abs_timeout=0x7ffff0318a00,set_status_on_timeout=true,wait_state_name=0x2d132c0)at/root/mysql5.7.14/percona-server-5.7.14-7/sql/mdl.cc:1899#60x00000000014a38a7inMDL_context::acquire_lock(this=0x7fff28009618,mdl_request=0x7ffff0318a70,lock_wait_timeout=31536000)at/root/mysql5.7.14/percona-server-5.7.14-7/sql/mdl.cc:3714#70x00000000017629efinGlobal_read_lock::lock_global_read_lock(this=0x7fff2800b300,thd=0x7fff28009510)at/root/mysql5.7.14/percona-server-5.7.14-7/sql/lock.cc:1131#80x00000000015ec19ainreload_acl_and_cache(thd=0x7fff28009510,options=16388,tables=0x0,write_to_binlog=0x7ffff03199fc)at/root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_reload.cc:222#90x00000000015a9c27inmysql_execute_command(thd=0x7fff28009510,first_level=true)at/root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_parse.cc:4379#100x00000000015adcd6inmysql_parse(thd=0x7fff28009510,parser_state=0x7ffff031a600)at/root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_parse.cc:5836#110x00000000015a1b95indispatch_command(thd=0x7fff28009510,com_data=0x7ffff031ad70,command=COM_QUERY)at/root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_parse.cc:1447#120x00000000015a09c6indo_command(thd=0x7fff28009510)at/root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_parse.cc:1010#130x00000000016e29d0inhandle_connection(arg=0x33d01c0)at/root/mysql5.7.14/percona-server-5.7.14-7/sql/conn_handler/connection_handler_per_thread.cc:312#140x0000000001d7b4b0inpfs_spawn_thread(arg=0x38424d0)at/root/mysql5.7.14/percona-server-5.7.14-7/storage/perfschema/pfs.cc:2188#150x0000003f74807aa1instart_thread()from/lib64/libpthread.so.0#160x0000003f740e8bcdinclone()from/lib64/libc.so.62、长时间的select堵塞FTWRL， 因为FTWRL要关闭所有的表，如果有活跃的语句正在执行table cache不能清空，因此需要等待。实际上即便是flush tables也不能在有语句执行的时候执行同样需要等待。

等待为：Waiting for table flush
堵塞栈帧：

#00x0000003f7480ba5einpthread_cond_timedwait@@GLIBC_2.3.2()from/lib64/libpthread.so.0#10x00000000018e7f33innative_cond_timedwait(cond=0x7fff28009688,mutex=0x7fff28009640,abstime=0x7ffff0318be0)at/root/mysql5.7.14/percona-server-5.7.14-7/include/thr_cond.h:129#20x00000000018e82a2insafe_cond_timedwait(cond=0x7fff28009688,mp=0x7fff28009618,abstime=0x7ffff0318be0,file=0x20013b8"/root/mysql5.7.14/percona-server-5.7.14-7/sql/mdl.cc",line=1899)at/root/mysql5.7.14/percona-server-5.7.14-7/mysys/thr_cond.c:88#30x00000000014a0149inmy_cond_timedwait(cond=0x7fff28009688,mp=0x7fff28009618,abstime=0x7ffff0318be0,file=0x20013b8"/root/mysql5.7.14/percona-server-5.7.14-7/sql/mdl.cc",line=1899)at/root/mysql5.7.14/percona-server-5.7.14-7/include/thr_cond.h:180#40x00000000014a06acininline_mysql_cond_timedwait(that=0x7fff28009688,mutex=0x7fff28009618,abstime=0x7ffff0318be0,src_file=0x20013b8"/root/mysql5.7.14/percona-server-5.7.14-7/sql/mdl.cc",src_line=1899)at/root/mysql5.7.14/percona-server-5.7.14-7/include/mysql/psi/mysql_thread.h:1229#50x00000000014a192binMDL_wait::timed_wait(this=0x7fff28009618,owner=0x7fff28009510,abs_timeout=0x7ffff0318be0,set_status_on_timeout=true,wait_state_name=0x2d09d00)at/root/mysql5.7.14/percona-server-5.7.14-7/sql/mdl.cc:1899#60x00000000016a48cainTABLE_SHARE::wait_for_old_version(this=0x7fff58984190,thd=0x7fff28009510,abstime=0x7ffff0318be0,deadlock_weight=100)at/root/mysql5.7.14/percona-server-5.7.14-7/sql/table.cc:4585#70x0000000001513e3ainclose_cached_tables(thd=0x7fff28009510,tables=0x0,wait_for_refresh=true,timeout=31536000)at/root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_base.cc:1289#80x00000000015ec1d8inreload_acl_and_cache(thd=0x7fff28009510,options=16388,tables=0x0,write_to_binlog=0x7ffff03199fc)at/root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_reload.cc:224#90x00000000015a9c27inmysql_execute_command(thd=0x7fff28009510,first_level=true)at/root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_parse.cc:4379#100x00000000015adcd6inmysql_parse(thd=0x7fff28009510,parser_state=0x7ffff031a600)at/root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_parse.cc:5836#110x00000000015a1b95indispatch_command(thd=0x7fff28009510,com_data=0x7ffff031ad70,command=COM_QUERY)at/root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_parse.cc:1447#120x00000000015a09c6indo_command(thd=0x7fff28009510)at/root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_parse.cc:1010#130x00000000016e29d0inhandle_connection(arg=0x33d01c0)at/root/mysql5.7.14/percona-server-5.7.14-7/sql/conn_handler/connection_handler_per_thread.cc:312#140x0000000001d7b4b0inpfs_spawn_thread(arg=0x38424d0)at/root/mysql5.7.14/percona-server-5.7.14-7/storage/perfschema/pfs.cc:2188#150x0000003f74807aa1instart_thread()from/lib64/libpthread.so.0#160x0000003f740e8bcdinclone()from/lib64/libc.so.63、长时间的commit(如大事物提交)也会堵塞FTWRL，因为FTWRL需要获取COMMIT的S锁，而commit语句会对commit持有IX（MDL_INTENTION_EXCLUSIVE）锁，根据兼容矩阵不兼容。

等待为Waiting for commit lock

基本我们看到所有的语句都会堵塞FTWRL。

四、FTWRL堵塞什么1、FTWRL会堵塞DDL\DML\FOR UPDATE操作

等待为：Waiting for global read lock
堵塞栈帧：

#00x0000003f7480ba5einpthread_cond_timedwait@@GLIBC_2.3.2()from/lib64/libpthread.so.0#10x00000000018e7f33innative_cond_timedwait(cond=0x7fff58000ee8,mutex=0x7fff58000ea0,abstime=0x7ffff0359660)at/root/mysql5.7.14/percona-server-5.7.14-7/include/thr_cond.h:129#20x00000000018e82a2insafe_cond_timedwait(cond=0x7fff58000ee8,mp=0x7fff58000e78,abstime=0x7ffff0359660,file=0x20013b8"/root/mysql5.7.14/percona-server-5.7.14-7/sql/mdl.cc",line=1899)at/root/mysql5.7.14/percona-server-5.7.14-7/mysys/thr_cond.c:88#30x00000000014a0149inmy_cond_timedwait(cond=0x7fff58000ee8,mp=0x7fff58000e78,abstime=0x7ffff0359660,file=0x20013b8"/root/mysql5.7.14/percona-server-5.7.14-7/sql/mdl.cc",line=1899)at/root/mysql5.7.14/percona-server-5.7.14-7/include/thr_cond.h:180#40x00000000014a06acininline_mysql_cond_timedwait(that=0x7fff58000ee8,mutex=0x7fff58000e78,abstime=0x7ffff0359660,src_file=0x20013b8"/root/mysql5.7.14/percona-server-5.7.14-7/sql/mdl.cc",src_line=1899)at/root/mysql5.7.14/percona-server-5.7.14-7/include/mysql/psi/mysql_thread.h:1229#50x00000000014a192binMDL_wait::timed_wait(this=0x7fff58000e78,owner=0x7fff58000d70,abs_timeout=0x7ffff0359660,set_status_on_timeout=true,wait_state_name=0x2d132c0)at/root/mysql5.7.14/percona-server-5.7.14-7/sql/mdl.cc:1899#60x00000000014a38a7inMDL_context::acquire_lock(this=0x7fff58000e78,mdl_request=0x7ffff03596e0,lock_wait_timeout=31536000)at/root/mysql5.7.14/percona-server-5.7.14-7/sql/mdl.cc:3714#70x0000000001517a08inopen_table(thd=0x7fff58000d70,table_list=0x7fff58006a70,ot_ctx=0x7ffff0359b00)at/root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_base.cc:3227#80x000000000151b24finopen_and_process_table(thd=0x7fff58000d70,lex=0x7fff58003350,tables=0x7fff58006a70,counter=0x7fff58003410,flags=0,prelocking_strategy=0x7ffff0359c30,has_prelocking_list=false,ot_ctx=0x7ffff0359b00)at/root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_base.cc:5171#90x000000000151c3abinopen_tables(thd=0x7fff58000d70,start=0x7ffff0359bf0,counter=0x7fff58003410,flags=0,prelocking_strategy=0x7ffff0359c30)at/root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_base.cc:5789#100x000000000151d7e5inopen_tables_for_query(thd=0x7fff58000d70,tables=0x7fff58006a70,flags=0)at/root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_base.cc:6564#110x00000000015acb58inexecute_sqlcom_select(thd=0x7fff58000d70,all_tables=0x7fff58006a70)at/root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_parse.cc:5362#120x00000000015a5320inmysql_execute_command(thd=0x7fff58000d70,first_level=true)at/root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_parse.cc:2889#130x00000000015adcd6inmysql_parse(thd=0x7fff58000d70,parser_state=0x7ffff035b600)at/root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_parse.cc:5836#140x00000000015a1b95indispatch_command(thd=0x7fff58000d70,com_data=0x7ffff035bd70,command=COM_QUERY)at/root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_parse.cc:1447#150x00000000015a09c6indo_command(thd=0x7fff58000d70)at/root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_parse.cc:1010#160x00000000016e29d0inhandle_connection(arg=0x346f2b0)at/root/mysql5.7.14/percona-server-5.7.14-7/sql/conn_handler/connection_handler_per_thread.cc:312#170x0000000001d7b4b0inpfs_spawn_thread(arg=0x38424d0)at/root/mysql5.7.14/percona-server-5.7.14-7/storage/perfschema/pfs.cc:2188#180x0000003f74807aa1instart_thread()from/lib64/libpthread.so.0#190x0000003f740e8bcdinclone()from/lib64/libc.so.62、FTWRL会堵塞commit操作

等待为Waiting for commit lock
堵塞栈帧：

#00x0000003f7480ba5einpthread_cond_timedwait@@GLIBC_2.3.2()from/lib64/libpthread.so.0#10x00000000018e7f33innative_cond_timedwait(cond=0x7fff58000ee8,mutex=0x7fff58000ea0,abstime=0x7ffff03599a0)at/root/mysql5.7.14/percona-server-5.7.14-7/include/thr_cond.h:129#20x00000000018e82a2insafe_cond_timedwait(cond=0x7fff58000ee8,mp=0x7fff58000e78,abstime=0x7ffff03599a0,file=0x20013b8"/root/mysql5.7.14/percona-server-5.7.14-7/sql/mdl.cc",line=1899)at/root/mysql5.7.14/percona-server-5.7.14-7/mysys/thr_cond.c:88#30x00000000014a0149inmy_cond_timedwait(cond=0x7fff58000ee8,mp=0x7fff58000e78,abstime=0x7ffff03599a0,file=0x20013b8"/root/mysql5.7.14/percona-server-5.7.14-7/sql/mdl.cc",line=1899)at/root/mysql5.7.14/percona-server-5.7.14-7/include/thr_cond.h:180#40x00000000014a06acininline_mysql_cond_timedwait(that=0x7fff58000ee8,mutex=0x7fff58000e78,abstime=0x7ffff03599a0,src_file=0x20013b8"/root/mysql5.7.14/percona-server-5.7.14-7/sql/mdl.cc",src_line=1899)at/root/mysql5.7.14/percona-server-5.7.14-7/include/mysql/psi/mysql_thread.h:1229#50x00000000014a192binMDL_wait::timed_wait(this=0x7fff58000e78,owner=0x7fff58000d70,abs_timeout=0x7ffff03599a0,set_status_on_timeout=true,wait_state_name=0x2d13380)at/root/mysql5.7.14/percona-server-5.7.14-7/sql/mdl.cc:1899#60x00000000014a38a7inMDL_context::acquire_lock(this=0x7fff58000e78,mdl_request=0x7ffff0359a10,lock_wait_timeout=31536000)at/root/mysql5.7.14/percona-server-5.7.14-7/sql/mdl.cc:3714#70x0000000000f63bd9inha_commit_trans(thd=0x7fff58000d70,all=true,ignore_global_read_lock=false)at/root/mysql5.7.14/percona-server-5.7.14-7/sql/handler.cc:1783#80x00000000016b36bbintrans_commit(thd=0x7fff58000d70)at/root/mysql5.7.14/percona-server-5.7.14-7/sql/transaction.cc:239#90x00000000015aa1f1inmysql_execute_command(thd=0x7fff58000d70,first_level=true)at/root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_parse.cc:4472#100x00000000015adcd6inmysql_parse(thd=0x7fff58000d70,parser_state=0x7ffff035b600)at/root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_parse.cc:5836#110x00000000015a1b95indispatch_command(thd=0x7fff58000d70,com_data=0x7ffff035bd70,command=COM_QUERY)at/root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_parse.cc:1447#120x00000000015a09c6indo_command(thd=0x7fff58000d70)at/root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_parse.cc:1010#130x00000000016e29d0inhandle_connection(arg=0x346f2b0)at/root/mysql5.7.14/percona-server-5.7.14-7/sql/conn_handler/connection_handler_per_thread.cc:312#140x0000000001d7b4b0inpfs_spawn_thread(arg=0x38424d0)at/root/mysql5.7.14/percona-server-5.7.14-7/storage/perfschema/pfs.cc:2188#150x0000003f74807aa1instart_thread()from/lib64/libpthread.so.0#160x0000003f740e8bcdinclone()from/lib64/libc.so.63、FTWRL不会堵塞SELECT操作五、percona对xtrbackup的改进

对于FTWRL percona做了改进应该是backup lock和binlog lock来代替。

到此，关于“MySQL FLTWL的堵塞和被堵塞分析”的学习就结束了，希望能够解决大家的疑惑。理论与实践的搭配能更好的帮助大家学习，快去试试吧！若想继续学习更多相关知识，请继续关注亿速云网站，小编会继续努力为大家带来更多实用的文章！