Mysql 的元数据锁

什么是元数据锁？

英文名叫Metadata Lock，缩写为MDL，顾名思义，它是针对元数据的一种锁，锁的是元数据。

那什么是元数据？

一张表有100条记录，这里的记录我们可以称之为表数据，一张表的名字叫t1，有c1、c2两个字段，c1的类型是int，c2的类型是varchar，这里的表名、字段名、字段类型叫做表的元数据，当我们修改某行记录时，叫做修改表数据，当我们修改表名或字段类型时，叫做修改表的元数据。

什么情况要锁元数据呢？

很简单，用到元数据的时候就要锁，那什么时候用到元数据呢？注意，表名也是一个表的元数据，因此，只要是针对表的操作，都需要用到对应表的元数据，都需要给该表加元数据锁。

啊？任何对表的操作都要加元数据锁吗？就连最普通的select查询也要吗？

要，或者说MySQL就是这么做的，不过，大家还记得大明湖畔的读写锁吗，或者说InnoDB中的共享锁和排他锁。

元数据锁也分为共享锁和排他锁，额外说一句，元数据锁是属于MySQL级别的，而行锁、间隙锁这些是属于InnoDB级别的，因此元数据锁的级别更高，一条SQL执行时，都会先在MySQL层加元数据锁，加成功了之后才加InnoDB中的锁。

既然是元数据锁，读元数据是最基本的需求，在这个基础上，再来根据其他需求划分不同的类型。

如果只需要读元数据，不需要修改元数据，不需要读表数据，不需要修改表数据，那就加MDL_SHARED类型的元数据锁，比如CREATE TABLE t2 LIKE t1语句就属于这种情况，只需要读取t1表的元数据。

如果需要读元数据，不需要修改元数据，但需要读表数据，不需要修改表数据，那就加MDL_SHARED_READ类型的锁，比如大家关心的普通select查询就属于这种情况，既不需要修改元数据，也不需要修改表数据，只需要读数据。

如果需要读元数据，不需要修改元数据，但需要读表数据，并需要修改表数据，那就加MDL_SHARED_WRITE类型的锁，比如大家熟悉的insert、update、delete就属于这种情况，不需要修改元数据，但需要修改表数据。

如果既需要修改元数据，又需要修改表数据，那就加MDL_EXCLUSIVE类型的锁，比如某些执行DDL语句就属于这种情况，比如加字段、修改字段类型，这些操作不仅需要修改元数据，还可能需要修改表数据，比如给表新增字段时，如果指定了字段默认值，那么就需要修改所有记录，大家可以了解一下数据行格式，从而可以了解到更多DDL时需要修改记录的情况（关注我，我后面会分析，公众号：IT周瑜）。

因此，大多数执行SQL的时候，都需要加元数据锁，只不过可能类型不一样而已。

那一个连接（注意，我用的是连接，而不是事务，因为事务是InnoDB中的概念，元数据锁是属于MySQL层的概念，当然，为了方便理解，可以把连接和事务划上等号），那一个连接给某张表加了某种元数据锁，其他连接还能操作这张表吗？

这就得看各个连接分别加的什么类型的元数据锁了，实际上，不管是MySQL中的元数据锁，还是InnoDB中的行锁、意向锁都有一个概念，叫做锁兼容。（行锁和意向锁，我已经研究清楚了，关注我，我后面也会分析，公众号：IT周瑜）

什么是锁兼容？

如果连接1加了A锁，连接2现在想要加B锁，如果A锁和B锁是兼容的，那么连接2就能成功加到B锁，如果是不兼容的，那么连接2就需要等待连接1释放A锁。

那以上几种类型的元数据锁兼容性是怎样的呢？

| | MDL_SHARED | MDL_SHARED_READ | MDL_SHARED_WRITE | MDL_EXCLUSIVE | | --- | --- | --- | --- | --- | | MDL_SHARED | + | + | + | - | | MDL_SHARED_READ | + | + | + | - | | MDL_SHARED_WRITE | + | + | + | - | | MDL_EXCLUSIVE | - | - | - | - |

+号表示兼容，-号表示不兼容

如何理解呢？

假如一个连接给t表加了MDL_SHARED锁，另一个连接也想加MDL_SHARED锁，当然没问题了，大家都只是想读元数据锁而已，并不冲突。

