环境
MySQL version 5.6.47
隔离级别:可重复读(RR)
加锁规则
可重复读隔离级别下加锁规则
next-key lock = 间隙锁 + 行锁
原则1:加锁的基本单位是next-key lock。next-key lock是前开后闭区间。
原则2:查找过程中访问到的对象才会加锁。
原则3:索引上的等值查询,给唯一索引加锁的时候,next-key lock退化为行锁。
原则4:索引上的等值查询,向右遍历时且最后一个值不满足等值条件的时候,next-key lock退化为间隙锁。
原则5:唯一索引上的范围查询会访问到不满足条件的第一个值为止。【注释:其实我不认为这是bug,只是唯一索引和非唯一索引保持一致】
知识点
锁是加在索引上的而不是加在数据上
锁是加在索引上的而不是加在数据上
lock in share mode与for update的区别
区别一:
lock in share mode加的是读锁
for update 加的是写锁
区别二:
在非主键索引上通过两种方式加锁是有区别的。
lock in share mode 只锁覆盖索引,也就说:lock in share mode只锁非主键索引对应的B+树中的索引内容。
for update:如果对非主键索引使用 for update加锁就不一样了。 执行 for update 时,mysql会认为你接下来要更新数据,因此会通过非主键索引中的主键值继续在主键索引对应的b+数上查找到对应的主键索引项进行加锁,也就是说:for update的加锁内容是非主键索引树上符合条件的索引项,以及这些索引项对应的主键索引树上相应的索引项。在两个索引上都加了锁。
案例数据
CREATE TABLE `t` (
`id` int(11) NOT NULL,
`c` int(11) DEFAULT NULL,
`d` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `c` (`c`)
) ENGINE=InnoDB;
insert into t values (5,5,5),
(10,10,10),(15,15,15),(20,20,20),(25,25,25);
id(主键) |
c(普通索引) |
d(无索引) |
5 |
5 |
5 |
10 |
10 |
10 |
15 |
15 |
15 |
20 |
20 |
20 |
25 |
25 |
25 |
以上数据为了解决幻读问题,更新的时候不只是对上述的五条数据增加行锁,还对于中间的取值范围增加了6个临键锁,(-∞,5](5,10](10,15](15,20](20,25](25,+supernum] (其中supernum是数据库维护的最大的值。为了保证临键锁都是左开右闭原则。)
案例一:间隙锁简单案例
步骤 |
事务A |
事务B |
1 |
begin; select * from t where id = 11 for update; |
- |
2 |
- |
insert into t value(10,10,10); //Duplicate entry '10' for key 'PRIMARY’ insert into t value(12,12,12) //blocked insert into t value(15,15,15) // Duplicate entry '15' for key 'PRIMARY' |
3 |
commit; |
- |
当有如下事务A和事务B时,事务A会对数据库表增加(10,15]这个区间锁,经过原则4,退化为间隙锁(10,15)。
这时insert id = 12 的数据的时候就会因为区间锁(10,15)而被锁住无法执行。
这时insert id = 10 和 insert id = 15的数据是未被锁定,报错:Duplicate entry '15' for key 'PRIMARY'
案例二: 间隙锁死锁问题
步骤 |
事务A |
事务B |
1 |
begin; select * from t |