InnoDB Record, Gap, and Next-Key Locks

考虑到翻译准确性，对于某些特殊名称不做翻译，以免误导；

InnoDB has several types of record-level locks including record locks, gap locks, and next-key locks. 

innodb 有几种行锁类型，包括，record locks（记录锁）,gap locks（间隙锁）,and next-key locks（组合）。

• Record lock: This is a lock on an index record.

• Gap lock: This is a lock on a gap between index records, or a lock on the gap before the first or after the last index record.

• Next-key lock: This is a combination of a record lock on the index record and a gap lock on the gap before the index record.

• 记录锁：加在索引行上；
• 间隙锁：间隙锁加在第一条索引行之前和最后一条索引行之后，和索引行之间的空隙；
• 组合锁：是由行锁和间隙锁组合的锁；

Record Locks

Record locks always lock index records, even if a table is defined with no indexes. For such cases, InnoDB creates a hidden clustered index and uses this index for record locking. 

记录锁锁住索引行，即使表中并没有索引。这种情况，innodb会创建一个隐式聚集索引，用这个索引来为每条记录加锁。

Next-key Lockss

By default, InnoDB operates in REPEATABLE READ transaction isolation level and with theinnodb_locks_unsafe_for_binlog system variable disabled. In this case, InnoDB uses next-key locks for searches and index scans, which prevents phantom rows (see Section 14.6.3.6, “Avoiding the Phantom Problem Using Next-Key Locking”).

默认innodb是RR隔离级别，并且参数innodb_locks_unsafe_for_binlog被禁止。这种情况，innodb 使用next-key锁来进行查找(走不走索引都一样)，next-key锁不会造成幻读；

Next-key locking combines index-row locking with gap locking. 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. In addition, 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.

next-key 锁由记录锁和间隙锁组成。在寻找和浏览索引的时候，innodb通过添加一个S或X锁在浏览过的每行记录上来实现行锁。这样，行锁就表现为索引记录锁。另外，next-key锁对被锁行的前一个GAP也是有影响的。就是说next-key是由索引记录锁和加在GAP的间隙锁组成的。如果一个会话加了一个共享锁或者排它锁在记录R上，R是索引中的键值，那么另一个会话将不能在R记录前面（按照索引顺序）的间隙插入数据，直到next-key锁被释放。

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 ( or ) denote exclusion of the interval endpoint and [ or ] denote inclusion of the endpoint:

 假设索引包含10,11,13和20这些键值值，那么可能这个索引的next-key locks覆盖下面区域，键值和键值区间；

