20230205-MySQL数据库-锁详解

什么是锁

现实生活中，什么是锁：
置于可启闭的器物上，以钥匙或暗码开启。
本义：加在门窗、器物等开合处或连接处，必须用钥匙、密码、磁卡等才能打开的金属装置。如：门锁、密码锁、锁钥。
比如入住酒店的，如果大家随意进出，就会出现多人抢夺同一个房间的情况，而在房间上装上锁，申请到钥匙的人才可以入住并且将房间锁起来，其他人只有等他使用完毕才可以再次使用。
而在数据库管理系统中，锁是在多用户并发存取数据库时，用于防止冲突存取的一种并发控制机制，保证访问数据库资源的次序。
他和现实中的锁的用途本质是一样的，都是限制或约束某种行为，从而达到保护某个事物的作用。

为什么需要锁

锁的作用是保障在多个事务并发执行，不相互影响，避免脏读、幻读、不可重复读的发生。
保证数据的一致性、保证数据的完整性、保证数据不被破坏。
也就是实现事物ACID特性中的隔离性。
SQL标准定义的四个隔离级别为：

• read uncommited（读未提交）
• read commited（不可重复读）
• repeatable read（可重复读）
• serializable （串行化 ）

不使用锁会有哪些问题

示例 1

A更新员工工资表，将B员工工资从1000更新到2000，但没有提交事务。
这时B员工读取工资表，发现自己的工作从1000上涨到2000了。
然后A员工发现更新错了，又将没有提交的数据撤回了，实际上B员工工资还是1000元。这就导致了B读取到的数据是错误的，也就是发生了脏读。

示例 2

A更新员工工资表，将B员工工资从1000更新到2000，但没有提交事务。
这时C员工执行删除工资表数据，删除工资大于1500的所有数据，由于C员工读取到B员工的工资2000大于1500，所以把B员工工资信息也删除了。
然后A员工发现更新错了，想将没有提交的数据撤回了，然后发现数据怎么没了。
这就导致了数据被误删除，也是脏读导致的错误。

示例 3

A员工在08:00读取一张大表数据，08:15读取完成。
B员工在08:05更改这种大表的，08:10分更改完成。
那么最终A员工读取到的是哪个是时间点的数据呢？
正常来讲，08:00发起的读取请求，不管读取时间多长，最终读取的数据应该全部都是8:00这一时刻的，而不应该是不同时刻的变化数据，在没有锁的情况下，最终查询是不一致的数据。

如何解决这几个场景的问题

示例 1 ，示例 2 ，示例 3 场景解决方案：

表锁

A更新员工工资表时，先申请一个表锁，然后在进行修改，这个锁会阻塞其他会话对工资表的读取和修改，其他员工在这段时间申请读、更新、删除数据时会卡住，直到A将事务提交或回退，这也就避免了示例1、2、3读取错误数据和误删除数据的场景。
所以第一阶段，数据库需要表锁。

表锁存在的问题：

但是虽然解决了前面示例的问题，事务只能串行执行，这在高并发下效率会极低，能否即解决前面示例 1,2,3 的问题，又能提高性能。

解决方式： MVCC

在执行数据修改时，还是先加锁，然后将修改前的旧数据拷贝出来放在一个地方，在数据提交或回滚前，其他数据读取数据时，读取的是之前拷贝的旧数据。
MVCC(multi-version-concurrent-control)
MVCC即多版本并发控制，MVCC是一种并发控制的方法，一般在数据库管理系统中，实现对数据库的并发访问，在编程语言中实现事务内存。
MVCC在MySQL InnoDB中的实现主要是为了提高数据库的并发性能，用更好的方式去处理读-写冲突，做到即使有读写冲突时，也能做到不加锁，非阻塞并发读。

如何降低锁定范围？

例如：
T1表有1千万条数据，A正在更新T1表中的一条数据，更新前添加表锁，锁住所有数据，这时其他会话申请更新T1表其他数据，由于存在表锁，导致更新被阻塞。
如何优化示例4场景，这需要降低锁的范围。

行锁

只锁定指定的行，不阻塞其他行的修改等操作，提高并发性能。

表锁分类

元数据锁 (MDL)

MDL(metadata lock)是表级锁，是在server层加的，适用于所有存储引擎。
MDL 是为了保证当用户对表执行 CRUD (增、删、改、查)操作时，防止其他线程对这个表结构做了变更。

如果没有 MDL元数据锁：
当一个长查询SQL正在执行查询时，已经查询出部分数据，这时如果另一个会话执行表字段的删除操作，如果没有MDL锁，是可以删除掉字段的，那么当前正在查询的SQL语句就会查询到与实际不符的数据，也就是少了一列的数据，实际上在发起查询最开始的时候是有这列数据的。
这和前面讲到的MVCC不同，MVCC指在执行insert,update,delete操作时，会将旧数据保存到undo里，形成多个版本，避免写阻塞读。

