MySQL Waiting for table metadata lock故障分析

场景三：显示或者隐式开启事务后未提交或回滚,比如查询完成后未提交或者回滚,使用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动作被阻塞.

（编辑：网站开发网_盐城站长网 ）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!