MySQL（十二）MDL锁介绍

科技前沿 • 2025-02-21 13:16 • 阅读 61

大家好，我是讯享网，很高兴认识大家。

一.什么是MDL锁

MDL锁:全称为meta data lock，中文叫元数据锁，是从MySQL5.5开始引入的锁，是为了解决DDL操作和DML操作之间操作一致性。从锁的作用范围上来说，MDL算是一种表级锁，是一个server层的锁。

其实MDL加锁过程是系统自动控制，无法直接干预，也不需要直接干预，当我们对一个表做增删改查操作的时候，会自动加MDL读锁；当我们要更新表结构的时候，加MDL写锁。加读锁则所有线程可正常读表的元数据，并且读锁不影响表的增删改查操作，只是不能修改表结构；而加写锁只有拥有锁的线程可以读写元数据，即只拥有锁的线程才能更新表结构，其他线程不能修改结构也不能执行相应的增删改查。

即MDL中，读读共享，读写互斥，写写互斥。

案例

数据库如下：
注意的是开了两个33061
在这里插入图片描述
讯享网

小例子1：

我们在第一个33061中

执行如下两句sql语句，
开启事务，自动加上MDL读锁
START TRANSACTION;
把id为一的数据的username从zhangsan改为aaa
UPDATE user2 SET username='aaa' WHERE id=1;
执行成功
在这里插入图片描述
表数据完成了更改

然后我们在第二个33061上进行操作（此时33061已经开启了事务）
执行sql语句
ALTER TABLE user2 ADD COLUMN age222 INT ;

在这里插入图片描述
此时就并没有办法执行成功，我们增添新行的sql指令就卡在这里了,因为第一个33061是开启了事务，且进行DDL操作，我们第二个33061也是进行DDL操纵，由于即MDL中，读读共享，读写互斥，写写互斥。故此我们这样操作就是错误的。

小例子2：

我们在第一个33061中进行提交事务操作:
在这里插入图片描述
发现第二个33061中增加新数据行的操作执行成功了。

小例子4 ：

在第一个33061中
START TRANSACTION;
进行查询：
SELECT * FROM user2 WHERE id =3;
在这里插入图片描述

在第二个33061中进行查询
SELECT * FROM user2 WHERE id =4;
在这里插入图片描述
发现这样是可以的，即我们上面说的：
即MDL中，读读共享，读写互斥，写写互斥。

Online DDL

MySQL5.6之后引入了Online DDL 从名字上就可以看出来在线执行DDL，不和其他操作冲突，具体执行流程如下：

1.DDL操作需要首先获取MDL写锁
2.将MDL写锁降级成MDL读锁。
3.做真正的DDL操作，这一步十分耗时，由于此时我们持有的是MDL读锁，因此并不会阻塞其他的增删改查操作。
4.做完DDL操作之后，接下来将MDL读锁升级成MDL写锁。
5. 释放MDL锁。

注意：Online DDL并不是让你随时随地地可以更新表结构，也还是有限制的，只有在第三步哪里可以继续执行增删改查，即使是在第三步，更新表结构也是不允许的。