MDL如何工作：

当有线程在执行 select 语句（ 加 MDL 读锁）的期间，如果有其他线程要更改该表的结构（ 申请 MDL 写锁），那么将会被阻塞，直到执行完 select 语句（ 释放 MDL 读锁）。
反之，当有线程对表结构进行变更（ 加 MDL 写锁）的期间，如果有其他线程执行了 CRUD 操作（ 申请 MDL 读锁），那么就会被阻塞，直到表结构变更完成（ 释放 MDL 写锁）。

MDL 不需要显示调用，那它是在什么时候释放的 ?

MDL 是在事务提交后才会释放，这意味着事务执行期间，MDL 是一直持有的。
所有的dml操作都会在表上加一个metadata读锁；所有的ddl操作都会在表上加一个metadata写锁。

读锁和写锁的阻塞关系如下：

1.读锁和写锁之间相互阻塞，即同一个表上的dml和ddl之间互相阻塞。
2.写锁和写锁之间互相阻塞，即两个session不能对表同时做表定义变更，需要串行操作。
3.读锁和读锁之间不会产生阻塞。也就是增删改查不会因为metadata lock产生阻塞，可以并发执行，日常工作中大家看到的dml之间的锁等待是innodb行锁引起的，和metadata lock无关。
不需要显示的使用 MDL，当对数据库表进行操作时，会自动给这个表加上 MDL：
1.对一张表进行 CRUD 操作时，加的是 MDL 读锁；
2.对一张表做结构变更操作的时候，加的是 MDL 写锁；

那如果数据库有一个长事务（所谓的长事务，就是开启了事务，但是一直还没提交），那在对表结构做变更操作的时候，可能会发生意想不到的事情，比如下面这个顺序的场景：
1.首先，线程 A 先启用了事务（但是一直不提交），然后执行一条 select 语句，此时就先对该表加上 MDL 读锁；
2.然后，线程 B 也执行了同样的 select 语句，此时并不会阻塞，因为「读读」并不冲突；
3.接着，线程 C 修改了表字段，此时由于线程 A 的事务并没有提交，也就是 MDL 读锁还在占用着，这时线程 C 就无法申请到 MDL 写锁，就会被阻塞，

那么在线程 C 阻塞后，后续有对该表的 select 语句，就都会被阻塞，如果此时有大量该表的 select 语句的请求到来，就会有大量的线程被阻塞住，这时数据库的线程很快就会爆满了。
为什么线程 C 因为申请不到 MDL 写锁，而导致后续的申请读锁的查询操作也会被阻塞？
这是因为申请 MDL 锁的操作会形成一个队列，队列中写锁获取优先级高于读锁，一旦出现 MDL 写锁等待，会阻塞后续该表的所有 CRUD 操作。
所以为了能安全的对表结构进行变更，在对表结构变更前，先要看看数据库中的长事务，是否有事务已经对表加上了 MDL 读锁，如果可以考虑 kill 掉这个长事务，然后再做表结构的变更。

意向锁

当对表添加表锁时，如果已经存在 S 共享锁或 X 排他锁时，是否可以直接加表锁呢？

比如：
A会话申请到行锁，正在对某一行数据进行更新操作，这时另一个会话申请MDL表锁，申请成功后，执行表结构修改操作，这个操作可能会影响当前正在执行的更新操作，也就是这两个锁是互斥的，当存在行锁时，不能直接添加表锁。
那么是否需要在添加表锁时，逐行查看哪些行存在行锁吗，当表数据量大时，这种操作非常耗时，所以引入了意向锁：

IS（ Intention Shared Lock），共享意向锁
IX （ Intention Exclusive Lock ），独占意向锁。

这两个锁是表级别的锁，当需要对表中的某条记录上 S 锁的时候，先在表上加个 IS 锁，表明此时表内有 S 锁。当需要对表中的某条记录上 X 锁的时候，先在表上加个 IX 锁，表明此时表内有 X 锁。
这样操作之后，如果要加表锁，就不需要遍历所有记录去找了，直接看看表上面有没有 IS 和 IX 锁。
比如，此时要上表级别的 S 锁，如果表上没有 IX ，说明表中没有记录有独占锁，其实就可以直接上表级 S 锁。
如果此时要上表级别的 X 锁，如果表上没有 IX 和 IS ，说明表中的所有记录都没加锁，其实就可以直接上表级 X 锁。
因此 IS 和 IX 的作用就是在上表级锁的时候，可以快速判断是否可以上锁，而不需要遍历表中的所有记录。
所以，意向锁的目的是为了快速判断表里是否有记录被加锁。