假如一个连接给t表加了MDL_SHARED锁，但是另一个连接想加MDL_EXCLUSIVE锁，那肯定不行了，我现在要读元数据，而你却想要修改元数据，会影响到我，会冲突，所以另一个连接只能等待，同时也表示MDL_SHARED锁和MDL_EXCLUSIVE锁是不兼容的，实际上MDL_EXCLUSIVE锁和谁都不兼容，因为它想修改元数据。

那如何理解MDL_SHARED和MDL_SHARED_READ、MDL_SHARED_WRITE都兼容呢，实际上MDL_SHARED_READ和MDL_SHARED_WRITE相当于是MDL_SHARED的子类，是MySQL做的更进一步区分，select读操作加的是MDL_SHARED_READ锁，update等写操作加的是MDL_SHARED_WRITE锁，但它们都只是需要读取元数据，不需要修改元数据，因此也是兼容的，也就是说当一个连接在执行select，另一个连接在执行update，还来一个连接也执行select，至少在元数据锁层面是不冲突的，是可以放行的，是可以加元数据锁成功的，至于在InnoDB层面会不会冲突，那就不是元数据锁该管的了，元数据锁只管元数据，不管表数据。

那MDL_SHARED_READ锁和MDL_SHARED_WRITE锁没啥用呗？ 也不是，至少我们可以通过查看元数据锁的情况，知道当前各个连接分别加了什么类型的元数据锁，并且知道锁是不是阻塞了，从而知道锁冲突的情况，根据锁类型知道是什么样的SQL阻塞了。

如何查看元数据锁的阻塞情况？

元数据锁可以查看？可以，我给你看看。

首先执行：

UPDATE performance_schema.setup_instruments
SET ENABLED = 'YES', TIMED = 'YES'
WHERE NAME = 'wait/lock/metadata/sql/mdl';

然后开启事务一，并执行简单的查询，但是事务先不提交：

begin;
select * from user_info;

然后开启事务二，并执行一个DDL语句：

begin;
alter table user_info drop column phone;

你会发现，DDL语句执行时会转圈圈，也就是会阻塞住。

然后执行以下SQL查看元数据锁的情况：

SELECT * FROM performance_schema.metadata_locks;

结果为：

先看OWNER_THREAD_ID字段，该字段表示持有元数据锁的线程，发现只有两个线程61和67。

再看OBJECT_NAME字段，该字段表示元数据锁锁的对象，比如61和67两个线程都锁了user_info这张表。

再看LOCK_TYPE字段，该字段表示元数据锁的类型，仔细看看就能发现：

1. 61线程针对user_info表加了SHARED_READ类型的锁

2. 67线程针对user_info表加了EXCLUSIVE和SHARED_UPGRADABLE类型的锁（SHARED_UPGRADABLE不用管）

再看LOCK_STATUS字段，该字段表示锁当前的状态，仔细看看就能发现：

1. 61线程，状态是GRANTED，表示成功加到了SHARED_READ类型的元数据锁

2. 67线程，虽然SHARED_UPGRADABLE类型的锁加成功了，但是EXCLUSIVE类型的锁是PENDING状态，表示EXCLUSIVE类型的元数锁没有加成功，在等待，等待什么呢？自然就是等待61线程释放掉SHARED_READ类型的元数据锁，因为EXCLUSIVE和SHARED_READ是不兼容的，所以67线程需要等待

那61线程什么时候释放SHARED_READ元数据锁呢？很简单，当事务提交或回滚就会释放了。

能否先总结一下元数据锁的作用？

可以，一般的select查询和DML操作只需要读取元数据，而DDL操作则需要修改元数据，所以元数据锁更多是用来保证DML和DDL、DDL和DDL之间的并发安全性，DML和DML之间的并发安全由InnoDB中的锁来解决。

那传说中的Online DDL是不是跟这个元数据锁有关系？

必须有关系，而且有很大的关系，要理解Online DDL就得理解DDL的底层执行过程，在MySQL5.7中有两种DDL算法，一种是COPY，一种是INPLACE，MySQL8.0中新增了一种INSTANT算法（本文不介绍），在执行DDL语句时，MySQL会自动判断应该用哪种算法，当然你可以手动指定（后面会看到如何指定），但并不是你指定了INPLACE算法，MySQL就一定会用，如果当前DDL操作不支持INPLACE算法，MySQL会给出提示，让你用COPY算法。

COPY算法DDL的底层执行流程

比如，给t1表新增一个字段，如果选择的是COPY算法，那么大概流程是这样的：

1. 在磁盘生成一张临时表，临时表的表名是MySQL自动生成的，临时表的字段是t1表+新字段

