[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。

上一篇:wordpress修改域名


下一篇:QQ游戏的PKG格式文件解压工具