共計 15780 個字符，預計需要花費 40 分鐘才能閱讀完成。
這篇文章主要介紹“MySQL FLTWL 的堵塞和被堵塞分析”，在日常操作中，相信很多人在 MySQL FLTWL 的堵塞和被堵塞分析問題上存在疑惑，丸趣 TV 小編查閱了各式資料，整理出簡單好用的操作方法，希望對大家解答”MySQL FLTWL 的堵塞和被堵塞分析”的疑惑有所幫助！接下來，請跟著丸趣 TV 小編一起來學習吧！
一、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.272547Z 7 [Note] [Call Acquire_lock] THIS MDL LOCK acquire [OK]:2018-08-07T08:03:59.272583Z 7 [Note] (MDL PRINT) |Thread id is 7|Current_state: starting|
2018-08-07T08:03:59.272599Z 7 [Note] (--- MDL PRINT) Namespace is:GLOBAL
2018-08-07T08:03:59.272613Z 7 [Note] (----- MDL PRINT) Mdl type is:MDL_SHARED(S)
2018-08-07T08:03:59.272627Z 7 [Note] (------ MDL PRINT) Mdl duration is:MDL_EXPLICIT
2018-08-07T08:03:59.272642Z 7 [Note] (------- MDL PRINT) Mdl status is:EMPTY
2018-08-07T08:03:59.292471Z 7 [Note] [Call Acquire_lock] THIS MDL LOCK acquire [OK]:
2018-08-07T08:03:59.292522Z 7 [Note] (MDL PRINT) |Thread id is 7|Current_state: starting| 2018-08-07T08:03:59.292538Z 7 [Note] (--- MDL PRINT) Namespace is:COMMIT
2018-08-07T08:03:59.292551Z 7 [Note] (----- MDL PRINT) Mdl type is:MDL_SHARED(S)
2018-08-07T08:03:59.292564Z 7 [Note] (------ MDL PRINT) Mdl duration is:MDL_EXPLICIT
2018-08-07T08:03:59.292580Z 7 [Note] (------- MDL PRINT) Mdl status is:EMPTY下面是 unlock tables 釋放 MDL LOCK 的信息：
2018-08-07T08:05:43.520540Z 7 [Note] [Call release_lock] this MDL LOCK will [RELEASE]:2018-08-07T08:05:43.520571Z 7 [Note] (MDL PRINT) |Thread id is 7|Current_state: starting|
2018-08-07T08:05:43.520597Z 7 [Note] (--- MDL PRINT) Namespace is:COMMIT
2018-08-07T08:05:43.520609Z 7 [Note] (----- MDL PRINT) Mdl type is:MDL_SHARED(S)
2018-08-07T08:05:43.520620Z 7 [Note] (------ MDL PRINT) Mdl duration is:MDL_EXPLICIT
2018-08-07T08:05:43.520634Z 7 [Note] (------- MDL PRINT) Mdl status is:EMPTY
2018-08-07T08:05:43.520658Z 7 [Note] [Call release_lock] this MDL LOCK will [RELEASE]:2018-08-07T08:05:43.520671Z 7 [Note] (MDL PRINT) |Thread id is 7|Current_state: starting|
2018-08-07T08:05:43.520682Z 7 [Note] (--- MDL PRINT) Namespace is:GLOBAL
2018-08-07T08:05:43.520693Z 7 [Note] (----- MDL PRINT) Mdl type is:MDL_SHARED(S)
2018-08-07T08:05:43.520704Z 7 [Note] (------ MDL PRINT) Mdl duration is:MDL_EXPLICIT
2018-08-07T08:05:43.520727Z 7 [Note] (------- MDL PRINT) Mdl status is:EMPTY如下是關于范圍 MDL LOCK 的兼容性：
| Type of active |
Request | scoped lock |
type | IS(*) IX S X |
---------+------------------+
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
堵塞棧幀：
#0 0x0000003f7480ba5e in pthread_cond_timedwait@@GLIBC_2.3.2 () from /lib64/libpthread.so.0#1 0x00000000018e7f33 in native_cond_timedwait (cond=0x7fff28009688, mutex=0x7fff28009640, abstime=0x7ffff0318a00)
at /root/mysql5.7.14/percona-server-5.7.14-7/include/thr_cond.h:129#2 0x00000000018e82a2 in safe_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#3 0x00000000014a0149 in my_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#4 0x00000000014a06ac in inline_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#5 0x00000000014a192b in MDL_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#6 0x00000000014a38a7 in MDL_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#7 0x00000000017629ef in Global_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#8 0x00000000015ec19a in reload_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#9 0x00000000015a9c27 in mysql_execute_command (thd=0x7fff28009510, first_level=true) at /root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_parse.cc:4379#10 0x00000000015adcd6 in mysql_parse (thd=0x7fff28009510, parser_state=0x7ffff031a600) at /root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_parse.cc:5836#11 0x00000000015a1b95 in dispatch_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#12 0x00000000015a09c6 in do_command (thd=0x7fff28009510) at /root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_parse.cc:1010#13 0x00000000016e29d0 in handle_connection (arg=0x33d01c0) at /root/mysql5.7.14/percona-server-5.7.14-7/sql/conn_handler/connection_handler_per_thread.cc:312#14 0x0000000001d7b4b0 in pfs_spawn_thread (arg=0x38424d0) at /root/mysql5.7.14/percona-server-5.7.14-7/storage/perfschema/pfs.cc:2188#15 0x0000003f74807aa1 in start_thread () from /lib64/libpthread.so.0#16 0x0000003f740e8bcd in clone () from /lib64/libc.so.62、長時間的 select 堵塞 FTWRL，因為 FTWRL 要關閉所有的表，如果有活躍的語句正在執行 table cache 不能清空，因此需要等待。實際上即便是 flush tables 也不能在有語句執行的時候執行同樣需要等待。
等待為：Waiting for table flush
堵塞棧幀：
#0 0x0000003f7480ba5e in pthread_cond_timedwait@@GLIBC_2.3.2 () from /lib64/libpthread.so.0#1 0x00000000018e7f33 in native_cond_timedwait (cond=0x7fff28009688, mutex=0x7fff28009640, abstime=0x7ffff0318be0)
at /root/mysql5.7.14/percona-server-5.7.14-7/include/thr_cond.h:129#2 0x00000000018e82a2 in safe_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#3 0x00000000014a0149 in my_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#4 0x00000000014a06ac in inline_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#5 0x00000000014a192b in MDL_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#6 0x00000000016a48ca in TABLE_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#7 0x0000000001513e3a in close_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#8 0x00000000015ec1d8 in reload_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#9 0x00000000015a9c27 in mysql_execute_command (thd=0x7fff28009510, first_level=true) at /root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_parse.cc:4379#10 0x00000000015adcd6 in mysql_parse (thd=0x7fff28009510, parser_state=0x7ffff031a600) at /root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_parse.cc:5836#11 0x00000000015a1b95 in dispatch_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#12 0x00000000015a09c6 in do_command (thd=0x7fff28009510) at /root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_parse.cc:1010#13 0x00000000016e29d0 in handle_connection (arg=0x33d01c0) at /root/mysql5.7.14/percona-server-5.7.14-7/sql/conn_handler/connection_handler_per_thread.cc:312#14 0x0000000001d7b4b0 in pfs_spawn_thread (arg=0x38424d0) at /root/mysql5.7.14/percona-server-5.7.14-7/storage/perfschema/pfs.cc:2188#15 0x0000003f74807aa1 in start_thread () from /lib64/libpthread.so.0#16 0x0000003f740e8bcd in clone () 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
堵塞棧幀：
#0 0x0000003f7480ba5e in pthread_cond_timedwait@@GLIBC_2.3.2 () from /lib64/libpthread.so.0#1 0x00000000018e7f33 in native_cond_timedwait (cond=0x7fff58000ee8, mutex=0x7fff58000ea0, abstime=0x7ffff0359660)
at /root/mysql5.7.14/percona-server-5.7.14-7/include/thr_cond.h:129#2 0x00000000018e82a2 in safe_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#3 0x00000000014a0149 in my_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#4 0x00000000014a06ac in inline_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#5 0x00000000014a192b in MDL_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#6 0x00000000014a38a7 in MDL_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#7 0x0000000001517a08 in open_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#8 0x000000000151b24f in open_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#9 0x000000000151c3ab in open_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#10 0x000000000151d7e5 in open_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#11 0x00000000015acb58 in execute_sqlcom_select (thd=0x7fff58000d70, all_tables=0x7fff58006a70) at /root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_parse.cc:5362#12 0x00000000015a5320 in mysql_execute_command (thd=0x7fff58000d70, first_level=true) at /root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_parse.cc:2889#13 0x00000000015adcd6 in mysql_parse (thd=0x7fff58000d70, parser_state=0x7ffff035b600) at /root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_parse.cc:5836#14 0x00000000015a1b95 in dispatch_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#15 0x00000000015a09c6 in do_command (thd=0x7fff58000d70) at /root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_parse.cc:1010#16 0x00000000016e29d0 in handle_connection (arg=0x346f2b0) at /root/mysql5.7.14/percona-server-5.7.14-7/sql/conn_handler/connection_handler_per_thread.cc:312#17 0x0000000001d7b4b0 in pfs_spawn_thread (arg=0x38424d0) at /root/mysql5.7.14/percona-server-5.7.14-7/storage/perfschema/pfs.cc:2188#18 0x0000003f74807aa1 in start_thread () from /lib64/libpthread.so.0#19 0x0000003f740e8bcd in clone () from /lib64/libc.so.62、FTWRL 會堵塞 commit 操作
等待為 Waiting for commit lock
堵塞棧幀：
#0 0x0000003f7480ba5e in pthread_cond_timedwait@@GLIBC_2.3.2 () from /lib64/libpthread.so.0#1 0x00000000018e7f33 in native_cond_timedwait (cond=0x7fff58000ee8, mutex=0x7fff58000ea0, abstime=0x7ffff03599a0)
at /root/mysql5.7.14/percona-server-5.7.14-7/include/thr_cond.h:129#2 0x00000000018e82a2 in safe_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#3 0x00000000014a0149 in my_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#4 0x00000000014a06ac in inline_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#5 0x00000000014a192b in MDL_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#6 0x00000000014a38a7 in MDL_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#7 0x0000000000f63bd9 in ha_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#8 0x00000000016b36bb in trans_commit (thd=0x7fff58000d70) at /root/mysql5.7.14/percona-server-5.7.14-7/sql/transaction.cc:239#9 0x00000000015aa1f1 in mysql_execute_command (thd=0x7fff58000d70, first_level=true) at /root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_parse.cc:4472#10 0x00000000015adcd6 in mysql_parse (thd=0x7fff58000d70, parser_state=0x7ffff035b600) at /root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_parse.cc:5836#11 0x00000000015a1b95 in dispatch_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#12 0x00000000015a09c6 in do_command (thd=0x7fff58000d70) at /root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_parse.cc:1010#13 0x00000000016e29d0 in handle_connection (arg=0x346f2b0) at /root/mysql5.7.14/percona-server-5.7.14-7/sql/conn_handler/connection_handler_per_thread.cc:312#14 0x0000000001d7b4b0 in pfs_spawn_thread (arg=0x38424d0) at /root/mysql5.7.14/percona-server-5.7.14-7/storage/perfschema/pfs.cc:2188#15 0x0000003f74807aa1 in start_thread () from /lib64/libpthread.so.0#16 0x0000003f740e8bcd in clone () from /lib64/libc.so.63、FTWRL 不會堵塞 SELECT 操作五、percona 對 xtrbackup 的改進
對于 FTWRL percona 做了改進應該是 backup lock 和 binlog lock 來代替。
到此，關于“MySQL FLTWL 的堵塞和被堵塞分析”的學習就結束了，希望能夠解決大家的疑惑。理論與實踐的搭配能更好的幫助大家學習，快去試試吧！若想繼續學習更多相關知識，請繼續關注丸趣 TV 網站，丸趣 TV 小編會繼續努力為大家帶來更多實用的文章！
正文完
