MySQL记录锁、间隙锁、临键锁(Next-Key Locks)加锁过程

  • innodb一定存在聚簇索引,默认以主键作为聚簇索引
  • 有几个索引,就有几棵B+树(不考虑hash索引的情形)
  • 聚簇索引的叶子节点为磁盘上的真实数据。非聚簇索引的叶子节点还是索引(id主键值),指向聚簇索引B+树。

锁类型:

共享锁(S锁):假设事务T1对数据A加上共享锁,那么事务T2可以读数据A,不能修改数据A。
排他锁(X锁):假设事务T1对数据A加上共享锁,那么事务T2不能读数据A,不能修改数据A。
我们通过updatedelete等语句加上的锁都是行级别的锁。只有LOCK TABLE … READLOCK TABLE … WRITE才能申请表级别的锁。

意向共享锁(IS锁):一个事务在获取(任何一行/或者全表)S锁之前,一定会先在所在的表上加IS锁。
意向排他锁(IX锁):一个事务在获取(任何一行/或者全表)X锁之前,一定会先在所在的表上加IX锁。

加锁算法:

Record Locks简单翻译为行锁吧,即锁住一条记录。注意了,该锁是对索引记录进行加锁!锁是在加索引上而不是行上的。注意了,innodb一定存在聚簇索引,因此行锁最终都会落到聚簇索引上!
Gap Locks简单翻译为间隙锁,是对索引的间隙加锁,其目的只有一个,防止其他事物插入数据。在Read Committed隔离级别下,不会使用间隙锁。隔离级别比Read Committed低的情况下,也不会使用间隙锁,如隔离级别为Read Uncommited时,也不存在间隙锁。当隔离级别为Repeatable ReadSerializable时,就会存在间隙锁。即锁定一个区间,左开右开。
Next-Key Locks这个理解为Record Lock+索引前面的Gap Lock记录锁+间隙锁锁定的区间,左开右闭。记住了,锁住的是索引前面的间隙!比如一个索引包含值,10,11,13和20。那么,间隙锁的范围如下:

(negative infinity, 10]
(10, 11]
(11, 13]
(13, 20]
(20, positive infinity)

加锁过程

加锁的基本单位为next-key lock = 间隙锁+行锁

无论什么情况下,InnoDB 会往前扫描到第一个不满足条件的行为止。

数据准备

id a b
0 0 0
4 4 4
8 8 8
16 16 16
32 32 32

其中,id 是主键索引(唯一索引),b 是普通索引(非唯一索引),a 是普通的列

唯一索引等值查询

结论

当查询的记录是存在的,在用「唯一索引进行等值查询」时,next-key lock 会退化成「记录锁」。
当查询的记录是不存在的,在用「唯一索引进行等值查询」时,next-key lock 会退化成「间隙锁」。

Demo(唯一索引等值查询记录存在)

会话1加锁变化过程如下:

  1. 加锁的基本单位是 next-key lock,因此会话1的加锁范围是(8, 16];
  2. 但是由于是用唯一索引进行等值查询,且查询的记录存在,所以 next-key lock 退化成记录锁,因此最终加锁的范围是 id = 16 这一行。

所以,会话 2 在修改 id=16 的记录时会被锁住,而会话 3 插入 id=9 的记录可以被正常执行

Demo(唯一索引等值查询记录不存在)

会话1加锁变化过程如下:

  1. 加锁的基本单位是 next-key lock,因此主键索引 id 的加锁范围是(8, 16];
  2. 但是由于查询记录不存在,next-key lock 退化成间隙锁,因此最终加锁的范围是 (8,16)。

所以,会话 2 要往这个间隙里面插入 id=9 的记录会被锁住,但是会话 3 修改 id =16 是可以正常执行的,因为 id = 16 这条记录并没有加锁。

唯一索引范围查询

