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

另外,测试时SESSION A要显式开启一个事务,否则查询会隐式回滚结束,无法重现上面的场景.SESSION B执行alter后,没有立即阻塞住,而是立马开始copy to tmp table,这个过程结束后,才进行了MDL锁等待.这怎么解释呢,应该是执行alter操作主要分为创建临时新表->插入老表的数据->临时新表rename to老表三个步骤,在这种情况下,到最后一步才需要MDL锁,所以copy过程中不会阻塞.由于没有查询在进行,而且查询也没有进入innodb层 (失败返回),所以show processlist和information_schema.innodb_trx没有可以参考的信息.

出现以上几种情况时,这个时候如果进行如下操作就会引起MDL：

• 创建、删除索引.
• 修改表结构.
• 表维护操作(optimize table、repair table等).
• 删除表.
• 获取表上表级写锁 (lock table tab_name write).

使用Profile分析场景三：显示或者隐式开启事务后未提交或回滚,使用alter会被堵住

# SESSION A
mysql> set profiling=on;
mysql> begin;
mysql> select * from sbtest.sbtest2 limit 1;

# SESSION B
mysql> set profiling=on;
mysql> alter table sbtest.sbtest5 add test2 int;   //等待SESSION A执行完;

# SESSION C
mysql> set profiling=on;
mysql> select * from sbtest.sbtest2 limit 1;

# SESSION D
mysql> set profiling=on;
mysql> show processlist;
+-----+------+-----------+------+---------+------+---------------------------------+------------------------------------------+
| Id  | User | Host      | db   | Command | Time | State                           | Info                                     |
+-----+------+-----------+------+---------+------+---------------------------------+------------------------------------------+
| 325 | root | localhost | NULL | Query   |   25 | Waiting for table metadata lock | alter table sbtest.sbtest2 add test5 int |
| 326 | root | localhost | NULL | Query   |    3 | Waiting for table metadata lock | select * from sbtest.sbtest2 limit 1     |
| 327 | root | localhost | NULL | Query   |    0 | starting                        | show processlist                         |
| 328 | root | localhost | NULL | Sleep   |   50 |                                 | NULL                                     |
+-----+------+-----------+------+---------+------+---------------------------------+------------------------------------------+
4 rows in set (0.00 sec)

然后回滚SESSION A,等待SESSION B和SESSION C执行完,查看profile.

查看SESSION A

# SESSION A
mysql> show profiles;
+----------+-------------+------------------------------------------+
| Query_ID | Duration    | Query                                    |
+----------+-------------+------------------------------------------+
|        1 | 42.81646375 | alter table sbtest.sbtest2 add test5 int |
+----------+-------------+------------------------------------------+
1 row in set,1 warning (0.00 sec)

mysql> show profile for query 1;
+--------------------------------+----------+
| Status                         | Duration |
+--------------------------------+----------+
| starting                       | 0.000069 |
| checking permissions           | 0.000004 |
| checking permissions           | 0.000004 |
| init                           | 0.000005 |
| Opening tables                 | 0.000128 |
| setup                          | 0.000032 |
| creating table                 | 0.000654 |
| After create                   | 0.000053 |
| Waiting for table metadata loc | 1.000069 |
.....................
| After create                   | 0.000013 |
| Waiting for table metadata loc | 0.871435 |
| After create                   | 0.000042 |
| System lock                    | 0.000013 |
| preparing for alter table      | 0.002475 |
| altering table                 | 9.752928 |
| committing alter table to stor | 0.185624 |
| end                            | 0.000021 |
| query end                      | 0.000010 |
| closing tables                 | 0.000007 |
| freeing items                  | 0.000020 |
| cleaning up                    | 0.000011 |
+--------------------------------+----------+
83 rows in set,1 warning (0.00 sec)

查看SESSION C

mysql> show profiles;
+----------+-------------+--------------------------------------+
| Query_ID | Duration    | Query                                |
+----------+-------------+--------------------------------------+
|        1 | 10.75216050 | select * from sbtest.sbtest2 limit 1 |
+----------+-------------+--------------------------------------+
1 row in set,1 warning (0.00 sec)

mysql> show profile for query 1;
+--------------------------------+-----------+
| Status                         | Duration  |
+--------------------------------+-----------+
| starting                       |  0.000080 |
| checking permissions           |  0.000007 |
| Opening tables                 |  0.000012 |
| Waiting for table metadata loc | 10.751829 |
| Opening tables                 |  0.000094 |
| init                           |  0.000019 |
| System lock                    |  0.000010 |
| optimizing                     |  0.000004 |
| statistics                     |  0.000011 |
| preparing                      |  0.000009 |
| executing                      |  0.000003 |
| Sending data                   |  0.000040 |
| end                            |  0.000006 |
| query end                      |  0.000008 |
| closing tables                 |  0.000008 |
| freeing items                  |  0.000014 |
| cleaning up                    |  0.000009 |
+--------------------------------+-----------+
17 rows in set,1 warning (0.00 sec)

从上述测试可以看出,SESSION C需要打开表时碰到了元数据锁.MySQL不论SESSION A执行的是select还是delete,此时alter table语句无法获取到metadata独占锁,会进行等待；所以会影响SESSION C的读取.

（编辑：ASP站长网）