(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.

在最后一个区间，next-key锁锁住了索引中最大值，和不在索引中但比索引上界要大一点的虚值之间的间隙。上界值并不是真正的索引记录，事实上，next-key锁住的只是最大索引值后面的间隙；

Gap Locks

The next-key locking example in the previous section shows that a gap might span a single index value, multiple index values, or even be empty.

前面next-key的例子表明gap可能覆盖一个单独的索引值，多个索引值，或可能是空的；

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.

在使用唯一索引或搜索唯一索引行的sql中，gap锁是不会被使用的（在查询条件只覆盖多维度唯一索引的部分维度时，是会使用gap锁的）例如，id列是一个唯一索引列，下面的查询只在id值等于100的行上加记录锁，并不限制其他会话向之前的间隙写数据；

SELECT * FROM child WHERE id = 100;

如果id不是索引列，或不是唯一索引，这个查询就会在数据行的前后间隙加上gap锁；

A type of gap lock called an insertion intention gap lock is set by INSERT operations prior to row insertion. This lock signals the intent to insert in such a way that multiple transactions inserting into the same index gap need not wait for each other if they are not inserting at the same position within the gap. Suppose that there are index records with values of 4 and 7. Separate transactions that attempt to insert values of 5 and 6 each lock the gap between 4 and 7 with insert intention locks prior to obtaining the exclusive lock on the inserted row, but do not block each other because the rows are nonconflicting. For more information about intention locks, seeSection 14.5.3, “InnoDB Lock Modes”.

有一种gap锁被称作插入意向gap锁，它是在insert操作期间产生的，加在插入行上的。这种锁是为了解决在多事务同时写入同一index索引间隙的时候，如果写入不在同一个gap，并不需要等待其他事务完成。假设有一个记录索引包含键值4和7，不同的事务分别插入5和6，每个事务都会产生一个加在4~7之间的插入意向锁，获取在插入行上的排它锁，但是不会被互相锁住，因为数据行并不冲突；

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 they only stop other transactions from inserting to the gap. Thus, a gap X-lock has the same effect as a gap S-lock.

值得一提的是互斥锁被不同的事务加在同一个gap上。例如：事务A占有一个共享gap锁（GAP S-LOCK），事务B占有一个排他gap锁（gap X-lock）加在同样的gap上。互斥gap锁的实现原因是如果一条记录被从索引里删除，那么其他事务加在这条记录上的gap就会被归并。

gap锁在innodb中意思是是“完全被禁止的”，gap锁只禁止其他事务向gap中写入。这样，gap X-lock跟gap S-lock的作用是一样的；

Disabling Gap Locking

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. 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.

There are also other effects of using the READ COMMITTED isolation level or enablinginnodb_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-consistent” read, such that it returns the latest committed version to MySQL so that MySQL can determine whether the row matches the WHEREcondition of the UPDATE.

禁止gap locking

Gap锁可以被显示禁用。方法是设置事务隔离级别修改为RC，或者开启innodb_locks_unsafe_for_binlog系统变量。在这些情况下，除了在外键一致性检查和逐渐冲突检查的时候，gap锁均不生效。


RC隔离级别和开启innodb_locks_unsafe_for_binlog参数也会带来其他影响，比如:在mysql分析where条件之后，未匹配行的记录锁就会被释放（违反了2PL：Two Parsing Lock原则）例如：在update中，innodb执行"半一致性"读，这样，最新的提交版本被告知mysql，然后由mysql决定行是否匹配执行Update操作的where条件。

内容总结：

用例子来说明吧！看如下表：

CREATE TABLE `sclist` (
  `id` int(8) NOT NULL,
  `name` varchar(32) NOT NULL,
  `age` smallint(4) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_sclist_name_age` (`name`,`age`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

表中写入下面数据：

假设在RR隔离级别下，不开启innodb_locks_unsafe_for_binlog参数，比如sql：update sclist set age = 36 where name=‘f‘;

再假设update语句走idx_sclist_name_age索引，那么，

1、record lock、gap lock、next-key的定义可以做如下解释：

（1）record lock：蓝色箭头指向的索引行。加在索引行数据上的锁。

（2）gap lock：红色箭头指向间隙。加在间隙上的锁。

（3）next-lock：两者混合。update查询为表添加了next-lock锁。

2、假如没有索引idx_sclist_name_age，那么在RR情况下，将会这样加锁；

update首先进行全表扫描，找到对应的id，这个过程中，扫描过哦的每一行都会被加上record lock，每两行的间隙都会被加上gap锁。这就是2PL的加锁过程。

对应ID之后，进行更新操作，事务提交前，进行2PL的第二个阶段，释放锁的过程。

3、再假设存在unique索引idx_sclist_name_age，在RR下，进行下面两条update；

update sclist set D = ‘XXX‘ where name=‘f‘;　　　　　　　　　　　　//会加gap锁

update sclist set D = ‘XXX‘ where name=‘f‘ and age=28;　　　　　 //不加gap锁

这就是官网上说的在唯一索引中，唯一索引为多维度时，若操作只包含了部分维度，那么，innodb是会加gap锁的，若包含了所有的维度，则不会产生gap锁；

4、2PL原则在某些情况下被打破。即在找到对应数据后，在更新之前，先释放掉不匹配的数据行。如2中：

找的过程：　　　　　　　　　　找到后：

（1）找的过程，每条便利的数据都会加锁，间隙锁；

（2）找到后，去掉不匹配的数据行和间隙的锁；

（3）更新后，提交前释放剩余的锁。

 这会导致 semi-consistent 读。

5、假如写入的数据不是相同的话，同一gap上可以由多个事务持多个gap锁。

假设索引idx_sclist_name_age为普通组合索引，隔离级别是RR，有下面两个事务

事务a:insert into sclist(name,age) select ‘f‘,25;

事务b:insert into sclist(name,age) select ‘g‘,32;

这两个事务都会在e~m的间隙上添加gap锁，但是彼此之间不冲突，不等待；

6、在RC下，或者开启innodb_locks_unsafe_for_binlog参数，gap锁不生效，除了除了在外键一致性检查和逐渐冲突检查的时候。