Mysql引擎innodb行锁的三种算法

2023-05-16

Mysql中的锁

基于锁的属性分类：共享锁、排他锁。

基于锁的状态分类：意向共享锁、意向排它锁

根据锁的粒度分类：全局锁、页锁、表级锁、行锁（记录锁、间隙锁、和临键锁），实际上的锁就这些，上面两种分类只是站在不同维度上看这些锁

行锁的三种算法

Record Lock(记录锁)

（1）记录锁是一种行级锁, 仅仅锁住索引记录的一行，在单条索引记录上加锁。

（2）record lock锁住的永远是索引，或者说主键，而非记录本身，即使该表上没有任何索引，那么innodb会在后台创建一个隐藏的聚集主键索引，那么锁住的就是这个隐藏的聚集主键索引。

查询语句必须为精准匹配 = ，不能为 >、<、like等，否则会退化成临键锁。

所以说当一条sql没有走任何索引时，那么将会在每一条聚合索引后面加X锁，这个类似于表锁，但原理上和表锁应该是完全不同的。
（2）READ COMMITTED级别下仅采用Record Lock.

使用记录锁

查询

 -- 事务1,查询id为7182的行，不提交
 begin;
 
 select * from w_word where id =7182 for update
 
 commit;

更新

-- 事务2，事务1如果不提交，此时执行下面的update语句会阻塞
begin;

update w_word set creator_user ='check' where id=7182

commit;

如图

Gap Lock（间隙锁）

间隙锁，锁定一个范围，单不包含记录本身。

（1）区间锁是一种行级锁, 仅仅锁住一个索引区间（开区间，不包括双端端点）。

（2）在索引记录之间的间隙中加锁，或者是在某一条索引记录之前或者之后加锁，并不包括该索引记录本身。
比如在 1、2、3中，间隙锁的可能值有 (∞, 1)，(1, 2)，(2, ∞)，

（3）间隙锁可用于防止幻读，保证索引间的不会被插入数据

产生的条件

可重复读的隔离级别
范围条件而不是相等条件索引数据
请求共享或排他锁时

间隙锁的危害

锁定整个范围内所有的索引键值，即使这个键值并不存在，无法插入锁定键值范围内的任何数据
解决的问题

解决了RR（可重复读）级别下幻读的问题

使用间隙锁解决幻读

表数据

锁住间隙（7182,7185）

中间只有记录7183

begin;

select * from w_word where id > 7182 and id < 7185 for update

插入记录7184

begin;

INSERT INTO cloud_learning.w_word
(id, word)
VALUES(7184, 'splurge1231');

commit;

此时锁住的（7182,7185）之间多了一条数据7184，造成幻读，间隙锁的作用也产线了。

select * from w_word where id > 7182 and id < 7185 for update

解决幻读

直接进行7183的查询for update，将会锁住（7182,7185）间隙，7183必须是不存在的记录，否则只会锁住7183这条记录
```
select * from w_word where id=7183 for update;
```
插入7184,这时候就阻塞住了，不会插入（71827185）间隙
```
INSERT INTO cloud_learning.w_word
  (id, word)
  VALUES(7184, 'splurge1231');
```
对于指定查询某一条记录的加锁语句，如果该记录不存在，会产生记录锁和间隙锁，如果记录存在，则只会产生记录锁，如：WHERE id = 7182 FOR UPDATE;

对于查找某一范围内的查询语句，会产生间隙锁，如：WHERE id BETWEEN 5 AND 7 FOR UPDATE;
普通索引的间隙锁

在普通索引列上，不管是何种查询，只要加锁，都会产生间隙锁，这跟唯一索引不一样；
在普通索引跟唯一索引中，数据间隙的分析，数据行是优先根据普通索引排序，再根据唯一索引排序。

Next-key Lock（临键锁）

Next-key Lock：记录锁+Gap锁（间歇锁），即：既包含索引记录，又包含索引区间，主要是为了解决幻读。

同时锁住记录和间隙，前开后闭区间(a,b]。

表结构

lock_key建立了普通索引

该表中 lock_key 列潜在的临键锁有：
(-∞, 12],
(12, 31],
(31, 35],
(35, +∞],

在事务 A 中执行如下命令：

非唯一索引列锁住lock_key=31这条记录；
```
begin;

select * from test_lock where lock_key =31 for update
```
之后如果在事务 B 中执行以下命令，则该命令会被阻塞：
```
begin;

INSERT into test_lock (id, lock_key) VALUES(36, 34);
```
事务 A 在对 lock_key 为 31 的列进行 for update 操作的同时，也获取了 (31, 35] 这个区间内的临键锁。
Next-Key Locking实现应用程序的唯一性检查

如果需要插入的值是唯一的，那么插入之前需求判断唯一性，此时并发会出现问题，但是如果加上lock in share mode，就不会出现问题，如图：
临间锁的退化

场景退化
唯一索引，=值匹配记录锁
唯一索引，=值匹配，但记录不存在间隙锁
唯一索引范围匹配（<>）记录锁+间隙锁

场景	退化
唯一索引，=值匹配	记录锁
唯一索引，=值匹配，但记录不存在	间隙锁
唯一索引范围匹配（<>）	记录锁+间隙锁

本文内容由网友自发贡献，版权归原作者所有，本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容，请联系:hwhale#tublm.com(使用前将#替换为@)

mysql

InnoDB

行锁的三种算法