【MySQL】MySQL-锁等待（Waiting for table metadata lock）

---

Waiting for table metadata lock

说明

在使用mysql的时候，我们有时会碰到Waiting for table metadata lock的锁等待。但是这个锁等待比较特殊，在innodb_lock_wait和show engine innodb status 表里面都查不到。

如果一个表产生了Waiting for table metadata lock，那么针对该表的任何操作都会被锁住，包括select，这个影响很大，可以通过配置参数lock_wait_timeout来减少这种锁可以等待的时长，不过最重要的还是减少这种锁等待的产生。

mysql> show variables like 'lock_wait_timeout';
+-------------------+----------+
| Variable_name     | Value    |
+-------------------+----------+
| lock_wait_timeout | 31536000 |
+-------------------+----------+
1 row in set, 1 warning (0.00 sec)

This variables specifies the timeout in seconds for attempts to acquire metadata locks. The permissible values range from 1 to 31536000（1 year）. The default is 35536000.

方法1：innodb_trx

select t.PROCESSLIST_ID,
       t.PROCESSLIST_USER,
			 t.PROCESSLIST_HOST,
			 t.PROCESSLIST_DB,
			 t.PROCESSLIST_STATE,
			 t.PROCESSLIST_COMMAND,
			 t.PROCESSLIST_TIME,
			 t.PROCESSLIST_INFO,
			 e.CURRENT_SCHEMA,
			 GROUP_CONCAT(e.SQL_TEXT SEPARATOR ' ') as sqL_text
from performance_schema.threads t,
		 information_schema.INNODB_TRX trx,
		 performance_schema.events_statements_history e
where t.THREAD_ID = e.THREAD_ID
      and t.PROCESSLIST_ID = trx.trx_mysql_thread_id
group by t.THREAD_ID DESC

如果能查询到一个会话，那么肯定就是这个造成的

如果查询结果是多于一个会话，可以通过processlist_info字段里面的SQL来判断出是哪一个。但是有些情况下，持有metadata锁的会话是在sleep状态下的。也就是说造成持有这个锁的语句已经执行过了，但是由于没有提交或者回滚，导致会话还是持有这个锁。如果碰到这种情况，processlist_info字段就可能是空的，那就只能通过判断会话已经执行过的语句来猜了。

MySQL有一个events_statements_history表，可以通过连接这个表来查看会话执行过什么语句。如果有涉及到等待锁的表的语句就能大概猜出是哪一个了。

但是这个events_statements_history有一个限制，他不会存储所有执行过的语句，而是存储最新执行过的N个语句。这个N是由performance_schema_events_statements_history_size控制的，该参数是只读的，只能重启mysql生效，默认是10。如果执行过的语句超过了这个限制，那么很有可能是看不到对这个表进行修改的语句的，这个时候只能靠猜了。

方法2：metadata_locks表

从MySQL5.7开始，有了performance_schema.metadata_locks表，用于显示等待和持有metadata锁的会话信息。有效的简化了处理metadata锁等待的方法。

5.7版本该特性不是默认开启的，需要手动启动。8.0开始是默认开启的，不需要配置。

#5.7的文档

Metadata lock instrumentation uses the wait/lock/metadata/sql/mdl instrument, which is disabled by default.

#8.0的文档

Metadata lock instrumentation uses the wait/lock/metadata/sql/mdl instrument, which is enabled by default.

开启和关闭

-- 查询是否开启
select * from performance_schema.setup_instruments where name = 'wait/lock/metadata/sql/mdl';

-- 在线开启方法
update performance_schema.setup_instruments
set ENABLED	= 'YES',TIMED = 'YES'
where NAME = 'wait/lock/metadata/sql/mdl';

-- 在线关闭方法
update performance_schema.setup_instruments
set ENABLED	= 'NO',TIMED = 'NO'
where NAME = 'wait/lock/metadata/sql/mdl';

-- 永久开启方法
在配置文件的mysqld区块里面添加
performance-schema-instrument='wait/lock/metadata/sql/mdl'

查询sql

select 
       t1.object_schema,
			 t1.object_name,
			 t1.lock_type,
			 t1.lock_status,
			 t2.PROCESSLIST_ID,
       t2.PROCESSLIST_USER,
			 t2.PROCESSLIST_HOST,
			 t2.PROCESSLIST_DB,
			 t2.PROCESSLIST_STATE,
			 t2.PROCESSLIST_COMMAND,
			 t2.PROCESSLIST_TIME,
			 t2.PROCESSLIST_INFO
from performance_schema.metadata_locks t1,
		 performance_schema.threads t2
where t1.owner_thread_id = t2.THREAD_ID
      and t1.object_schema = '数据库名'
			and t1.object_name = '表名';

只有开启特性以后出现的持有或者等待metadata锁的会话才会被记录。

查询结果中lock_status字段，granted表示持有锁，pending表示等待锁

总结

• MySQL 5.6，使用方法1处理
• MySQL 5.7，先判断是否开启了metadata_locks表特性，如果开启了使用方法2，否则使用方法1
• MySQL 8.0，使用方法2
• 及时提交或者缩短SQL执行时间（优化SQL），可以减少Waiting for table metadata lock出现次数