InnoDB has several types of record-level locks

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

InnoDB has several types of record-level locks:

• Record lock: This is a lock on an index record.--innodb的锁都是加在索引上

• 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.----gap lock定义很模糊:它可以是两个值之间的记录,也可以是区间之外的记录

• 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.----innodb的行锁,总是锁索引.即使没有明确的定义索引,innodb也会建立一个隐含的聚簇索引来加锁

By default, InnoDB operates inREPEATABLE 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.----repeatable read + innodb_locks_unsafe_for_binlog=off 才能避免幻读,其实对于主库来说,repeatable read就够了,但是为了复制的安全,必须加上innodb_locks_unsafe_for_binlog=off 才能避免.

Next-key locking combines index-row locking with gap locking.--就是前面说的,next-key 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. ---innodb总是通过索引来给对应的记录加锁,锁总是加在索引上 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 recordR in an index, another session cannot insert a new index record in the gap immediately beforeR 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. For example, if theid column has a unique index, the following statement uses only an index-record lock for the row havingid value 100 and it does not matter whether other sessions insert rows in the preceding gap:----如果有索引,而且是唯一索引,那么innodb只会给该行加上索引锁,而不会加上间隙锁:注意这里有两个条件(1)必须有索引(2)必须是唯一索引.缺少任何一个,innodb都会给行加上间隙锁

SELECT * FROM child WHERE id = 100 for update;

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 byINSERT 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 toREAD COMMITTED or enable theinnodb_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 theREAD COMMITTED isolation level or enablinginnodb_locks_unsafe_for_binlog: Record locks for nonmatching rows are released after MySQL has evaluated the WHERE condition.----不会锁定不符合where条件的行.

 

 

http://hi.baidu.com/fishhust/blog/item/d849de860d22003767096ea4.html