Mysql 行锁、间隙锁和next-key锁详解

感谢评论区 渔父歌 的指点,我重新梳理了一下三种行级锁的用法

一 、基本概念

InnoDB支持几种不同的行级锁,MyISAM只支持表级锁
行锁(Record Lock): 对索引记录加锁。
间隙锁(Gap Lock): 锁住整个区间,包括:区间里具体的索引记录,不存在的空闲空间(可以是两个索引记录之间,也可能是第一个索引记录之前或最后一个索引记录之后的空间)。
next-key锁: 行锁和间隙锁组合起来。

注意:如果检索条件不是索引的话会全表扫描,则是表级锁,不是行级锁

二、行锁(Record Lock)

对主键或者唯一索引进行增删改或显示的加锁,InnoDB会加行锁,如:

-- 显示的加锁
select * from  people where id =3 for update;
update people set name='James' where id=3

注意:

  1. 正常的查询语句使用的是共享锁。
  2. 对于显示的加锁或增删改操作,条件判断必须是精确匹配(也就是=) ,不能用>,<,between或like等范围查询方式,因为这样会使行锁变成next-key Lock。
三、间隙锁(Gap Lock)

官方文档描述:Gap Lock的唯一目的就是阻止其他事务插入到间隙中。Gap Lock可以同时存在,不同的事务可以同时获取相同的Gap Lock,并不会互相冲突。Gap Lock也是可以显示的被禁止的,只要将事务的隔离级别降低到 READ COMMITTED。

对于间隙锁,什么叫锁住不存在的空闲空间,举个例子:
一个表有id为1,2,3,5,6,9行数据,执行如下sql语句

select * from  people where id > 3 AND id <7 for update;

这是一个范围检索,InnoDB不仅会锁住id为5和6两行的数据,也会锁住id为4(虽然该行并不存在)的纪录。

四、next-key Lock

官方文档描述:Record Lock+Gap Lock,如果一个事务在记录R上的某个索引有共享/互斥锁,也会对其前面一个范围加锁

锁定的区域
根据索引会形成一个个左开右闭的一个区间,根据查询的条件其所在的区间,并且包括其后的区间。

这里给出一个people表

idnameage
1JAMES37
2OVEN28
3LOVE34

如果age是索引的话,相关的区域有
(-无穷,28]
(28,34]
(34,37]
(37,+无穷)

如果执行如下语句:

select * from  people where age =34 for update;

那么会锁住(28,37]这么范围

如果执行如下语句:

select * from  people where age =33 for update;

那么会锁住(28,34)这么范围

间隙锁的目的是为了防止幻读,其主要通过两个方面实现这个目的:
(1)防止间隙内有新数据被插入
(2)防止已存在的数据,更新成间隙内的数据

innodb自动使用间隙锁的条件
(1)必须在Repeatable Read级别下
(2)检索条件必须有普通索引(没有索引的话,mysql会全表扫描,那样会锁定整张表所有的记录,包括不存在的记录,此时其他事务不能修改不能删除不能添加)

注意:这里的普通索引不包括主键索引和唯一索引,如果在这两个索引下因为能精确检索出结果,所以会使用Record Lock直接锁定具体的行(范围查询除外)。

举例: 对于表people

mysql> select * from people ;
+----+-------+
| id | name  |
+----+-------+
|  1 | JAMES |
|  2 | OVEN  |
|  3 | LOVE  |
+----+-------+
时间顺序session Asession B
1Begin;
2Begin;
3select * from people where name=“OVEN” for update ;(查询到“OVEN”)
4update people set name=“zph” where id=1; (此操作会被锁住)

因为通过name索引执行select …for update会将(1,“JAMES”)到(3,“LOVE”)之间的数据都锁住,所以第4步会被锁住。

但是如果通过id主键索引来检索

时间顺序session Asession B
1Begin;
2Begin;
3select * from people where id=2 for update ;(查询到“OVEN”)
4update people set name=“zph” where id=1; (此操作会被执行)

对于间隙锁的许多例子可以看Mysql中的几种行锁(间隙锁、next-key锁)

这个博客中的图片显示不了,可以通过下面的url访问
https://img-blog.csdnimg.cn/20200524231954401.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM5NzUxMzIw,size_16,color_FFFFFF,t_70

五 、快照读和当前读

快照读: 通过MVCC实现,该技术不仅可以保证innodb的可重复读,而且可以防止幻读。但是他读取的数据虽然是一致的,但是数据是历史数据。

简单的select操作(不包括 select … lock in share mode, select … for update)

当前读: 要做到保证数据是一致的,同时读取的数据是最新的数据。innodb提供了next-key lock,即gap锁与行锁结合来实现。

select … lock in share mode

select … for update

insert

update

delete

总结

在可重复读隔离级别下并不能避免幻读,如果要避免的话需要使用Next-Key Lock。但是有了Next-Key Lock以后,会导致并发插入的时候产生等待,所以这时候需要进行相关的优化。

参考

Mysql的锁机制

Mysql中的几种行锁(间隙锁、next-key锁)

mySQL数据库间隙锁(mysql是如何解决幻读的)

已标记关键词 清除标记
©️2020 CSDN 皮肤主题: 深蓝海洋 设计师:CSDN官方博客 返回首页