会话 1 加锁变化过程如下:

  1. 最开始要找的第一行是 id = 8,因此 next-key lock(4,8],但是由于 id 是唯一索引,且该记录是存在的,因此会退化成记录锁,也就是只会对 id = 8 这一行加锁;
  2. 由于是范围查找,就会继续往后找存在的记录,也就是会找到 id = 16 这一行停下来,然后加 next-key lock (8, 16],但由于 id = 16 不满足 id < 9,所以会退化成间隙锁,加锁范围变为 (8, 16)。

所以,会话 1 这时候主键索引的锁是记录锁 id=8 和间隙锁(8, 16)。
会话 2 由于往间隙锁里插入了 id = 9 的记录,所以会被锁住了,而 id = 8 是被加锁的,因此会话 3 的语句也会被阻塞。
由于 id = 16 并没有加锁,所以会话 4 是可以正常被执行。

普通索引等值查询

结论

当查询的记录存在时,除了会加 next-key lock 外,还额外加间隙锁,也就是会加两把锁。
当查询的记录不存在时,只会加 next-key lock,然后会退化为间隙锁,也就是只会加一把锁。

Demo(普通索引等值查找记录存在)

会话 1 加锁变化过程如下:

  1. 先会对普通索引 b 加上 next-key lock,范围是(4,8];
  2. 然后因为是非唯一索引,且查询的记录是存在的,所以还会加上间隙锁,规则是向下遍历到第一个不符合条件的值才能停止,因此间隙锁的范围是(8,16)。

所以,会话1的普通索引 b 上共有两个锁,分别是 next-key lock (4,8] 和间隙锁 (8,16) 。
那么,当会话 2 往间隙锁里插入 id = 9 的记录就会被锁住,而会话 4 是因为更改了 next-key lock 范围里的记录而被锁住的。
然后因为 b = 16 这条记录没有加锁,所以会话 5 是可以正常执行的。

Demo(普通索引等值查找记录不存在)

会话 1 加锁变化过程如下:

  1. 先会对普通索引 b 加上 next-key lock,范围是(8,16];
  2. 但是由于查询的记录是不存在的,所以不会再额外加个间隙锁,但是 next-key lock 会退化为间隙锁,最终加锁范围是 (8,16)。

会话 2 因为往间隙锁里插入了 b = 9 的记录,所以会被锁住,而 b = 16 是没有被加锁的,因此会话 3 的语句可以正常执行。

普通索引范围查找

会话 1 加锁变化过程如下:

  1. 最开始要找的第一行是 b = 8,因此 next-key lock(4,8],但是由于 b 不是唯一索引,并不会退化成记录锁。
  2. 但是由于是范围查找,就会继续往后找存在的记录,也就是会找到 b = 16 这一行停下来,然后加 next-key lock (8, 16],因为是普通索引查询,所以并不会退化成间隙锁。

所以,会话 1 的普通索引 b 有两个 next-key lock,分别是 (4,8] 和(8, 16]。这样,你就明白为什么会话 2 、会话 3 、会话 4 的语句都会被锁住了。

总结

唯一索引等值查询:
1.当查询的记录是存在的,next-key lock 会退化成「记录锁」。
2.当查询的记录是不存在的,next-key lock 会退化成「间隙锁」。

非唯一索引等值查询:
1.当查询的记录存在时,除了会加 next-key lock 外,还额外加间隙锁,也就是会加两把锁。
2.当查询的记录不存在时,只会加 next-key lock,然后会退化为间隙锁,也就是只会加一把锁。

非唯一索引和主键索引的范围查询的加锁规则不同之处在于:
唯一索引在满足一些条件的时候,next-key lock 退化为间隙锁和记录锁。
非唯一索引范围查询,next-key lock 不会退化为间隙锁和记录锁。

怎样加锁的

非唯一索引等值查询

例如:在下面这张表当中,id为主键索引age普通索引

id age name sex
1 15 abc
2 20 def
3 25 ghi
4 29 lmn
5 35 opq

当查询记录不存在的时候:锁住这个二级索引最近的范围(使用间隙锁)

例如:

select*from table where age=16 for update;

这个时候,age这一列对应查询的值16不在表当中。

