001 MySQL之间隙锁 之 官网解释

学软件技术，读第一手资料，去官方网站：MySQL 5.7参考手册

---

Gap Locks

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. 
For example, SELECT c1 FROM t WHERE c1 BETWEEN 10 and 20 FOR UPDATE; 
prevents other transactions from inserting a value of 15 into column t.c1, 
whether or not there was already any such value in the column, 
because the gaps between all existing values in the range are locked.

A gap might span a single index value, multiple index values, or even be empty.

Gap locks are part of the tradeoff between performance and concurrency, 
and are used in some transaction isolation levels and not others.

Gap locking is not needed for statements that lock rows using a unique index 
to search for a unique row. (This does not include the case that 
the search condition includes only some columns of a multiple-column unique index; 
in that case, gap locking does occur.) For example, 
if the id column has a unique index, 
the following statement uses only an index-record lock 
for the row having id value 100 and it does not matter 
whether other sessions insert rows in the preceding gap:

SELECT * FROM child WHERE id = 100;

If id is not indexed or has a nonunique index, the statement does lock the preceding 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.

Gap locks in InnoDB are “purely inhibitive”, which means that their only purpose 
is to prevent other transactions from inserting to the gap. Gap locks can co-exist. 
A gap lock taken by one transaction does not prevent another transaction 
from taking a gap lock on the same gap. There is no difference between 
shared and exclusive gap locks. They do not conflict with each other, 
and they perform the same function.

Gap locking can be disabled explicitly. This occurs if you change 
the transaction isolation level to READ COMMITTED or enable the innodb_locks_unsafe_for_binlog system
 variable (which is now deprecated). 
In this case, gap locking is disabled for searches and index scans and is 
used only for foreign-key constraint checking and duplicate-key checking.

There are also other effects of using the READ COMMITTED isolation level or
 enabling innodb_locks_unsafe_for_binlog. 
Record locks for nonmatching rows are 
released after MySQL has evaluated the WHERE condition. For UPDATE statements,
 InnoDB does a “semi-
MySQL can determine whether the row matches the WHERE condition of the UPDATE. 

大致的解释：

间隙锁是在在索引记录间隙中的一种锁，或者是锁在第一个之前或最后一个之后。

例如：

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

上面语句就会阻止其他事物插入一个c1列的值在10-20之间，无论之前有没有值存在，因为这之间的所有间隙都已经被锁。

间隙可能跨越单个索引，多个索引值，也可能是空。

间隙锁是在性能与并发上做了一个权衡。在有些事物隔离级别上存在，有的则没有。

间隙锁在使用唯一索引查询唯一行记录是不需要的，例如id列有唯一索引，那么下面语句只会对id=100的行使用索引记录锁定，其他会在这行记录之前插入也没有关系。不会影响查询结果。如果id不是索引列或不是唯一索引列，那么这条语句就 会锁定100之前的所有间隙。

SELECT * FROM child WHERE id = 100;

在这里还值得注意的是，可以通过不同的事务将冲突的锁保持在间隙上。例如，事务A可以在间隙上保留一个共享的间隙锁（间隙S锁），而事务B可以在同一间隙上保留排他的间隙锁（间隙X锁）。允许冲突的间隙锁的原因是，如果从索引中清除记录，则必须合并由不同事务保留在记录上的间隙锁。

间隙锁在InnoDB 引擎中是“纯抑制性”，怎么讲呢，这个意思就说他们的唯一目的就是阻止其他事务在间隙上插入记录。还有一点比较重要，间隙锁是可以共存的，一个事务在一个间隙上加锁并不会阻止其他事务在相同间隙上加锁，这里的共存和锁类型无关，无论是共享锁还是排它锁，他们互不冲突，执行相同的功能。

间隙锁也可以声明为禁用，在你修改事务隔离级别为 READ COMMITTED 的时候。在这种情况下，间隙锁在查询、索引扫描是被禁用，只有在外键约束性检查和重复键校验是可用的

在使用  READ COMMITTED  事务隔离级别的时候也有带来一些其他影响，mysql评估where条件中未匹配行的记录锁，对于更新语句，InnoDB 有一个“半一致”读取，当更新出现锁等待的时候，InnoDB 返回最新提交版本，然后由mysql决定是否匹配where的更新条件，如果满足where条件，那么再次进入innodb层，真正加锁或者发生锁等待。