发现问题

最开始是发布生产的时候，运维执行了版本sql语句（alter语句），一直卡在第一条，半天没动静，等了一会儿没有反应，针对该表的任何操作都会被锁住，包括select，于是开始了排查问题。。。

查看mysql执行列表

  1. show processlist;

首先通过查看mysql执行任务列表，发现执行的sql出现在执行列表，并且状态是Waiting for table metadata lock
字面意思是元数据锁等待，但是这个锁等待比较特殊，在innodb_lock_wait和show engine innodb status 表里面都查不到。

  1. SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;
  2. show engine innodb status;

影响范围

如果一个表产生了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

查看当前活跃事物

  1. SELECT
  2.     t.PROCESSLIST_ID,
  3.     t.PROCESSLIST_USER,
  4.     t.PROCESSLIST_HOST,
  5.     t.PROCESSLIST_DB,
  6.     t.PROCESSLIST_STATE,
  7.     t.PROCESSLIST_COMMAND,
  8.     t.PROCESSLIST_TIME,
  9.     t.PROCESSLIST_INFO,
  10.     e.CURRENT_SCHEMA,
  11.     group_concat(e.SQL_TEXT separator '\n') as sql_text
  12. FROM
  13.     `performance_schema`.threads t ,
  14.     `information_schema`.INNODB_TRX trx ,
  15.     `performance_schema`.events_statements_history e 
  16. WHERE
  17.  t.thread_id = e.thread_id and 
  18.  t.PROCESSLIST_ID = trx.trx_mysql_thread_id
  19. group by t.THREAD_ID desc

这个语句需要root权限，没有的话可以使用下面这条语句。

  1. select * from information_schema.innodb_trx;

如果从INNODB_TRX只能查到一个会话，那就算运气很好了，肯定是这个造成的。
那么如果碰到多于一个的情况呢，可以通过PROCESSLIST_INFO字段里面的sql来判断出来是哪一个。但是有些情况下，持有metadata锁的会话是在sleep状态下的。也就是说造成持有这个锁的语句已经执行过了，但是由于没有提交或者回滚，导致会话还是持有着这个锁。如果碰到这种情况，PROCESSLIST_INFO字段就可能是空的了，那就只能通过判断会话已经执行过的语句来猜了。
当然猜也不是瞎猜，是有根据的猜。mysql有一个events_statements_history表，可以通过连接这个表来查看会话执行过什么语句。如果有涉及到等待锁的表的语句就能大概猜出来是哪一个了。比如下面这个结果

  1. +----------------+------------------+------------------+----------------+-------------------+---------------------+------------------+------------------+----------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------+
  2. | PROCESSLIST_ID | PROCESSLIST_USER | PROCESSLIST_HOST | PROCESSLIST_DB | PROCESSLIST_STATE | PROCESSLIST_COMMAND | PROCESSLIST_TIME | PROCESSLIST_INFO | CURRENT_SCHEMA | sql_text                                                                                                                                                     |
  3. +----------------+------------------+------------------+----------------+-------------------+---------------------+------------------+------------------+----------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------+
  4. |          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 |
  5. +----------------+------------------+------------------+----------------+-------------------+---------------------+------------------+------------------+----------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------+

最后的字段显示了这个会话执行过了什么语句。12268会话就执行过一个select，两个update等等语句。这很可能说明12268会话持有t表的metadata锁。一般来说杀了这个会话就可以解决问题了。
但是这个events_statements_history有一个限制，他不会存储所有执行过的语句，而是存储最新执行过的N个语句。这个N是由performance_schema_events_statements_history_size控制的，该参数是只读的，只能重启mysql生效，默认是10。如果执行过的语句超过了这个限制，那么很有可能是看不到对这个表进行修改的语句的,这个时候就需要猜了。

我是查询的时候发现一个已经占用两个多小时的事物一直没有提交，猜测就是这个事物引起的，于是kill掉他试试。

杀死异常占用事物

  1. Kill id(上面查询出来的trx_mysql_thread_id)

方法二 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.

在线开启方法

  1. UPDATE performance_schema.setup_instruments
  2. SET ENABLED = 'YES', TIMED = 'YES'
  3. WHERE NAME = 'wait/lock/metadata/sql/mdl';

在线关闭方法

  1. UPDATE performance_schema.setup_instruments
  2. SET ENABLED = 'NO', TIMED = 'NO'
  3. WHERE NAME = 'wait/lock/metadata/sql/mdl';

永久开启方法

在配置文件的mysqld区块里面添加

  1. performance-schema-instrument='wait/lock/metadata/sql/mdl=ON'

