暂无图片
暂无图片
暂无图片
暂无图片
暂无图片
首页 / MySQL 实战笔记 第02期：MySQL 元数据锁

MySQL 实战笔记 第02期：MySQL 元数据锁

悦专栏 2020-12-02
491
作者简介
无为，多年 MySQL DBA 工作经验，现就职于某知名互联网公司，对 MySQL、 Redis、PostgrepSQL 等主流数据库有一定了解，拥有丰富的一线运维经验。

当我们在 MySQL 中执行 DDL 语句时，经常会发现语句没有在你预期的时间完成，这时候我们通常会使用 show full processlist ，来看看发生了什么状况。当你看到 waiting for table metadata lock 时，那就碰到元数据锁了。那元数据锁是怎样产生的又应该怎样避免呢？让我们从这篇文章开始了解它。

1、什么是元数据锁

MDL 全称为 metadata lock，即元数据锁，一般也可称为字典锁。MDL 的主要作用是为了管理数据库对象的并发访问和确保元数据一致性。元数据锁适用对象包含：table、schema、procedures, functions, triggers, scheduled events、tablespaces 。

2、加锁规则

获取规则：
  • 语句逐个（ one by one ）获取元数据锁，不是同时获取，并在获取过程中执行死锁检测。
  • DML 语句获取锁按照语句中 table 出现的顺序来获取锁。
  • DDL 语句、LOCK TABLES 和其他类似语句按名称顺序获取锁，对于隐式使用的表（例如外键关系中也必须锁定的表）可能会以不同的顺序获取锁。
  • DDL 的写锁请求优先级高于 DML

3、模拟加锁规则

两个相同表结构的表 t 和 t_new 开始。三个线程来操作这些表：
场景一
线程 1:
    LOCK TABLE t WRITE, t_new WRITE;
    复制
    该语句按表名顺序在 t 和 t_new 上获取写锁

    线程 2:
      INSERT INTO t VALUES(1);
      复制
      该语句处于也需要获取表 t 上的 MDL 所以处于等待状态

      线程 3:
        RENAME TABLE t TO t_old, t_new TO t;
        复制
        该语句需要按表名顺序在 t 、t_new、t_old 上获取互斥锁，所以也处于等待状态


        线程 1:
          UNLOCK TABLES;
          复制
          该语句释放对 t 和 t_new 的写锁定。线程 3 对 t 加写锁的优先级高于 线程 2 ，因此线程 3 在 t 上优先获得互斥锁，然后依次在 t_new、t_old 上获取互斥锁，执行重命名后释放其锁定。线程 2 获得 t 上的写锁，执行插入操作，然后释放其锁定。rename 操作在 insert 之前执行。

          场景二
          两个具有相同表结构的表 t 和 new_t ，同样是三个线程来操作这些表
          线程 1:
            LOCK TABLE t WRITE, new_t WRITE;
            复制
            该语句按表名顺序在 new_t 和 t 上获取写锁              

            线程 2:
              INSERT INTO t VALUES(1);
              复制
              该语句处于也需要获取表 t 上的 MDL 所以处于等待状态

              线程 3:
                RENAME TABLE t TO old_t, new_t TO t;
                复制
                该语句需要按表名顺序在 new_t 、old_t、t 上获取互斥锁，所以也处于等待状态
                该语句释放对 t 和 new_t 的写锁定。对于 t 首先发起锁请求的是线程 2 ，因此线程 2 优先获得了 t 上的元数据写锁，执行完插入操作，然后释放该锁。线程 3 首先获取的是 new_t 、old_t 的互斥锁，最后才会请求 t 上的互斥锁，所以线程 3 在线程 2 执行完毕之前都是处于等待状态的。rename 操作在 insert 操作之后。

                4、 如何监控元数据锁

                performance_schema.metadata_locks 表中记录了元数据锁相关的信息，开启方式如下：
                在线开启 metadata_locks，操作如下：
                  --UPDATE performance_schema.setup_consumers SET ENABLED = 'YES' WHERE NAME ='global_instrumentation';
                  --此值默认已开启了，可检查确认。


                  UPDATE performance_schema.setup_instruments SET ENABLED = 'YES' WHERE NAME ='wait/lock/metadata/sql/mdl';
                  复制
                  若可停库维护，则在 my.cnf 中添加如下：
                    [mysqld]
                    performance-schema-instrument='wait/lock/metadata/sql/mdl=ON'
                    复制


                    5、如何优化元数据锁

                    MDL 锁一旦发生会对业务造成极大影响，因为后续所有对该表的访问都会被阻塞，造成连接积压。我们日常要尽量避免 MDL 锁的发生，下面给出几点优化建议可供参考：
                    • 开启 metadata_locks 表记录 MDL 锁。
                    • 设置参数 lock_wait_timeout 为较小值，使被阻塞端主动停止。
                    • 规范使用事务，及时提交事务，避免使用大事务。
                    • 增强监控告警，及时发现 MDL 锁。
                    • DDL 操作及备份操作放在业务低峰期执行。



                    欢迎加入 MySQL 交流社群
                    群内不定期邀请一些身边的 MySQL 大牛
                    交流分享，解答工作中遇到的的问题
                    分享工作经验、面试技巧等！
                    入群请添加下方群秘微信，备注“MySQL”
                    另外，悦专栏欢迎各位大牛投稿，内容可以是数据库、开发、算法、大数据、运维、产品、运营等。只要在公众号回复“投稿”即可。


                    LIKECOLUMN

                    悦专栏


                    在这里，学好编程

                    做更优秀的 IT人！


                    mysql
                    文章转载自悦专栏，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

                    评论