gap lock(间隙锁),主要用在mysql中解决RR级别事务隔离的幻读问题
下面是mysql对gap lock给出的官方解释:
A gap lock is a lock on a gap between index records, or a lock on the gap before the first or after the last index record。
在官方详细解析中有提到一个要点,也是导致本次事务死锁的根本原因
Gap locks in InnoDB are “purely inhibitive”, which means they only stop other transactions from inserting to the gap. They do not prevent different transactions from taking gap locks on the same gap. Thus, a gap X-lock has the same effect as a gap S-lock.
大致意思是:X-lock的gap和S-lock的效果是一样的,都是可以被多个事务获取到。
下面开始先分析gap lock 在唯一索引中的表现
表结构:
建表sql
DROP TABLE IF EXISTS `staff_role_biz_role_id`;
CREATE TABLE `staff_role_biz_role_id` (
`