自增( AUTO-INC ) 锁

在为某个字段声明 AUTO_INCREMENT 属性时，之后可以在插入数据时，可以不指定该字段的值，数据库会自动给该字段赋值递增的值，这主要是通过 AUTO-INC 锁实现的。
AUTO-INC 锁是特殊的表锁机制，锁不是在一个事务提交后才释放，而是再执行完插入语句后就会立即释放。
在插入数据时，会加一个表级别的 AUTO-INC 锁，然后为被 AUTO_INCREMENT 修饰的字段赋值递增的值，等插入语句执行完成后，才会把 AUTO-INC 锁释放掉。
那么，一个事务在持有 AUTO-INC 锁的过程中，其他事务的如果要向该表插入语句都会被阻塞，从而保证插入数据时，被 AUTO_INCREMENT 修饰的字段的值是连续递增的。
但是， AUTO-INC 锁在对大量数据进行插入的时候，会影响插入性能，因为另一个事务中的插入会被阻塞。
因此， 在 MySQL 5.1.22 版本开始，InnoDB 存储引擎提供了一种轻量级的锁来实现自增。
一样也是在插入数据的时候，会为被 AUTO_INCREMENT 修饰的字段加上轻量级锁，然后给该字段赋值一个自增的值，就把这个轻量级锁释放了，而不需要等待整个插入语句执行完后才释放锁。
InnoDB 存储引擎提供了个 innodb_autoinc_lock_mode 的系统变量，是用来控制选择用 AUTO-INC 锁，还是轻量级的锁。
1.当 innodb_autoinc_lock_mode = 0，就采用 AUTO-INC 锁；
2.当 innodb_autoinc_lock_mode = 2，就采用轻量级锁；
3.当 innodb_autoinc_lock_mode = 1，这个是默认值，两种锁混着用，如果能够确定插入记录的数量就采用轻量级锁，不确定时就采用 AUTO-INC 锁。
不过，当 innodb_autoinc_lock_mode = 2 是性能最高的方式，但是会带来一定的问题。因为并发插入的存在，在每次插入时，自增长的值可能不是连续的，这在有主从复制的场景中是不安全的。

行锁分类

MySQL 的行锁是在引擎层由各个引擎自己实现的， 且锁定颗粒度在 MySQL 中是最小的，比如 MyISAM 引擎就不支持行锁，行级锁主要应用于 InnoDB 存储引擎。
不支持行锁意味着并发控制只能使用表锁，对于这种引擎的表，同一张表上任何时刻只能有一个更新在执行，这就会影响到并发度，只针对操作的当前行进行加锁，所以行级锁发生锁定资源争用的概率也最小。
比如事务 A 更新了一行，而这时候事务 B 也要更新同一行，则必须等事务 A 的操作完成后才能进行更新。

行级锁的类型主要有三类：
1.Record Lock，记录锁，也就是仅仅把一条记录锁上；
2.Gap Lock，间隙锁，锁定一个范围，但是不包含记录本身；
3.Next-Key Lock：Record Lock + Gap Lock 的组合，锁定一个范围，并且锁定记录本身。

记录锁(Record Locks)

属于为行锁，表示对某一行记录加锁。

例如：
id 列为主键列或唯一索引列

SELECT * FROM test WHERE id = 10 FOR UPDATE;

记录锁总是锁定索引记录（SELECT和UPDATE都会加锁），即使表没有定义索引。对于这种情况， InnoDB创建一个隐藏的聚集索引并使用该索引进行记录锁定。但因为可能会扫描全表，那么该锁也就会退化为表锁。
注意：
1.id列必须为唯一索引或主键列，否则上述语句加的锁会变成临键锁。
2.查询语句必须为精准匹配=，不能>、<、like等，否则也会退化成临键锁。

间隙锁 (Gap Locks)

如何解决幻读问题？
前面讲的记录锁是加到已经存在的记录上，但是如果要给此时还未存在的记录加锁怎么办？也就是要预防幻读的出现，这需要使用间隙锁。
比如此时有 1、3、5、10 这四条记录，数据页中还有两条虚拟的记录，分别是 Infimum 和 Supremum。

