[MySQL 5.7 metadata lock] 测试

介绍：
Metadata：事物内的表级锁
5.5开始引入进来，5.6.6前，事物开启后，会锁定表的meta data lock，其他会话对表有DDL操作时，均需要等待DML释放后方可继续、5.6.6后，不再阻塞其他会话执行的DDL，但原来的会话再次访问数据表时，会有error提示：table definition has changed ， please retry transaction

是想要对一个表执行DDL时，会查看表有没有DML如果有则等待修改数据结构之前如何产生MDL锁，都会产生MDL 锁，meta data lock 锁，执行过程中避免修改，使用lock_wait_time 超时时间为1年，需要进行调小 

mysql@3306>[rds_test]>show variables like "%lock_wait_timeout%";
+--------------------------+----------+
| Variable_name            | Value    |
+--------------------------+----------+
| innodb_lock_wait_timeout | 50       |
| lock_wait_timeout        | 31536000 |
+--------------------------+----------+
2 rows in set (0.03 sec)

案例：
DDL操作被大查询block(阻塞)
当应用上线进入维护阶段，则开始会有较多的数据库变更操作，比如：添加字段，添加索引等操作，这一类操作导致锁故障也非常频繁，下面将会介绍一则案例，一个DDL操作被查询block，导致数据库连接堆积。

mysql@3306>[rds_test]>show processlist;
+-----------+--------------+---------------------+----------+---------+------+---------------------------------+----------------------------------------+
| Id        | User         | Host                | db       | Command | Time | State                           | Info                                   |
+-----------+--------------+---------------------+----------+---------+------+---------------------------------+----------------------------------------+
| 218380240 | dba_test     | 10.173.185.41:60754 | test_sql | Query   |   12 | Waiting for table metadata lock | update a set id=8888888 where id=1     |
| 219013568 | dba_test     | 10.173.185.41:60746 | test_sql | Query   |  746 | User sleep                      | select id , sleep(200) from a          |
| 268877248 | dba_test     | 10.173.185.41:60752 | test_sql | Query   |  688 | Waiting for table dba_test      | select * from a limit 1                |
| 269152246 |dba_test      | 10.173.185.41:60748 | test_sql | Query   |  742 | Waiting for table metadata lock | alter table a  add column gmt_c1  date |
| 269224487 | dba_test      | 10.173.185.41:60750 | rds_test | Query   |    0 | init                            | show processlist                       |
+-----------+--------------+---------------------+----------+---------+------+---------------------------------+----------------------------------------+
5 rows in set (0.02 sec)

metadata lock wait含义：为了在并发环境下维护表元数据的数据一致性，在表上有活动事物（显示或者隐式）的时候，不可以对元数据进行写入操作，因此MySQL引入了metadata lock，来饱和表的元数据信息。因此在对表进行上述操作时，如果表上有活动事物（未提交或回滚），请求写入的会话，会等待在metadata lock wait。

导致 Metadata lock wait 等待的常见因素包括：活动事务，当前有对表的长时间查询，显示或者隐式开启事务后未提交或回滚，比如查询完成后未提交或者回滚，表上有失败的查询事务等。

查询，更新和DDL操作的线程状态都为Waiting for table metadata lock，对表t的操作全部被阻塞，前端源源不断的请求到达数据库，这个时候数据库的连接很容易被打满，那我们来分析一下为什么有这这些锁等待：

1.alter 操作的锁等待：由于在表t上做了一个添加字段的操作，该操作会在结束前对表获取一个metadata lock，但是该表上已经有一个查询一直未结束，导致metadata lock一直获取不到，所以添加字段操作只能等待查询结束，这就解释了alter操作为什么状态为Waiting for table metadata lock。
2.查询和更新的锁等待：由于前面进行的alter操作已经在a表上试图获取metadata lock，所以后续对表a的的查询和更新操作在获取metadata lock的时候会被alter操作所阻塞，进而导致这些线程状态为Waiting for table metadata lock。