执行 select ... for update 语句,如果查询条件没有索引字段的话,是加行锁还是加表锁?

大家好,我是小林。

昨天在群里看到大家在讨论一个 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 锁(行级锁),这样就相当于锁住的全表,这时如果其他事务对该表进行增、删、改操作的时候,都会被阻塞

评论 9
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值