gap间隙锁

1、什么式gap锁

（1）在索引记录之间，或者在索引之前，或者索引之后的区间上加锁，就是gap锁。比如：

SELECT c1 FROM t WHERE c1 BETWEEN 10 and 20 FOR UPDATE;

 

由于在c1=10和c2=20之间已经加上gap锁，所以不管数据表中是否有c1=15这条数据，这个sql都会阻止试图插入c1=15的事务。

（2）一个gap锁可能会锁一个索引、多个索引、或者空索引。

（3）gap锁权衡了性能和并发，并且它只用作于特定的隔离级别。

 

2、什么时候会出现gap间隙锁

 用唯一索引查询唯一的行数据，并不会产生gap锁。比如：

SELECT * FROM child WHERE id = 100;

如果id是唯一索引，就不会产生gap锁；如果id不是索引或者id不是唯一索引，那么会产生gap锁。

It is also worth noting here that conflicting locks can be held on a gap by different transactions. For example, transaction A can hold a shared gap lock (gap S-lock) on a gap while transaction B holds an exclusive gap lock (gap X-lock) on the same gap. The reason conflicting gap locks are allowed is that if a record is purged from an index, the gap locks held on the record by different transactions must be merged.

比如，事务A在间隙上拥有共享锁，事务B在同样的间隙上拥有排他锁。