可以看到，记录之前都有间隙，间隙锁锁的就是这个间隙。
比如把3和5之间的间隙锁了，此时要插入 id = 4 的记录，就会被这个间隙锁给阻塞了，这样就避免了幻读的产生！也就实现了锁定未插入的记录的需求！
间隙锁(Gap Locks)是对索引（非唯一索引）记录之间的间隙，锁定一个区间：加在两个索引之间，或者加在第一个索引之前，或者加在最后一个索引之后的间隙。
这里还值得注意的是，间隙锁只阻止其他事务插入到间隙中，并不阻止其他事务在同一个间隙上获得间隙锁，所以 gap x lock 和 gap s lock 有相同的作用，即不同的事务可以在间隙上持有冲突的锁。 例如，事务 A 可以在间隙上持有共享间隙锁（间隙 S 锁），而事务 B 在同一间隙上持有排他间隙锁（间隙 X 锁）。允许冲突间隙锁的原因是，如果从索引中清除记录，则必须合并不同事务在记录上持有的间隙锁。

如何显示禁用 间隙锁？
间隙锁是在事务隔离级别为可重复读的时候生效的，如果将事务隔离级别更改为

READ COMMITTED
，就会禁用了，此时，间隙锁对于搜索和索引扫描是禁用的，仅用于外键约束检查和重复键检查。

总结：
1.间隙锁锁区间的索引，使用唯一索引搜索唯一行不需要间隙锁定。
2.在READ COMMITTED（RC）隔离级别下，不会使用gap lock，在RR级别及以上（Serializable）才会使用它。
3.间隙锁可以共存。一个事务采用的间隙锁不会阻止另一个事务在同一间隙上采用间隙锁。共享和排他间隙锁之间没有区别。它们彼此不冲突，并且执行相同的功能。
临界锁 (Next-Key)

临键锁(Next-Key)简单理解是 “记录锁+间隙锁” 的组合，但Next-Key lock与record lock加锁的粒度一样，都是加在一条索引记录上的。一个next-key lock=对应的索引记录的record lock+该索引前面的间隙的gap lock，通过临键锁可以解决幻读的问题。
默认情况下，InnoDB在 REPEATABLE READ事务隔离级别运行，在这种情况下，InnoDB使用临键锁进行搜索和索引扫描，以防止幻像行，比如select … in share mode或者select … for update语句。但即使你的隔离级别是RR，如果你这是使用普通的select语句，那么InnoDB将是快照读，不会使用任何锁，因而还是无法防止幻读。
总结：InnoDB在RR事务隔离级别下，在根据非唯一索引对记录行进行UPDATE \ FOR UPDATE \ LOCK IN SHARE MODE 操作时，InnoDB 会获取该记录行的临键锁 ，并同时获取该记录行下一个区间间隙锁。

全局读锁(FTWRL)

对整个数据库实例加锁。
这个命令可以使整个库处于只读状态。
使用该命令之后，数据更新语句、数据定义语句和更新类事务的提交语句等操作都会被阻塞。使用场景：全库逻辑备份。
Flush tables with read lock
如果要释放全局锁，则要执行这条命令：
unlock tables
当然，当会话断开了，全局锁会被自动释放。

风险：
1.如果在主库备份，在备份期间不能更新，业务停摆
2.如果在从库备份，备份期间不能执行主库同步的binlog，导致主从延迟
官方自带的逻辑备份工具mysqldump，当mysqldump使用参数–single-transaction的时候，会启动一个事务，确保拿到一致性视图。而由于MVCC的支持，这个过程中数据是可以正常更新的。
-single-transaction 命令加锁
官方自带的逻辑备份工具是 mysqldump。当 mysqldump 使用参数 –single-transaction 的时候，导数据之前就会启动一个事务，来确保拿到一致性视图。而由于 MVCC 的支持，这个过程中数据是可以正常更新的。

页锁

页级锁是MySQL中比较独特的一种锁定级别。页级锁定的特点是锁定颗粒度介于行级锁定与表级锁之间，所以获取锁定所需要的资源开销，以及所能提供的并发处理能力也介于表级锁和行级锁中间。 使用页级锁定的主要是 BerkeleyDB 存储引擎。

锁分类总结

按照锁定范围 (粒度)

全局锁。
表锁：元数据锁 (MDL) 、意向锁、 AUTO-INC 锁。
页锁。
行锁 : 记录锁 (Record Locks) 、间隙锁 (Gap Locks) 、临界锁 (Next Key Locks) 。

按照锁的属性

共享锁 ( 读锁 ) 。
排他锁 ( 写锁 ) 。

按照锁的意愿

1. 悲观锁
   当要对数据库中的一条数据进行修改的时候，为了避免同时被其他人修改，最好的办法就是直接对该数据进行加锁以防止并发。这种借助数据库锁机制，在修改数据之前先锁定，再修改的方式被称之为悲观并发控制【 Pessimistic Concurrency Control ，缩写“ PCC ”，又名“悲观锁”】。

