记一次MySQL中Waiting for table metadata lock问题的处理

起因：由于需要，要把一张表的一个字段从不是 null 改成 可null，我用的Navicat Premium ，但是在保存的时候，工具无响应了，几个同事操作都是这样的，很奇怪，怀疑是不是由于表被锁了还是什么原因

 

去数据库看下：

show processlist;

发现有一个线程很奇怪：这个进程的State是Waiting for table metadata lock ，Info是alter table bus_order modify...  并且Time已经800多秒了

能确定这就是刚才修改字段属性的保存操作，但是为什么卡死了呢，Google了下Waiting for table metadata lock发现原因了：

原来是有操作这张表的未提交事务，在事务没有完成之前，这个表上的锁不会释放，alter table同样获取不到metadata的独占锁

通过下面两个语句，定位到操作该表的未提交事务：

select t.trx_mysql_thread_id from information_schema.innodb_trx t；
show processlist;

然后kill掉这个线程，再保存，立刻保存成功了。

结论&拓展：

• 情景1：一个较耗时的事务运行的时候，这时进行alter table，就会阻塞DDL，继而阻塞所有当前表的后续操作
  

  通过show processlist可以看到所操作表上有正在进行的操作（包括读），此时alter table语句无法获取到metadata 独占锁，会进行等待。

  这是最基本的一种情形，这个和mysql 5.6中的online DLL并不冲突。一般alter table的操作过程中，在after create步骤会获取metadata 独占锁，当进行到altering table的过程时（通常是最花时间的步骤），对该表的读写都可以正常进行，这就是online ddl的表现，并不会像之前在整个alter table过程中阻塞写入。（当然，也并不是所有类型的alter操作都能online的，具体可以参见官方手册：http://dev.mysql.com/doc/refman/5.6/en/innodb-create-index-overview.html）
  处理方法： kill 掉 DDL所在的session.

• 情景2：存在未提交事务，阻塞DDL，继而阻塞所有同表的后续操作
  通过show processlist看不到TableA上有任何操作，但实际上存在有未提交的事务，可以通过下面的命令查看未提交的事务：
  

  select t.trx_mysql_thread_id from information_schema.innodb_trx t

  在事务没有完成之前，Table上的锁不会释放，alter table同样获取不到metadata的独占锁

  处理方法：通过上面的命令找到未提交的事务，然后 kill 掉，让其回滚。
• 场景3：
  

  通过show processlist看不到TableA上有任何操作，在information_schema.innodb_trx中也没有任何进行中的事务。这很可能是因为在一个显式的事务中，对TableA进行了一个失败的操作（比如查询了一个不存在的字段），这时事务没有开始，但是失败语句获取到的锁依然有效，没有释放。从performance_schema.events_statements_current表中可以查到失败的语句。

  官方手册上对此的说明如下：

  If the server acquires metadata locks for a statement that is syntactically valid but fails during execution, it does not release the locks early. Lock release is still deferred to the end of the transaction because the failed statement is written to the binary log and the locks protect log consistency.

  也就是说除了语法错误，其他错误语句获取到的锁在这个事务提交或回滚之前，仍然不会释放掉。because the failed statement is written to the binary log and the locks protect log consistency 但是解释这一行为的原因很难理解，因为错误的语句根本不会被记录到二进制日志。

  处理方法：通过performance_schema.events_statements_current找到其sid, kill 掉该session. 也可以 kill 掉DDL所在的session.
  
  

总之，alter table的语句是很危险的(其实他的危险其实是未提交事物或者长事务导致的)，在操作之前最好确认对要操作的表没有任何进行中的操作、没有未提交事务、也没有显式事务中的报错语句。如果有alter table的维护任务，在无人监管的时候运行，最好通过lock_wait_timeout设置好超时时间，避免长时间的metedata锁等待。