Auto-increment ids with multiple inserts in mysql/mariadb

Question

Suppose I have a table with an auto_increment column, and then insert multiple values in a single statement:

insert into foo (bar) values ('a'), ('b'), ('c');

From the documentation, last_insert_id() returns the first generated ID for the query, the ID for a in this case.

Does mysql give any guarantees that a b and c will then be assigned sequential values?

In other words, if last_insert_id() returns 1 for a, is there any guarantee that b = 2 and c = 3? Or could there be a scenario where due to multiple concurrent inserts, I could end up with something like "a = 1, b = 5, c = 9"?

Answer 1

Yes, for that particular statement, MySQL knows how many rows are going to be inserted, and a lock and/or a mutex is used to retrieve the ids to be inserted at the start of the query. It is fully deterministic and values will be consecutive.

However, depending on the variable innodb_autoinc_lock_mode, other non-trivial inserts like insert... on duplicate key update and insert... select may not guarantee that deterministic behavior (as they may not know the actual number of rows to be inserted). This is to allow for extra concurrency by sacrifying the consecutive values. In all cases, a unique, monotonically increasing value is guaranteed for the table as a whole, it is just that in some cases there may be gaps and interleavings.

You can know more about innodb autoinc behavior in the manual. And you can also test it in practice by doing:

$ mysql test -e "INSERT INTO test (session) VALUES (1), (sleep(1)), (1)"

while you insert as fast as possible other value in concurrency.

Addendum: I just realized a case in which you cannot assume that: if you use auto_increment_offset :-)