2. 乐观锁
   乐观锁是相对悲观锁而言的，乐观锁假设数据一般情况不会造成冲突，所以在数据进行提交更新的时候，才会正式对数据的冲突与否进行检测，如果冲突，则返回给用户异常信息，让用户决定如何去做。乐观锁适用于读多写少的场景，这样可以提高程序的吞吐量。

锁测试

MDL元数据锁问题分析

—会话4

mysql> select connection_id();
+-----------------+
| connection_id() |
+-----------------+
|               4 |
+-----------------+
1 row in set (0.00 sec)

—执行慢查询SQL，会加元数据读锁MDL。

mysql> select sleep(100) from t1;
mysql> select * from performance_schema.metadata_locks;
+-------------+--------------------+----------------+-----------------------+-------------+---------------+-------------+-------------------+-----------------+----------------+
| OBJECT_TYPE | OBJECT_SCHEMA      | OBJECT_NAME    | OBJECT_INSTANCE_BEGIN | LOCK_TYPE   | LOCK_DURATION | LOCK_STATUS | SOURCE            | OWNER_THREAD_ID | OWNER_EVENT_ID |
+-------------+--------------------+----------------+-----------------------+-------------+---------------+-------------+-------------------+-----------------+----------------+
| TABLE       | cjc                | t1             |       140735273864736 | SHARED_READ | TRANSACTION   | GRANTED     | sql_parse.cc:6020 |              34 |             19 |
| TABLE       | performance_schema | metadata_locks |       140734804093088 | SHARED_READ | TRANSACTION   | GRANTED     | sql_parse.cc:6020 |              37 |             16 |
+-------------+--------------------+----------------+-----------------------+-------------+---------------+-------------+-------------------+-----------------+----------------+
2 rows in set (0.00 sec)

查询thread_id和processlist_id对应关系

mysql> select name,thread_id,processlist_id ,thread_os_id from performance_schema.threads where thread_id in (34);
+---------------------------+-----------+----------------+--------------+
| name                      | thread_id | processlist_id | thread_os_id |
+---------------------------+-----------+----------------+--------------+
| thread/sql/one_connection |        34 |              4 |         4179 |
+---------------------------+-----------+----------------+--------------+
1 row in set (0.00 sec)

—会话5

mysql> select connection_id();
+-----------------+
| connection_id() |
+-----------------+
|               5 |
+-----------------+
1 row in set (0.00 sec)

—可以正常查询数据

mysql> select * from t1;
+----+------+
| id | name |
+----+------+
|  1 | cjc  |
+----+------+
1 row in set (0.00 sec)

—会话6

mysql> select connection_id();
+-----------------+
| connection_id() |
+-----------------+
|               6 |
+-----------------+
1 row in set (0.00 sec)

—新增字段，卡住

mysql> alter table t1 add col1 int;

—会话5

mysql> select connection_id();
+-----------------+
| connection_id() |
+-----------------+
|               5 |
+-----------------+
1 row in set (0.00 sec)

—此时会话5，再次执行查询会卡住，被阻塞
这是因为申请 MDL 锁的操作会形成一个队列，队列中写锁获取优先级高于读锁，一旦出现 MDL 写锁等待，会阻塞后续该表的所有CRUD操作。

mysql> select * from t1;

—会话7

mysql> select connection_id();
+-----------------+
| connection_id() |
+-----------------+
|               7 |
+-----------------+
1 row in set (0.00 sec)

—processlist查看信息

mysql> show processlist;
+----+-----------------+-----------+--------------------+---------+------+---------------------------------+-----------------------------+
| Id | User            | Host      | db                 | Command | Time | State                           | Info                        |
+----+-----------------+-----------+--------------------+---------+------+---------------------------------+-----------------------------+
|  1 | event_scheduler | localhost | NULL               | Daemon  |  431 | Waiting on empty queue          | NULL                        |
|  4 | root            | localhost | cjc                | Query   |   87 | User sleep                      | select sleep(100) from t1   |
|  5 | root            | localhost | cjc                | Query   |   52 | Waiting for table metadata lock | select * from t1            |
|  6 | root            | localhost | cjc                | Query   |   69 | Waiting for table metadata lock | alter table t1 add col1 int |
|  7 | root            | localhost | performance_schema | Query   |    0 | starting                        | show processlist            |
+----+-----------------+-----------+--------------------+---------+------+---------------------------------+-----------------------------+
5 rows in set (0.00 sec)

查询MDL信息

