Innodb + auto increment + multi-row insert = guaranteed sequential IDs ?

MySQL & MariaDB Other MySQL® Questions
1

Hello all ! )

I would like to know if IDs generated by an AUTO INCREMENT column, when doing a single multi-row insert, are always in order.

From MySQL documentation :

10.10.3. Information Functions

[B]Quote:[/B]
If you insert multiple rows using a single INSERT statement, LAST_INSERT_ID() returns the value generated for the [B]first inserted row[/B] only. The reason for this is to make it possible to reproduce easily the same INSERT statement against some other server.

Sweet ! But…

Multi-row INSERTs

[B]Quote:[/B]
Anyway, my question is this. If I do a single-statement multi-line insert, are the auto-increment IDs of the rows inserted guaranteed to be sequential? Bear in mind also that I'm using InnoDB tables here.
[B]Quote:[/B]
I'm surprised that nobody knows the answer on that for sure...

Any idea ?

2

AFAIU, this is what innodb autoinc lock was created - to lock table and guarantee sequential values for autoinc fields.

In 5.1.12+ there are few different behaviors implemented and I’m not really sure (need to check docs) how it would work there.

3

Found it. Thanks for the tip !

How AUTO_INCREMENT Handling Works in InnoDB

[B]Quote:[/B]
With innodb_autoinc_lock_mode set to 0 (“traditional”) or 1 (“consecutive”), the auto-increment values generated by any given statement will be [B]consecutive, without gaps[/B], because the table-level AUTO-INC lock is held until the end of the statement, and only one such statement can execute at a time.

MySQL 5.1 documentation is much clearer than 5.0. )