Official document Translator: InnoDB's Lock

InnoDB Record, Gap, and Next-key Locks

In view of the accuracy of translation, for some special names do not translate, so as not to mislead;

InnoDBhas several types of record-level locks including record locks, Gap locks, and next-key locks.

InnoDB has several types of row locks, including, record locks (recording lock), Gap locks (Gap Lock), and Next-key locks (combination).

• Record Lock:this is a lock in 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 rec Ord.

• 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 Recor D.

• Record Lock: Added to the index line;
• Gap Lock: Gap locks are added between the first index row and the last index row, and the gap between the index rows;
• Combination Lock: A lock composed of a row lock and a gap lock;

Record Locks

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

A record lock locks the index row, even if there is no index in the table. In this case, InnoDB creates an implicit clustered index that uses this index to lock each record.

Next-key LOCKSS

By default, operates at transaction isolation level and with the InnoDB REPEATABLE READ innodb_locks_unsafe_for_binlog system variable disabled. InnoDBuses 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 ").

The default InnoDB is the RR isolation level, and the parameterinnodb_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 it searches or scans a table index, it sets shared O R exclusive locks on the index records it encounters. Thus, the Row-level locks is actually index-record locks. In addition, a next-key lock on an index record also affects the  "gap "  before that index record. That's, 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.

The Next-key lock consists of a record lock and a gap lock. When searching and browsing the index, InnoDB implements a row lock by adding an S or x lock on each row of the browsed record. In this way, the row lock behaves as an index record lock. In addition, the Next-key lock has an effect on the previous gap in the locked line. This means that the next-key is composed of an index record lock and a gap lock added to the gap. If a session has a shared lock or an exclusive lock on record R, R is the key value in the index, then the other session will not be able to insert data in the gap before the R record (in index order) until the Next-key lock is released.

Suppose that an index contains the values, one, one, and 20. The possible Next-key locks for this index cover the following intervals, where ( or ) denote ex Clusion of the interval endpoint and [ or ] denote inclusion of the endpoint:

Assuming that the index contains 10,11,13 and 20 of these key value values, then it is possible that the Next-key locks of the index covers the following areas, key values and key-value intervals;