mysql> select * from performance_schema.metadata_locks;
+-------------+--------------------+----------------+-----------------------+---------------------+---------------+-------------+-------------------+-----------------+----------------+
| OBJECT_TYPE | OBJECT_SCHEMA      | OBJECT_NAME    | OBJECT_INSTANCE_BEGIN | LOCK_TYPE           | LOCK_DURATION | LOCK_STATUS | SOURCE            | OWNER_THREAD_ID | OWNER_EVENT_ID |
+-------------+--------------------+----------------+-----------------------+---------------------+---------------+-------------+-------------------+-----------------+----------------+
| TABLE       | cjc                | t1             |       140735273864736 | SHARED_READ         | TRANSACTION   | GRANTED     | sql_parse.cc:6020 |              34 |             18 |
| GLOBAL      | NULL               | NULL           |       140735005409760 | INTENTION_EXCLUSIVE | STATEMENT     | GRANTED     | sql_base.cc:5533  |              36 |             23 |
| SCHEMA      | cjc                | NULL           |       140735005409504 | INTENTION_EXCLUSIVE | TRANSACTION   | GRANTED     | sql_base.cc:5518  |              36 |             23 |
| TABLE       | cjc                | t1             |       140735005408624 | SHARED_UPGRADABLE   | TRANSACTION   | GRANTED     | sql_parse.cc:6020 |              36 |             23 |
| TABLE       | cjc                | t1             |       140735005431216 | EXCLUSIVE           | TRANSACTION   | PENDING     | mdl.cc:3919       |              36 |             23 |
| TABLE       | cjc                | t1             |       140734938293072 | SHARED_READ         | TRANSACTION   | PENDING     | sql_parse.cc:6020 |              35 |             12 |
| TABLE       | performance_schema | metadata_locks |       140734804093088 | SHARED_READ         | TRANSACTION   | GRANTED     | sql_parse.cc:6020 |              37 |             13 |
+-------------+--------------------+----------------+-----------------------+---------------------+---------------+-------------+-------------------+-----------------+----------------+
7 rows in set (0.00 sec)

查询thread_id和processlist_id对应关系

mysql> select name,thread_id,processlist_id ,thread_os_id from performance_schema.threads where thread_id in (34,35,36,37);
+---------------------------+-----------+----------------+--------------+
| name                      | thread_id | processlist_id | thread_os_id |
+---------------------------+-----------+----------------+--------------+
| thread/sql/one_connection |        34 |              4 |         4179 |
| thread/sql/one_connection |        35 |              5 |         4289 |
| thread/sql/one_connection |        36 |              6 |         4355 |
| thread/sql/one_connection |        37 |              7 |         4464 |
+---------------------------+-----------+----------------+--------------+
4 rows in set (0.00 sec)

可以看到thread_id=36，processlist_id=6和thread_id=35，processlist_id=5的LOCK_STATUS为PENDING，被阻塞。
如果查询performance_schema.metadata_locks没有数据，需要开启锁监控。
—metadata_locks没有MDL元数据锁信息

mysql> select * from performance_schema.metadata_locks;
Empty set (0.00 sec)

—检查锁监控没有开启

mysql> select * from performance_schema.setup_instruments where name like '%lock%' limit 20;

metadata_locks是5.7中被引入，记录了metadata lock的相关信息，包括持有对象、类型、状态等信息。
但5.7默认设置是关闭的（8.0默认打开），需要通过下面命令打开设置：
—开启锁监控

mysql> update performance_schema.setup_instruments set enabled = 'YES' where  name like '%lock%';
Query OK, 175 rows affected (0.00 sec)
Rows matched: 182  Changed: 175  Warnings: 0

如果要永久生效，需要在配置文件中加入如下内容：

[mysqld]
performance-schema-instrument='wait/lock/metadata/sql/mdl=ON'

元数据锁MDL等待超时时间，默认1年。

mysql> show variables like 'lock_wait_timeout';
+-------------------+----------+
| Variable_name     | Value    |
+-------------------+----------+
| lock_wait_timeout | 31536000 |
+-------------------+----------+
1 row in set (0.00 sec)

分析锁源头

select
    locked_schema,
    locked_table,
    locked_type,
    waiting_processlist_id,
    waiting_age,
    waiting_query,
    waiting_state,
    blocking_processlist_id,
    blocking_age,
    substring_index(sql_text,"transaction_begin;" ,-1) as blocking_query,
    sql_kill_blocking_connection
from
    (
        select
            b.owner_thread_id as granted_thread_id,
            a.object_schema as locked_schema,
            a.object_name as locked_table,
            "metadata lock" as locked_type,
            c.processlist_id as waiting_processlist_id,
            c.processlist_time as waiting_age,
            c.processlist_info as waiting_query,
            c.processlist_state as waiting_state,
            d.processlist_id as blocking_processlist_id,
            d.processlist_time as blocking_age,
            d.processlist_info as blocking_query,
            concat('kill ', d.processlist_id) as sql_kill_blocking_connection
        from
            performance_schema.metadata_locks a
        join performance_schema.metadata_locks b on a.object_schema = b.object_schema
        and a.object_name = b.object_name
        and a.lock_status = 'pending'
        and b.lock_status = 'granted'
        and a.owner_thread_id <> b.owner_thread_id
        and a.lock_type = 'exclusive'
        join performance_schema.threads c on a.owner_thread_id = c.thread_id
        join performance_schema.threads d on b.owner_thread_id = d.thread_id
    ) t1,
    (
        select
            thread_id,
            group_concat(   case when event_name = 'statement/sql/begin' then "transaction_begin" else sql_text end order by event_id separator ";" ) as sql_text
        from
           performance_schema.events_statements_history
        group by thread_id
    ) t2
