mysql隔离级别RR下的行锁、临键锁、间隙锁详解及运用

一：mysql 锁的基本概念

锁：悲观锁、乐观锁

悲观锁：写锁 for update、读锁for share 

写锁：只允许当前事务读写，其它事务全部等待，包括读取数据，锁的数据范围需要具体分析

读锁：允许所有事务进行读取，但不允许写，首先获得数据读锁的事务可以在事务中进行写操作，一旦进行了写操作，其它事务就会全部进行等待，包括读也不允许了，所以一般需要读又要写的使用“写锁”

二：加锁数据范围规则

        原则1：访问到的数据都要加"临键锁"
        原则2：查询时会在索引上访问到第一个不满足需要查询数据的值为止，不满足条件的第一个值上也会加"临键锁"
        原则3：索引查询(除唯一索引等值{查到了值}查询和非唯一索引范围查询以外)都加 "临键锁退化为间隙锁"
        原则4：唯一索引等值查询{查到了值}退化为"行锁"

        bug： 唯一索引范围查询时依旧会往下一个值查询，且不会退化为间隙锁，依旧是左开右闭；不过有的mysql版本是左开又开(间隙锁)；范围查询有等于的时候不会向下一个滑寻找不等于自己的值；如果存在Bug的版本就和非唯一索引的结果一样，都是临建锁+下滑；如果修复了bug那就是间隙锁    {仅只针对唯一索引范围查询存在的Bug}

结论:

        理论上： 

        当前测试的mysql版本：

           结果：

        1.唯一索引和非唯一索引（除了唯一索引等值查询）都会向右遍历且最后一个值不满足等值条件的时候才停止。

        2.唯一索引和非唯一索引“范围查询”都不会退化为间隙锁，非唯一索引本身就不会退化，唯一索引是因为mysql版本的bug原因不退化 ==》     范围查询存在BUG就会下滑且都加临建锁

        3.非唯一索引和唯一索引等值查询(未差查到值) 都是会下滑且都会优化为间隙锁

可以通过  select * from performance_schema.data_locks; 检验

测试数据

一：索引

        1、非唯一索引

        2、唯一索引

二：非唯一索引

select * from  cs where age=20  for update
-- 非唯一索引等值查询{查到了值}
-- 首先给20上临键锁，锁范围(10,20]
-- 根据原则2会去寻找首个不等于20的值为止，也就是30,给30上临键锁，范围(20,30]，又根据原则3退化为间隙锁范围(20,30)
-- 最终范围(10,30)

select * from  cs where age=21  for update
-- 非唯一索引等值查询{没查到值}
-- 首先21的值没查到，没查到的值不用上临键锁
-- 根据原则2会去寻找首个不等于21的值为止，也就是30,给30上临键锁，范围(20,30]，又根据原则3退化为间隙锁范围(20,30)
-- 最终范围(20,30)

select * from  cs where age=2000  for update
-- 非唯一索引等值查询{没查到值}
-- 首先2000的值没查到，并且是超出了表的已有数据范围之外的数据，可以自己默认上个临键锁(100,2000]
-- 根据原则2会去寻找首个不等于2000的值为止，但此时没有符合条件的，此时就会一直找+∞，所以会锁定(2000,+∞]的数据
-- 最终范围(100,+∞]

select * from  cs where age>20 for update
-- 非唯一索引范围查询
-- 首先>20是属于20-30之间的一个不确定值(不含20),也就是不存在的值,没查到的值不用上临键锁
-- 根据原则2会去寻找首个不等于这个大于20的值为止，也就是30，给30上临键锁，范围(20,30],由于是范围查询不会退化
-- 又因为是>20没有后部分的区间，所以会加锁(30,+∞]
-- 最终范围(20,+∞]

select * from  cs where age>=20 for update
-- 非唯一索引范围查询
-- 首先>=20,20上临键锁，锁范围(10,20]
-- 根据原则2会去寻找首个不等于这个等于20的值为止，也就是30，给30上临键锁，范围(20,30],由于是范围查询不会退化
-- 又因为是>=20没有后部分的区间，所以会加锁(30,+∞]
-- 最终范围(10,+∞]

select * from  cs where age>20 and age<22 for update
-- 非唯一索引范围查询
-- 首先>20是第一范围区间是在20-30之间的一个不确定值(不含20),也就是不存在的值,没查到的值不用上临键锁
-- 根据原则2会去寻找下一个首个不等于这个大于20的值为止，也就是30，优先暂时给30上临键锁，范围(20,30],由于是范围查询不会退化
-- 再处理 <22，也是第一范围区间是在20-30之间的一个不确定值(不含230)
-- 根据原则2会去寻找下一个首个不等于这个小于22的值为止，也就是30，优先暂时给30上临键锁，范围(20,30],由于是范围查询不会退化
-- 最终范围(20,30]

