mysql innodb next-key locking（小翻译）

InnoDB has several types of record-level 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 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. See Section 13.2.10.1, “Clustered and Secondary Indexes”.

By default, InnoDB operates in REPEATABLE READ transaction isolation level and with the innodb_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 13.2.8.5, “Avoiding the Phantom Problem Using Next-Key Locking”).

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 （也就是说，next-key是一个索引记录锁和加在gap上的gap锁的组合） . 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
（如果一个session 在R记录的索引上 获得一个共享或互斥锁，那么另一个session不能以索引顺序立即在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 ( 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.

The preceding example shows that a gap might span a single index value, multiple index values, or even be empty.

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:
（如果一个语句用了唯一索引那么间隙锁是不需要的（当然对几列组成的唯一索引是不适用的）例如，如果 id这一列有一个唯一索引，那么下面的查询只用 index-record lock 锁住id为100的行，而是否有另一个session要往该行前的间隙插入一条新纪录是没有关系的，是可以的）
SELECT * FROM child WHERE id = 100;

If id is not indexed or has a nonunique index, the statement does lock the preceding 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.
（一种叫做insertion gap lock 的锁是用做插入操作前 插入行（这句怎么翻译 ）？？？？这种锁信号以这种方式插入：当很多事务同时插入到同一个索引的间隙中时，只要这些插入不是要插入到同一位置就不用互相等待。假设有值为4和7的索引纪录，当几个事务要在这之间插入5和6时分别用insertion gap lock锁住4和7之间的间隙，取得插入的行的排斥锁，但是不会互相阻塞，因为这些插入行之间是没有冲突的。）
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 is also another effect 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.

英文部分摘自：http://dev.mysql.com/doc/refman/5.0/en/innodb-record-level-locks.html