Tables InnoDB  allocates more auto-increment values than the number of rows to be inserted. However, all values automatically assigned are consecutively generated (and thus higher than) the auto-increment value generated by the most recently executed previous statement. “Excess”  numbers are lost. In this lock mode, no “ INSERT -like”  statements use the table-level  AUTO-INC  lock, and multiple statements can execute at the same time. This is the fastest and most scalable lock mode, but it is  not safe  when using statement-based replication or recovery scenarios when SQL statements are replayed from the binary log. In this lock mode, auto-increment values are guaranteed to be unique and monotonically increasing across all concurrently executing “ INSERT -like”  statements. However, because multiple statements can be generating numbers at the same time (that is, allocation of numbers is  interleaved  across statements), the values generated for the rows inserted by any given statement may not be consecutive. If the only statements executing are “simple inserts”  where the number of rows to be inserted is known ahead of time, there are no gaps in the numbers generated for a single statement, except for “mixed- mode inserts” . However, when “bulk inserts”  are executed, there may be gaps in the auto-increment values assigned by any given statement. If you are using statement-based replication, set  innodb_autoinc_lock_mode  to 0 or 1 and use the same value on the source and its replicas. Auto-increment values are not ensured to be the same on the replicas as on the source if you use  innodb_autoinc_lock_mode  = 2 (“interleaved” ) or configurations where the source and replicas do not use the same lock mode. If you are using row-based or mixed-format replication, all of the auto-increment lock modes are safe, since row-based replication is not sensitive to the order of execution of the SQL statements (and the mixed format uses row-based replication for any statements that are unsafe for statement-based replication). In all lock modes (0, 1, and 2), if a transaction that generated auto-increment values rolls back, those auto-increment values are “lost” . Once a value is generated for an auto-increment column, it cannot be rolled back, whether or not the “ INSERT -like”  statement is completed, and whether or not the containing transaction is rolled back. Such lost values are not reused. Thus, there may be gaps in the values stored in an  AUTO_INCREMENT  column of a table. In all lock modes (0, 1, and 2), if a user specifies NULL or 0 for the  AUTO_INCREMENT  column in an INSERT ,  InnoDB  treats the row as if the value was not specified and generates a new value for it. In all lock modes (0, 1, and 2), the behavior of the auto-increment mechanism is undefined if you assign a negative value to the  AUTO_INCREMENT  column. In all lock modes (0, 1, and 2), the behavior of the auto-increment mechanism is undefined if the value becomes larger than the maximum integer that can be stored in the specified integer type. 3037 •If the  AUTO_INCREMENT  value becomes larger than the maximum integer for the specified integer type •Assigning a negative value to the  AUTO_INCREMENT  column •Specifying NULL or 0 for the  AUTO_INCREMENT  column •“Lost”  auto-increment values and sequence gaps •Using auto-increment with replication InnoDB AUTO_INCREMENT Lock Mode Usage Implications • innodb_autoinc_lock_mode = 2  (“interleaved”  lock mode)