mysql: Waiting for table metadata lock的问题排查

在数据库中修改表结构的时候不成功，然后show processlist(select * from information_schema.processlist where Command != 'Sleep' order by Time desc;)发现大量的 Waiting for table metadata lock信息。（注：在这之前，我使用kill id的方法(就是show processlist的id列),并不能解决问题。通过重启数据库......也不能解决问题）

在mysql中，metadata lock 是为了保护 database objects （包括 表结构、存储过程、触发器等）而设计的。在事务中，当它需要访问一个 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 由于其它原因需要回滚的时候，就回滚不了，因为表名被修改了，这样导致数据不一致。

所以推测Waiting for table metadata lock应该跟事务有关系，要么是一个长事务在运行，要么是事务没有提交造成的。

接下来复现问题：

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

show variables like '%autocommit%';

为了模拟长事务，我们首先将 autocommit 设置为 false。

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

步骤：

1、首先开启一个会话：

创建一个表：create table test(id int,name varchar(20));

执行查询语句：select * from test;

（执行之前可以先执行下show processlist命令看看当前系统有没有事务在运行，执行select后，再执行show processlist看看。）

2、另开启一个会话：

执行ddl语句：alter table test drop column name;

发现hang住了。

然后show processlist，发现了 metadata lock： waiting for table metadata lock的记录。

通过以上试验，可以大概推测线上出现meta lock的原因应该是某个程序没有显示的启用事务导致的。通过 information_schema.innodb_trx 中的 trx_mysql_thread_id 的值知道了正在运行的事务的线程 id，然后在 show processlist 中通过查找对应 id 的 User, Host, db 信息定位到是哪个程序引起的。把这个线程 kill 掉就恢复正常了。（或者直接kill那个程序也可以，之前通过kill掉show processlist结果行id的方式没有凑效的原因是该程序在运行过程中，不断会有新的连接产生所以无法解决问题。）

通过进一步分析发现是 pymysql 链接 MySQL 的时候默认 autocommit 设置为 0，而这个程序没有单独设置这个值，所以就出现这样的结果。

最后总结下：使用事务需要小心，记得 commit，同时需要关注autocommit的值。我们在执行DDL操作时，可以先检查一遍innodb_trx再执行。另外设置合理的lock_wait_timeout。

参考文档：mysql: Waiting for table metadata lock