Waiting for table metadata lock

1 .为什么alter table 操作能引起数据库挂起？ 

2. 什么情况下select 能 “阻塞”select 操作？

3. 如何避免alter table 对线上环境的影响？

实验如下：

session1:模拟执行一个需要运行时间很长的Select操作.

mysql> select sleep(1000), now() from t2;

session2: 测试update 能正常执行。

mysql> update t2 set name='b';
Query OK, 7 rows affected (0.01 sec)
Rows matched: 7 Changed: 7 Warnings: 0

测试alter table 能否正常执行。执行alter table时，被卡住。

mysql> alter table t2 drop column sex1;
Query OK, 0 rows affected (1 min 41.09 sec)
Records: 0 Duplicates: 0 Warnings: 0

session3： select 操作也被卡住。 
mysql> select * from t2;

 

session4: 查看数据库在做什么
mysql> show processlist;
+----+------------+--------------------+------+-------------+-------+-----------------------------------------------------------------------+-----------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------------+--------------------+------+-------------+-------+-----------------------------------------------------------------------+-----------------------------------+
| 3 | binlogrepl | 192.168.1.20:35866 | NULL | Binlog Dump | 84677 | Master has sent all binlog to slave; waiting for binlog to be updated | NULL |
| 16 | root | localhost | d1 | Query | 86 | Waiting for table metadata lock | alter table t2 drop column sex1 |
| 18 | root | localhost | NULL | Query | 0 | init | show processlist |
| 19 | root | localhost | d1 | Query | 146 | User sleep | select sleep(1000), now() from t2 |
| 21 | root | localhost | d1 | Query | 7 | Waiting for table metadata lock | select * from t2 |
+----+------------+--------------------+------+-------------+-------+-----------------------------------------------------------------------+-----------------------------------+
5 rows in set (0.00 sec)

-- 我们看到有三个会话在对t2表做操作： ID为19的操作正在运行中。  ID为16,21 出现了Waiting for table metadata lock。

-- 杀掉ID 为19的会话后，可以看到ID 为16和21的会话立刻执行完成。

mysql> kill 19;
Query OK, 0 rows affected (0.00 sec)

mysql> show processlist;
+----+------------+--------------------+------+-------------+-------+-----------------------------------------------------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------------+--------------------+------+-------------+-------+-----------------------------------------------------------------------+------------------+
| 3 | binlogrepl | 192.168.1.20:35866 | NULL | Binlog Dump | 84693 | Master has sent all binlog to slave; waiting for binlog to be updated | NULL |
| 16 | root | localhost | d1 | Sleep | 102 | | NULL |
| 18 | root | localhost | NULL | Query | 0 | init | show processlist |
| 21 | root | localhost | d1 | Sleep | 23 | | NULL |
+----+------------+--------------------+------+-------------+-------+-----------------------------------------------------------------------+------------------+
4 rows in set (0.00 sec)

回答开始提出的三个问题：

1 .为什么alter table 操作能引起数据库挂起？ 

答：在有长时间未执行完成的SQL时，执行alter table 更新正在查询操作中的表，可能会引发Waiting for table metadata lock。

        这个会导致对这个表的所有操作都挂起。直到Waiting for table metadata lock被释放。这在生产环境上是一个很危险的操作。

2. 什么情况下select 能 “阻塞”select 操作？

答：当某个操作触发了Waiting for table metadata lock后，以后对相关表的select 操作都会被挂起。

3. 如何避免alter table 对线上环境的影响？

答： a.选择在业务低峰期执行alter table操作。

        b.使用pt-online-schema-change 工具做变更

        c.有些场景下可以考虑，优先在从库做变更，变更后做主从切换。

因此：在执行alter table操作时要用show processlist 查看数据库中是否有长时间未执行完的操作。避免Waiting for table metadata lock 导致的等待。

单纯的select之间是不会产生metadata lock的。如果有长时间的select未完成，此时有一个alter操作。
就会产生metadata lock，以后的select语句也会被阻塞。这就是alter 容易导致数据库挂起的原因之一。