天天看點
傳回

MySQL:FLTWL的堵塞和被堵塞總結

水準有限有誤請指出，簡單記錄一下

建議閱讀：

http://blog.itpub.net/7728585/viewspace-2143093/ 這是我以前寫的一篇MDL LOCK的文章，比較詳細。

  • FTWRL = flush tables with read lock
  • MDL = META DATA LOCK

一、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 相關鎖。

  • 對于DMLFOR 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、長時間的DDLDMLFOR 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.6
2、長時間的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.6
3、長時間的commit(如大事物送出)也會堵塞FTWRL，因為FTWRL需要擷取COMMIT的S鎖，而commit語句會對commit持有IX（MDL_INTENTION_EXCLUSIVE）鎖，根據相容矩陣不相容。

等待為Waiting for commit lock

基本我們看到所有的語句都會堵塞FTWRL。

四、FTWRL堵塞什麼

1、FTWRL會堵塞DDLDMLFOR UPDATE操作 
#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.6
2、FTWRL會堵塞commit操作 
#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.6
3、FTWRL不會堵塞SELECT操作

五、percona對xtrbackup的改進

對于FTWRL percona做了改進應該是backup lock和binlog lock來代替。

具體參考如下：

https://www.percona.com/doc/percona-server/5.6/management/backup_locks.html?spm=5176.11156381.0.0.30d8e606aPdjeQ

六、總結

  • FTWRL會被基本所有的常用語句堵塞如長時間的DDLDMLSELECTCOMMIT。
  • FTWRL會堵塞 DDLDMLCOMMIT，但是不會堵塞SELECT。直到unlock tables解鎖。
sql 關系型資料庫 mysql ftp mysql認證 mysql學習小結 頭檔案mysql aop mysql讀寫 mysql源碼學習file
上一篇: Maven學習筆記（一）
下一篇: Spring 4.2.2以上版本和swagger內建方案和踩過的坑

繼續閱讀