Waiting for table metadata lock问题处理

妖狐艹你老母 2023-02-16 00:57 14阅读 0赞

![format_png][]

在使用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,
        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锁的会话才会被记录。

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

--------------------

*  由叶老师主讲的知数堂「MySQL优化课」课程早已升级到MySQL 8.0版本了，现在上车刚刚好，扫码开启MySQL 8.0的修行之旅吧。
    
    ![format_png 1][]
    
    --------------------
    
    另外，叶老师在腾讯课堂《**MySQL性能优化**》精编版第一期已完结，本课程讲解读几个MySQL性能优化的核心要素：**合理利用索引，降低锁影响，提高事务并发度**。
    
    下面是自动拼团的二维码直接享受组团价
    
    ![format_png 2][]

[format_png]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9tbWJpei5xcGljLmNuL3N6X21tYml6X2pwZy83aDRpY0o0aWNzSkJqSnZ0TG9pY3lMRjcxaWN5MERQaWFLMDd5V2RpY0VvYjBuTkZvMUtMZnpGZklNNHVoQXJWMzVJNk42OEJEa3VkZGVJWXZuVElpYTkzVXloNlEvNjQw?x-oss-process=image/format,png
[format_png 1]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9tbWJpei5xcGljLmNuL21tYml6X2pwZy9udHM1Mm5IaGVUeEx5ajlRdWJEY0hQeUlwemgwaWNXSVpnTm96aFo3TmxDbW53MkU1ekpqUzZTTjE0TENaR0JvUW04WGliVHkzZDFtaWFqYWlhQVhTcTFTVVEvNjQw?x-oss-process=image/format,png
[format_png 2]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9tbWJpei5xcGljLmNuL21tYml6X3BuZy9udHM1Mm5IaGVUek5xdmliY3IwZmJDQkU3MXBoMzF5aWJ5TzNFOVJONnlLd0NYd1R6cVZmc1ZxM2RxUUFpYWRoUDlGSkVXOUluNlk0Mnl4ZTVUUWVuMm9BUS82NDA?x-oss-process=image/format,png