where
    t1.granted_thread_id = t2.thread_id \G;

结果如下：

*************************** 1. row ***************************
               locked_schema: cjc
                locked_table: t1
                 locked_type: metadata lock
      waiting_processlist_id: 6
                 waiting_age: 6
               waiting_query: alter table t1 add col1 int
               waiting_state: Waiting for table metadata lock
     blocking_processlist_id: 4
                blocking_age: 12
              blocking_query: alter table t1 drop col1;alter table t1 drop col2;alter table t1 drop col5;select * from t1;select connection_id();select sleep(100) from t1;select sleep(100) from t1;select sleep(100) from t1;select sleep(100) from t1;select sleep(100) from t1
sql_kill_blocking_connection: kill 4
1 row in set (0.00 sec)

可以看到

processlist_id=4 是阻塞的源头，阻塞了processlist_id=6的alter table t1 add col1 int操作。

如果是dml操作，例如delete是阻塞源头，查询信息类似如下：

*************************** 1. row ***************************
               locked_schema: cjc
                locked_table: t1
                 locked_type: metadata lock
      waiting_processlist_id: 6
                 waiting_age: 74
               waiting_query: alter table t1 add col2 int
               waiting_state: Waiting for table metadata lock
     blocking_processlist_id: 4
                blocking_age: 92
              blocking_query: delete from t1
sql_kill_blocking_connection: kill 4
1 row in set (0.00 sec)

MDL阻塞场景：

场景一：
在执行下面增、删、改、查时，会加MDL读锁，如果这些执行较慢或没有及时提交，

select id from t1 where ...;
delete from t1 where ...;
update t1 set id=xx where xxx;
insert into t1 values(...);

会阻塞其他会话的DDL操作，
例如添加列、删除列、修改列、创建索引、清空表等操作。

alter table t1 add col1 int;
alter table t1 drop col1;
alter table t1 modify name char(20);
create index i_t1_id on t1(id);
truncate table t1;

总结：
增、删、改、查时会添加元数据MDL读锁，这个锁不会阻塞其他会话的查询，也不会阻塞其他会话的其他行的增、删、改操作。
如果其他会话开始执行DDL，由于已经有元数据MDL读锁，索引无法申请到元数据MDL写锁，所以会阻塞DDL操作，
并且由于在MDL申请队列里，MDL写锁优先级别较高，所以正在等待的DDL操作又会阻塞其他会话对表的增、删、改、查等操作。

场景二：
创建测试数据

mysql> insert into t1 select * from t1;
......
mysql> select count(*) from t1;
+----------+
| count(*) |
+----------+
| 16777216 |
+----------+
1 row in set (42.41 sec)

添加字段，耗时长

mysql> alter table t1 add col1 char(255) default 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx';

查看对应MDL信息

mysql> select * from performance_schema.metadata_locks;
+-------------+--------------------+----------------+-----------------------+---------------------+---------------+-------------+-------------------+-----------------+----------------+
| OBJECT_TYPE | OBJECT_SCHEMA      | OBJECT_NAME    | OBJECT_INSTANCE_BEGIN | LOCK_TYPE           | LOCK_DURATION | LOCK_STATUS | SOURCE            | OWNER_THREAD_ID | OWNER_EVENT_ID |
+-------------+--------------------+----------------+-----------------------+---------------------+---------------+-------------+-------------------+-----------------+----------------+
| GLOBAL      | NULL               | NULL           |       140735273869632 | INTENTION_EXCLUSIVE | STATEMENT     | GRANTED     | sql_base.cc:5533  |              34 |             81 |
| SCHEMA      | cjc                | NULL           |       140735273864736 | INTENTION_EXCLUSIVE | TRANSACTION   | GRANTED     | sql_base.cc:5518  |              34 |             81 |
| TABLE       | cjc                | t1             |       140735283470544 | SHARED_UPGRADABLE   | TRANSACTION   | GRANTED     | sql_parse.cc:6020 |              34 |             81 |
| TABLE       | performance_schema | metadata_locks |       140734804148624 | SHARED_READ         | TRANSACTION   | GRANTED     | sql_parse.cc:6020 |              37 |             41 |
+-------------+--------------------+----------------+-----------------------+---------------------+---------------+-------------+-------------------+-----------------+----------------+
4 rows in set (0.00 sec)

