Waiting for table metadata lock问题处理

👋 相关阅读

• MySQL 正则替换数据：REGEXP_REPLACE 函数
• Mysql 窗口函数学习
• mysql 临时表空间，ibtmp1 表空间暴增解决办法
• 一份非常完整、详细的MySQL规范
• mysql 已有大数据量表进行分区踩坑

在使用 mysql 的时候，我们有时会碰到 Waiting for table metadata lock 的锁等待。但是这个锁等待比较特殊，在 innodb_lock_wait 和 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

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
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

如果从 INNODB_TRX 只能查到一个会话，那就算运气很好了，肯定是这个造成的。

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

当然猜也不是瞎猜，是有根据的猜。mysql 有一个 events_statements_history 表，可以通过连接这个表来查看会话执行过什么语句。如果有涉及到等待锁的表的语句就能大概猜出来是哪一个了。比如下面这个结果

最后的字段显示了这个会话执行过了什么语句。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 锁的会话才会被记录。

上面的结果中 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 的出现次数了。

via: Waiting for table metadata lock问题处理 - 简书 https://www.jianshu.com/p/a96d02d7944d

×