等待表元数据锁定的MySQL故障分析

场景一：当前有执行DML操作时执行ALTRE操作。# SESSION A mysql> insert into sbtest2 select * from sbtest1; # SESSION B mysql> alter table sbtest2 add test1 int;   //等待SESSION A执行完; # SESSION C mysql> show processlist; +-----+------+-----------+--------+---------+------+---------------------------------+-------------------------------------------+ | Id  | User | Host      | db     | Command | Time | State                           | Info                                      | +-----+------+-----------+--------+---------+------+---------------------------------+-------------------------------------------+ | 267 | root | localhost | sbtest | Query   |    7 | Sending data                    | insert into sbtest2 select * from sbtest1 | | 271 | root | localhost | sbtest | Query   |    3 | Waiting for table metadata lock | alter table sbtest2 add test1 int         | | 272 | root | localhost | NULL   | Query   |    0 | starting                        | show processlist                          | +-----+------+-----------+--------+---------+------+---------------------------------+-------------------------------------------+ 3 rows in set (0.00 sec) # SESSION D mysql> select * from sbtest2 limit 10;    //等待元数据锁; # SESSION E mysql> show processlist; +-----+------+-----------+--------+---------+------+---------------------------------+-------------------------------------------+ | Id  | User | Host      | db     | Command | Time | State                           | Info                                      | +-----+------+-----------+--------+---------+------+---------------------------------+-------------------------------------------+ | 267 | root | localhost | sbtest | Query   |   20 | Sending data                    | insert into sbtest2 select * from sbtest1 | | 271 | root | localhost | sbtest | Query   |   13 | Waiting for table metadata lock | alter table sbtest2 add test1 int         | | 272 | root | localhost | NULL   | Query   |    0 | starting                        | show processlist                          | | 308 | root | localhost | sbtest | Query   |    3 | Waiting for table metadata lock | select * from sbtest2 limit 10            | +-----+------+-----------+--------+---------+------+---------------------------------+-------------------------------------------+ 4 rows in set (0.00 sec)从上述例子可以看出，我们在执行DDL语句的时候得事先看一下，进程中是否已经存在某些DML语句占用了表的元数据锁，这样会导致DDL语句处于锁等待状态。一旦出现Waiting for table metadata lock等待现象，后续所有对该表的访问都会阻塞在该等待上，包括读操作，导致连接堆积，业务受影响。

场景二：当前有对表的长时间查询或使用mysqldump/mysqlpump时，使用alter会被堵住。# SESSION A mysql> select *,sleep(10) from sbtest2; # SESSION B mysql> alter table sbtest2 add test2 int;   //等待SESSION A执行完; # SESSION C mysql> show processlist; +-----+------+-----------+--------+---------+------+---------------------------------+---------------------------------------+ | Id  | User | Host      | db     | Command | Time | State                           | Info                                  | +-----+------+-----------+--------+---------+------+---------------------------------+---------------------------------------+ | 267 | root | localhost | sbtest | Query   |   12 | User sleep                      | select *,sleep(10) from sbtest2       | | 271 | root | localhost | sbtest | Query   |    8 | Waiting for table metadata lock | alter table sbtest2 add test3 int     | | 272 | root | localhost | NULL   | Query   |    0 | starting                        | show processlist                      | | 311 | root | localhost | NULL   | Query   |    3 | Waiting for table metadata lock | select * from sbtest.sbtest2 limit 10 | +-----+------+-----------+--------+---------+------+---------------------------------+---------------------------------------+ 4 rows in set (0.00 sec)