不会阻塞增、删、改、查操作

mysql> select * from t1 where id=1000;
+------+------+
| id   | name |
+------+------+
| 1000 | cjc  |
+------+------+
1 row in set (0.00 sec)
mysql> insert into t1 values(3,'aaa');
Query OK, 1 row affected (0.81 sec)
mysql> update t1 set id=100 where id=0;
Query OK, 0 rows affected (0.52 sec)
Rows matched: 0  Changed: 0  Warnings: 0
mysql> delete from t1 where id=0;
Query OK, 0 rows affected (0.03 sec)

会阻塞DDL操作

mysql> alter table t1 add col2 int;

卡住

mysql> select * from performance_schema.metadata_locks;
+-------------+--------------------+----------------+-----------------------+---------------------+---------------+-------------+-------------------+-----------------+----------------+
| OBJECT_TYPE | OBJECT_SCHEMA      | OBJECT_NAME    | OBJECT_INSTANCE_BEGIN | LOCK_TYPE           | LOCK_DURATION | LOCK_STATUS | SOURCE            | OWNER_THREAD_ID | OWNER_EVENT_ID |
+-------------+--------------------+----------------+-----------------------+---------------------+---------------+-------------+-------------------+-----------------+----------------+
| GLOBAL      | NULL               | NULL           |       140735273869632 | INTENTION_EXCLUSIVE | STATEMENT     | GRANTED     | sql_base.cc:5533  |              34 |             81 |
| SCHEMA      | cjc                | NULL           |       140735273864736 | INTENTION_EXCLUSIVE | TRANSACTION   | GRANTED     | sql_base.cc:5518  |              34 |             81 |
| TABLE       | cjc                | t1             |       140735283470544 | SHARED_UPGRADABLE   | TRANSACTION   | GRANTED     | sql_parse.cc:6020 |              34 |             81 |
| GLOBAL      | NULL               | NULL           |       140735005409760 | INTENTION_EXCLUSIVE | STATEMENT     | GRANTED     | sql_base.cc:5533  |              36 |             44 |
| SCHEMA      | cjc                | NULL           |       140735005429680 | INTENTION_EXCLUSIVE | TRANSACTION   | GRANTED     | sql_base.cc:5518  |              36 |             44 |
| TABLE       | cjc                | t1             |       140735005431216 | SHARED_UPGRADABLE   | TRANSACTION   | PENDING     | sql_parse.cc:6020 |              36 |             44 |
| TABLE       | performance_schema | metadata_locks |       140734804148624 | SHARED_READ         | TRANSACTION   | GRANTED     | sql_parse.cc:6020 |              37 |             42 |
+-------------+--------------------+----------------+-----------------------+---------------------+---------------+-------------+-------------------+-----------------+----------------+
7 rows in set (0.35 sec)
mysql> show processlist;
+----+-----------------+-----------+--------------------+---------+------+---------------------------------+----------------------------------------------------------------------------------------------+
| Id | User            | Host      | db                 | Command | Time | State                           | Info                                                                                         |
+----+-----------------+-----------+--------------------+---------+------+---------------------------------+----------------------------------------------------------------------------------------------+
|  1 | event_scheduler | localhost | NULL               | Daemon  | 8634 | Waiting on empty queue          | NULL                                                                                         |
|  4 | root            | localhost | cjc                | Query   |  227 | altering table                  | alter table t1 add col1 char(255) default 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx' |
|  5 | root            | localhost | cjc                | Sleep   |  187 |                                 | NULL                                                                                         |
|  6 | root            | localhost | cjc                | Query   |   91 | Waiting for table metadata lock | alter table t1 add col2 int                                                                  |
|  7 | root            | localhost | performance_schema | Sleep   |   88 |                                 | NULL                                                                                         |
| 12 | root            | localhost | NULL               | Query   |    0 | starting                        | show processlist                                                                             |
+----+-----------------+-----------+--------------------+---------+------+---------------------------------+----------------------------------------------------------------------------------------------+
6 rows in set (0.50 sec)

总结：
添加字段、删除字段等DDL操作，不会阻塞其他会话的增、删、改、查操作，会阻塞其他会话的DDL操作。

参考：

https://www.zhihu.com/question/422489306
https://zhuanlan.zhihu.com/p/385693319
https://blog.csdn.net/songguojiebd/article/details/127930451
https://www.cnblogs.com/ilovebath/p/15893673.html

欢迎关注我的公众号《IT小Chen》