InnoDB Transaction Model • REPEATABLE READ •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 •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. 3086 # Session A START TRANSACTION; Suppose that one session performs an  UPDATE  using these statements: 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. Consider the following example, beginning with this table: Using  READ COMMITTED  has additional effects: 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. 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” . 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. 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 , and  DELETE  statements, locking depends on whether the statement uses a unique index with a unique search condition, or a range-type search condition. 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” . 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;