大家好，我是小林。
昨天在群里看到大家在讨论一个 MySQL 锁的问题，就是执行 select … for update 语句，如果查询条件没有索引字段的话，是加「行锁」还是加「表锁」？
如果你做过这个实验的话，你会发现执行 select … for update 语句的时候，如果查询条件没有索引字段的话，整张表都无法进行增删改了，从这个现象看，好像是把表锁起来了，那难道是因为表锁的原因吗？
先不着急说结论。
MySQL 有提供分析数据表加了什么锁的命令，我们就通过这种方式来看看具体加的是什么锁，才导致整张表都无法进行增删改了。
做好准备
为了方便后续故事的展开，先创建一张 t_user 表。
表里有一个主键索引（id 字段），其他字段都不是索引字段，而是普通字段，表里面有下面这三条记录。
一条 select 语句会加什么锁？
不知道大家有没有好奇过，执行一条 select 查询语句会加什么锁呢？
相信大家都知道普通的 select 查询（快照读）语句是不会加行级锁（Innodb 层的锁），因为它是通过 MVCC 技术实现的无锁查询。
要验证这个结论也很简单，在 MySQL 8.0 以上的版本，可以执行 select * from performance_schema.data_locks\G; 这条语句，查看 Innodb 存储引擎为事务加了什么锁。
假设事务 a 执行了这条普通 select 的查询语句：
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from t_user where age < 20;
+----+--------+-----+------------+
| id | name | age | reward |
+----+--------+-----+------------+
| 1 | 路飞 | 19 | 3000000000 |
+----+--------+-----+------------+
1 row in set (0.00 sec)
mysql>
select * from performance_schema.data_locks\G; 这条语句，输出结果如下：
可以看到，输出结果是空，说明普通 select 的查询语句， Innodb 存储引擎不会为事务加任何锁。
那难道什么锁都不加吗？
当然不是的。
当我们对数据库表进行 DML 和 DDL 操作的时候，MySQL 会给这个表加上 MDL 锁，即元数据锁，MDL 锁是 server 层实现的表级锁，适用于所有存储引擎。
- 对一张表进行增删查改操作（DML 操作）的时候，加的是 MDL 读锁；
- 对一张表进行表结构变更操作（DDL 操作）的时候，加的是 MDL 写锁；
之所以需要 MDL 锁，就是因为事务执行的时候，不能发生表结构的改变，否则就会导致同一个事务中，出现混乱的现象，如果当前有事务持有 MDL 读锁，DDL 操作就不能申请 MDL 写锁，从而保证表元数据的数据一致性。
MDL 的读锁与写锁满足读读共享，读写互斥，写写互斥的关系，比如：
- 读读共享：MDL 读锁和 MDL 读锁之间不会产生阻塞，就是说增删改查不会因为 MDL 读锁产生而阻塞，可以并发执行，如果不是这样，数据库就是串行操作了；
- 读写互斥：MDL 读锁和 MDL 写锁之间相互阻塞，即同一个表上的 DML 和 DDL 之间互相阻塞；
- 写写互斥：MDL 写锁和 MDL 写锁之间互相阻塞，即两个 session 不能同时对一张表结构做变更操作，需要串行操作；
如果在工作中，发现很多会话执行的 SQL 提示”Waiting for table metadata lock”的等待，这时候就是因为 MDL 的读锁与写锁发生冲突了，如果要应急解决问题，这时候就要考虑 kill 掉持有 MDL 锁的事务了，因为 MDL 锁是在事务提交后才会释放，这意味着事务执行期间，MDL 锁是一直持有的。
如何查看事务是否持有 MDL 锁？
在前面，我们的事物 A 执行了普通 select 查询语句，如果要看该事务持有的 MDL 锁，可以通过这条命令 select * from performance_schema.metadata_locks;。
可以看到，事务 A 此时持有一个表级别的 MDL 锁，锁的类型是 SHARED_READ，也就是 MDL 读锁。
对于，增删改操作，申请的 MDL 锁的类型是 SHARED_WRITE，它也属于 MDL 读锁，因为 SHARED_WRITE 与 SHARED_READ 这两个锁的类型是相互兼容的。
因此，我们常说的普通查询不加锁，其实指的是不加 Innodb 的行级锁，但实际上是需要持有 MDL 锁的。
一条 select … for update 会加什么锁？
select ... for update 语句属于锁定读语句，它会对表的记录加 X 型的行级锁。
不同隔离级别下，行级锁的种类是不同的。
在读已提交隔离级别下，行级锁的种类只有记录锁，也就是仅仅把一条记录锁上。
在可重复读隔离级别下，行级锁的种类除了有记录锁，还有间隙锁（目的是为了避免幻读），所以行级锁的种类主要有三类：
- Record Lock，记录锁，也就是仅仅把一条记录锁上；
- Gap Lock，间隙锁，锁定一个范围，但是不包含记录本身；
- Next-Key Lock：Record Lock + Gap Lock 的组合，锁定一个范围，并且锁定记录本身。
行级锁加锁规则比较复杂，不同的场景，加锁的形式是不同的。
加锁的对象是索引，加锁的基本单位是 next-key lock，它是由记录锁和间隙锁组合而成的，next-key lock 是前开后闭区间，而间隙锁是前开后开区间。
但是，next-key lock 在一些场景下会退化成记录锁或间隙锁。
那到底是什么场景呢？总结一句，在能使用记录锁或者间隙锁就能避免幻读现象的场景下， next-key lock 就会退化成记录锁或间隙锁。之前也写过一篇详细介绍了不同的场景下， 行级锁的加锁规则，参见：保姆级教程！2 万字 + 30 张图搞懂 MySQL 是怎么加行级锁的？
这次我们只讨论，执行 select … for update 语句，如果查询条件没有索引字段的话，会加什么锁？
现在假设事务 A 执行了下面这条语句，查询条件中 age 不是索引字段。
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from t_user where age < 20 for update;
+----+-----------+-----+------------+
| id | name | age | reward |
+----+-----------+-----+------------+
| 1 | 路飞 | 19 | 3000000000 |
+----+-----------+-----+------------+
1 rows in set (0.00 sec)
这时候有其他事务对这张表进行增删改，都会发生阻塞。
先来看看，事务 A 持有什么类型的 MDL 锁？
可以执行 select * from performance_schema.metadata_locks\G; 这条语句，查看事务 A 此时持有了有什么类型的 MDL 锁。
执行结果如下：
可以看到，事务 A 此时持有一个表级别的 MDL 锁，锁的类型是 SHARED_WRITE，属于 MDL 读锁。
而在前面我提到过，当事务对表进行增删查改操作的时候，事务会申请 MDL 读锁，而 MDL 读锁之间是相互兼容的。
所以，当事务 A 执行了查询条件没有索引字段的 select … for update 语句后，不可能是因为事务 A 持 MDL 读锁，才导致其他事务无法进行增删改操作。
再来看看，事务 A 持有哪些行级锁？
可以执行 select * from performance_schema.data_locks\G; 这条语句，查看事务 A 此时持有了哪些行级锁。
输出结果如下，我删减了不必要的信息：
*************************** 1. row ***************************
ENGINE: INNODB //存储引擎
................
OBJECT_NAME: t_user //表名称
................
INDEX_NAME: NULL //索引名称，表示在哪个索引加的锁
................
LOCK_TYPE: TABLE //锁的类型：TABLE 表示「表级锁」
LOCK_MODE: IX //锁的模式：IX 表示「X型的意向锁」
LOCK_STATUS: GRANTED //锁的状态，GRANTED 表示成功获取到锁
LOCK_DATA: NULL //锁住的数据，表示在哪条索引记录加的锁
*************************** 2. row ***************************
ENGINE: INNODB //存储引擎
................
OBJECT_NAME: t_user //表名称
................
INDEX_NAME: PRIMARY //索引名称，PRIMARY 表示在主键索引加了锁
LOCK_TYPE: RECORD //锁的类型：RECORD 表示「行级锁」
LOCK_MODE: X //锁的模式：X 表示「X型的行级锁」
LOCK_STATUS: GRANTED //锁的状态，GRANTED 表示成功获取到锁
LOCK_DATA: 1 //锁住的数据，表示在 id 为 1 的记录加了「X型的行级锁」
*************************** 3. row ***************************
ENGINE: INNODB //存储引擎
................
OBJECT_NAME: t_user //表名称
................
INDEX_NAME: PRIMARY //索引名称，PRIMARY 表示在主键索引加了锁
LOCK_TYPE: RECORD //锁的类型：RECORD 表示「行级锁」
LOCK_MODE: X //锁的模式：X 表示「X型的行级锁」
LOCK_STATUS: GRANTED //锁的状态，GRANTED 表示成功获取到锁
LOCK_DATA: 2 //锁住的数据，表示在 id 为 2 的记录加了「X型的行级锁」
*************************** 4. row ***************************
ENGINE: INNODB //存储引擎
................
OBJECT_NAME: t_user //表名称
................
INDEX_NAME: PRIMARY //索引名称，PRIMARY 表示在主键索引加了锁
LOCK_TYPE: RECORD //锁的类型：RECORD 表示「行级锁」
LOCK_MODE: X //锁的模式：X 表示「X型的行级锁」
LOCK_STATUS: GRANTED //锁的状态，GRANTED 表示成功获取到锁
LOCK_DATA: 3 //锁住的数据，表示在 id 为 2 的记录加了「X型的行级锁」
*************************** 5. row ***************************
ENGINE: INNODB //存储引擎
................
OBJECT_NAME: t_user //表名称
................
INDEX_NAME: PRIMARY //索引名称，PRIMARY 表示在主键索引加了锁
LOCK_TYPE: RECORD //锁的类型：RECORD 表示「行级锁」
LOCK_MODE: X //锁的模式：X 表示「X型的行级锁」
LOCK_STATUS: GRANTED //锁的状态，GRANTED 表示成功获取到锁
LOCK_DATA: supremum pseudo-record //锁住的数据，表示在最后一条记录加了「X型的行级锁」
从上图可以看到，共加了两种类型的锁，分别是：
- 1 个表级锁：X 类型的意向锁（表级别的锁）；
- 4 个行级锁：X 类型的行级锁；
什么是意向锁？
在 InnoDB 存引擎中，当事务执行锁定读、插入、更新、删除操作后，需要先对表加上「意向锁」，然后再对记录加「行级锁」。
之所以要设计「意向锁」，目的是为了快速判断表里是否有行级锁，具体的说明参见：MySQL 全局锁、表级锁、行级锁，你搞清楚了吗？
意向锁不会和行级锁发生冲突，而且意向锁之间也不会发生冲突，意向锁只会和共享表锁（lock tables … read）和独占表锁（lock tables … write）发生冲突。
所以，当事务 A 执行了查询条件没有索引字段的 select … for update 语句后，不可能是因为事务 A 持有了意向锁，才导致其他事务无法进行增删改操作。
具体是哪 4 个行级锁？
图中 LOCK_TYPE 中的 RECORD 表示行级锁，而不是记录锁的意思：
- 如果 LOCK_MODE 为
X，说明是 X 型的 next-key 锁； - 如果 LOCK_MODE 为
X, REC_NOT_GAP，说明是 X 型的记录锁； - 如果 LOCK_MODE 为
X, GAP，说明是 X 型的间隙锁；
然后通过 LOCK_DATA 信息，可以确认 next-key 锁的范围，具体怎么确定呢？
根据我的经验，如果 LOCK_MODE 是 next-key 锁或者间隙锁，那么 LOCK_DATA 就表示锁的范围最右值，而锁范围的最左值为 LOCK_DATA 的上一条记录的值。
因此，此时事务 A 在主键索引（INDEX_NAME : PRIMARY）上加了 4 个 next-key 锁，如下：
- X 型的 next-key 锁，范围：(-∞, 1]
- X 型的 next-key 锁，范围：(1, 2]
- X 型的 next-key 锁，范围：(2, 3]
- X 型的 next-key 锁，范围：(3, +∞]
这相当于把整个表给锁住了，其他事务在对该表进行增、删、改操作的时候
都会被阻塞。只有在事务 A 提交了事务，事务 A 执行过程中产生的锁才会被释放。
为什么因为事务 A 对表所有记录加了 X 型的 next-key 锁后，其他事务就无法进行增、删、改操作了呢？
其他事务在执行「删除或者更新操作」的时候，也会申请 X 型的 next-key 锁，next-key 锁是包含记录锁和间隙锁的，间隙锁之间虽然是相互兼容的，但是记录锁之间存在 X 型和 S 型的关系，即读读共享、读写互斥、写写互斥的关系。
所以当事务 A 持有了 X 型的 next-key 锁后，其他事务就无法申请 X 型的 next-key 锁，从而发生阻塞。
比如，前面的例子，事务 B 在更新 id = 1 的记录的时候，它会申请 X 型的记录锁（唯一索引等值操作， next-key 锁会退化为记录锁），但是因为事务 A 持有了 X 型的 next-key 锁，所以事务 B 在申请 X 型的记录锁的时候，会发生阻塞。
我们也可以通过 select * from performance_schema.data_locks\G; 这条语句得知。
事务 C 的删除操作被阻塞的原因，也是这个原因。
事务 D 的插入操作被阻塞的原因，跟事务 B 和事务 C 的原因不同。
插入语句在插入一条记录之前，需要先定位到该记录在 B+树 的位置，如果插入的位置的下一条记录的索引上有间隙锁，如果已加间隙锁，此时会生成一个插入意向锁，然后锁的状态设置为等待状态，现象就是插入语句会被阻塞。
事务 D 插入了一条 id = 10 的新记录，在主键索引树上定位到插入的位置，而该位置的下一条记录是 supremum pseudo-record，该记录是一个特殊的记录，用来标识最后一条记录，而该特殊记录上正好持有了间隙锁（next-key 锁包含间隙锁），所以这条插入语句会发生阻塞。
我们也可以通过 select * from performance_schema.data_locks\G; 这条语句得知。
为什么只是查询年龄 20 岁以下的行记录，而把整个表给锁住了呢？
这是因为事务 A 的这条锁定读查询语句，没有使用索引列作为查询条件，所以扫描的方式是全表扫描，行级锁是在遍历索引的时候加上的，并不是针对输出的结果加行级锁。
不只是锁定读查询语句不加索引才会导致这种情况，update 和 delete 语句如果查询条件不加索引，那么由于扫描的方式是全表扫描，于是就会对每一条记录的索引上都会加 next-key 锁，这样就相当于锁住的全表。
因此，在线上在执行 update、delete、select … for update 等具有加锁性质的语句，一定要检查语句是否走了索引，如果是全表扫描的话，会对每一个索引加 next-key 锁，相当于把整个表锁住了，这是挺严重的问题。
如果数据量很大，还是一样的原因吗？
前面我们结论得出，如果如果锁定读查询语句，没有使用索引列作为查询条件，导致扫描是全表扫描。那么，每一条记录的索引上都会加 X 型的 next-key 锁（行级锁）。正是因为这个原因，才导致其他事务，无法对该表进行增删改操作。
那如果一张表的数据量超过几百万行，还是一样对每一条记录的索引上都会加 X 型的 next-key 锁吗？
群里有小伙伴提出了这个说法，说如果 MySQL 认为数据量太大时，自动将行所升级到表锁。
不着急说结论，我们直接做个实验。
我在 t_user 表插入了 300 多万条数据。
现在有个事务执行了这条查询语句，查询条件 age 字段不是索引字段。
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from t_user where age < 20 for update;
然后，我们执行 select * from performance_schema.data_locks\G; 这条语句（我执行了好长时间，至少有几十分钟）。
可以看到，每一条记录的索引上都会加 X 型的 next-key 锁（行级锁）。
所以，MySQL 认为数据量太大时，自动将行所升级到表锁 ，这句话并不准确。
总结
在执行 select … for update 语句的时候，会有产生 2 个表级别的锁：
- 一个是 Server 层表级别的锁：MDL 锁。事务在进行增删查改的时候，server 层申请 MDL 锁都是 MDL 读锁，而 MDL 读锁之间是相互兼容的，MDL 读锁只会和 MDL 写锁发生冲突，在对表结构进行变更操作的时候，才会申请 MDL 写锁。
- 一个是 Inoodb 层表级别的锁：意向锁。事务在进行增删改和锁定读的时候，inoodb 层会申请意向锁，意向锁不会和行级锁发生冲突，而且意向锁之间也不会发生冲突，意向锁只会和共享表锁（lock tables … read）和独占表锁（lock tables … write）发生冲突。
如果 select … for update 语句的查询条件没有索引字段的话，整张表都无法进行增删改了，从这个现象看，好像是把表锁起来了，但是并不是因为上面这两个表级锁的原因。
而是因为如果锁定读查询语句，没有使用索引列作为查询条件，导致扫描是全表扫描。那么，每一条记录的索引上都会加 next-key 锁（行级锁），这样就相当于锁住的全表，这时如果其他事务对该表进行增、删、改操作的时候，都会被阻塞。
转载：https://blog.csdn.net/qq_34827674/article/details/128353104