(negative infinity, 10] (10, 11] (11, 13] (13, 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 has a value higher than any value actually in the index. The supremum is isn't a real index record, so, in effect, this next-key lock locks only the gap following the largest index Value.

In the last interval, the Next-key lock locks the gap between the maximum value in the index and the imaginary value that is not in the index but is larger than the upper bound of the peso. The upper bound value is not a true index record, in fact, Next-key locks only the gap after the maximum index value;

Gap Locks

The Next-key locking example in the previous sections shows that a gap might span a single index value multiple index Valu ES, or even be empty.

The previous example of Next-key indicates that gap may cover a single index value, multiple index values, or may be empty;

Gap locking is not needed for statements, that's lock rows using a unique index to search for a unique row. (This does does include the case, the search condition includes only some columns of a multiple-column unique index; That case, gap locking does occur.) For example, if the id column have a unique index, the following statement uses only a index-record lock for the row ha Ving Tsun id value and it does not matter whether other sessions insert rows in the preceding gap:

SELECT * FROM child WHERE id = 100;

If id is isn't indexed or has a nonunique index, the statement does lock the preceding gap.

In SQL that uses a unique index or searches for a unique index row, the gap lock is not used (the gap lock is used when the query condition covers only a subset of the dimensions of a unique index) For example, the ID column is a unique index column, and the following query only adds a record lock on a row with an ID value equal to 100. does not limit other sessions to the previous gap write data;

SELECT * FROM child WHERE id = 100;

If the ID is not an indexed column, or is not a unique index, the query will add a gap lock to the front and back of the data row;

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-to-multiple transactions inserting into the same index gap need not Wait for each of the other if they is not inserting at the same position within the gap. Suppose that there is index records with values of 4 and 7. Separate transactions that attempt to inserts values of 5 and 6 each lock the gap between 4 and 7 with insert intention Loc KS prior to obtaining the exclusive lock in the inserted row, but does not block each other because the rows is nonconflict Ing. For more information about intention locks, see section 14.5.3, "innodb   Lock Modes ".

There is a gap lock called the insert intent Gap lock, which is generated during the insert operation and is added to the Insert row. This kind of lock is to resolve when multiple transactions write the same index gap at the same time, if the write is not the same gap, do not need to wait for other transactions to complete. Suppose that there is a record index that contains key values 4 and 7, and that different transactions are inserted into 5 and 6, each transaction produces an insert intent lock added to the 4~7, acquires an exclusive lock on the insert row, but is not locked together because the data row does not conflict;

It is also worth noting this 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 Loc K (Gap X-lock) on the same gap. The reason conflicting gap locks is allowed was that if a record was purged from an index, the gap locks held on the record By different transactions must is merged.

Gap locks InnoDB in is "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.

It is worth mentioning that the mutex is added to the same gap by different transactions. For example: Transaction a occupies a shared gap lock (Gap S-lock), and transaction B occupies an exclusive gap lock (Gap X-lock) on the same gap. The reason for the mutual exclusion gap lock is that if a record is deleted from the index, the gap that is added to the record by the other transaction is merged.

Gap lock in InnoDB means "completely forbidden", Gap lock only prohibits other transactions from writing to the gap. In this way, Gap x-lock with the Gap s-lock role is the same;

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 are used only for Foreign-key constrain t checking and duplicate-key checking.

There is also other effects of a using the READ COMMITTED isolation level or enabling innodb_locks_unsafe_for_binlog : Record locks for nonmatching rows is re Leased after MySQL has evaluated the WHERE condition. UPDATEfor statements, InnoDB does a "semi-consistent" read, such that it returns the latest committed Ver Sion to MySQL so that MySQL can determine whether the row matches the WHERE condition of the UPDATE .

Ban Gap locking

Gap locks can be displayed disabled. You do this by setting the transaction isolation level to RC, or by opening theinnodb_locks_unsafe_for_binlog系统变量。在这些情况下，除了在外键一致性检查和逐渐冲突检查的时候，gap锁均不生效。


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

Summary of content:

Use examples to illustrate it! Look at the following table:

CREATE TABLE' sclist ' (' ID ')int(8) not NULL, ' name 'varchar( +) not NULL, ' age 'smallint(4) not NULL,  PRIMARY KEY(' id '),KEY' idx_sclist_name_age ' (' Name ', ' age ') ENGINE=InnoDBDEFAULTCHARSET=UTF8;

The following data is written to the table:

Assuming that the RR isolation level is not turned oninnodb_locks_unsafe_for_binlog参数，比如sql：update sclist set age = 36 where name=‘f‘;

Assuming that the UPDATE statement goes Idx_sclist_name_age index,

1. The definition of record lock, Gap lock and Next-key can be explained as follows:

(1) Record Lock: The index line that the blue arrow points to. The lock that is added to the index row data.

(2) Gap Lock: The red arrow points to the gap. The lock that is added to the gap.

(3) Next-lock: Mix the two. The update query adds a next-lock lock to the table.

2, if there is no index idx_sclist_name_age, then in the case of RR, will be such a lock;

Update first scan the full table to find the corresponding ID, the process, each scan of each line will be added to the record lock, every two lines of clearance will be added gap lock. This is the 2PL lock process .

After the corresponding ID, the update operation, before the transaction commits, the second phase of 2PL, the process of releasing the lock .

3, then assume that there is a unique index idx_sclist_name_age, under the RR, the following two update;

Update sclist Set D = ' XXX ' where name= ' f '; Will add gap lock

Update sclist Set D = ' XXX ' where name= ' f ' and age=28; Do not add gap lock

That's what the official says. In a unique index, when the unique index is multidimensional, if the operation contains only a subset of dimensions, then the InnoDB will be added Gap lock, if it contains all the dimensions, it will not produce gap lock;

4. The 2PL principle is broken in some cases. That is, after the corresponding data is found, the unmatched rows of data are freed before the update. As in 2:

Find the process: after finding:

(1) The process of finding, each convenient data will be locked, GAP lock;

(2) After finding, remove the mismatched data lines and gaps of the lock;

(3) After the update, release the remaining locks before committing.

This will cause semi-consistent to read.

5, if the data written is not the same, the same gap can be multiple transactions hold multiple gap lock.

Assuming that the index idx_sclist_name_age is a normal composite index, the isolation level is RR, with the following two transactions

Transaction A:insert into Sclist (name,age) Select ' F ', 25;

Transaction B:insert into Sclist (name,age) Select ' G ', 32;

These two transactions will add gap locks on the e~m gap, but do not conflict with each other, do not wait ;

6, under RC, or open innodb_locks_unsafe_for_binlog parameters, Gap Lock does not take effect, except in addition to the foreign key consistency check and progressive conflict check .