场景三：显示或者隐式开启事务后未提交或回滚，比如查询完成后未提交或者回滚，使用alter会被堵住。# SESSION A mysql> begin; mysql> select * from sbtest2; # SESSION B mysql> alter table sbtest2 add test2 int;   //等待SESSION A执行完; # SESSION C mysql> show processlist; +-----+------+-----------+--------+---------+------+---------------------------------+-----------------------------------+ | Id  | User | Host      | db     | Command | Time | State                           | Info                              | +-----+------+-----------+--------+---------+------+---------------------------------+-----------------------------------+ | 267 | root | localhost | sbtest | Sleep   |   36 |                                 | NULL                              | | 271 | root | localhost | sbtest | Query   |   30 | Waiting for table metadata lock | alter table sbtest2 add test2 int | | 272 | root | localhost | NULL   | Query   |    0 | starting                        | show processlist                  | +-----+------+-----------+--------+---------+------+---------------------------------+-----------------------------------+ 3 rows in set (0.00 sec)

场景四：表上有失败的查询事务，比如查询不存在的列，语句失败返回，但是事务没有提交，此时alter仍然会被堵住。# SESSION A mysql> begin; mysql> select error from sbtest2; ERROR 1054 (42S22): Unknown column 'error' in 'field list' # SESSION B mysql> alter table sbtest2 add test3 int;    //等待SESSION A提交或回滚; # SESSION C mysql> show processlist; +-----+------+-----------+--------+---------+------+---------------------------------+-----------------------------------+ | Id  | User | Host      | db     | Command | Time | State                           | Info                              | +-----+------+-----------+--------+---------+------+---------------------------------+-----------------------------------+ | 267 | root | localhost | sbtest | Sleep   |    7 |                                 | NULL                              | | 271 | root | localhost | sbtest | Query   |    3 | Waiting for table metadata lock | alter table sbtest2 add test3 int | | 272 | root | localhost | NULL   | Query   |    0 | starting                        | show processlist                  | | 311 | root | localhost | NULL   | Sleep   |  413 |                                 | NULL                              | +-----+------+-----------+--------+---------+------+---------------------------------+-----------------------------------+ 4 rows in set (0.00 sec) # SESSION D mysql> select * from information_schema.innodb_trx; Empty set (0.00 sec)其实SESSION A中的事务并未开启，但是由于select获取表元数据的语句，语法上是有效的，虽然执行失败了，但是任然不会释放元数据锁，故而导致SESSION B的alter动作被阻塞。通过SESSION D查看当前打开事务时，你会发现没有，从而找不到原因。所以当出现这种场景时，如何判断是哪个进程导致的呢，我们可以尝试查看表performance_schema. events_statements_current，分析进程状态来进行判断。mysql> select * from performance_schema. events_statements_current/G *************************** 1. row ***************************              THREAD_ID: 293               EVENT_ID: 32           END_EVENT_ID: 32             EVENT_NAME: statement/sql/select                 SOURCE: socket_connection.cc:101            TIMER_START: 2954000              TIMER_END: 2807000             TIMER_WAIT: 113853000              LOCK_TIME: 0               SQL_TEXT: select error from sbtest2                 DIGEST: 0bbb2d5d1be45e77debea68111264885            DIGEST_TEXT: SELECT ERROR FROM `sbtest2`         CURRENT_SCHEMA: sbtest            OBJECT_TYPE: NULL          OBJECT_SCHEMA: NULL            OBJECT_NAME: NULL  OBJECT_INSTANCE_BEGIN: NULL            MYSQL_ERRNO: 1054      RETURNED_SQLSTATE: 42S22           MESSAGE_TEXT: Unknown column 'error' in 'field list'                 ERRORS: 1然后找到其sid, kill掉该session，也可以kill掉DDL所在的session解决可以解决此问题。另外，测试时SESSION A要显式开启一个事务，否则查询会隐式回滚结束，无法重现上面的场景。SESSION B执行alter后，没有立即阻塞住，而是立马开始copy to tmp table，这个过程结束后，才进行了MDL锁等待。这怎么解释呢，应该是执行alter操作主要分为创建临时新表->插入老表的数据->临时新表rename to老表三个步骤，在这种情况下，到最后一步才需要MDL锁，所以copy过程中不会阻塞。由于没有查询在进行，而且查询也没有进入innodb层 (失败返回)，所以show processlist和information_schema.innodb_trx没有可以参考的信息。