select...forupdate，表锁？行锁？间隙锁？

大家好呀，我是楼仔。

创新互联于2013年成立，是专业互联网技术服务公司，拥有项目网站制作、网站建设网站策划，项目实施与项目整合能力。我们以让每一个梦想脱颖而出为使命，1280元大通做网站,已为上家服务,为大通各地企业和个人服务,联系电话:18980820575

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

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

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

不 BB，上文章目录：

图片

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

查看锁信息：

图片

• 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) 区间，查看锁信息：

图片

• 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

图片

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

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) 区间。

图片

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

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

图片

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

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

图片

这里锁住的是 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

图片

这里锁住的是 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

图片

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

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

2.8 无索引

执行悲观锁查询：

select * from user where user_name = "楼仔" for update;

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

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

这里明显是锁表了，但是为什么锁的信息还是行锁呢，知道的同学，可以私我哈~~

结论：查询条件为无索引，表锁。

03 加锁规则

3.1 规律总结

我们把上面的结论进行汇总：

图片

总结如下规律：

1. 当查询条件为主键和唯一索引，当有值时，是行锁；
2. 当查询条件为主键和唯一索引，当为空值时，是间隙锁；
3. 当查询条件为普通索引，是间隙锁；
4. 当查询条件为索引，且为范围查询，是间隙锁；
5. 当查询条件无索引，是表锁。

3.2 加锁规则

那是否有一套加锁规则呢？

为了便于大家理解，我先普及 3 个概念：

• Record Lock：行锁
• Gap Lock：间隙锁，锁定一个范围，但不包含记录本身
• Next-Key Lock：行锁 + 间隙锁，左开右闭，比如(1,5]

其实 MySQL 大佬林晓斌在极客时间讲过，后来也有很多博主转发过他的加锁规则，我直接把这套规则贴一下。

两个“原则”：

• 原则 1：加锁的基本单位是 next-key lock，其中 next-key lock 是前开后闭区间；
• 原则 2：查找过程中访问到的对象才会加锁。

两个“优化”：

• 优化 1：索引上的等值查询，给唯一索引加锁的时候，next-key lock退化为行锁；
• 优化 2：索引上的等值查询，向右遍历时且最后一个值不满足等值条件的时候，next-key lock 退化为间隙锁。

3.3 分析一下

这里我们结合上面的案例，来解读这套加锁规则。

针对我们前面总结的 5 条规律，我们先分析这两条：

• 当查询条件为主键和唯一索引，当有值时，是行锁；
• 当查询条件为主键和唯一索引，当为空值时，是间隙锁。

下面我们根据 “两个原则” + “两个优化” 来分析一下。

根据 “原则 1”，加锁的基本单位是 next-key lock，当 “索引上为等值查询” 时（即能查到该数据），根据 “优化 1”，间隙锁退化为行锁。

同理，根据 “优化 2”，索引上的等值查询，向右遍历时且最后一个值不满足等值条件的时候，next-key lock 退化为间隙锁。

再分析这两条：

• 当查询条件为普通索引，是间隙锁；
• 当查询条件为索引，且为范围查询，是间隙锁；

同上，通过 “原则 1” 和 “优化 2”，普通索引 是加的间隙锁。

对于范围查询，个人认为上面的规则还不能完全覆盖，当时林晓斌针对这些规则，举了 4 个示例，然后进行详细剖析，包括间隙锁的范围区间计算。

因为篇幅原因，这里就不再详细展开，如果后续需要，我也可能会单独出一篇。

04 写在最后

最后我们再回顾一下（RR 隔离级别）：

1. 当查询条件为主键和唯一索引，当有值时，是行锁；
2. 当查询条件为主键和唯一索引，当为空值时，是间隙锁；
3. 当查询条件为普通索引，是间隙锁；
4. 当查询条件为索引，且为范围查询，是间隙锁；
5. 当查询条件无索引，是表锁。

至于间隙锁的范围，如何计算，本文没有详细阐述，但是上面的这些规则，就能基本满足我们日常工作需要。

标题名称：select...forupdate，表锁？行锁？间隙锁？
分享网址：http://www.shufengxianlan.com/qtweb/news25/305925.html

网站建设、网络推广公司-创新互联，是专注品牌与效果的网站制作，网络营销seo公司；服务项目有等

广告

声明：本网站发布的内容（图片、视频和文字）以用户投稿、用户转载内容为主，如果涉及侵权请尽快告知，我们将会在第一时间删除。文章观点不代表本网站立场，如需处理请联系客服。电话：028-86922220；邮箱：631063699@qq.com。内容未经允许不得转载，或转载时需注明来源： 创新互联