bulkinsert - Does a MySQL multi-row insert grab sequential autoincrement IDs?

Question

I think this is true, but I haven't found anything on the web to confirm it. I can get the first id generated for the autoincrement field using last_insert_id(), but can I assume that the next records will have sequential IDs? Or could another user grab an id so that the resulting IDs are not sequential?

Example: insert into mytable (asdf, qwer) values (1,2), (3,4), (5,6), ... , (10000,10001);

If mytable has an autoincrement column, and if two users run this statement at the same time, will one user grab 10000 sequential IDs, and the other user the next 10000 sequential IDs? How about for hundreds of users?

Thanks.

Answer 1

It depends on the storage engine used.

• MyISAM guarantees the inserted rows to have sequential ID's, since a table-level lock is held during the insert.
• InnoDB: unless innodb_autoinc_lock_mode is set to interleaved (2), the inserted rows will have sequential ID's. By default InnoDB runs in consecutive mode since 5.1 and traditional mode prior.

For more on the innodb_autoinc_lock_mode option, see 13.6.4.3. AUTO_INCREMENT Handling in InnoDB