select...for update，表锁？行锁？间隙锁？

2023-11-06 微信公众号楼仔

加入收藏

对于这个问题，我 4 年前就专门研究过，最近看到网上很多相关的文章，要么总结得不全，要么存在很多问题。

感觉有必要自己写一篇，一方面对网上的知识进行纠偏，另一方面也想全面总结一下这块知识，方便大家学习。

这篇文章应该是全网总结最全的，如果有发现比我这篇写得更好，更全，一定要私我哈。

不 BB，上文章目录：

select...for update，表锁？行锁？间隙锁？图片

01 环境准备

在验证之前，我们先准备好具体的环境和数据，事务隔离级别 RR，数据库版本 5.7.26。

为了方便测试，索引都是整型：

CREATE TABLE user (
  id int(11) unsigned NOT NULL AUTO_INCREMENT,
  user_no int(11) NOT NULL COMMENT '用户编号',
  user_name varchar(16) DEFAULT NULL COMMENT '用户名',
  age int(3) DEFAULT NULL COMMENT '年龄',
  PRIMARY KEY (id),
  UNIQUE KEY un_idx_user_no (user_no),
  KEY idx_age (age)
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8;

初始化数据：

insert into user values(1, 10, '楼仔', 18);
insert into user values(4, 15, '二哥', 28);
insert into user values(8, 20, '一灰', 38);

常用命令操作：

> start transaction; // 开启事务
> commit; // 提交事务
> rollback; // 回滚事务
> select @@transaction_isolation; // 查看事务隔离级别
> select @@version; // 查看数据库版本
> SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS; //  查询锁

02 场景分类

2.1 主键（有值）

说明：主键查询，查询数据存在。

执行悲观锁查询：

select * from user where id = 1 for update;

执行更新操作，被锁住了：

update user set user_name = "楼仔小弟" where id = 1;
ERROR 1205 (HY000): Lock wAIt timeout exceeded; try restarting transaction

查看锁信息：

select...for update，表锁？行锁？间隙锁？图片

lock_mode 为 X（排他锁）
lock_type 为 RECORD，行级锁

结论：查询条件为主键，且有值，行锁

2.2 主键（空值）

操作：主键查询，查询数据不存在。

执行悲观锁查询：

select * from user where id = 2 for update;

执行插入操作，被锁住了：

insert into user values(3, 14, '楼仔小弟', 28);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

这里的间隙锁，锁住的区间是 id 字段的 (1，4) 区间，查看锁信息：

select...for update，表锁？行锁？间隙锁？图片

lock_mode 为 X（排他锁）+ Gap（间隙锁）
lock_type 为 RECORD，行级锁

结论：查询条件为主键，且空值，间隙锁

2.3 唯一索引（有值）

说明：唯一索引查询，数据存在。

执行悲观锁查询：

select * from user where user_no = 10 for update;

执行更新操作，被锁住了：

update user set user_name = "楼仔小弟" where user_no = 10;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

select...for update，表锁？行锁？间隙锁？图片

结论：查询条件为唯一索引，且有值，行锁

2.4 唯一索引（空值）

说明：唯一索引查询，数据不存在。

执行悲观锁查询：

select * from user where user_no = 11 for update;

执行插入操作，被锁住了：

insert into user values(3, 14, '楼仔小弟', 28);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

这里的间隙锁，锁住的区间是 user_no 字段的 (1，4) 区间。

select...for update，表锁？行锁？间隙锁？图片

结论：查询条件为唯一索引，且空值，间隙锁

2.5 普通索引（有值）

说明：普通索引，数据存在。

执行悲观锁查询：

select * from user where age = 18 for update;

执行更新操作，被锁住了：

update user set user_name = "楼仔小弟" where age = 18;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

select...for update，表锁？行锁？间隙锁？图片

执行插入操作，被锁住了：

insert into user values(3, 14, '楼仔小弟', 20);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

select...for update，表锁？行锁？间隙锁？图片

这里锁住的是 age 字段的 [18, 28) 这区间。

结论：查询条件为普通索引，且有值，间隙锁

2.6 普通索引（空值）

说明：普通索引，数据不存在。

执行悲观锁查询：

select * from user where age = 19 for update;

执行插入操作，被锁住了：

insert into user values(3, 14, '楼仔小弟', 20);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

select...for update，表锁？行锁？间隙锁？图片

这里锁住的是 age 字段的 (18, 28) 这区间。

结论：查询条件为普通索引，且空值，间隙锁

2.7 索引（范围查询）

说明：这里的索引，包括主键索引、唯一索引和普通索引。

执行悲观锁查询：

select * from user where id > 1 for update;

执行插入操作，被锁住了：

insert into user values(3, 14, '楼仔小弟', 20);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

select...for update，表锁？行锁？间隙锁？图片

这里其实可以对 id = 1 的数据进行更新，对于其它数据，都被锁住，锁住的范围是 id 字段的 (1, 4],(4, 8],(8, 正无穷) 区间。

结论：查询条件为索引，且是范围查询，间隙锁。

2.8 无索引