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 '\n') 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的出现次数了。