select * from  cs where age>20 and age<30 for update
-- 非唯一索引范围查询
-- 首先>20是第一范围区间是在20-30之间的一个不确定值(不含20),也就是不存在的值,没查到的值不用上临键锁
-- 根据原则2会去寻找下一个首个不等于这个大于20的值为止，也就是30，优先暂时给30上临键锁，范围(20,30],由于是范围查询不会退化
-- 再处理 <30，也是第一范围区间是在20-30之间的一个不确定值(不含30)
-- 根据原则2会去寻找下一个首个不等于这个小于30的值为止，也就是30，优先暂时给30上临键锁，范围(20,30],由于是范围查询不会退化
-- 最终范围(20,30]

select * from  cs where age>20 and age<=30 for update
-- 非唯一索引范围查询
-- 首先>20是第一范围区间是在20-30之间的一个不确定值(不含20),也就是范围内不存在的值,没查到的值不用上临键锁
-- 根据原则2会去寻找下一个首个不等于这个大于20的值为止，也就是30，优先暂时给30上临键锁，范围(20,30],由于是范围查询不会退化
-- 再处理 <=30，30上临键锁，锁范围(20,30]
-- 根据原则2会去寻找下一个首个不等于这个30的值为止，也就是35，给35上临键锁，范围(30,35],由于是范围查询不会退化
-- 最终范围(20,35]

select * from  cs where age>=20 and age<30 for update
-- 非唯一索引范围查询
-- 首先>=20，20上临键锁，锁范围(10,20]
-- 根据原则2会去寻找下一个首个不等于这个20的值为止，也就是30，优先暂时给30上临键锁，范围(20,30],由于是范围查询不会退化
-- 再处理 <30，也是第一范围区间是在20-30之间的一个不确定值(不含30),也就是不存在的值,没查到的值不用上临键锁
-- 根据原则2会去寻找下一个首个不等于这个小于30的值为止，也就是30，给30上临键锁，范围(20,30],由于是范围查询不会退化
-- 最终范围(10,30]

select * from  cs where age>=20 and age<=30 for update
-- 非唯一索引范围查询
-- 首先>=20，20上临键锁，锁范围(10,20]
-- 根据原则2会去寻找下一个首个不等于这个20的值为止，也就是30，优先暂时给30上临键锁，范围(20,30],由于是范围查询不会退化
-- 再处理 <=30，30上临键锁，锁范围(20,30]
-- 根据原则2会去寻找下一个首个不等于这个30的值为止，也就是35，给35上临键锁，范围(30,35],由于是范围查询不会退化
-- 最终范围(10,35]

select * from  cs where age>=20 and age<=100 for update
-- 非唯一索引范围查询
-- 首先>=20，20上临键锁，锁范围(10,20]
-- 根据原则2会去寻找下一个首个不等于这个20的值为止，也就是30，优先暂时给30上临键锁，范围(20,30],由于是范围查询不会退化
-- 再处理 <=100，100上临键锁，锁范围(42,100]
-- 根据原则2会去寻找下一个首个不等于这个100的值为止,但100后面没有值了，此时就锁(100,+∞]
-- 最终范围(10,+∞]

select * from  cs where age>=1 and age<=1000 for update
-- 非唯一索引范围查询
-- 首先>=1，1上临键锁，锁范围(-∞,1]
-- 根据原则2会去寻找下一个首个不等于这个1的值为止，也就是10，优先暂时给10上临键锁，范围(1,10],由于是范围查询不会退化
-- 再处理 <=1000，1000上临键锁，锁范围(42,100]
-- 根据原则2会去寻找下一个首个不等于这个1000的值为止,但此时没有符合条件的，此时就会一直找+∞，所以会锁定(1000,+∞]的数据
-- 由于是范围查询，根据原则1被查询的到的数据都会加锁，所以1-100的数据都会加临键锁
-- 最终范围[-∞,+∞]

select * from  cs where age>=1 and age<100 for update
-- 非唯一索引范围查询
-- 首先>=1，1上临键锁，锁范围(-∞,1]
-- 根据原则2会去寻找下一个首个不等于这个1的值为止，也就是10，优先暂时给10上临键锁，范围(1,10],由于是范围查询不会退化
-- 再处理 <100，也是第一范围区间是在42-100之间的一个不确定值(不含100),也就是不存在的值,没查到的值不用上临键锁
-- 根据原则2会去寻找下一个首个不等于这个小于100的值为止，也就是100，给100上临键锁，范围(42,100],由于是范围查询不会退化
-- 由于是范围查询，根据原则1被查询的到的数据都会加锁，所以1-100的数据都会加临键锁
-- 最终范围[-∞,100]

