如何使用mysql观测MDL锁，相信很多没有经验的人对此束手无策，为此本文总结了问题出现的原因和解决方法，通过这篇文章希望你能解决这个问题。

在给一个小表加字段的时候，一直拿不到锁，报错：

ERROR1205(HY000):Lockwaittimeoutexceeded;tryrestartingtransaction

期间show processlist查看，ddl在等待MDL锁。MySQL 的 Metadata Lock 机制是为了保护并发访问数据对象的一致性，并且showps也看不到活动的事务。

如何查到MDL锁的源头呢？innodb_locks是看不到MDL锁的信息的。performance_schema下面有一张表叫metadata_locks

mysql>descmetadata_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||+-----------------------+---------------------+------+-----+---------+-------+10rowsinset(0.00sec)

默认需要开启metadata 的 instrument，才能拿到MDL锁的信息。

callsys.ps_setup_enable_instrument('wait/lock/metadata/sql/mdl%')或者：UPDATEperformance_schema.setup_consumersSETENABLED='YES'WHERENAME='global_instrumentation';UPDATEperformance_schema.setup_instrumentsSETENABLED='YES'WHERENAME='wait/lock/metadata/sql/mdl';

开启后重新执行DDL语句，然后观测

mysql>select*fromperformance_schema.metadata_lockswhereobject_name='t'\G***************************1.row***************************OBJECT_TYPE:TABLEOBJECT_SCHEMA:mingOBJECT_NAME:tOBJECT_INSTANCE_BEGIN:87772208LOCK_TYPE:SHARED_READLOCK_DURATION:TRANSACTIONLOCK_STATUS:GRANTEDSOURCE:OWNER_THREAD_ID:29OWNER_EVENT_ID:990***************************2.row***************************OBJECT_TYPE:TABLEOBJECT_SCHEMA:mingOBJECT_NAME:tOBJECT_INSTANCE_BEGIN:140151695148016LOCK_TYPE:SHARED_UPGRADABLELOCK_DURATION:TRANSACTIONLOCK_STATUS:GRANTEDSOURCE:OWNER_THREAD_ID:31OWNER_EVENT_ID:178***************************3.row***************************OBJECT_TYPE:TABLEOBJECT_SCHEMA:mingOBJECT_NAME:tOBJECT_INSTANCE_BEGIN:140151695148992LOCK_TYPE:EXCLUSIVELOCK_DURATION:TRANSACTIONLOCK_STATUS:PENDINGSOURCE:OWNER_THREAD_ID:31OWNER_EVENT_ID:2373rowsinset(0.00sec)

可以看到已经会话被授予（GRANTED）了SHARED_READ类型的MDL锁。DDL会话先被授予了SHARED_UPGRADABLE，然后需要被授予EXCLUSIVE的锁，但是拿不到，所以处于PENDING状态。所以只要找到GRANTED的会话是哪个，然后杀掉就可以了。

