next-key lock

精选转载

jiwang1980 2011-06-07 21:29:40 博主文章分类：MySQL

文章标签 职场休闲 next-key lock gap lock 文章分类 MySQL 数据库

官方解释：

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 的插入。

在应用程序中可以通过一个 next-key locking 来实现一个唯一性(uniqueness)检查：如果以一个共享模式读取数据并没有发现与将要插入的数据存在重复值，那么在读取过程中 next-key lock 将被设置在你的记录的后继者(successor)上，这将阻止其它用户在期间插入相同的记录，因而你可以安全地插入你的记录。所以， next-key locking 可以允许你 'lock' 你的表中并不存在的记录。

http://dev.mysql.com/doc/refman/5.0/en/innodb-record-level-locks.html