​ 因此,加锁的过程就是这样的:首先根据age,name构成的这两列构成的索引列进行扫描,扫描到的第一条不符合条件的二级索引记录就会退化为间隙锁.

​ 也就是间隙锁的范围是(15,20)锁住的对象是age这一列的索引,不会对主键索引加锁。


当查询记录存在的时候:先锁住二级索引(next-key lock),然后锁住主键索引(Record-lock),最后锁住二级索引的下一行(间隙锁)

例如:执行

select*from table where age=22 for update;

这个时候,age=22在这一个索引当中。

id(主键) (age name) sex
1 19 abc
2 20 def
4 22 lmn
5 39 opq

因此,加锁的过程分为3个步骤:

步骤 对于二级索引 对于主键索引
1 对于age=22往下的范围,加锁。范围是(20,22]的X型next-key lock
2 age=22对应的主键索引为id=4;于是对id=4的主键索引加上X型的记录锁(record-lock)
3 对于(22,39)的二级索引范围加上X型间隙锁

非唯一索引范围查询

例如查询的sql语句为:

首先,开启一个事物:

然后执行下面的查询语句

select*from table where age>=25 for update
id (age name) sex
1 15 abc
2 20 def
3 25 ghi
4 30 opq

一共涉及5次加锁

步骤 说明
第一步 由于涉及等值查询,因此对于二级索引age加上next-key lock,锁住的范围为(20,25].
第二步 对于age=25这一行数据的主键索引加锁,主键索引为3
第三步 因为涉及范围查询,因此需要扫描已经存在的,最近的一行二级索引的记录, 对于id=4这一行的主键索引记录锁(record_lock)
第四步 对于(25,30]的范围的二级索引加上间隙锁(next-key lock)
第五步 对于(30,+∞)的二级索引加上间隙锁

没有加索引的查询

id (age name) sex
1 15 abc
2 20 def

例如,在上表当中,对于sex这一列,没有设计索引,那么查询:

select*from table where sex='男' for update

​ 这样的语句的时候,就会导致全表扫描。因此,就会对于每一条记录的主键索引加上next-key lock,这样其他事物都没有办法对于这张表进行任何增删改以及携带update的查询操作。

​ 因此,在线上在执行 update、delete、select ... for update 等具有加锁性质的语句,一定要检查语句是否走了索引,如果是全表扫描的话,会对每一个索引加 next-key 锁,相当于把整个表锁住了,这是挺严重的问题。

delete删除数据的语句条件上加索引

从性能和加锁来看delete操作;

一般来说,DELETE的加锁和SELECT FOR UPDATE 或 UPDATE 并没有太大的差异;

因为,在MySQL数据库中,执行DELETE语句其实并没有直接删除记录,而是在记录上打上一个删除标记,然后通过后台的一个叫做purge的线程来清理;从这一点来看,DELETE和UPDATE确实是非常相像;事实上,DELETE和UPDATE的加锁也几乎是一样的;

如果更新条件可以走索引,则间隙锁会加在条件所在的索引位置的前后间隙;如果查询条件没走索引走全表扫描,则对全表所有行之间加间隙锁;

MySQL delete语句的加锁分析总结可参考:

因此建议删除语句的条件尽量走索引查询,或者先查出这条记录的主键ID,再根据主键ID(唯一索引)条件删除;

在mysql中select分为快照读和当前读,执行下面的语句:

1、下面这个sql 执行的是快照读,读的是数据库记录的快照版本,是不加锁的。(这种说法在隔离级别为Serializable中不成立。)

select * from table where id = ?;

2、那么这个sql 是当前读,会对读取记录加S锁 (共享锁)。

select * from table where id = ? lock in share mode;

3、最后下面这个sql 会对读取记录加X锁(排它锁),这是悲观锁的一种实现形式。

select * from table where id = ? for update

现在,大家考虑下,上面两个加锁查询的sql,是加的表锁(将整个表锁住)还是加的行锁(将行记录锁住)呢?

