MySQL-加锁规则(间隙锁、临键锁、行锁、表锁)

环境

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 where id = 9 for update;	-
2