Waiting for table metadata lock问题处理
Waiting for table metadata lock问题处理
作者：魏新平
在使用mysql的时候，我们有时会碰到Waiting for table metadata lock的锁等待。但是这个锁等待比较特殊，在innodb_lock_wait和show engine innodb status 表里面都查不到。
该锁的影响
还是用例子来展示一下吧，更加简单直观。
+-------+-----------------+-----------+------+---------+--------+---------------------------------+-----------------------------------------------------------------------+
| ID | USER | HOST | DB | COMMAND | TIME | STATE | INFO |
+-------+-----------------+-----------+------+---------+--------+---------------------------------+-----------------------------------------------------------------------+
| 1 | event_scheduler | localhost | NULL | Daemon | 365869 | Waiting on empty queue | NULL |
| 13006 | root | localhost | NULL | Query | 35 | Waiting for table metadata lock | alter table test.t add column s int |
| 13040 | root | localhost | NULL | Query | 0 | executing | select * from information_schema.processlist where command != 'sleep' |
| 13044 | root | localhost | NULL | Query | 4 | Waiting for table metadata lock | update test.t set b = 10 where a = 3 |
| 13043 | root | localhost | NULL | Query | 21 | Waiting for table metadata lock | select * from test.t |
+-------+-----------------+-----------+------+---------+--------+---------------------------------+-----------------------------------------------------------------------+
可以看到，如果一个表产生了Waiting for table metadata lock，那么针对该表的任何操作都会被锁住，包括select，这对生产会产生巨大的影响。我们可以通过配置参数lock_wait_timeout来减少这种锁可以等待的时长。不过最重要的还是减少这种锁等待的产生。
lock_wait_timeout
This variable specifies the timeout in seconds for attempts to acquire metadata locks. The permissible values range from 1 to 31536000 (1 year). The default is 31536000.
这个参数控制的是这个metadata锁等待可以等待的最长时间，如果超过就会报等待超时，默认是31536000秒，也就是一年，可以动态修改。
那接下来将介绍两种方法来处理这种问题。
方法一：INNODB_TRX
sql:
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
如果从INNODB_TRX只能查到一个会话，那就算运气很好了，肯定是这个造成的。
那么如果碰到多于一个的情况呢，可以通过PROCESSLIST_INFO字段里面的sql来判断出来是哪一个。但是有些情况下，持有metadata锁的会话是在sleep状态下的。也就是说造成持有这个锁的语句已经执行过了，但是由于没有提交或者回滚，导致会话还是持有着这个锁。如果碰到这种情况，PROCESSLIST_INFO字段就可能是空的了，那就只能通过判断会话已经执行过的语句来猜了。
当然猜也不是瞎猜，是有根据的猜。mysql有一个events_statements_history表，可以通过连接这个表来查看会话执行过什么语句。如果有涉及到等待锁的表的语句就能大概猜出来是哪一个了。比如下面这个结果
+----------------+------------------+------------------+----------------+-------------------+---------------------+------------------+------------------+----------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------+
| PROCESSLIST_ID | PROCESSLIST_USER | PROCESSLIST_HOST | PROCESSLIST_DB | PROCESSLIST_STATE | PROCESSLIST_COMMAND | PROCESSLIST_TIME | PROCESSLIST_INFO | CURRENT_SCHEMA | sql_text |
+----------------+------------------+------------------+----------------+-------------------+---------------------+------------------+------------------+----------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 12268 | root | localhost | NULL | NULL | Sleep | 435 | NULL | NULL | select @@version_comment limit 1;select USER();begin;select * from t;select * from test.t;update t set b = 4 where a = 3;update test.t set b = 4 where a = 3 |
+----------------+------------------+------------------+----------------+-------------------+---------------------+------------------+------------------+----------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------+
最后的字段显示了这个会话执行过了什么语句。12268会话就执行过一个select，两个update等等语句。这很可能说明12268会话持有t表的metadata锁。一般来说杀了这个会话就可以解决问题了。
但是这个events_statements_history有一个限制，他不会存储所有执行过的语句，而是存储最新执行过的N个语句。这个N是由performance_schema_events_statements_history_size控制的，该参数是只读的，只能重启mysql生效，默认是10。如果执行过的语句超过了这个限制，那么很有可能是看不到对这个表进行修改的语句的,这个时候就需要猜了。
方法二：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.
在线开启方法
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=ON'
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_COMMAND,
t2.PROCESSLIST_STATE,
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 = 'test'
AND t1.OBJECT_NAME = 't'
只有开启特性以后出现的持有或者等待metadata锁的会话才会被记录。
+---------------+-------------+-------------------+-------------+----------------+------------------+------------------+----------------+---------------------+---------------------------------+--------------------------------+
| OBJECT_SCHEMA | OBJECT_NAME | LOCK_TYPE | LOCK_STATUS | PROCESSLIST_ID | PROCESSLIST_USER | PROCESSLIST_HOST | PROCESSLIST_DB | PROCESSLIST_COMMAND | PROCESSLIST_STATE | PROCESSLIST_INFO |
+---------------+-------------+-------------------+-------------+----------------+------------------+------------------+----------------+---------------------+---------------------------------+--------------------------------+
| test | t | SHARED_WRITE | GRANTED | 12268 | root | localhost | test | Sleep | NULL | NULL |
| test | t | SHARED_READ | GRANTED | 12268 | root | localhost | test | Sleep | NULL | NULL |
| test | t | SHARED_UPGRADABLE | GRANTED | 12378 | root | localhost | test | Query | Waiting for table metadata lock | alter table t add column f int |
| test | t | EXCLUSIVE | PENDING | 12378 | root | localhost | test | Query | Waiting for table metadata lock | alter table t add column f int |
+---------------+-------------+-------------------+-------------+----------------+------------------+------------------+----------------+---------------------+---------------------------------+--------------------------------+
上面的结果中LOCK_STATUS字段表示连接对于matadata锁的持有状态，GRANTED表示持有，PENDING表示等待。很容易可以看出来12268会话持有了锁，而12378会话正在等待这个锁。通过kill 12268语句，或者让12268提交回滚都能解决这个问题。
总结
如果你的mysql还是5.6，那么只能按照先方法一来处理。如果你的mysql是5.7版本，可以先执行select * from performance_schema.setup_instruments WHERE NAME = 'wait/lock/metadata/sql/mdl';来查看是不是开启了metadata_locks表的特性，如果开启了可以直接使用方法二来处理。那如果是mysql8了，那么恭喜你，可以直接使用方法二来处理，简单快捷。一般及时提交或者尽量优化sql，缩短sql的执行时间，就可以减少Waiting for table metadata lock的出现次数了。