针对这点,我们先回忆一下事务的四个隔离级别,他们由弱到强如下所示:

  • Read Uncommited(RU):读未提交,一个事务可以读到另一个事务未提交的数据!
  • Read Committed (RC):读已提交,一个事务可以读到另一个事务已提交的数据!
  • Repeatable Read (RR):可重复读,加入间隙锁,一定程度上避免了幻读的产生!注意了,只是*一定程度上*,并没有完全避免! 另外就是记住从该级别才开始加入间隙锁!
  • Serializable:串行化,该级别下读写串行化,且所有的select语句后都自动加上lock in share mode,即使用了共享锁。因此在该隔离级别下,使用的是当前读,而不是快照读。

关于是表锁还是行锁

InnoDB行锁是通过给索引上的索引项加锁来实现的,这一点MySQL与Oracle不同,后者是通过在数据块中对相应数据行加锁来实现的。 InnoDB这种行锁实现特点意味着:只有通过索引条件检索数据,InnoDB才使用行级锁,否则,InnoDB将使用表锁!

这句话本身有错误!
错误:并不是用表锁来实现锁表的操作,而是利用了*Next-Key Locks*,也可以理解为是用了行锁+间隙锁来实现锁表的操作!**
为了便于说明,我来个例子,假设有表数据如下,pId为主键索引

pId(int) name(varchar) num(int)
1 aaa 100
2 bbb 200
7 ccc 200\

执行语句(name列无索引)

select * from table where name = `aaa` for update

那么此时在pId=1,2,7这三条记录上存在行锁(把行锁住了)。另外,在(-∞,1)(1,2)(2,7)(7,+∞)上存在间隙锁(把间隙锁住了)。因此, 给人一种整个表锁住的错觉!

分析

下面来对开始的问题作出解答,假设有表如下,pId为主键索引

pId(int) name(varchar) num(int)
1 aaa 100
2 bbb 200
3 bbb 300
7 ccc 200

RR/Serializable+条件列非索引

RR级别需要多考虑的就是gap lock 间隙锁,他的加锁特征在于,无论你怎么查都是锁全表( 使用行锁+间隙锁实现锁全表 )。

如下所示,接下来分析开始

(1)、在RR级别下,不加任何锁,是快照读。
在Serializable级别下,在pId = 1,2,3,7(全表所有记录)的聚簇索引上加S锁。并且在聚簇索引的所有间隙(-∞,1)(1,2)(2,3)(3,7)(7,+∞)加gap lock

select * from table where num = 200

(2)、在RR级别下,不加任何锁,是快照读。
在Serializable级别下,在pId = 1,2,3,7(全表所有记录)的聚簇索引上加S锁。并且在聚簇索引的所有间隙(-∞,1)(1,2)(2,3)(3,7)(7,+∞)加gap lock

select * from table where num > 200

(3)、在pId = 1,2,3,7(全表所有记录)的聚簇索引上加S锁。并且在聚簇索引的所有间隙(-∞,1)(1,2)(2,3)(3,7)(7,+∞)加gap lock

select * from table where num = 200 lock in share mode

(4)、在pId = 1,2,3,7(全表所有记录)的聚簇索引上加S锁。并且在聚簇索引的所有间隙(-∞,1)(1,2)(2,3)(3,7)(7,+∞)加gap lock

select * from table where num > 200 lock in share mode

(5)、在pId = 1,2,3,7(全表所有记录)的聚簇索引上加X锁。并且在聚簇索引的所有间隙(-∞,1)(1,2)(2,3)(3,7)(7,+∞)加gap lock

select * from table where num = 200 for update

(6)、在pId = 1,2,3,7(全表所有记录)的聚簇索引上加X锁。并且在聚簇索引的所有间隙(-∞,1)(1,2)(2,3)(3,7)(7,+∞)加gap lock

select * from table where num > 200 for update

RR/Serializable+条件列是聚簇索引