2. 然后将t1表的数据复制到临时表中，我通过源码debug发现，这个过程是从通过调用InnoDB的查询接口每次查询一行，然后再调用InnoDB的插入数据接口，将数据插入到临时表中，从而完成数据的复制，就是这么一行一行进行复制的，所以效率很低

3. 数据复制完之后，删除t1表，并将临时表重命名为t1

可以发现，COPY算法的核心是第二步，最耗时的也是第二步，COPY算法完全是属于MySQL层的，它只是调用了InnoDB提供的查询和插入数据的接口。

COPY算法执行过程中会出现如下情况:

其中两个奇怪名字的文件就是临时表相关的文件，frm表示表定义文件，ibd表示表数据文件，奇怪的名字就是临时表名。

INPLACE算法DDL的底层执行流程

而INPLACE算法，表示“就地”修改，“就地”的意思是直接在InnoDB内部进行修改，你可以认为，如果能采用INPLACE算法进行DDL（注意，我说的是能，并不是所有的DDL都能采用INPLACE算法），那么MySQL层需要做的事情就比较少了，核心步骤都交给InnoDB了，而InnoDB中也分为了三个步骤，源码函数分别为：

1. ha_innobase::prepare_inplace_alter_table()

2. ha_innobase::inplace_alter_table()

3. ha_innobase::commit_inplace_alter_table()

那采用INPLACE算法，还要不要复制表的数据呢，仍然需要，只不过是InnoDB来实现了，比如执行过程中会出现如下情况： 

也会生成临时文件，只不过是InnoDB生成的，当然，frm文件是MySQL层生成，而另外一个ibd文件则是InnoDB生成的，我还发现，如果采用INPLACE算法，是不会像COPY算法那样每次从原始表查一行数据，然后插一行数据到临时表的，至于InnoDb是怎么将原始表数据复制到临时表的，肯定采用了效率更高的方式，只不过我还没研究清楚，这里就先不介绍了。

不过可以肯定的是，采用INPLACE算法效率会更高，一方面由InnoDB自身复制数据肯定比COPY算法那样由MySQL层调用InnoDB查询和插入数据的接口来复制数据效率要高，另外一方面就是元数据锁的使用了。

我相信大家都听过这样的结论：如果采用COPY算法，DDL在复制数据的过程中，DML操作是会被阻塞的，而如果采用INPLACE算法，DDL在复制数据的过程中，DML操作是不会阻塞的，这也是为什么叫做Online DDL，表示DDL的过程中，可以在线同时执行DML操作。

真的是这样吗？我们来测测。

COPY算法会阻塞select吗？

我做了一个实验，表结构是这样的：

