• REPEATABLE READ 

This is the default isolation level for InnoDB . Consistent reads within the same transaction read the snapshot established by the first read. This means that if you issue several plain (nonlocking) SELECT statements within the same transaction, these SELECT  statements are consistent also with respect to each other. See Section 15.7.2.3, “Consistent Nonlocking Reads” . 

For locking reads ( SELECT  with FOR UPDATE  or FOR SHARE ), UPDATE , and DELETE  statements, locking depends 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, 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” . 

• READ COMMITTED 

Each consistent read, even within the same transaction, sets and reads its own fresh snapshot. For information about consistent reads, see Section 15.7.2.3, “Consistent Nonlocking Reads” . 

For locking reads ( SELECT  with FOR UPDATE  or FOR SHARE ), UPDATE  statements, and DELETE statements, InnoDB  locks only index records, not the gaps before them, and thus permits the free insertion of new records next to locked records. Gap locking is only used for foreign-key constraint checking and duplicate-key checking. 

Because gap locking is disabled, phantom row problems may occur, as other sessions can insert new rows into the gaps. For information about phantom rows, see Section 15.7.4, “Phantom Rows” . 

Only row-based binary logging is supported with the READ COMMITTED  isolation level. If you use READ COMMITTED  with binlog_format=MIXED , the server automatically uses row-based logging. 

Using READ COMMITTED  has additional effects: 

•For UPDATE  or DELETE  statements, InnoDB  holds locks only for rows that it updates or deletes. Record locks for nonmatching rows are released after MySQL has evaluated the WHERE  condition. This greatly reduces the probability of deadlocks, but they can still happen. 

•For UPDATE  statements, if a row is already locked, InnoDB  performs a “semi-consistent”  read, returning the latest committed version to MySQL so that MySQL can determine whether the row matches the WHERE  condition of the UPDATE . If the row matches (must be updated), MySQL reads the row again and this time InnoDB  either locks it or waits for a lock on it. 

Consider the following example, beginning with this table: 

CREATE TABLE t (a INT NOT NULL, b INT) ENGINE = InnoDB; INSERT INTO t VALUES (1,2),(2,3),(3,2),(4,3),(5,2); COMMIT; 

In this case, the table has no indexes, so searches and index scans use the hidden clustered index for record locking (see Section 15.6.2.1, “Clustered and Secondary Indexes” ) rather than indexed columns. 

Suppose that one session performs an UPDATE  using these statements: 

# Session A START TRANSACTION; 

