sets shared next-key locks on the index records it encounters. However, only an index record lock is required for statements that lock rows using a unique index to search for a unique row. 

• SELECT ... FOR UPDATE  and SELECT ... FOR SHARE  statements that use a unique index acquire locks for scanned rows, and release the locks for rows that do not qualify for inclusion in the result set (for example, if they do not meet the criteria given in the WHERE  clause). However, in some cases, rows might not be unlocked immediately because the relationship between a result row and its original source is lost during query execution. For example, in a UNION , scanned (and locked) rows from a table might be inserted into a temporary table before evaluating whether they qualify for the result set. In this circumstance, the relationship of the rows in the temporary table to the rows in the original table is lost and the latter rows are not unlocked until the end of query execution. 

•For locking reads ( SELECT  with FOR UPDATE  or FOR SHARE ), UPDATE , and DELETE  statements, the locks that are taken depend on whether the statement uses a unique index with a unique search condition or a range-type search condition. 

•For a unique index with a unique search condition, InnoDB  locks only the index record found, not the gap before it. 

•For other search conditions, and for non-unique indexes, InnoDB  locks the index range scanned, using gap locks or next-key locks to block insertions by other sessions into the gaps covered by the range. For information about gap locks and next-key locks, see Section 15.7.1, “InnoDB Locking” . 

•For index records the search encounters, SELECT ... FOR UPDATE  blocks other sessions from doing SELECT ... FOR SHARE  or from reading in certain transaction isolation levels. Consistent reads ignore any locks set on the records that exist in the read view. 

• UPDATE ... WHERE ...  sets an exclusive next-key lock on every record the search encounters. However, only an index record lock is required for statements that lock rows using a unique index to search for a unique row. 

•When UPDATE  modifies a clustered index record, implicit locks are taken on affected secondary index records. The UPDATE  operation also takes shared locks on affected secondary index records when performing duplicate check scans prior to inserting new secondary index records, and when inserting new secondary index records. 

• DELETE FROM ... WHERE ...  sets an exclusive next-key lock on every record the search encounters. However, only an index record lock is required for statements that lock rows using a unique index to search for a unique row. 

• INSERT  sets an exclusive lock on the inserted row. This lock is an index-record lock, not a next-key lock (that is, there is no gap lock) and does not prevent other sessions from inserting into the gap before the inserted row. 

Prior to inserting the row, a type of gap lock called an insert intention gap lock is set. 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. 

If a duplicate-key error occurs, a shared lock on the duplicate index record is set. This use of a shared lock can result in deadlock should there be multiple sessions trying to insert the same row if another session already has an exclusive lock. This can occur if another session deletes the row. Suppose that an InnoDB  table t1  has the following structure: 

CREATE TABLE t1 (i INT, PRIMARY KEY (i)) ENGINE = InnoDB; 

