暂无图片
暂无图片
暂无图片
暂无图片
暂无图片
首页 / MySQL为什么要增加元数据锁（MDL）？

MySQL为什么要增加元数据锁（MDL）？

MySQL数据库联盟 2023-12-22
209

上一次推广书籍还是上一次，再不推广一下都要过年了。

进入正文：

在 MySQL 中，DDL 是不属于事务范畴的。如果事务和 DDL 并行执行同一张表时，可能会出现事务特性被破坏、binlog 顺序错乱等 bug（比如 bug#989）。

https://bugs.mysql.com/bug.php?id=989


1 官方Bug列表举例MDL锁存在的问题

MySQL 5.5.3之前，没有MDL锁存在的问题。

步骤

session1

session2

1

begin;


2

insert into t ...;

drop table t;

3

commit;



而落到MySQL的Binlog

就是

drop table t;

begin;

insert into t ...;

commit;


就出现了这样位置错乱的Binlog，如果传到从库，就会导致复制中断。


为了解决这类问题，从MySQL 5.5.3开始，引入了元数据锁（Metadata Locking，简称：MDL 锁）。

比如上面这种情况，有MDL锁存在，在session2执行drop操作实际是需要等待的，直到session1中的事务提交。


2 增加MDL之后的实验

创建一张测试表

    create table t(id int);


    步骤

    session1

    session2

    1

    begin;


    2

    insert into t values(1);

    drop table t;

    （会等待）

    3

    commit;

    session1在commit之后，drop操作才会执行成功


    有MDL之后，会通过加锁的方式，强制session2的drop操作在session1事务结束之后。

    就可以避免刚才我们所讲的Binlog乱序的情况。

    所以MDL锁的出现解决了同一张表上事务和DDL并行执行时可能导致数据不一致的问题。

    但是，我们在工作中，很多情况需要考虑 MDL 的存在，否则可能导致长时间锁等待甚至连接被打满的情况。如下例：


    3 元数据锁导致查询阻塞举例

    写入测试数据

      use martin;
      drop table if exists t14;
      CREATE TABLE `t14` (
      `id` int NOT NULL AUTO_INCREMENT,
      `a` int NOT NULL,
      `b` int NOT NULL,  
      PRIMARY KEY (`id`),
      KEY `idx_a` (`a`)
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
      insert into t14(a,b) values(1,1);


      步骤

      session1

      session2

      session3

      1

      select id,a,b,sleep(100) from t14 limit 1;



      2


      alter table t14 add column c int;

      （等待）

      select id,a,b from t14 limit 1;

      （等待）

      3

      select id,a,b,sleep(100) from t14 limit 1;/* sql5 */

      …

      1 row in set (1 min 40.00 sec)

      （100秒后步骤1执行的查询语句返回结果）

      alter table t14 add column c int;/* sql6 */

      Query OK, 0 rows affected (1 min 33.98 sec)

      Records: 0 Duplicates: 0 Warnings: 0

      （session1 的查询语句执行完成后，sql6 立马执行完毕）

      select id,a,b from t14 limit 1;/* sql7 */

      …

      1 row in set (1 min 26.65 sec)

      （session1 的查询语句执行完成后，sql7 立马执行完毕）


      上面的实验中，我们在 session1 查询了表 t14 的数据，其中使用了 sleep(100) ，表示在 100 秒后才会返回结果；

      然后在 session2 执行 DDL 操作时会等待（原因是 session1 执行期间会对表 t14 加一个 MDL，而 session2 又会跟 session1 争抢 MDL）；

      而 session3 执行查询时也会继续等待。因此如果 session1 的语句一直没结束，其它所有的查询都会等待。

      这种情况下，如果这张表查询比较频繁，很可能短时间把数据库的连接数打满，导致新的连接无法建立而报错，如果是正式业务，影响是非常恐怖的。


      当然如果出现这种情况，假如你还有 session 连着数据库，可以 kill 掉 session1 中的语句或者终止 session2 中的 DDL 操作，可以让业务恢复。

      但是出现这种情况的根源其实是：session1 中有长时间未提交的事务。因此对于开发来说，在工作中应该尽量避免慢查询、尽量保证事务及时提交、避免大事务等，当然对于 DBA 来说，也应该尽量避免在业务高峰执行 DDL 操作。


      4 监控元数据锁

      再来看下怎样监控MDL。


      步骤

      session1

      session2

      session3

      1



      select * from performance_schema.metadata_locks;

      2

      select id,a,b,sleep(200) from t14 limit 1;



      3



      select * from performance_schema.metadata_locks;

      4


      alter table t14 add column c int;

      （等待）


      5



      select * from performance_schema.metadata_locks;

      或者

      show processlist


      发现多了一行关于表t14的锁记录，加锁类型是SHARED_READ，


      查看performance_schema.metadata_locks表

        select * from performance_schema.metadata_locks;


        下图是第5步的结果：

        各字段解释：

        OBJECT_TYPE，元数据锁子系统中使用的锁类型

        OBJECT_SCHEMA，库名

        OBJECT_NAME，对象名

        OBJECT_INSTANCE_BEGIN，对象在内存中的地址

        LOCK_TYPE，元数据锁子系统的锁类型

        SHARED_READ（共享读锁），多个事务可以同时持有共享读锁，用于读取共享资源。多个事务可以同时持有共享读锁，不会相互阻塞。只有当有其他事务持有排他锁（EXCLUSIVE）时，新的共享读锁请求才会被阻塞

        INTENTION_EXCLUSIVE（意向排他锁），意向排他锁是一种表示事务即将获取排他锁（EXCLUSIVE）的信号。当一个事务打算获取排他锁时，它需要首先获取意向排他锁。这种锁的引入是为了提高并发性，允许其他事务同时获取共享读锁。

        SHARED_UPGRADABLE（可升级共享锁），可升级共享锁是一种特殊类型的锁，在这个状态下，事务持有一个共享读锁，但是希望在不释放该锁的情况下将其升级为排他锁。这种锁允许在不释放锁的情况下安全地升级为排他锁。

        EXCLUSIVE（排他锁），它阻止其他事务获取任何类型的锁（共享读锁、意向排他锁等）。当一个事务持有排他锁时，其他事务无法同时持有任何类型的锁，从而确保了数据的一致性和完整性。

        LOCK_DURATION，元数据锁定子系统的锁定持续时间，值可能是：STATEMENT、TRANSACTION或EXPLICIT

        STATEMENT，表示在语句结束时，隐式释放的锁

        TRANSACTION，表示在事务结束时，隐式释放的锁

        EXPLICIT，表示语句或事务结束，仍然存在的锁。

        LOCK_STATUS，元数据锁子系统的锁定状态

        SOURCE，包含生成事件的检测代码的源文件的名称以及发生检测的文件中的行号

        OWNER_THREAD_ID，请求元数据锁的线程

        OWNER_EVENT_ID，请求元数据锁的事件


        也可以执行

          show processlist;


          5 MDL读锁和写锁之间的关系

          所有DML操作会在表上加一个MDL读锁

          所有的DDL操作都会在表上加一个MDL写锁

          读锁和写锁之间相互阻塞

          即同一个表上的dml和ddl之间互相阻塞。

          写锁和写锁之间互相阻塞

          即两个session不能对表同时做表定义变更，需要串行操作。

          读锁和读锁之间不会产生阻塞

          也就是增删改查不会因为metadata lock产生阻塞，可以并发执行，日常工作中大家看到的dml之间的锁等待，是InnoDB行锁引起的，和MDL无关。这个锅他不背。


          6 元数据锁注意事项

          对元数据锁进行监控

          通过performance_schema.metadata_locks表监控元数据锁；

          有大量元数据锁及时告警。

          规范操作

          使用事务，及时提交事务，避免使用大事务；

          DDL操作和备份操作放在业务低峰执行。

          尽量不依靠MDL锁等待超时

          我们可以查看MDL锁等待的时间：

            show global variables like "lock_wait_timeout";

            元数据锁默认等待时间是一年，不过一般也不会等到元数据锁超时，而是应该尽可能找到导致元数据锁的源头；

            长时间的查询，如果不重要，kill掉。

            是大事务的话，及时提交事务；

            有DDL操作，也想办法终止掉。


            点击下方阅读原文跳转到小编的DBA体系课程。

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

            评论