二：唯一索引  【范围中带等于的是因为某些版本的bug会下滑，不是所有版本都会下滑，有些版本会退化为行锁，临键锁也会退化为间隙锁】

select * from  cs where wy=20  for update
-- 唯一索引等值查询{查到了值}
-- 首先给20上临键锁，锁范围(10,20]
-- 根据原则2会去寻找首个不等于20的值为止，也就是30,给30上临键锁，范围(20,30]，又根据原则3退化为间隙锁范围(20,30)

-- 根据原则4所有的锁都将退化为行锁
-- 最终范围 20

select * from  cs where wy=21  for update
-- 唯一索引等值查询{没查到值}
-- 首先21的值没查到，没查到的值不用上临键锁
-- 根据原则2会去寻找首个不等于21的值为止，也就是30,给30上临键锁，范围(20,30]，又根据原则3退化为间隙锁范围(20,30)
-- 最终范围(20,30)

select * from  cs where wy=2000  for update
-- 唯一索引等值查询{没查到值}
-- 首先2000的值没查到，并且是超出了表的已有数据范围之外的数据，可以自己默认上个临键锁(100,2000]
-- 根据原则2会去寻找首个不等于2000的值为止，但此时没有符合条件的，此时就会一直找+∞，所以会锁定(2000,+∞]的数据

--但因为是唯一索引，如果超出数据范围外就不会再上锁
-- 最终范围    无锁

select * from  cs where wy>20 for update
-- 唯一索引范围查询
-- 首先>20是属于20-30之间的一个不确定值(不含20),也就是不存在的值,没查到的值不用上临键锁
-- 根据原则2会去寻找首个不等于这个大于20的值为止，也就是30，给30上临键锁，范围(20,30],

-- 根据原则3会退化为间隙锁(20,30)  这里会不会退化有带考证，可能会因为Bug不退化
-- 又因为是>20没有后部分的区间，所以会加锁(30,+∞]
-- 最终范围(20,+∞]

select * from  cs where wy>=20 for update
-- 唯一索引范围查询
-- 首先>=20,20上临键锁，锁范围(10,20]，根据原则3会退化为间隙锁(10,20) 这里会不会退化有带考证，可能会因为Bug不退化，根据原则4还会上行锁 20    所以最终(10,20]
-- 根据原则2会去寻找首个不等于这个等于20的值为止，也就是30，给30上临键锁，范围(20,30] 

-- 根据原则3会退化为间隙锁(20,30)  这里会不会退化有带考证，可能会因为Bug不退化
-- 又因为是>=20没有后部分的区间，所以会加锁(30,+∞]
-- 最终范围(10,+∞]     当然还有对应的主键id锁

select * from  cs where wy>20 and wy<22 for update        
-- 唯一索引范围查询
-- 首先>20是第一范围区间是在20-30之间的一个不确定值(不含20),也就是不存在的值,没查到的值不用上临键锁
-- 根据原则2会去寻找下一个首个不等于这个大于20的值为止，也就是30，优先暂时给30上临键锁，范围(20,30],根据原则3会退化为间隙锁(20,30)   但因为bug，还是(20,30]
-- 再处理 <22，也是第一范围区间是在20-30之间的一个不确定值(不含30)
-- 根据原则2会去寻找下一个首个不等于这个小于22的值为止，也就是30，优先暂时给30上临键锁，范围(20,30],根据原则3会退化为间隙锁(20,30)   但因为bug，还是(20,30]
-- 最终范围(20,30]        因为bug根据原则2会锁住下一个值的临建锁且不会退化

select * from  cs where wy>20 and wy<30 for update 
-- 唯一索引范围查询
-- 首先>20是第一范围区间是在20-30之间的一个不确定值(不含20),也就是不存在的值,没查到的值不用上临键锁
-- 根据原则2会去寻找下一个首个不等于这个大于20的值为止，也就是30，优先暂时给30上临键锁，范围(20,30],根据原则3会退化为间隙锁(20,30)   但因为bug，还是(20,30]
-- 再处理 <30，也是第一范围区间是在20-30之间的一个不确定值(不含30)
-- 根据原则2会去寻找下一个首个不等于这个小于30的值为止，也就是30，优先暂时给30上临键锁，范围(20,30],根据原则3会退化为间隙锁(20,30)  但因为bug，还是(20,30]
-- 最终范围(20,30]

