文章详情

短信预约-IT技能 免费直播动态提醒

请输入下面的图形验证码

提交验证

短信预约提醒成功

select...for update,表锁?行锁?间隙锁?

2024-11-30 05:18

关注

对于这个问题,我 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

查看锁信息:

图片

结论:查询条件为主键,且有值,行锁

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) 区间,查看锁信息:

图片

结论:查询条件为主键,且空值,间隙锁

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 个概念:

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

两个“原则”:

两个“优化”:

3.3 分析一下

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

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

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

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

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

再分析这两条:

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

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

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

04 写在最后

最后我们再回顾一下(RR 隔离级别):

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

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

来源:楼仔内容投诉

免责声明:

① 本站未注明“稿件来源”的信息均来自网络整理。其文字、图片和音视频稿件的所属权归原作者所有。本站收集整理出于非商业性的教育和科研之目的,并不意味着本站赞同其观点或证实其内容的真实性。仅作为临时的测试数据,供内部测试之用。本站并未授权任何人以任何方式主动获取本站任何信息。

② 本站未注明“稿件来源”的临时测试数据将在测试完成后最终做删除处理。有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341

软考中级精品资料免费领

  • 历年真题答案解析
  • 备考技巧名师总结
  • 高频考点精准押题
  • 2024年上半年信息系统项目管理师第二批次真题及答案解析(完整版)

    难度     813人已做
    查看
  • 【考后总结】2024年5月26日信息系统项目管理师第2批次考情分析

    难度     354人已做
    查看
  • 【考后总结】2024年5月25日信息系统项目管理师第1批次考情分析

    难度     318人已做
    查看
  • 2024年上半年软考高项第一、二批次真题考点汇总(完整版)

    难度     435人已做
    查看
  • 2024年上半年系统架构设计师考试综合知识真题

    难度     224人已做
    查看

相关文章

发现更多好内容

猜你喜欢

AI推送时光机
位置:首页-资讯-后端开发
咦!没有更多了?去看看其它编程学习网 内容吧
首页课程
资料下载
问答资讯