MDL_SHARED_READ,/*Asharedmetadatalockforcaseswhenthereisanintentiontomodify(andnotjustread)datainthetable.AconnectionholdingSWlockcanreadtablemetadataandmodifyorreadtabledata(afteracquiringappropriatetableandrow-levellocks).TobeusedfortablestobemodifiedbyINSERT,UPDATE,DELETEstatements,butnotLOCKTABLE...WRITEorDDL).AlsotakenbySELECT...FORUPDATE.*/MDL_SHARED_UPGRADABLE,/*Asharedmetadatalockforcaseswhenweneedtoreaddatafromtableandblockallconcurrentmodificationstoit(forbothdataandmetadata).UsedbyLOCKTABLESREADstatement.

通过perforamnce_schema.threads查找：

mysql>select*fromperformance_schema.threadswherethread_id=29\G***************************1.row***************************THREAD_ID:29NAME:thread/sql/one_connectionTYPE:FOREGROUNDPROCESSLIST_ID:2PROCESSLIST_USER:rootPROCESSLIST_HOST:localhostPROCESSLIST_DB:mingPROCESSLIST_COMMAND:SleepPROCESSLIST_TIME:107PROCESSLIST_STATE:NULLPROCESSLIST_INFO:NULLPARENT_THREAD_ID:1ROLE:NULLINSTRUMENTED:YESHISTORY:YESCONNECTION_TYPE:SocketTHREAD_OS_ID:28221rowinset(0.00sec)

取processlist_id，kill掉该会话即可。

mysql>kill2;QueryOK,0rowsaffected(0.00sec)mysql>altertabletaddc4int;QueryOK,0rowsaffected(3min58.00sec)Records:0Duplicates:0Warnings:0

查看pstack： 阻塞源头

Thread9(Thread0x7f77c3043700(LWP2822)):#00x00007f77f1debf0dinpoll()from/lib64/libc.so.6#10x00000000012c48dfinvio_io_wait(vio=<optimizedout>,event=<optimizedout>,timeout=28800000)at/export/home/pb2/build/sb_0-32013917-1545390211.74/mysql-5.7.25/vio/viosocket.c:786#20x00000000012c49d3invio_socket_io_wait(vio=<optimizedout>,event=<optimizedout>)at/export/home/pb2/build/sb_0-32013917-1545390211.74/mysql-5.7.25/vio/viosocket.c:77#30x00000000012c5008invio_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#40x0000000000c6e4a3innet_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#50x0000000000c6ed3binnet_read_packet_header(net=0x50683f8)at/export/home/pb2/build/sb_0-32013917-1545390211.74/mysql-5.7.25/sql/net_serv.cc:756#6net_read_packet(net=<optimizedout>,complen=0x7f77c3042cf8)at/export/home/pb2/build/sb_0-32013917-1545390211.74/mysql-5.7.25/sql/net_serv.cc:822#70x0000000000c6efecinmy_net_read(net=0x50683f8)at/export/home/pb2/build/sb_0-32013917-1545390211.74/mysql-5.7.25/sql/net_serv.cc:899#80x0000000000c7c88cinProtocol_classic::read_packet(this=0x5067c98)at/export/home/pb2/build/sb_0-32013917-1545390211.74/mysql-5.7.25/sql/protocol_classic.cc:808#90x0000000000c7b622inProtocol_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#100x0000000000d1e3d7indo_command(thd=0x5066c40)at/export/home/pb2/build/sb_0-32013917-1545390211.74/mysql-5.7.25/sql/sql_parse.cc:964#110x0000000000defb14inhandle_connection(arg=<optimizedout>)at/export/home/pb2/build/sb_0-32013917-1545390211.74/mysql-5.7.25/sql/conn_handler/connection_handler_per_thread.cc:306#120x000000000125d3f4inpfs_spawn_thread(arg=0x50008f0)at/export/home/pb2/build/sb_0-32013917-1545390211.74/mysql-5.7.25/storage/perfschema/pfs.cc:2190#130x00007f77f333ce25instart_thread()from/lib64/libpthread.so.0#140x00007f77f1df6badinclone()from/lib64/libc.so.6

被阻塞者：

Thread7(Thread0x7f77c2fdf700(LWP2869)):#00x00007f77f3340d42inpthread_cond_timedwait@@GLIBC_2.3.2()from/lib64/libpthread.so.0#10x0000000000c67d40innative_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#2my_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#3inline_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#4MDL_wait::timed_wait(this=0x7f779c000b78,owner=0x7f779c000ae0,abs_timeout=0x7f77c2fda790,set_status_on_timeout=false,wait_state_name=<optimizedout>)at/export/home/pb2/build/sb_0-32013917-1545390211.74/mysql-5.7.25/sql/mdl.cc:1861#50x0000000000c6a917inMDL_context::acquire_lock(this=0x7f779c000b78,mdl_request=0x7f77c2fda810,lock_wait_timeout=<optimizedout>)at/export/home/pb2/build/sb_0-32013917-1545390211.74/mysql-5.7.25/sql/mdl.cc:3655#60x0000000000c6b68finMDL_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#70x0000000000d82ad7inmysql_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#80x0000000000d8550einmysql_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#90x0000000000e8de2cinSql_cmd_alter_table::execute(this=<optimizedout>,thd=0x7f779c000ae0)at/export/home/pb2/build/sb_0-32013917-1545390211.74/mysql-5.7.25/sql/sql_alter.cc:327#100x0000000000d17f2ainmysql_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#110x0000000000d1c3fdinmysql_parse(thd=0x7f779c000ae0,parser_state=<optimizedout>)at/export/home/pb2/build/sb_0-32013917-1545390211.74/mysql-5.7.25/sql/sql_parse.cc:5570#120x0000000000d1d625indispatch_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#130x0000000000d1e4d4indo_command(thd=0x7f779c000ae0)at/export/home/pb2/build/sb_0-32013917-1545390211.74/mysql-5.7.25/sql/sql_parse.cc:1025#140x0000000000defb14inhandle_connection(arg=<optimizedout>)at/export/home/pb2/build/sb_0-32013917-1545390211.74/mysql-5.7.25/sql/conn_handler/connection_handler_per_thread.cc:306#150x000000000125d3f4inpfs_spawn_thread(arg=0x5d629c0)at/export/home/pb2/build/sb_0-32013917-1545390211.74/mysql-5.7.25/storage/perfschema/pfs.cc:2190#160x00007f77f333ce25instart_thread()from/lib64/libpthread.so.0

看完上述内容，你们掌握如何使用mysql观测MDL锁的方法了吗？如果还想学到更多技能或想了解更多相关内容，欢迎关注亿速云行业资讯频道，感谢各位的阅读！