【Mysql】metadatalock锁

原文地址：http://blog.itpub.net/26250550/viewspace-1071987/
metadata lock的超时时间是lock_wait_timeout，并不是innodb_lock_wait_timeout

MySQL 5.5.3版本中引入了Metadata lock: DDL语句打破了事务的隔离级别
    在5.5.3版本之前,MySQL事务对于表结构元数据(Metadata)的锁定是语句(statement)粒度的，即语句执行完成后，不管事务是否可以完成，其表结构就可以被其他会话更新掉！
    引入Metadata lock后，表结构元数据(Metadata)的锁定变成了事务(transaction)粒度的，即只有事务结束时才会释放Metadata lock。

    现象：
        显式开启事务后start transaction; 该事务内的query语句(包含select)会占用相关表的metadata lock(profile:Opening tables阶段)
        导致DDL语句被阻塞，因为获取不到表的metadata lock

MySQL 5.6.6版本后优化
metadata lock不阻塞DDL语句，但原有session再访问此表时会返回Error信息“Table definition has changed, please retry transaction”

案列

目前创新互联建站已为上千余家的企业提供了网站建设、域名、网站空间、网站托管、企业网站设计、青神网站维护等服务，公司将坚持客户导向、应用为本的策略，正道将秉承"和谐、参与、激情"的文化，与客户和合作伙伴齐心协力一起成长，共同发展。

session1
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from test1 where id=1;
+------+------+
| id | name |
+------+------+
| 1 | 1 |
+------+------+
1 row in set (0.00 sec)
session2
1. mysql> set profiling=on;
2. Query OK, 0 rows affected (0.00 sec)
3. mysql> alter table test1 add column c2 int;
4. ---堵塞
6. mysql> show full processlist;
  +----+------+-----------+------+---------+------+---------------------------------+-------------------------------------+
  | Id | User | Host | db | Command | Time | State | Info |
  +----+------+-----------+------+---------+------+---------------------------------+-------------------------------------+
  | 4 | root | localhost | test | Sleep | 10 | | NULL |
  | 5 | root | localhost | test | Query | 6 | Waiting for table metadata lock | alter table test1 add column c2 int |
  | 6 | root | localhost | NULL | Query | 0 | NULL | show full processlist |
  +----+------+-----------+------+---------+------+---------------------------------+-------------------------------------+
  
  我们可以看到ALTER TABLE会应Metadata lock而阻塞。一旦Session 1中的事务提交或者回滚，即释放了table1表的Metadata lock，Session2的操作立即可以执行
