InnoDB的三种行锁(提供具体sql执行案例分析)

最新推荐文章于 2024-10-03 08:12:15 发布

三3三

最新推荐文章于 2024-10-03 08:12:15 发布

阅读量1.4k

点赞数 4

分类专栏： mysql 文章标签：数据库 java mysql

本文链接：https://blog.csdn.net/lt11hka/article/details/131392416

版权

mysql 专栏收录该内容

18 篇文章 3 订阅

订阅专栏

InnoDB存储引擎有3种行锁的算法，其分别是：

Record Lock（记录锁）：单个行记录上的范围 (锁住某一行记录)
Gap Lock（间隙锁）：间隙锁，锁定一个范围，但不包含记录本身 (锁住一段左开右开的区间)
Next-key Lock（临键锁）：Gap Lock + Record Lock，锁定一个范围，并且锁定记录本身 (锁住一段左开右闭的区间)

在学习具体行锁加锁规则之前，小伙伴们需要记住加锁规则的两条核心：

1）查找过程中访问到的对象才会加锁
这句话该怎么理解？比如有主键 id 为 1 2 3 4 5 … 10 的10 条记录，我们要找到 id = 7 的记录。注意，查找并不是从第一行开始一行一行地进行遍历，而是根据 B+ 树的特性进行二分查找，所以一般存储引擎只会访问到要找的记录行（id = 7）的相邻区间

2）加锁的基本单位是 Next-key Lock

哪些语句上面会加行锁？

1.对于常见的 DML 语句（如 UPDATE、DELETE 和 INSERT ），InnoDB 会自动给相应的记录行加写锁
2.默认情况下对于普通 SELECT 语句，InnoDB 不会加任何锁，但是在 Serializable 隔离级别下会加行级读锁
上面两种是隐式锁定，InnoDB 也支持通过特定的语句进行显式锁定：
3.SELECT * FROM table_name WHERE … FOR UPDATE，加行级写锁
4.SELECT * FROM table_name WHERE … LOCK IN SHARE MODE，加行级读锁

下面我们通过一些sql，来看下不同情况下sql的加锁情况

现在有这么一张表，id是主键(唯一索引),age是普通索引，name就是普通字段没有任何索引，如下图所示：
在这里插入图片描述

1.唯一索引的等值查询

当我们用唯一索引进行等值查询的时候，mysql会根据查询的记录是否存在，使用不同的加锁规则：

1.当查询记录是存在的，Next-key Lock（临键锁）会的退化成Record Lock（记录锁）;
2.当查询的记录不存在，Next-key Lock（临键锁）会的退化成Gap Lock（间隙锁）;

（1）查询的记录存在

例如： select * from user where id = 3 for update;

结合加锁的两条核心：查找过程中访问到的对象才会加锁 + 加锁的基本单位是 Next-key Lock（左开右闭），我们可以分析出，这条语句的加锁范围是 (1, 3]
不过，由于这个唯一索引等值查询的记录 id = 35 是存在的，因此，Next-key Lock 会退化成记录锁，因此最终的加锁范围是 id = 3 这一行数据

（2）查询的记录不存在

例如：select * from user where id = 4 for update;

结合加锁的两条核心：查找过程中访问到的对象才会加锁 + 加锁的基本单位是 Next-key Lock（左开右闭），我们可以分析出，这条语句的加锁范围是 (3, 6]

这里为什么是 (3，6] 而不是 (3, 4]，因为 id = 4 的记录不存在呀，InnoDB 先找到 id = 3 的记录，发现不匹配，于是继续往下找，发现 id = 6，因此，id = 6 的这一行被扫描到了，所以整体的加锁范围是 (3, 6]

由于这个唯一索引等值查询的记录 id = 4 是不存在的，因此，Next-key Lock 会退化成间隙锁，因此最终在主键 id 上的加锁范围是 Gap Lock (3, 6)

2.唯一索引的范围查询

唯一索引范围查询的规则和等值查询的规则一样，只有一个区别，就是唯一索引的范围查询需要一直向右遍历到第一个不满足条件的记录，下面结合案例来分析：

例如：select * from user where id >= 3 and id < 4 for update;

先来看语句查询条件的前半部分 id >= 3，因此，这条语句最开始要找的第一行是 id = 3，结合加锁的两个核心，需要加上 Next-key Lock (1,3]。又由于 id 是唯一索引，且 id = 3 的这行记录是存在的，因此会退化成记录锁，也就是只会对 id = 3 这一行加锁。

再来看语句查询条件的后半部分 id < 4，由于是范围查找，就会继续往后找第一个不满足条件的记录，也就是会找到 id = 6 这一行停下来，然后加 Next-key Lock (3, 6]，重点来了，但由于 id = 6 不满足 id < 4，因此会退化成间隙锁，加锁范围变为 (3, 6)。

所以，上述语句在主键 id 上的最终的加锁范围是 Record Lock id = 3 以及 Gap Lock (3, 6)

3.非唯一索引的等值查询

当我们用非唯一索引进行等值查询的时候，根据查询的记录是否存在，加锁的规则会有所不同：

1.当查询的记录是存在的，除了会加 Next-key Lock 外，还会额外加间隙锁（规则是向下遍历到第一个不符合条件的值才能停止），也就是会加两把锁
2.当查询的记录是不存在的，Next-key Lock 会退化成间隙锁（这个规则和唯一索引的等值查询是一样的）

（1）查询的记录存在

例如： select * from user where age = 12 for update;

结合加锁的两条核心，这条语句首先会对普通索引 age 加上 Next-key Lock，范围是 (10,12]
又因为是非唯一索引等值查询，且查询的记录 age = 12 是存在的，所以还会加上间隙锁，规则是向下遍历到第一个不符合条件的值才能停止，因此间隙锁的范围是 (12,15)
所以，上述语句在普通索引 age 上的最终加锁范围是 Next-key Lock (10,12] 以及 Gap Lock (12,15)