如何使用觀(guān)測MDL鎖，相信很多沒(méi)有經(jīng)驗的人對此束手無(wú)策，為此本文總結了問(wèn)題出現的原因和解決方法，通過(guò)這篇文章希望你能解決這個(gè)問(wèn)題。
在給一個(gè)小表加字段的時(shí)候，一直拿不到鎖，報錯：
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
期間show processlist查看，ddl在等待MDL鎖。MySQL 的 Metadata Lock 機制是為了保護并發(fā)訪(fǎng)問(wèn)數據對象的一致性，并且showps也看不到活動(dòng)的事務(wù)。
如何查到MDL鎖的源頭呢？innodb_locks是看不到MDL鎖的信息的。performance_schema下面有一張表叫metadata_locks
mysql> desc metadata_locks; +-----------------------+---------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-----------------------+---------------------+------+-----+---------+-------+ | OBJECT_TYPE | varchar(64) | NO | | NULL | | | OBJECT_SCHEMA | varchar(64) | YES | | NULL | | | OBJECT_NAME | varchar(64) | YES | | NULL | | | OBJECT_INSTANCE_BEGIN | bigint(20) unsigned | NO | | NULL | | | LOCK_TYPE | varchar(32) | NO | | NULL | | | LOCK_DURATION | varchar(32) | NO | | NULL | | | LOCK_STATUS | varchar(32) | NO | | NULL | | | SOURCE | varchar(64) | YES | | NULL | | | OWNER_THREAD_ID | bigint(20) unsigned | YES | | NULL | | | OWNER_EVENT_ID | bigint(20) unsigned | YES | | NULL | | +-----------------------+---------------------+------+-----+---------+-------+ 10 rows in set (0.00 sec)
默認需要開(kāi)啟metadata 的 instrument，才能拿到MDL鎖的信息。
call sys.ps_setup_enable_instrument('wait/lock/metadata/sql/mdl%')
或者：
UPDATE performance_schema.setup_consumers SET ENABLED = 'YES' WHERE NAME ='global_instrumentation';
UPDATE performance_schema.setup_instruments SET ENABLED = 'YES' WHERE NAME ='wait/lock/metadata/sql/mdl';開(kāi)啟后重新執行DDL語(yǔ)句，然后觀(guān)測
mysql> select * from performance_schema.metadata_locks where object_name='t'\G *************************** 1. row *************************** OBJECT_TYPE: TABLE OBJECT_SCHEMA: ming OBJECT_NAME: t OBJECT_INSTANCE_BEGIN: 87772208 LOCK_TYPE: SHARED_READ LOCK_DURATION: TRANSACTION LOCK_STATUS: GRANTED SOURCE: OWNER_THREAD_ID: 29 OWNER_EVENT_ID: 990 *************************** 2. row *************************** OBJECT_TYPE: TABLE OBJECT_SCHEMA: ming OBJECT_NAME: t OBJECT_INSTANCE_BEGIN: 140151695148016 LOCK_TYPE: SHARED_UPGRADABLE LOCK_DURATION: TRANSACTION LOCK_STATUS: GRANTED SOURCE: OWNER_THREAD_ID: 31 OWNER_EVENT_ID: 178 *************************** 3. row *************************** OBJECT_TYPE: TABLE OBJECT_SCHEMA: ming OBJECT_NAME: t OBJECT_INSTANCE_BEGIN: 140151695148992 LOCK_TYPE: EXCLUSIVE LOCK_DURATION: TRANSACTION LOCK_STATUS: PENDING SOURCE: OWNER_THREAD_ID: 31 OWNER_EVENT_ID: 237 3 rows in set (0.00 sec)
可以看到已經(jīng)會(huì )話(huà)被授予（GRANTED）了SHARED_READ類(lèi)型的MDL鎖。DDL會(huì )話(huà)先被授予了SHARED_UPGRADABLE，然后需要被授予EXCLUSIVE的鎖，但是拿不到，所以處于PENDING狀態(tài)。所以只要找到GRANTED的會(huì )話(huà)是哪個(gè)，然后殺掉就可以了。
MDL_SHARED_READ, /* A shared metadata lock for cases when there is an intention to modify (and not just read) data in the table. A connection holding SW lock can read table metadata and modify or read table data (after acquiring appropriate table and row-level locks). To be used for tables to be modified by INSERT, UPDATE, DELETE statements, but not LOCK TABLE ... WRITE or DDL). Also taken by SELECT ... FOR UPDATE. */ MDL_SHARED_UPGRADABLE, /* A shared metadata lock for cases when we need to read data from table and block all concurrent modifications to it (for both data and metadata). Used by LOCK TABLES READ statement.
通過(guò)perforamnce_schema.threads查找：
mysql> select * from performance_schema.threads where thread_id=29\G *************************** 1. row *************************** THREAD_ID: 29 NAME: thread/sql/one_connection TYPE: FOREGROUND PROCESSLIST_ID: 2 PROCESSLIST_USER: root PROCESSLIST_HOST: localhost PROCESSLIST_DB: ming PROCESSLIST_COMMAND: Sleep PROCESSLIST_TIME: 107 PROCESSLIST_STATE: NULL PROCESSLIST_INFO: NULL PARENT_THREAD_ID: 1 ROLE: NULL INSTRUMENTED: YES HISTORY: YES CONNECTION_TYPE: Socket THREAD_OS_ID: 2822 1 row in set (0.00 sec)
取processlist_id，kill掉該會(huì )話(huà)即可。
mysql> kill 2; Query OK, 0 rows affected (0.00 sec) mysql> alter table t add c4 int; Query OK, 0 rows affected (3 min 58.00 sec) Records: 0 Duplicates: 0 Warnings: 0
查看pstack： 阻塞源頭
Thread 9 (Thread 0x7f77c3043700 (LWP 2822)): #0 0x00007f77f1debf0d in poll () from /lib64/libc.so.6 #1 0x00000000012c48df in vio_io_wait (vio=<optimized out>, event=<optimized out>, timeout=28800000) at /export/home/pb2/build/sb_0-32013917-1545390211.74/mysql-5.7.25/vio/viosocket.c:786 #2 0x00000000012c49d3 in vio_socket_io_wait (vio=<optimized out>, event=<optimized out>) at /export/home/pb2/build/sb_0-32013917-1545390211.74/mysql-5.7.25/vio/viosocket.c:77 #3 0x00000000012c5008 in vio_read (vio=0x505ab10, buf=0x5372800 "\001", size=4) at /export/home/pb2/build/sb_0-32013917-1545390211.74/mysql-5.7.25/vio/viosocket.c:132 #4 0x0000000000c6e4a3 in net_read_raw_loop (net=0x50683f8, count=4) at /export/home/pb2/build/sb_0-32013917-1545390211.74/mysql-5.7.25/sql/net_serv.cc:672 #5 0x0000000000c6ed3b in net_read_packet_header (net=0x50683f8) at /export/home/pb2/build/sb_0-32013917-1545390211.74/mysql-5.7.25/sql/net_serv.cc:756 #6 net_read_packet (net=<optimized out>, complen=0x7f77c3042cf8) at /export/home/pb2/build/sb_0-32013917-1545390211.74/mysql-5.7.25/sql/net_serv.cc:822 #7 0x0000000000c6efec in my_net_read (net=0x50683f8) at /export/home/pb2/build/sb_0-32013917-1545390211.74/mysql-5.7.25/sql/net_serv.cc:899 #8 0x0000000000c7c88c in Protocol_classic::read_packet (this=0x5067c98) at /export/home/pb2/build/sb_0-32013917-1545390211.74/mysql-5.7.25/sql/protocol_classic.cc:808 #9 0x0000000000c7b622 in Protocol_classic::get_command (this=0x5067c98, com_data=0x7f77c3042da0, cmd=0x7f77c3042dcc) at /export/home/pb2/build/sb_0-32013917-1545390211.74/mysql-5.7.25/sql/protocol_classic.cc:965 #10 0x0000000000d1e3d7 in do_command (thd=0x5066c40) at /export/home/pb2/build/sb_0-32013917-1545390211.74/mysql-5.7.25/sql/sql_parse.cc:964 #11 0x0000000000defb14 in handle_connection (arg=<optimized out>) at /export/home/pb2/build/sb_0-32013917-1545390211.74/mysql-5.7.25/sql/conn_handler/connection_handler_per_thread.cc:306 #12 0x000000000125d3f4 in pfs_spawn_thread (arg=0x50008f0) at /export/home/pb2/build/sb_0-32013917-1545390211.74/mysql-5.7.25/storage/perfschema/pfs.cc:2190 #13 0x00007f77f333ce25 in start_thread () from /lib64/libpthread.so.0 #14 0x00007f77f1df6bad in clone () from /lib64/libc.so.6
被阻塞者：
Thread 7 (Thread 0x7f77c2fdf700 (LWP 2869)): #0 0x00007f77f3340d42 in pthread_cond_timedwait@@GLIBC_2.3.2 () from /lib64/libpthread.so.0 #1 0x0000000000c67d40 in native_cond_timedwait (abstime=0x7f77c2fda790, mutex=0x7f779c000b78, cond=0x7f779c000ba8) at /export/home/pb2/build/sb_0-32013917-1545390211.74/mysql-5.7.25/include/thr_cond.h:129 #2 my_cond_timedwait (abstime=0x7f77c2fda790, mp=0x7f779c000b78, cond=0x7f779c000ba8) at /export/home/pb2/build/sb_0-32013917-1545390211.74/mysql-5.7.25/include/thr_cond.h:182 #3 inline_mysql_cond_timedwait (src_line=1861, src_file=0x140f5d0 "/export/home/pb2/build/sb_0-32013917-1545390211.74/mysql-5.7.25/sql/mdl.cc", abstime=0x7f77c2fda790, mutex=0x7f779c000b78, that=0x7f779c000ba8) at /export/home/pb2/build/sb_0-32013917-1545390211.74/mysql-5.7.25/include/mysql/psi/mysql_thread.h:1229 #4 MDL_wait::timed_wait (this=0x7f779c000b78, owner=0x7f779c000ae0, abs_timeout=0x7f77c2fda790, set_status_on_timeout=false, wait_state_name=<optimized out>) at /export/home/pb2/build/sb_0-32013917-1545390211.74/mysql-5.7.25/sql/mdl.cc:1861 #5 0x0000000000c6a917 in MDL_context::acquire_lock (this=0x7f779c000b78, mdl_request=0x7f77c2fda810, lock_wait_timeout=<optimized out>) at /export/home/pb2/build/sb_0-32013917-1545390211.74/mysql-5.7.25/sql/mdl.cc:3655 #6 0x0000000000c6b68f in MDL_context::upgrade_shared_lock (this=0x7f779c000b78, mdl_ticket=0x7f779c0103f0, new_type=MDL_EXCLUSIVE, lock_wait_timeout=31536000) at /export/home/pb2/build/sb_0-32013917-1545390211.74/mysql-5.7.25/sql/mdl.cc:3919 #7 0x0000000000d82ad7 in mysql_inplace_alter_table (thd=0x7f779c000ae0, table_list=0x7f779c005fe0, table=0x7f779c01af60, altered_table=0x7f779c068980, ha_alter_info=0x7f77c2fdc1e0, inplace_supported=HA_ALTER_INPLACE_NO_LOCK_AFTER_PREPARE, target_mdl_request=0x7f77c2fdc9a0, alter_ctx=0x7f77c2fdac40) at /export/home/pb2/build/sb_0-32013917-1545390211.74/mysql-5.7.25/sql/sql_table.cc:7449 #8 0x0000000000d8550e in mysql_alter_table (thd=0x7f779c000ae0, new_db=0x7f779c068980 "\360\222\006\234w\177", new_name=0x7f77c2fdc1e0 "\300\323\375\302w\177", create_info=0x7f77c2fdd3c0, table_list=0x7f779c005fe0, alter_info=0x7f77c2fdd4c0) at /export/home/pb2/build/sb_0-32013917-1545390211.74/mysql-5.7.25/sql/sql_table.cc:9798 #9 0x0000000000e8de2c in Sql_cmd_alter_table::execute (this=<optimized out>, thd=0x7f779c000ae0) at /export/home/pb2/build/sb_0-32013917-1545390211.74/mysql-5.7.25/sql/sql_alter.cc:327 #10 0x0000000000d17f2a in mysql_execute_command (thd=0x7f779c000ae0, first_level=true) at /export/home/pb2/build/sb_0-32013917-1545390211.74/mysql-5.7.25/sql/sql_parse.cc:4835 #11 0x0000000000d1c3fd in mysql_parse (thd=0x7f779c000ae0, parser_state=<optimized out>) at /export/home/pb2/build/sb_0-32013917-1545390211.74/mysql-5.7.25/sql/sql_parse.cc:5570 #12 0x0000000000d1d625 in dispatch_command (thd=0x7f779c000ae0, com_data=0x7f77c2fdeda0, command=COM_QUERY) at /export/home/pb2/build/sb_0-32013917-1545390211.74/mysql-5.7.25/sql/sql_parse.cc:1484 #13 0x0000000000d1e4d4 in do_command (thd=0x7f779c000ae0) at /export/home/pb2/build/sb_0-32013917-1545390211.74/mysql-5.7.25/sql/sql_parse.cc:1025 #14 0x0000000000defb14 in handle_connection (arg=<optimized out>) at /export/home/pb2/build/sb_0-32013917-1545390211.74/mysql-5.7.25/sql/conn_handler/connection_handler_per_thread.cc:306 #15 0x000000000125d3f4 in pfs_spawn_thread (arg=0x5d629c0) at /export/home/pb2/build/sb_0-32013917-1545390211.74/mysql-5.7.25/storage/perfschema/pfs.cc:2190#16 0x00007f77f333ce25 in start_thread () from /lib64/libpthread.so.0
免責聲明：本站發(fā)布的內容（圖片、視頻和文字）以原創(chuàng )、來(lái)自本網(wǎng)站內容采集于網(wǎng)絡(luò )互聯(lián)網(wǎng)轉載等其它媒體和分享為主，內容觀(guān)點(diǎn)不代表本網(wǎng)站立場(chǎng)，如侵犯了原作者的版權，請告知一經(jīng)查實(shí)，將立刻刪除涉嫌侵權內容，聯(lián)系我們QQ:712375056，同時(shí)歡迎投稿傳遞力量。
Copyright ? 2009-2022 56dr.com. All Rights Reserved. 特網(wǎng)科技 特網(wǎng)云 版權所有 特網(wǎng)科技 粵ICP備16109289號
域名注冊服務(wù)機構:阿里云計算有限公司（萬(wàn)網(wǎng)） 域名服務(wù)機構：煙臺帝思普網(wǎng)絡(luò )科技有限公司（DNSPod） CDN服務(wù)：阿里云計算有限公司 百度云 中國互聯(lián)網(wǎng)舉報中心 增值電信業(yè)務(wù)經(jīng)營(yíng)許可證B2
建議您使用Chrome、Firefox、Edge、IE10及以上版本和360等主流瀏覽器瀏覽本網(wǎng)站