7. ctrl+c掉后我们查看profile执行计划
9. mysql> show profile for query 6;
  +----------------------+----------+
  | Status | Duration |
  +----------------------+----------+
  | starting | 0.000045 |
  | checking permissions | 0.000005 |
  | checking permissions | 0.000005 |
  | init | 0.000007 |
  | Opening tables | 0.000061 |
  | System lock | 0.000008 |
  | setup | 0.000018 |
  | creating table | 0.002776 |
  | After create | 0.000063 |
  | copy to tmp table | 0.000312 |
  | rename result table | 1.459601 |
  | query end | 0.000031 |
  | closing tables | 0.000285 |
  | freeing items | 0.002271 |
  | cleaning up | 0.000073 |
  +----------------------+----------+
  15 rows in set (0.00 sec)
  
  可以看到，卡住的那一步是在rename result table，MySQL后台将ALTER保存变成连续操作“创建临时新表->插入老表的数据->临时新表取到老表(RENAME)”，即实际的变化老表的操作在RENAME阶段才发生。而这一步即是被阻塞的那一步。所以可以确认，Metadata lock影响到的是要真实修改表结构的动作。（回想：和pt-osc那节，一个select未执行完，alter 修改表结构产生metadata lock一个道理）
  
  基于此，我们可以验证ALTER TABLE,RENAME TABLE,DROP TABLE都会被Metadata lock影响，验证步骤与上类似，省略。
  
  值得注意的是，创建一个已存在的表也会被Metadata lock影响。如果在一个事务中用到了表A的Metadata lock（如SELECT了该表）且事务未完成，那么创建同名的表也会被卡住。所参考的文章中提及这一点。有兴趣可参看：讨论页，这里不作讨论。
  
  metadata lock的另一个副作用：如上例中，ALTER TABLE应metadata lock被阻塞后，这导致后续其他事务针对该表的SELECT也会被阻塞！即ALTER TABLE的操作会影响到其他SELECT操作。根据上述示例：
  
  Session2 mysql> alter table table1 add column c1 int;
  
  此步一直处于等待状态时，新会话中查询table1。
  
  Session3 mysql> select * from test1;  #一直等待
  
  Session4 mysql>show full processlist;
  mysql> show full processlist;
  +----+------+-----------+------+---------+------+---------------------------------+-------------------------------------+
  | Id | User | Host | db | Command | Time | State | Info |
  +----+------+-----------+------+---------+------+---------------------------------+-------------------------------------+
  | 4 | root | localhost | test | Sleep | 1857 | | NULL |
  | 5 | root | localhost | test | Query | 504 | Waiting for table metadata lock | select * from test1 |
  | 6 | root | localhost | NULL | Query | 0 | NULL | show full processlist |
  | 8 | root | localhost | test | Query | 511 | Waiting for table metadata lock | alter table test1 add column c2 int |
  +----+------+-----------+------+---------+------+---------------------------------+-------------------------------------+
  
  Session1 mysql> commit;
  
  第一个会话提交后，Session2、Session3也成功执行。看看Session3的select在哪个步骤需要等待：
  
  Session3 mysql> show profile;
  +——————————–+———–+
  | Status                         | Duration |
  +——————————–+———–+
  | starting                       | 0.000018 |
  | Waiting for query cache lock   | 0.000003 |
  | checking query cache for query | 0.000037 |
  | checking permissions           | 0.000007 |
  | Opening tables                 | 22.502591 |
  | System lock                    | 0.000013 |
  | Waiting for query cache lock   | 0.000024 |
  | init                           | 0.000022 |
  | optimizing                     | 0.000009 |
  | statistics                     | 0.000011 |
  | preparing                      | 0.000015 |
  | executing                      | 0.000003 |
  | Sending data                   | 0.000036 |
  | end                            | 0.000009 |
  | query end                      | 0.000005 |
  | closing tables                 | 0.000006 |
  | freeing items                  | 0.000007 |
  | Waiting for query cache lock   | 0.000003 |
  | freeing items                  | 0.000015 |
  | Waiting for query cache lock   | 0.000002 |
  | freeing items                  | 0.000002 |
  | storing result in query cache | 0.000004 |
  | logging slow query             | 0.000002 |
  | logging slow query             | 0.000028 |
  | cleaning up                    | 0.000003 |
  +——————————–+———–+
  
  可以看到SELECT语句首先检查查询缓存，没有命中，然后在Opening tables阶段卡住。
  
  这引出了另一个特点，基于上述场景，如果后续的SELECT能够在查询缓存中命中，那么不会被ALTER TABLE卡住，因为不需要Opening tables操作。命中缓存SELECT的SHOW PROFILE如下：
  
  mysql> show profile;
  +——————————–+———-+
  | Status                         | Duration |
  +——————————–+———-+
  | starting                       | 0.000017 |
  | Waiting for query cache lock   | 0.000004 |
  | checking query cache for query | 0.000006 |
  | checking privileges on cached | 0.000004 |
  | checking permissions           | 0.000007 |
  | sending cached result to clien | 0.000011 |
  | logging slow query             | 0.000002 |
  | cleaning up                    | 0.000003 |
  +——————————–+———-+
  8 rows in set (0.00 sec)

遗留问题1.为什么第二次查询，记录没有发生变化，但结果集返回空？

这个问题在这个页面进行了讨论，确认原因是：

MySQL中ALTER操作实际后台是“创建新表”->”拷贝老表数据”->”RENAME新表成老表”。InnoDB引擎针对客户端的REPEATABLE READ隔离级别，采用的是基于多版本(Multi-version)功能的”一致性读”，即在事务中第一个查询时，获取的是当时版本的表的快照，而ALTER操作后实际是创建的新表，这个新表对于之前的快照而言是没有数据的，所以第二次查询新表基于快照的数据，返回空。在MySQL 5.6.6版本后，这样的第二个查询会返回Error信息“Table definition has changed, please retry transaction”。详细请看这里。

遗留问题2.为什么没有锁等待超时？？

我们知道InnoDB行锁的等待时间超过innodb_lock_wait_timeout就会报超时错误。这里的metadata lock一直等待着，为什么没有超时呢？其实metadata lock的超时设置是另一个系统变量lock_wait_timeout。

lock_wait_timeout控制着所有涉及到metadata lock的操作，包括DML and DDL，以及在表、视图、存储过程、存储函数上的操作，以及LOCK TABLES、FLUSH TABLES WITH READ LOCK、HANDLER等。

lock_wait_timeout的默认设置时一年，可以动态设置，根据实际场景进行调整。

大量参考：
http://www.mysqlperformanceblog.com/2013/02/01/implications-of-metadata-locking-changes-in-mysql-5-5/

分享文章：【Mysql】metadatalock锁
网站路径：http://pwwzsj.com/article/geedcj.html

平武建站

【Mysql】metadatalock锁

其他资讯