MySql技术内幕:MySql的三种行锁算法(解决幻读)——详解
文章目录
一. MySql的三种行锁算法
1. 概述
-
mysql中InnoDB存储引擎支持三种行锁的算法:
- Record Lock:单个记录上的锁
- Gap Lock: 间隙锁,锁定一个范围,但不包含记录本身
- Next-Key Lock:Record Lock + Gap Lock,锁定一个范围,并且锁定记录本身
2. Record Lock
- Record Lock总是会锁住索引记录,如果InnoDB在建表的时候没有设置索引,那么会使用隐式的主键来进行锁定
- InnoDB存储引擎在建表的时候如果没有指定主键,会寻找第一列非空的列作为主键,如果没有的话会自动生成一列为6字节的主键,也就是上面所说的隐式主键。
3.Next-Key Lock
-
Next-Key Lock的实现是结合了Record Lock和Gap Lock。在这种锁算法下,InnoDB对行的查询都是使用这种锁定算法
-
例如一个索引有10,11,13,20这四个值,那么该索引可能被Next-Key Locking的区间为:
- (-∞,10】
- (10,11】
- (11,13】
- (13,20】
- (20,+∞)
-
还有另外一种比较相似的锁算法:previous-key locking技术,区别其实不大,它会将区间分为:
- (-∞,10)
- 【10,11)
- 【11,13)
- 【13,20)
- 【20,+∞)
-
采用这种算法的主要目的就是为了解决幻读的问题,也就是说当我们某个事物进行插入更新的时候,会锁住一定的范围,在这个范围内就不允许其他的事务同时进行插入。但是这种范围锁的机制只针对于辅助索引,也就是对聚集索引(单个索引属性)的插入Next-Key Lock会自动降级成Record Lock。下面会详细讲解
4. Next-Key Lock降级Record Lock
- 幻读:幻读指的是在一个事务内多次进行范围查询的时候,两次查询的内容行数不同,也就是在这个时间段有其他的事务对该范围进行了插入操作。
- 刚刚谈到Next-Key Lock是为了解决幻读的问题,也就是说当我们某个事物进行插入的时候,会锁住一定的范围,在这个范围内就不允许其他的事务同时进行插入。
- 实际上,当我们查询的索引含有唯一属性时,Next-Key Lock会进行优化,降级为Record Lock,也就是说锁住的仅仅是索引本身,而不是范围,下面举例来说明:
建表语句: 此时表中有主键 1,2,5
执行下表的语句:
- 首先根据主键查找5,并进行更新操作,按理来说此时应该将(2,5)的范围进行加X锁(排它锁)
- 之后在事务A还未提交之前执行事务B,插入主键为4的记录,如果已经加上了范围锁,按理来说是需要进行等待的,但是实际上不需要等待,直接完成,提交
- 所以当我们查询的索引含有唯一属性(例如聚集索引)时,Next-Key Lock会进行优化,降级为Record Lock,也就是说锁住的仅仅是索引本身,而不是范围
5.Next-Key Lock实例说明
建表语句: 此时表z中的列b是辅助索引
-
若事务A先执行 Select * from z where b = 3 for update,此时事务A还未提交
-
之后事务B执行下列语句:
-
Select * from z where a = 5
-
Insert into z select 4,2
-
Insert into z select 6,5
-
-
第一个sql语句不能被执行,因为事务A中的语句已经对聚集索引中a = 5 的行加上了X锁(排它锁),因此执行会被阻塞
-
第二个sql语句插入主键4没有问题,但是插入辅助索引2,这就有问题了,因为事务A中的SQL语句已经对辅助索引中的(1,3)加上了范围锁,所以无法插入辅助索引2,该sql语句还是会被阻塞
-
第三个sql语句插入主键6没有问题,插入辅助索引5也不在(1,3)范围内,但是却在锁定的另一个范围(3,6)内,所以也会被阻塞
-
如果事务B执行以下sql语句则不会被阻塞:
6.总结
- 其实很容易理解,mysql使用一种范围锁的机制解决了幻读的问题,每次进行插入更新操作的时候,会加上一个范围X锁,这样其他事务在同一时间内无法对该范围进行更新插入操作
- 但是要注意一点,加范围锁会导致并发的性能降低,所以在对插入更新索引含有唯一属性值的时候(也就是类似于聚集索引不重复的索引时),就不会加范围锁,而是降级为行记录锁,这样提高了并发能力