MySQL记录锁、间隙锁、临键锁(Next-Key Locks)加锁过程
- innodb一定存在聚簇索引,默认以主键作为聚簇索引
- 有几个索引,就有几棵B+树(不考虑hash索引的情形)
- 聚簇索引的叶子节点为磁盘上的真实数据。非聚簇索引的叶子节点还是索引(id主键值),指向聚簇索引B+树。
锁类型:
共享锁(S锁):假设事务T1对数据A加上共享锁,那么事务T2可以读数据A,不能修改数据A。
排他锁(X锁):假设事务T1对数据A加上共享锁,那么事务T2不能读数据A,不能修改数据A。
我们通过update
、delete
等语句加上的锁都是行级别的锁。只有LOCK TABLE … READ
和LOCK TABLE … WRITE
才能申请表级别的锁。
意向共享锁(IS锁):一个事务在获取(任何一行/或者全表)S锁之前,一定会先在所在的表上加IS锁。
意向排他锁(IX锁):一个事务在获取(任何一行/或者全表)X锁之前,一定会先在所在的表上加IX锁。
加锁算法:
Record Locks
:简单翻译为行锁吧,即锁住一条记录。注意了,该锁是对索引记录进行加锁!锁是在加索引上而不是行上的。注意了,innodb一定存在聚簇索引,因此行锁最终都会落到聚簇索引上!
Gap Locks
:简单翻译为间隙锁,是对索引的间隙加锁,其目的只有一个,防止其他事物插入数据。在Read Committed
隔离级别下,不会使用间隙锁。隔离级别比Read Committed
低的情况下,也不会使用间隙锁,如隔离级别为Read Uncommited
时,也不存在间隙锁。当隔离级别为Repeatable Read
和Serializable
时,就会存在间隙锁。即锁定一个区间,左开右开。
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加锁变化过程如下:
- 加锁的基本单位是 next-key lock,因此会话1的加锁范围是(8, 16];
- 但是由于是用唯一索引进行等值查询,且查询的记录存在,所以 next-key lock 退化成记录锁,因此最终加锁的范围是 id = 16 这一行。
所以,会话 2 在修改 id=16 的记录时会被锁住,而会话 3 插入 id=9 的记录可以被正常执行
Demo(唯一索引等值查询记录不存在)
会话1加锁变化过程如下:
- 加锁的基本单位是 next-key lock,因此主键索引 id 的加锁范围是(8, 16];
- 但是由于查询记录不存在,next-key lock 退化成间隙锁,因此最终加锁的范围是 (8,16)。
所以,会话 2 要往这个间隙里面插入 id=9 的记录会被锁住,但是会话 3 修改 id =16 是可以正常执行的,因为 id = 16 这条记录并没有加锁。
唯一索引范围查询
会话 1 加锁变化过程如下:
- 最开始要找的第一行是 id = 8,因此 next-key lock(4,8],但是由于 id 是唯一索引,且该记录是存在的,因此会退化成记录锁,也就是只会对 id = 8 这一行加锁;
- 由于是范围查找,就会继续往后找存在的记录,也就是会找到 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 加锁变化过程如下:
- 先会对普通索引 b 加上 next-key lock,范围是(4,8];
- 然后因为是非唯一索引,且查询的记录是存在的,所以还会加上间隙锁,规则是向下遍历到第一个不符合条件的值才能停止,因此间隙锁的范围是(8,16)。
所以,会话1的普通索引 b 上共有两个锁,分别是 next-key lock (4,8] 和间隙锁 (8,16) 。
那么,当会话 2 往间隙锁里插入 id = 9 的记录就会被锁住,而会话 4 是因为更改了 next-key lock 范围里的记录而被锁住的。
然后因为 b = 16 这条记录没有加锁,所以会话 5 是可以正常执行的。
Demo(普通索引等值查找记录不存在)
会话 1 加锁变化过程如下:
- 先会对普通索引 b 加上 next-key lock,范围是(8,16];
- 但是由于查询的记录是不存在的,所以不会再额外加个间隙锁,但是 next-key lock 会退化为间隙锁,最终加锁范围是 (8,16)。
会话 2 因为往间隙锁里插入了 b = 9 的记录,所以会被锁住,而 b = 16 是没有被加锁的,因此会话 3 的语句可以正常执行。
普通索引范围查找
会话 1 加锁变化过程如下:
- 最开始要找的第一行是 b = 8,因此 next-key lock(4,8],但是由于 b 不是唯一索引,并不会退化成记录锁。
- 但是由于是范围查找,就会继续往后找存在的记录,也就是会找到 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]