【MySQL】MySQL gap lock产生时机

1）提出问题：我们通常说在Repeate read下面，会有next-key lock（LOCK_ORDINARY）对应值0，而READ COMMITTED隔离级别下只会有记录锁LOCK_REC_NOT_GAP（对应值1024），那么什么时候会有gap lock(LOCK_GAP)对应值512？


2）官方的一个死锁例子（之所以会选这个例子，是因为这个例子非常典型，为了更好的效果，我将隔离级别设置为READ COMMITTED）：

    创建表并插入数据
    CREATE TABLE `locktest6` (   `id` int(11) NOT NULL,   `a` int(11) NOT NULL,   `b` varchar(30) NOT NULL DEFAULT 'xDDD',   PRIMARY KEY (`id`),   UNIQUE KEY `a` (`a`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

    INSERT INTO `locktest6` VALUES (3,2,'xddd'),(9,20,'ddd'),(12,13,'ddd'),(19,7,'cccc'),(20,5,'abcd'),(21,4,'fff');

    开启三个会话，会话的顺序是（1->2->3）
    session 1：
    



    session 2：
    

    session 3
    

    实验现象，这里session2、session3会阻塞（这里读者可以自己实验）

    现象分析：
    我们来看阻塞时的锁获取与等待情况
                            


这里唯一键a=17的二级索引加了LOCK_X|LOCK_REC_NOT_GAP|LOCK_REC锁，其次这条记录上面还有两把锁（LOCK_S|LOCK_ORDINARY|LOCK_WaiTTING）在等待，分别是session2与session3的

这里的原因是：
session 1的insert 本来不加锁(是隐式锁，隐式锁是一种延迟加锁的策略);

当session插入a=17的记录时，发现与session1重复，这时session2会给session1加锁，锁为LOCK_X|LOCK_REC_NOT_GAP|LOCK_REC，同时把自己置为等待状态LOCK_S|LOCK_ORDINARY|LOCK_WAITTING，注意，这里为什么自己（session2）等待的时候是LOCK_S而不是LOCK_X呢，其实是为了提高并发；

session 3处于等待是因为session1 a=17的记录已经被session2加上了LOCK_X|LOCK_REC_NOT_GAP|LOCK_REC锁，而session3的a=17与session1的a=17重复，为了检测唯一性，也会将自己置为等待状态，锁为LOCK_S|LOCK_ORDINARY|LOCK_WAITTING


回滚session1，session3会成为死锁牺牲品，session2插入成功

session2

session3




这是的死锁情况以及加锁情况

这里我们发现了gap锁的踪迹，我们稍后分析
死锁牺牲session3之后，这时的session2的加锁情况

这里锁信息连到了一起，哈哈，如何解释呢，我们稍后分晓


先分析上面的死锁：
session 1 rollback后，二级索引记录为(33,17)的记录的被删除，上面的session2，session3上的LOCK_S|LOCK_ORDINARY|LOCK_WAITTING将他的gap锁分别迁移到下一条记录，即二级索引（20,9）的记录上由两把锁，分别为session2与session占有，锁为LOCK_S|LOCK_GAP|LOCK_REC，即死锁图中的lock mode s lock gap before rec；与此同时，session 2与session3在插入的时候，还要检测下一条记录上（即（20,9）的记录上）的锁是否与LOCK_X|LOCK_GAP|LOCK_INTENSION冲突，这样就形成了session2的LOCK_X|LOCK_GAP|LOCK_INTENSION在等待session3 的LOCK_S|LOCK_GAP|LOCK_REC释放，而session3的LOCK_X|LOCK_GAP|LOCK_INTENSION则在等待session2的LOCK_S|LOCK_GAP|LOCK_REC释放，这样就形成了死锁，这里事务选择回滚了session3

再分析session2的加锁情况：
session3回滚后 ，session2获得了LOCK_X|LOCK_GAP|LOCK_INTENSION锁与LOCK_S|LOCK_GAP|LOCK_REC，这个锁加在记录(20,9)上；heap no 8的记录即session2插入的记录也获得了LOCK_S|LOCK_GAP|LOCK_REC锁，这是因为插入时，会进行锁分裂，将heap no 8下一条记录的(20,9)的锁LOCK_S|LOCK_GAP|LOCK_REC迁移到了heap no 8的记录上；为啥上面的锁信息会连在一起呢，即既有LOCK_S|LOCK_ORDINARY|LOCK_REC又有LOCK_S|LOCK_GAP|LOCK_REC呢，答案是：这里的LOCK_S|LOCK_ORDINARY|LOCK_REC只是表示下面行LOCK_S|LOCK_GAP|LOCK_REC有LOCK_S|LOCK_ORDINARY|LOCK_REC继承而来，并不表示记录上还加了LOCK_S|LOCK_ORDINARY|LOCK_REC锁


3）总结
    gap锁一般很少见到，当唯一索引检测重复的值的时候，往往会产生gap锁；另外当可重复读隔离级别下，做等值查询时，也会碰到gap锁（这个读者可以自己去实验），时间仓促，没有整理。