MySQL Waiting for table metadata lock故障分析(2)

场景三：显示或者隐式开启事务后未提交或回滚,比如查询完成后未提交或者回滚,使用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: 212721717099954000
              TIMER_END: 212721717213807000
             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解决可以解决此问题.

（编辑：ASP站长网）