Tables Another type of “mixed-mode insert”  is  INSERT ... ON DUPLICATE KEY UPDATE , which in the worst case is in effect an  INSERT  followed by a  UPDATE , where the allocated value for the  AUTO_INCREMENT column may or may not be used during the update phase. There are three possible settings for the  innodb_autoinc_lock_mode  variable. The settings are 0, 1, or 2, for “traditional” , “consecutive” , or “interleaved”  lock mode, respectively. As of MySQL 8.0, interleaved lock mode ( innodb_autoinc_lock_mode=2 ) is the default setting. Prior to MySQL 8.0, consecutive lock mode is the default ( innodb_autoinc_lock_mode=1 ). The default setting of interleaved lock mode in MySQL 8.0 reflects the change from statement-based replication to row based replication as the default replication type. Statement-based replication requires the consecutive auto-increment lock mode to ensure that auto-increment values are assigned in a predictable and repeatable order for a given sequence of SQL statements, whereas row-based replication is not sensitive to the execution order of SQL statements. The traditional lock mode provides the same behavior that existed before the innodb_autoinc_lock_mode  variable was introduced. The traditional lock mode option is provided for backward compatibility, performance testing, and working around issues with “mixed-mode inserts”, due to possible differences in semantics. In this lock mode, all “INSERT-like”  statements obtain a special table-level  AUTO-INC  lock for inserts into tables with  AUTO_INCREMENT  columns. This lock is normally held to the end of the statement (not to the end of the transaction) to ensure that auto-increment values are assigned in a predictable and repeatable order for a given sequence of  INSERT  statements, and to ensure that auto-increment values assigned by any given statement are consecutive. In the case of statement-based replication, this means that when an SQL statement is replicated on a replica server, the same values are used for the auto-increment column as on the source server. The result of execution of multiple  INSERT  statements is deterministic, and the replica reproduces the same data as on the source. If auto-increment values generated by multiple  INSERT  statements were interleaved, the result of two concurrent  INSERT  statements would be nondeterministic, and could not reliably be propagated to a replica server using statement-based replication. To make this clear, consider an example that uses this table: Suppose that there are two transactions running, each inserting rows into a table with an AUTO_INCREMENT  column. One transaction is using an  INSERT ... SELECT  statement that inserts 1000 rows, and another is using a simple  INSERT  statement that inserts one row: InnoDB  cannot tell in advance how many rows are retrieved from the  SELECT  in the  INSERT  statement in Tx1, and it assigns the auto-increment values one at a time as the statement proceeds. With a table- level lock, held to the end of the statement, only one  INSERT  statement referring to table  t1  can execute at a time, and the generation of auto-increment numbers by different statements is not interleaved. The auto-increment values generated by the Tx1  INSERT ... SELECT  statement are consecutive, and the 3035 • innodb_autoinc_lock_mode = 0  (“traditional”  lock mode) CREATE TABLE t1 ( c1 INT(11) NOT NULL AUTO_INCREMENT, c2 VARCHAR(10) DEFAULT NULL, PRIMARY KEY (c1) ) ENGINE=InnoDB; Tx1: INSERT INTO t1 (c2) SELECT 1000 rows from another table ... Tx2: INSERT INTO t1 (c2) VALUES (’xxx’);