Waiting for table metadata lock

一、了解一下 metadata lock

metadata lock 是为了保护 database objects （包括 表结构、存储过程、触发器等）而设计的。那 metadata lock 和事务有什么关系呢？在事务中，当它需要访问一个 database object 时都需要先获得其 metadata lock，在事务结束后才会释放 metadata lock。这样做有几个目的：

第一是为了进一步保证事务的一致性。

比如我在事务 A 中对某一行记录进行了更新，我的事务现在还没有提交，但是这个时候另外一个会话2要修改表名，如果事务 A 持有了 metadata lock，那么这时候另一个会话2将无法修改，show processlist 会发现它在 Waiting for table metadata lock 。直到事务 A 提交或回滚后，才能获得 metadata lock 修改成功。如果我们没有 metadata lock 的机制，那么会话2 就可以直接修改表名，这样当事务 A 由于其它原因需要回滚的时候，就回滚不了，因为表名被修改了，这样导致数据不一致。

第二是为了解决 binlog 同步的一个 bug，这个和上面的原因一样。

binlog 的操作是基于事务的提交顺序的。事务 A 还未提交，另一个会话删除了相关表，这样 binlog 先记录的是删除表的操作，从库执行的顺序就不对了。

有了这些基础知识后，我们就知道出现 Waiting for table metadata lock 这种情况多半是和事务有关，要么是一个长事务在运行，要么是事务没有提交造成的。下面我们在本地重现一下这个问题：

二、实验

MySQL 执行任何 DML 语句都是按事务来执行的，我们之所以不需要显示的提交或回滚是因为 MySQL 设置了默认全局自动提交，通过下面的命令查看 autocommit 的值

show global variables like '%autocommit%';

+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | ON    |
+---------------+-------+
1 row in set (0.00 sec)

为了测试方便，我们将当前会话的 autocommit 设置为 false；

set autocommit = 0;
show variables like '%autocommit%';

+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | OFF   |
+---------------+-------+

环境搭建好后，我们开始重现这个问题：首先我们查询tb_user_tmp这张表，由于我们将当前会话的 autocommit 设置为 false，查询完后我们先不 commit。执行前我们先看看当前系统有没有事务在运行：

select * from information_schema.innodb_trx \G;

Empty set (0.02 sec)

select * from tb_user_tmp limit 0,1;

......

执行后我们看到我们这个事务正在运行：

select * from information_schema.innodb_trx \G;

*************************** 1. row ***************************
                    trx_id: 421624796451504
                 trx_state: RUNNING
               trx_started: 2021-09-18 16:24:32
     trx_requested_lock_id: NULL
          trx_wait_started: NULL
                trx_weight: 0
       trx_mysql_thread_id: 692663
                 trx_query: NULL
       trx_operation_state: NULL
         trx_tables_in_use: 0
         trx_tables_locked: 0
          trx_lock_structs: 0
     trx_lock_memory_bytes: 1136
           trx_rows_locked: 0
         trx_rows_modified: 0
   trx_concurrency_tickets: 0
       trx_isolation_level: REPEATABLE READ
         trx_unique_checks: 1
    trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
 trx_adaptive_hash_latched: 0
 trx_adaptive_hash_timeout: 0
          trx_is_read_only: 0
trx_autocommit_non_locking: 0
1 row in set (0.00 sec)

现在我们开启另外一个会话，我们尝试修改 tb_user_tmp 的表名为tb_user_tmp_2：

ALTER TABLE `ucs`.`tb_user_tmp` RENAME TO  `ucs`.`tb_user_tmp_2` ;

这时我们发现我们一直被阻塞，通过 show processlist 我们发现，我们在等待 metadata locak： waiting for table metadata lock



| 713819 | root | localhost| NULL| Query|4 | Waiting for table metadata lock | ALTER TABLE `ucs`.`tb_user_tmp` RENAME TO  `ucs`.`tb_user_tmp_2` |

这个时候有非常严重的问题，当我们其它的会话都需要查询这张表时它们都被阻塞了：