select * from  cs where wy>20 and wy<=30 for update   
-- 唯一索引范围查询
-- 首先>20是第一范围区间是在20-30之间的一个不确定值(不含20),也就是范围内不存在的值,没查到的值不用上临键锁
-- 根据原则2会去寻找下一个首个不等于这个大于20的值为止，也就是30，优先暂时给30上临键锁，范围(20,30],根据原则3会退化为间隙锁(20,30)   但因为bug，还是(20,30]
-- 再处理 <=30，30上临键锁，锁范围(20,30]
-- 根据原则2会去寻找下一个首个不等于这个30的值为止，也就是35，给35上临键锁，范围(30,35],根据原则3会退化为间隙锁(30,35)   但因为bug，还是(20,35]
-- 最终范围(20,35]

select * from  cs where wy>=20 and wy<30 for update     
-- 唯一索引范围查询
-- 首先>=20，20上临键锁，锁范围(10,20]  
-- 根据原则2会去寻找下一个首个不等于这个20的值为止，也就是30，优先暂时给30上临键锁，范围(20,30],根据原则3会退化为间隙锁(20,30)

--根据原则4退化为行锁 最终锁定行锁   20     但因为bug，还是(20,30]
-- 再处理 <30，也是第一范围区间是在20-30之间的一个不确定值(不含30),也就是不存在的值,没查到的值不用上临键锁
-- 根据原则2会去寻找下一个首个不等于这个小于30的值为止，也就是30，给30上临键锁，范围(20,30],根据原则3会退化为间隙锁(20,30)   但因为bug，还是(20,30]
-- 最终范围(10,30]

select * from  cs where wy>=20 and wy<=30 for update     
-- 唯一索引范围查询
-- 首先>=20，20上临键锁，锁范围(10,20] 
-- 根据原则2会去寻找下一个首个不等于这个20的值为止，也就是30，优先暂时给30上临键锁，范围(20,30],由于是范围查询不会退化   但因为bug，还是(20,30]
-- 再处理 <=30，30上临键锁，锁范围(20,30]
-- 根据原则2会去寻找下一个首个不等于这个30的值为止，也就是35，给35上临键锁，范围(30,35],根据原则3会退化为间隙锁(30,35)    但因为bug，还是(30,35]
-- 最终范围(10,35]

select * from  cs where wy>=20 and wy<=100 for update  
-- 唯一索引范围查询
-- 首先>=20，20上临键锁，锁范围(10,20]
-- 根据原则2会去寻找下一个首个不等于这个20的值为止，也就是30，优先暂时给30上临键锁，范围(20,30],  但因为bug，还是(20,30]
-- 再处理 <=100，100上临键锁，锁范围(42,100]
-- 根据原则2会去寻找下一个首个不等于这个100的值为止,但100后面没有值了，此时就锁(100,+∞]
-- 最终范围(10,+∞]

select * from  cs where wy>=1 and wy<=1000 for update  
-- 唯一索引范围查询
-- 首先>=1，1上临键锁，锁范围(-∞,1]
-- 根据原则2会去寻找下一个首个不等于这个1的值为止，也就是10，优先暂时给10上临键锁，范围(1,10],  但因为bug，还是(1,10]
-- 再处理 <=1000，1000上临键锁，锁范围(42,100]
-- 根据原则2会去寻找下一个首个不等于这个1000的值为止,但此时没有符合条件的，此时就会一直找+∞，所以会锁定(1000,+∞]的数据
-- 由于是范围查询，根据原则1被查询的到的数据都会加锁，所以1-100的数据都会加临键锁
-- 最终范围[-∞,+∞]

select * from  cs where wy>=1 and wy<100 for update   
-- 唯一索引范围查询
-- 首先>=1，1上临键锁，锁范围(-∞,1]
-- 根据原则2会去寻找下一个首个不等于这个1的值为止，也就是10，优先暂时给10上临键锁，范围(1,10],  但因为bug，还是(1,10]
-- 再处理 <100，也是第一范围区间是在42-100之间的一个不确定值(不含100),也就是不存在的值,没查到的值不用上临键锁
-- 根据原则2会去寻找下一个首个不等于这个小于100的值为止，也就是100，给100上临键锁，范围(42,100],  但因为bug，还是(1,10]
-- 由于是范围查询，根据原则1被查询的到的数据都会加锁，所以1-100的数据都会加临键锁
-- 最终范围[-∞,100]