【官方文档】mysql gap lock next-key lock
【官方文档】mysql gap lock next-key lock
gap lock
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:
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. Under these circumstances, gap locking is disabled for searches and index scans and is used only for foreign-key constraint checking and duplicate-key checking.
gap lock不需要用于那些使用唯一索引锁住行来查找唯一行的语句。(这不包括一个情况，那就是当查询条件包含了复合唯一索引的一部分时，gap lock确实会存在。)。
gap lock的意义只在于阻止区间被插入，因此是可以共存的。一个事务获取的gap lock不会阻止另一个事务获取同一个gap的gap lock。共享和排他的gap lock是没有区别的。他们相互不冲突，且功能相同。
gap lock可以被显示禁止。当在READ COMMITTED等级下时，gap lock被禁止用于索引查找，而只用于外检约束检查和重复键检查。
Next-Key Locks
InnoDB performs row-level locking in such a way that when it searches or scans a table index, it sets shared or exclusive locks on the index records it encounters. Thus, the row-level locks are actually index-record locks. A next-key lock on an index record also affects the “gap” before that index record. That is, a next-key lock is an index-record lock plus a gap lock on the gap preceding the index record. If one session has a shared or exclusive lock on record R in an index, another session cannot insert a new index record in the gap immediately before R in the index order.
innoDB实现行级锁的方法如下：当其单条查找或范围查找索引时，它会在其遇到的记录上施加共享/互斥锁。因此，行级锁其实都是在索引上的。某索引上的一个next-key锁同样会影响该索引前面的间隙。也就是说，一个next-key锁=索引上的记录锁+锁住前面间隙的gap lock。如果一个事务在记录R上的某个索引持有了共享/互斥锁，则另一个事务不能马上在R的对应索引前面插入新的记录。
Suppose that an index contains the values 10, 11, 13, and 20. The possible next-key locks for this index cover the following intervals, where a round bracket denotes exclusion of the interval endpoint and a square bracket denotes inclusion of the endpoint: (negative infinity, 10] (10, 11] (11, 13] (13, 20] (20, positive infinity)
For the last interval, the next-key lock locks the gap above the largest value in the index and the “supremum” pseudo-record having a value higher than any value actually in the index. The supremum is not a real index record, so, in effect, this next-key lock locks only the gap following the largest index value.
假如有一个索引包含值如下：10,11,13,20。则其上的next-key lock包含如下区间，圆括号表示开，方括号表示闭：
(negative infinity, 10]
(10, 11]
(11, 13]
(13, 20]
(20, positive infinity)对于最后一个区间，next-key锁施加在一个叫做"无穷大"的伪记录上，从而会锁住大于最大值20的间隙。这个伪记录具有大于所有实际存在于索引的值。"无穷大"记录不是一个真实的记录，因此，实际它上面的next-key锁只锁住最大值20之后的间隙。
个人补充： next-key lock的效果相当于一个记录锁加一个间隙锁。当next-key lock加在某索引上，则该记录和它前面的区间都被锁定。 假设有记录1, 3, 5, 7，现在记录5上加next-key lock，则会锁定区间(3, 5]，任何试图插入到这个区间的记录都会阻塞。 注意，由于其效果相当于(3, 5)上的gap lock加5上的record lock，而且gap lock是可重入的，相互不阻塞的(上文讲过)，当其它事务试图获取(3, 5)的gap lock时，不会被阻塞；但如果要获取5上的record lock，就会阻塞；如果要获取5上的next-key lock，同样会阻塞。
总结
record lock、gap lock、next-key lock，都是加在索引上的。假设有记录1,3,5,7，则5上的记录锁会锁住5，5上的gap lock会锁住(3,5)，5上的next-key lock会锁住(3,5]。