mysql: Waiting for table metadata lock

最近遇到一个问题,老大在线上修改表结构的时候不成功,show processlist 发现大量的 Waiting for table metadata lock 线上大部分业务被阻塞,于是赶紧 kill 掉修改表结构的语句,调查原因。

在这之前我们需要先了解一下 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%';

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

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

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

select * from information_schema.innodb_trx \G;

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

现在我们开启另外一个会话,我们尝试修改 wy_dict2 的表名为 wy_dict:

ALTER TABLE `ff`.`wy_dict2` RENAME TO  `ff`.`wy_dict` ;

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

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

到这,我们就把线上的问题重现了。通过 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 操作呢?看下面的例子,我们查询了一个不存在的列

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

现在我们去执行重命名操作,发现还是出现 Waiting for table metadata lock 的问题,这是为什么呢?因为 information_schema.innodb_trx 中不会记录执行失败的事务,但是在这个执行失败的事务回滚前,它依然持有 metadata lock,所以 DDL 操作依然会被阻塞。这个时候我们可以通过查找 performance_schema.events_statements_current 表来找到相关的语句和会话信息,将其杀死。

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

最后我们简单总结一下:

  1. 长事务很危险,运行需谨慎。
  2. 即使你没有显示的开启事务只是执行简单的查询语句,你也需要关注 autocommit 的值
  3. 使用事务需要小心,记得 commit,捕获异常 rollback
  4. 做 DDL 操作前先检查一遍 innodb_trx,实在不行先把 DDL 操作干掉,再排查问题
  5. 设置合理的 lock_wait_timeout
发布于 2018-01-06