Shulou(Shulou.com)05/31 Report--

This article mainly introduces "the analysis of blocking and being blocked in MySQL FLTWL". In the daily operation, I believe that many people have doubts about the analysis of blocking and being blocked in MySQL FLTWL. The editor has consulted all kinds of data and sorted out simple and easy-to-use operation methods. I hope it will be helpful to answer the doubts of "blocking and being blocked in MySQL FLTWL". Next, please follow the editor to study!

I. the role of FTWRL

Generally speaking, flush tables with read lock is mostly used to lock global tables to obtain binlog information when backing up. Although the holding time is very short, it is easy to be blocked so that the backup can not be completed. So what exactly did flush tables with read lock do as follows:

Acquire the S lock of MDL GLOBAL until unlock tables is released.

Acquire the S lock of MDL COMMIT until unlock tables is released.

Close all tables and reopen, first release table cache (including TABLE_SHARED), and then reload the generated table cache.

(the following information is achieved by increasing the output in the source code functions acquire_lock and release_lock)

The following information can be seen that flush tables with read lock acquires MDL LOCK information:

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

Here is the information about the release of MDL LOCK by unlock tables:

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

The following is about the compatibility of range MDL LOCK:

| Type of active | Request | scoped lock | type | IS (*) IX S X |-+-+ IS | + + | IX | + +-| S | +-- | X | +-| second, common operations are about locks related to FTWRL MDL.

For DML\ FOR UPDATE: the IX lock that needs to acquire GLOBAL holds until the end of the statement, but TABLE MDL holds until the end of the transaction is usually (MDL_SHARED_WRITE (SW)), and DML holds COMMIT's IX lock when it is submitted.

SELECT: GLOBAL's IX lock is not required, but TABLE-level MDL needs to be held until the end of a thing is usually (MDL_SHARED_READ (SR)).

DDL: you need to acquire the IX lock of GLOBAL until the end of the statement, and the TABLE MDL is changeable.

What is the blockage of FTWRL? 1. The FTWRL is blocked by DDL\ DML\ FOR UPDATE for a long time, because FTWRL needs to acquire GLOBAL's S lock, and these statements will hold IX (MDL_INTENTION_EXCLUSIVE) locks for GLOBAL, which are not compatible according to the compatibility matrix.

Wait for: Waiting for global read lock

Block stack frames:

# 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 up sqlbank 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 blocking FTWRL for a long time Because FTWRL closes all tables, table cache cannot be emptied if there are active statements executing, so you have to wait. In fact, even flush tables cannot execute when a statement is executed, and it also needs to wait.

Wait for: Waiting for table flush

Block stack frames:

# 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 up sqlbank 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, Long-term commit (such as big thing submission) can also block FTWRL Because FTWRL needs to acquire the S lock of COMMIT, and the commit statement holds an IX (MDL_INTENTION_EXCLUSIVE) lock on commit, it is not compatible according to the compatibility matrix.

Wait for Waiting for commit lock

Basically we see that all statements block FTWRL.

4. What does FTWRL block? 1. FTWRL will block DDL\ DML\ FOR UPDATE operations.

Wait for: Waiting for global read lock

Block stack frames:

# 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 up sqlbank 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 will block commit operations

Wait for Waiting for commit lock

Block stack frames:

# 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 up sqlbank 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 will not block SELECT operation 5. Percona's improvement of xtrbackup

Improvements to FTWRL percona should be replaced by backup lock and binlog lock.

At this point, the study of "blocking and blocking Analysis of MySQL FLTWL" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

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.