CREATE TABLE `user_info` (
  `id` int,
  `name` varchar(255),
  `phone` varchar(45) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

用存储过程模拟插入数据：

CREATE DEFINER=`root`@`%` PROCEDURE `InsertUserInfo`(IN num_records INT)
BEGIN
    DECLARE i INT DEFAULT 1;
    DECLARE phone_prefix CHAR(3) DEFAULT '123';
    DECLARE phone_suffix CHAR(8) DEFAULT '';
    DECLARE name VARCHAR(255);

    WHILE i <= num_records DO
        SET phone_suffix = LPAD(i, 8, '0');
        SET name = CONCAT('User_', i);

        INSERT INTO user_info (id, name, phone)
        VALUES (i, name, CONCAT(phone_prefix, phone_suffix));

        SET i = i + 1;
    END WHILE;
END

调用存储过程：

call my_db.InsertUserInfo(1000000);

打开三个连接：

1. 连接1用来开启事务执行select语句

2. 连接2用来进行DDL

3. 连接3用来查看元数据情况

先用连接2进行DDL操作，删除表中的phone字段，先测试COPY算法：

alter table user_info drop column phone, ALGORITHM=COPY, LOCK=SHARED;

注意，使用COPY算法时，LOCK只能是SHARED，不能是NONE，不然MySQL会提示你要用SHARED。

然后用连接3查看元数据锁情况，没有PENDDING，因为现在只有连接2，线程id为78，在使用user_info表： 然后用连接1开启事务并执行查询：

begin; 
select * from user_info;

注意，是能查出结果的！是能查出结果的！是能查出结果的！

但是！等了很久之后，它始终能查出phone字段，难道DDL操作一直没执行完（DDL操作是在删除phone字段）?

并不是，我们用连接3查看元数据锁，发现78线程对user_info多加了一把元数据锁，而且是EXCLUSIVE类型的，而且是PENDDING的： 78线程就是DDL操作的线程，也就代表DDL线程被阻塞了，为什么会被阻塞呢？

因为连接1的事务在执行select的时候，给user_info加了一把SHARED_READ的元数据锁，但是事务一直没有提交，SHARED_READ元数据锁一直没有释放，而连接2的DDL操作在数据复制完了之后需要修改元数据，因此需要给user_info加EXCLUSIVE类型的元数据锁，这个时候就阻塞了，从而导致DDL操作一直没有完成，从而导致连接1的select的查询结果一直没有发生变化，此时我们也可以看一下文件的情况： 发现临时文件确实已经创建，并且数据也确实已经复制完成了，注意临时ibd文件的大小。

因此，现在只要连接1提交事务，整个过程就不会阻塞了，连接1提交之后，连接2的DDL操作才会真正结束，此后连接1再查询数据时，才不会查找phone字段。

因此，可以得出结论，COPY算法的DDL在数据复制过程中，并不会阻塞查询操作，但是由于最后阶段会加EXCLUSIVE类型的元数据锁，从���导致DDL操作可能会阻塞（比如连接1的事务一直不提交），一直完成不了。

因此如果有长事务，尽管是查询操作，也可能会导致DDL操作阻塞，所以要尽量避免长事务。

也就是说，select如果一直不提交，会阻塞DDL，反过来，DDL操作复制数据之后都会需要修改元数据，需要加EXCLUSIVE类型的元数据锁，也可能会阻塞select操作，因此我们只能说，DDL操作的数据复制过程中不会阻塞select，不能说DDL整个操作不会阻塞select。

INPLACE算法会阻塞select吗？

再来看看INPLACE算法，首先，INPLACE算法的执行速度要快很多，在不产生锁冲突的情况下，COPY算法删除字段：

alter table user_info drop column phone, ALGORITHM=COPY, LOCK=SHARED;

需要执行17秒左右。

而INPLACE算法：

alter table user_info drop column phone, ALGORITHM=INPLACE, LOCK=NONE;

只需要执行3秒左右。

这效率不是一般的高。

经过上面同样的测试步骤，发现INPLACE算法DDL在复制数据过程中，也不会阻塞select查询，但是在数据复制完之后，也会加EXCLUSIVE类型的锁，按上面同样的步骤，也会出现同样的元数据锁情况：

而文件的情况也类似： 

也是数据复制完了，但临时文件一直在，就是因为DDL操作最后被阻塞了，和COPY算法类似，只有连接1提交事务，整个DDL过程才会结束。

从这个测试看，COPY算法和INPLACE算法，只有效率上的不同，在数据复制过程中都可以查询数据，但是查询事务如果一直不提交，则会阻塞DDL操作，因为DDL操作的最后需要修改元数据，会给表加EXCLUSIVE类型的锁，从而和查询事务的SHARED_READ类型的锁冲突，导致DDL操作阻塞。

注意，我说的是数据复制过程中不会阻塞select查询，数据复制的前面步骤和后面步骤其实都会给表加EXCLUSIVE类型的锁（时间比较短），从而可能导致短暂的阻塞select查询

那假如是不是查询语句呢，而是insert、update、delete这些DML语句呢？

COPY算法会阻塞DML操作吗？为什么？

先测试COPY算法的DDL操作，先利用连接2执行：

alter table user_info drop column phone, ALGORITHM=COPY, LOCK=SHARED;

这个语句，需要执行一段时间（表里的数据增加到了200万），在这个过程中，用连接1执行：

begin; 
insert into user_info values(3000001, 'zhangfei');

发现insert语句会直接阻塞。

连接3查看元数据锁的情况：

SELECT * FROM performance_schema.metadata_locks;

79线程是DDL线程，81线程是insert语句的线程，加的是SHARED_WRITE类型的锁，状态是PENDING，因此insert语句阻塞了，因此看出，在使用COPY算法执行DDL过程中，就算是数据复制过程中，其他连接都不能执行insert语句，同样，我也测了update和delete，和insert语句一样，都是加的SHARED_WRITE类型的锁，所以都会阻塞，因此COPY算法的DDL，不仅数据复制慢，而且过程中还导致其他事务不能执行DML操作，只能执行select。

INPLACE算法会阻塞DML操作吗？为什么？

采用同样的流程，来测试INPLACE算法。

先用连接2执行INPLACE算法的DDL操作：

alter table user_info drop column phone, ALGORITHM=INPLACE, LOCK=NONE;

然后用连接1执行insert、update、delete操作：

begin;
update user_info set name = '111' where id = 1;
insert into user_info values(3000011, 'zhangfei', '123');
delete from user_info where id = 1;

会发现和COPY算法确实不一样，此时连接1是能执行这些DML操作的，这就是Online的意思，如果使用INPLACE算法进行DDL，那么在数据复制过程中，其他连接可以同时进行DML操作。

但是和select查询一样，如果DML操作的事务一直不提交，那么DDL操作也一直无法完成，因为DDL操作在数据复制完了之后，需要修改元数据，从而会加EXCLUSIVE类型的元数据锁，而其他连接，不管是执行select读操作，还是执行insert、update、delete这些写操作，都会加SHARED_READ或SHARED_WRITE类的元数据锁，而事务一直不提交，那么锁就一直不释放，从而导致DDL操作在执行完数据复制操作之后，EXCLUSIVE类型的锁加不成功，导致DDL操作完成不了。

COPY算法和INPLACE算法有什么相同点和不同点？

最后，再总结一遍：

1. 对于COPY算法和INPLACE算法的DDL操作，如果另外一个连接执行的是select查询，那么两种算法除开数据复制效率的差别之外，没有其他差别，都能查到数据。

2. 对于COPY算法的DDL操作，如果另外一个连接执行的是insert、update、delete等写操作，则这些写操作会阻塞，直到DDL操作完成

3. 对于INPLACE算法的DDL操作，如果另外一个连接执行的insert、update、delete等写操作，这些写操作不会阻塞

长事务和Online DDL会产生什么影响？

不管是COPY算法还是INPLACE算法，只要DDL操作复制数据结束之后，DDL操作都需要修改元数据，从而都需要给元数据加EXCLUSIVE类型的锁，因此其他连接如果一直不提交事务，就会导致DDL操作一直获取不到EXCLUSIVE类型元数据锁，也就会导致DDL操作一直完成不了，因此大家在进行DDL操作时，就算是用INPLACE算法，最好也在业务低峰期，这样DDL操作能尽快完成。

Online DDL什么情况下会导致锁表？

特别要注意，当DDL操作给表加了EXCLUSIVE类型的元数据锁之后，假如来了一个新连接4，它执行最简单的select查询都是执行不了的，因为它需要加SHARED_READ类型的元数据锁，SHARED_READ和EXCLUSIVE类型的元数据锁是冲突的。

也就是可能会出现以下“锁表”的情况:

1. 连接2先执行DDL操作（不管是COPY还是INPLACE算法）

2. 连接1在DDL操作过程中执行了select、insert、update、delete等操作，但一直不提交，一直占用着SHARED_READ或SHARED_WRITE类型的锁

3. 连接2的DDL操作数据复制执行完了，需要加EXCLUSIVE类型的锁，但是只能阻塞

4. 连接3来执行select、insert、update、delete等操作，需要加SHARED_READ或SHARED_WRITE类型的锁，此时就会阻塞，导致SQL执行不了，转圈圈

这种情况就比较严重了，因此一定要尽快让连接1的事务提交，不然就会一直卡着，整个表都无法处理请求，看上去就是“锁表”了。

总结

最后，再总结一下，凡事要用到表的时候，就会给表加元数据锁，只不过不同操作加的不同类型的元数据锁，不同类型的元数据锁之间可能兼容，可能冲突。

比如select、insert、update、delete这些操作，只需要读表的元数据，因此会给表加SHARED类型的元数据锁，而很多DDL操作需要修改表的元数据，比如添加字段，因此需要加EXCLUSIVE类型的锁，EXCLUSIVE和SHARED是不兼容的，所以可能存在，DML和DML之间的元数据锁不会冲突，但是DML和DDL，DDL和DDL之间的元数据锁会产生冲突。

而DML和DDL之间，就是通常说的Online DDL，表示DDL的数据复制期间，其他事务可以正常执行DML，但是数据复制完了之后仍然会跟DML产生冲突。

因此，系统中一定尽量避免比较长事务，比如慢查询，如果在DDL期间遇到了慢查询，那么DDL整个操作就得等待慢查询执行结束才会完成，而如果DDL操作一直不结束，就相当于一直给表加了EXCLUSIVE类型的锁，导致“锁表”，一旦“锁表”，其他新事务针对这个表的任何SQL都无法执行了(我测了select * 和 select count(*)都不能执行，大家可以再试试别的)。