sql

  1. SELECT
  2.     t1.OBJECT_SCHEMA,
  3.      t1.OBJECT_NAME,
  4.     t1.LOCK_TYPE,
  5.     t1.LOCK_STATUS,
  6.     t2.PROCESSLIST_ID,
  7.     t2.PROCESSLIST_USER,
  8.     t2.PROCESSLIST_HOST,
  9.     t2.PROCESSLIST_DB,
  10.     t2.PROCESSLIST_COMMAND,
  11.     t2.PROCESSLIST_STATE,
  12.     t2.PROCESSLIST_INFO
  13. FROM
  14.     `performance_schema`.metadata_locks t1,
  15.     `performance_schema`.threads t2
  16. WHERE
  17.     t1.owner_thread_id = t2.thread_id
  18. AND t1.OBJECT_SCHEMA = 'test'
  19. AND t1.OBJECT_NAME = 't'

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

  1. +---------------+-------------+-------------------+-------------+----------------+------------------+------------------+----------------+---------------------+---------------------------------+--------------------------------+
  2. | OBJECT_SCHEMA | OBJECT_NAME | LOCK_TYPE         | LOCK_STATUS | PROCESSLIST_ID | PROCESSLIST_USER | PROCESSLIST_HOST | PROCESSLIST_DB | PROCESSLIST_COMMAND | PROCESSLIST_STATE               | PROCESSLIST_INFO               |
  3. +---------------+-------------+-------------------+-------------+----------------+------------------+------------------+----------------+---------------------+---------------------------------+--------------------------------+
  4. | test          | t           | SHARED_WRITE      | GRANTED     |          12268 | root             | localhost        | test           | Sleep               | NULL                            | NULL                           |
  5. | test          | t           | SHARED_READ       | GRANTED     |          12268 | root             | localhost        | test           | Sleep               | NULL                            | NULL                           |
  6. | test          | t           | SHARED_UPGRADABLE | GRANTED     |          12378 | root             | localhost        | test           | Query               | Waiting for table metadata lock | alter table t add column f int |
  7. | test          | t           | EXCLUSIVE         | PENDING     |          12378 | root             | localhost        | test           | Query               | Waiting for table metadata lock | alter table t add column f int |
  8. +---------------+-------------+-------------------+-------------+----------------+------------------+------------------+----------------+---------------------+---------------------------------+--------------------------------+

上面的结果中LOCK_STATUS字段表示连接对于matadata锁的持有状态，GRANTED表示持有，PENDING表示等待。很容易可以看出来12268会话持有了锁，而12378会话正在等待这个锁。通过kill 12268语句，或者让12268提交回滚都能解决这个问题。

出现场景

MySQL在进行alter table等DDL操作时，有时会出现Waiting for table metadata lock的等待场景。而且，一旦alter table TableA的操作停滞在Waiting for table metadata lock的状态，后续对TableA的任何操作（包括读）都无法进行，因为他们也会在Opening tables的阶段进入到Waiting for table metadata lock的锁等待队列。如果是产品环境的核心表出现了这样的锁等待队列，就会造成灾难性的后果。
造成alter table产生Waiting for table metadata lock的原因其实很简单，一般是以下几个简单的场景：

场景一 长事物运行，阻塞DDL，继而阻塞所有同表的后续操作

通过show processlist可以看到TableA上有正在进行的操作（包括读），此时alter table语句无法获取到metadata 独占锁，会进行等待。
这是最基本的一种情形，这个和mysql 5.6中的online ddl并不冲突。一般alter table的操作过程中（见下图），在after create步骤会获取metadata 独占锁，当进行到altering table的过程时（通常是最花时间的步骤），对该表的读写都可以正常进行，这就是online ddl的表现，并不会像之前在整个alter table过程中阻塞写入。（当然，也并不是所有类型的alter操作都能online的，具体可以参见官方手册：http://dev.mysql.com/doc/refman/5.6/en/innodb-create-index-overview.html）
处理方法： kill 掉 DDL所在的session.
image.png

场景二 未提交事物，阻塞DDL，继而阻塞所有同表的后续操作

通过show processlist看不到TableA上有任何操作，但实际上存在有未提交的事务，可以在 information_schema.innodb_trx中查看到。在事务没有完成之前，TableA上的锁不会释放，alter table同样获取不到metadata的独占锁。
处理方法：通过 select from *information_schema.innodb_trx\G, 找到未提交事物的sid, 然后 kill 掉，让其回滚。

场景三

通过show processlist看不到TableA上有任何操作，在information_schema.innodb_trx中也没有任何进行中的事务。这很可能是因为在一个显式的事务中，对TableA进行了一个失败的操作（比如查询了一个不存在的字段），这时事务没有开始，但是失败语句获取到的锁依然有效，没有释放。从performance_schema.events_statements_current表中可以查到失败的语句。

官方手册上对此的说明如下：

If the server acquires metadata locks for a statement that is syntactically valid but fails during execution, it does not release the locks early. Lock release is still deferred to the end of the transaction because the failed statement is written to the binary log and the locks protect log consistency.

也就是说除了语法错误，其他错误语句获取到的锁在这个事务提交或回滚之前，仍然不会释放掉。because the failed statement is written to the binary log and the locks protect log consistency 但是解释这一行为的原因很难理解，因为错误的语句根本不会被记录到二进制日志。
处理方法：通过performance_schema.events_statements_current找到其sid, kill 掉该session. 也可以 kill 掉DDL所在的session.

总之，alter table的语句是很危险的(其实他的危险其实是未提交事物或者长事务导致的)，在操作之前最好确认对要操作的表没有任何进行中的操作、没有未提交事务、也没有显式事务中的报错语句。如果有alter table的维护任务，在无人监管的时候运行，最好通过lock_wait_timeout设置好超时时间，避免长时间的metedata锁等待。

总结

如果你的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的出现次数了。