恩,大家应该知道pId是主键列,因此pId用的就是聚簇索引。该情况的加锁特征在于,如果where后的条件为精确查询(=的情况),那么只存在record lock。如果where后的条件为范围查询(><的情况),那么存在的是record lock + gap lock。

(1)、在RR级别下,不加任何锁,是快照读。
在Serializable级别下,是当前读,在pId=2的聚簇索引上加S锁,不存在gap lock

select * from table where pId = 2

(2)、在RR级别下,不加任何锁,是快照读。
在Serializable级别下,是当前读,在pId=3,7的聚簇索引上加S锁。在(2,3)(3,7)(7,+∞)加上gap lock

select * from table where pId > 2

(3)、是当前读,在pId=2的聚簇索引上加S锁,不存在gap lock。

select * from table where pId = 2 lock in share mode

(4)、是当前读,在pId=3,7的聚簇索引上加S锁。在(2,3)(3,7)(7,+∞)加上gap lock

select * from table where pId > 2 lock in share mode

(5)、是当前读,在pId=2的聚簇索引上加X锁。

select * from table where pId = 2 for update

(6)、在pId=3,7的聚簇索引上加X锁。在(2,3)(3,7)(7,+∞)加上gap lock

select * from table where pId > 2 for update

(7)、注意了,pId=6是不存在的列,这种情况会在(3,7)上加gap lock。

select * from table where pId = 6 [lock in share mode|for update]

(8)、注意了,pId>18,查询结果是空的。在这种情况下,是在(7,+∞)上加gap lock。

select * from table where pId > 18 [lock in share mode|for update]

RR/Serializable+条件列是非聚簇索引

这里非聚簇索引,需要区分是否为唯一索引。因为如果是非唯一索引,间隙锁的加锁方式是有区别的。

先说一下,唯一索引的情况。如果是唯一索引,情况和RR/Serializable+条件列是聚簇索引类似,唯一有区别的是:这个时候有两棵索引树,加锁是加在对应的非聚簇索引树和聚簇索引树上!

下面说一下,非聚簇索引是非唯一索引的情况,他和唯一索引的区别就是通过索引进行精确查询以后不仅存在record lock,还存在gap lock。而通过唯一索引进行精确查询后只存在record lock,不存在gap lock

老规矩在num列建立非唯一索引:

(1)、在RR级别下,不加任何锁,是快照读。
在Serializable级别下,是当前读,在pId=2,7的聚簇索引上加S锁,在num=200的非聚集索引上加S锁,在(100,200)(200,300)加上gap lock。

select * from table where num = 200

(2)、在RR级别下,不加任何锁,是快照读。
在Serializable级别下,是当前读,在pId=3的聚簇索引上加S锁,在num=300的非聚集索引上加S锁。在(200,300)(300,+∞)加上gap lock

select * from table where num > 200

(3)、是当前读,在pId=2,7的聚簇索引上加S锁,在num=200的非聚集索引上加S锁,在(100,200)(200,300)加上gap lock。

select * from table where num = 200 lock in share mode

(4)、是当前读,在pId=3的聚簇索引上加S锁,在num=300的非聚集索引上加S锁。在(200,300)(300,+∞)加上gap lock。

select * from table where num > 200 lock in share mode

(5)、是当前读,在pId=2,7的聚簇索引上加S锁,在num=200的非聚集索引上加X锁,在(100,200)(200,300)加上gap lock。

select * from table where num = 200 for update

(6)、是当前读,在pId=3的聚簇索引上加S锁,在num=300的非聚集索引上加X锁。在(200,300)(300,+∞)加上gap lock

select * from table where num > 200 for update

(7)、注意了,num=250是不存在的列,这种情况会在(200,300)上加gap lock。

select * from table where num = 250 [lock in share mode|for update]

(8)、注意了,pId>400,查询结果是空的。在这种情况下,是在(400,+∞)上加gap lock。

select * from table where num > 400 [lock in share mode|for update]
posted @ 2024-01-11 15:24  caibaotimes  阅读(451)  评论(0编辑  收藏  举报