To prevent phantoms, InnoDB uses an algorithm called next-key locking that combines index-row locking with gap locking. InnoDB performs row-level locking in such a way that when it searches or scans a tableindex, it sets shared orexclusive locks on the index records it encounters. Thus, the row-level locks are actually index-record locks. In addition, a next-key lockon an indexrecordalso affects the “gap” before that indexrecord. That is, a next-key lockis an index-recordlock plus a gap lockon the gap preceding the indexrecord. If one session has a shared orexclusivelockonrecord R in an index, another session cannot insert a newindex recordin the gap immediately before R in the indexorder.
... For consistent reads, there is an important difference from the READ COMMITTED isolation level: All consistent reads within the same transaction read the snapshot established by the first read. ...
For locking reads (SELECTwithFORUPDATEorLOCKINSHAREMODE), UPDATE, andDELETE statements, locking depends on whether the statement uses a uniqueindexwith a uniquesearch condition, or a range-typesearch condition. For a uniqueindexwith a uniquesearch condition, InnoDB locks only the indexrecordfound, not the gap before it. For other search conditions, InnoDB locks the indexrange scanned, using gap locks ornext-key locks toblock insertions by other sessions into the gaps covered by the range.
For locking reads (SELECTwithFORUPDATEorLOCKINSHAREMODE), UPDATE statements, andDELETE statements, InnoDB locks onlyindexrecords, not the gaps before them, and thus permits the free insertion ofnewrecordsnexttolocked records.
This level is like REPEATABLE READ, but InnoDB implicitly converts all plain SELECT statements toSELECT ... LOCKINSHAREMODEif autocommit is disabled.
和REPEATABLE READ的主要区别在于把普通的SELECT变成SELECT … LOCK IN SHARE MODE，即对普通的select都会获取gap锁或next-key锁。