(single) auto-increment value used by the INSERT  statement in Tx2 is either smaller or larger than all those used for Tx1, depending on which statement executes first. 

As long as the SQL statements execute in the same order when replayed from the binary log (when using statement-based replication, or in recovery scenarios), the results are the same as they were when Tx1 and Tx2 first ran. Thus, table-level locks held until the end of a statement make INSERT  statements using auto-increment safe for use with statement-based replication. However, those table-level locks limit concurrency and scalability when multiple transactions are executing insert statements at the same time. 

In the preceding example, if there were no table-level lock, the value of the auto-increment column used for the INSERT  in Tx2 depends on precisely when the statement executes. If the INSERT  of Tx2 executes while the INSERT  of Tx1 is running (rather than before it starts or after it completes), the specific auto-increment values assigned by the two INSERT  statements are nondeterministic, and may vary from run to run. 

Under the consecutive lock mode, InnoDB  can avoid using table-level AUTO-INC  locks for “simple insert”  statements where the number of rows is known in advance, and still preserve deterministic execution and safety for statement-based replication. 

If you are not using the binary log to replay SQL statements as part of recovery or replication, the interleaved lock mode can be used to eliminate all use of table-level AUTO-INC  locks for even greater concurrency and performance, at the cost of permitting gaps in auto-increment numbers assigned by a statement and potentially having the numbers assigned by concurrently executing statements interleaved. 

• innodb_autoinc_lock_mode = 1  (“consecutive”  lock mode) 

In this mode, “bulk inserts”  use the special AUTO-INC  table-level lock and hold it until the end of the statement. This applies to all INSERT ... SELECT , REPLACE ... SELECT , and LOAD DATA statements. Only one statement holding the AUTO-INC  lock can execute at a time. If the source table of the bulk insert operation is different from the target table, the AUTO-INC  lock on the target table is taken after a shared lock is taken on the first row selected from the source table. If the source and target of the bulk insert operation are the same table, the AUTO-INC  lock is taken after shared locks are taken on all selected rows. 

“Simple inserts”  (for which the number of rows to be inserted is known in advance) avoid table-level AUTO-INC  locks by obtaining the required number of auto-increment values under the control of a mutex (a light-weight lock) that is only held for the duration of the allocation process, not  until the statement completes. No table-level AUTO-INC  lock is used unless an AUTO-INC  lock is held by another transaction. If another transaction holds an AUTO-INC  lock, a “simple insert”  waits for the AUTO-INC lock, as if it were a “bulk insert” . 

This lock mode ensures that, in the presence of INSERT  statements where the number of rows is not known in advance (and where auto-increment numbers are assigned as the statement progresses), all auto-increment values assigned by any “ INSERT -like”  statement are consecutive, and operations are safe for statement-based replication. 

Simply put, this lock mode significantly improves scalability while being safe for use with statement- based replication. Further, as with “traditional”  lock mode, auto-increment numbers assigned by any given statement are consecutive . There is no change  in semantics compared to “traditional”  mode for any statement that uses auto-increment, with one important exception. 

The exception is for “mixed-mode inserts” , where the user provides explicit values for an AUTO_INCREMENT  column for some, but not all, rows in a multiple-row “simple insert” . For such inserts, 