select * from tb_user_tmp limit 0,1;

被阻塞

show processlist;

| 713819 | root | localhost| NULL | Query|311 | Waiting for table metadata lock | ALTER TABLE `ucs`.`tb_user_tmp` RENAME TO  `ucs`.`tb_user_tmp_2` |
| 713864 | root | localhost | ucs | Query|40 | Waiting for table metadata lock | select * from tb_user_tmp limit 0,1|

到这，我们就把线上的问题重现了。通过 information_schema.innodb_trx 中的 trx_mysql_thread_id 的值知道了正在运行的事务的线程 id，然后在 show processlist 中通过查找对应 id 的 User, Host, db 信息定位到是哪个程序引起的。把这个线程 kill 掉就恢复正常了。最后我们去分析这个程序发现这个程序没有显示的启用事务，都是一些简单的查询语句，进一步分析发现是 pymysql 链接 MySQL 的时候默认 autocommit 设置为 0，而这个程序没有单独设置这个值，所以就出现这样的结果。

这里还有一个问题如果我们不做 DDL 操作，那其它的会话会被阻塞吗？测试一下我们修改表名，发现其它的会话不会被阻塞，这是为什么呢？因为多个事务可以同时持有同一个 database object 上的 metadata 锁，DML 语句只是修改表数据而不会修改表结构所以同时持有同一个 database object 上的 metadata 锁并不会有什么问题，还可以提高并发。那问题又来了，为什么在这些 DML 语句之前执行了一个 DDL 操作就会阻塞其后面的 DML 语句呢？网上有同学推测，对 metadata 锁的获取维护了一个先进先出的队列，这样可以避免 DDL 操作一直获取不到 metadata 锁的问题。

到这里我们是不是可以说如果在 information_schema.innodb_trx 中没有看到长时间运行的事务，则我们就可以放心的执行 DDL 操作呢？看下面的例子，我们查询了一个不存在的列

MySQL [ucs]> select test from tb_user_tmp limit 0,1;

ERROR 1054 (42S22): Unknown column 'test' in 'field list'

然后我们再去查看 information_schema.innodb_trx 中没有数据，

MySQL [(none)]> select * from information_schema.innodb_trx \G;

Empty set (0.00 sec)

现在我们去执行重命名操作，发现还是出现 Waiting for table metadata lock 的问题，这是为什么呢？

MySQL [(none)]> show processlist;


| 713819 | root | localhost| NULL| Query| 19 | Waiting for table metadata lock | ALTER TABLE `ucs`.`tb_user_tmp` RENAME TO  `ucs`.`tb_user_tmp_2` |

因为 information_schema.innodb_trx 中不会记录执行失败的事务，但是在这个执行失败的事务回滚前，它依然持有 metadata lock，所以 DDL 操作依然会被阻塞。这个时候我们可以通过查找 performance_schema.events_statements_current 表来找到相关的语句和会话信息，将其杀死。

MySQL [(none)]> select * from performance_schema.events_statements_current \G;

SQL_TEXT: select test from tb_user_tmp limit 0,1
DIGEST: 95702e43cf29493b3aa1a091c79bc802
DIGEST_TEXT: SELECT `test` FROM `tb_user_tmp` LIMIT ?, ...
......
MYSQL_ERRNO: 1054
RETURNED_SQLSTATE: 42S22
MESSAGE_TEXT: Unknown column 'test' in 'field list'
......

为了减少 metadata lock 带来的危害，设置一个合理的 lock_wait_timeout 比较重要，这个值默认是 365 天，我们可以根据自身业务来考虑，避免长时间的 metadata lock 等待。

最后我们简单总结一下：

1、长事务很危险，运行需谨慎。

2、即使你没有显示的开启事务只是执行简单的查询语句，你也需要关注 autocommit 的值

3、使用事务需要小心，记得 commit，捕获异常 rollback

4、做 DDL 操作前先检查一遍 innodb_trx，实在不行先把 DDL 操作干掉，再排查问题

5、设置合理的 lock_wait_timeout