文章详情

短信预约-IT技能 免费直播动态提醒

请输入下面的图形验证码

提交验证

短信预约提醒成功

【Mysql】metadata lock锁

2024-04-02 19:55

关注
原文地址: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”

案列

  1. session1
  2. 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)


  3. 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. ---堵塞

    5. 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的操作立即可以执行
    6. ctrl+c掉后我们查看profile执行计划


    7. 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)




    免责声明:

    ① 本站未注明“稿件来源”的信息均来自网络整理。其文字、图片和音视频稿件的所属权归原作者所有。本站收集整理出于非商业性的教育和科研之目的,并不意味着本站赞同其观点或证实其内容的真实性。仅作为临时的测试数据,供内部测试之用。本站并未授权任何人以任何方式主动获取本站任何信息。

    ② 本站未注明“稿件来源”的临时测试数据将在测试完成后最终做删除处理。有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341

    软考中级精品资料免费领

    • 历年真题答案解析
    • 备考技巧名师总结
    • 高频考点精准押题
    • 资料下载
    • 历年真题
    • 2024年上半年信息系统项目管理师第二批次真题及答案解析(完整版)

      难度     813人已做
      查看
    • 【考后总结】2024年5月26日信息系统项目管理师第2批次考情分析

      难度     354人已做
      查看
    • 【考后总结】2024年5月25日信息系统项目管理师第1批次考情分析

      难度     318人已做
      查看
    • 2024年上半年软考高项第一、二批次真题考点汇总(完整版)

      难度     435人已做
      查看
    • 2024年上半年系统架构设计师考试综合知识真题

      难度     224人已做
      查看

    相关文章

    发现更多好内容
    咦!没有更多了?去看看其它编程学习网 内容吧