mysql可重复读隔离级别加锁分析

问题

myql可重复读隔离级别下可能会导致插入阻塞，问题复现如下

表中有3列都是int类型 

其索引情况如下：

id为主索引，c,d为普通索引

现在开始制作问题：

在这里我分别开启两个事务：第一个事务中执行一个update 语句更新一个不存在的数据，这里没有执行commit也就事务未结束，根据二阶段锁协议，锁的释放是在事务结束后。

现在另一个窗口中执行insert操作，这里它一直在阻塞中。等待上面事务commit或者超时

过一段事务出现下面提示，出现无法获取锁的错误

 

想看详细信息，可以执行show engine innodb  status \G;

上面告诉我们插入意向等待获取主键id上面的锁

 

原因分析：

1.锁是加在索引上面的，上面更新时用到的锁是id

2.根据可重复读加载锁规则为next-key lock,上面id的锁 是 （0,1],(1,5],(5,15],(15,20],(20,25],(25,supernum]

最后的sumpernum是很大的数字可以认为是无穷大

 

我们执行执行 update t_test set c = 100 where id =100;由于id在最后一个范围中所以它持有的间隙锁是（25，supernum）,间隙锁和间隙锁不会有冲突，但和插入意向锁存在冲突。所以上面会阻塞住

 

如何解决：

由于间隙锁只存在可重复读隔离级别下，可以修改数据库的隔离级别为读提交

第二种就是减少其锁的时间，可以通过减少事务执行时间，减少锁等待时间