next-key lock

官方解釋：

13.2.8.4. 

InnoDB

Record, Gap, and Next-Key Locks

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

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:

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

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.

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://man.chinaunix.net/database/mysql/inonodb_zh/8.htm

Next-key locking: avoiding the 'phantom problem'

在 InnoDB 的行級鎖定上使用一個稱作 next-key locking 算法。在 InnoDB 在搜尋或掃描表的索引時将進行行鎖，它将在所通路到的索引上設定共享或排它的鎖定。因而行鎖是更加精确地而又稱為索引記錄鎖定。

InnoDB 在索引記錄上設定的鎖同樣會影響索引記錄之前的“間隙(gap)”。如果一個使用者對索引記錄 R 加了一個共享或排它的鎖定，那其它使用者将不能在 R 之前立即插入新的記錄。這種間隙鎖定用于防止所謂的“phantom problem”。假設需讀取和鎖定表

CHILD

中辨別符大于 100 的子行，并更新所搜尋到的記錄中某些字段。

SELECT * FROM CHILD WHERE ID > 100 FOR UPDATE;
	      

假設表

CHILD

中有一個索引字段

ID

。我們的查詢将從

ID

大于100的第一條記錄開始掃描索引記錄。 現在，假設加在索引記錄上的鎖定不能阻止在間隙處的插入，一個新的子記錄将可能在事務進行中被插入到表中。 如果現在在事務中再次執行

SELECT * FROM CHILD WHERE ID > 100 FOR UPDATE;
	      

在查詢傳回的記錄集中将會有一個新的子記錄。這與事務的隔離規則相違背的：一個事務必須能夠順串(run)， 因而在事務進行中所讀取的資料将不會發生改變。而新的 'phantom' 子記錄将會打破這個隔離規則。

當 InnoDB 掃描索引時，它同樣會鎖定在索引中在結尾記錄(the last record)之後的間隙。這僅僅在上例中會發生： InnoDB 設定的鎖定将阻止任何

ID

大于 100 的插入。