InnoDB Locking 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: If  id  is not indexed or has a nonunique index, the statement does lock the preceding gap. It is also worth noting here that 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 lock (gap X-lock) on the same gap. The reason conflicting gap locks are allowed is that if a record is purged from an index, the gap locks held on the record by different transactions must be merged. Gap locks in  InnoDB  are “purely inhibitive” , which means that their only purpose is to prevent other transactions from inserting to the gap. Gap locks can co-exist. A gap lock taken by one transaction does not prevent another transaction from taking a gap lock on the same gap. There is no difference between shared and exclusive gap locks. They do not conflict with each other, and they perform the same function. Gap locking can be disabled explicitly. This occurs if you change the transaction isolation level to  READ COMMITTED . In this case, gap locking is disabled for searches and index scans and is used only for foreign-key constraint checking and duplicate-key checking. SELECT * FROM child WHERE id = 100; There are also other effects of using the  READ COMMITTED  isolation level. Record locks for nonmatching rows are released after MySQL has evaluated the  WHERE  condition. For  UPDATE  statements,  InnoDB  does a “semi-consistent”  read, such that it returns the latest committed version to MySQL so that MySQL can determine whether the row matches the  WHERE  condition of the  UPDATE . A next-key lock is a combination of a record lock on the index record and a gap lock on the gap before the index record. 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. 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 a round bracket denotes exclusion of the interval endpoint and a square bracket denotes inclusion of the endpoint: Next-Key Locks (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. By default,  InnoDB  operates in  REPEATABLE READ  transaction isolation level. In this case,  InnoDB  uses next-key locks for searches and index scans, which prevents phantom rows (see Section 15.7.4, “Phantom Rows” ). 3083