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

**URL:** <https://forums.percona.com/t/innodb-auto-increment-multi-row-insert-guaranteed-sequential-ids/568>\
**Category:** Other MySQL® Questions\
**Created:** [December 14, 2007, 3:33pm UTC](https://forums.percona.com/t/innodb-auto-increment-multi-row-insert-guaranteed-sequential-ids/568 "2007-12-14T15:33:24Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![philippe](https://avatars.discourse-cdn.com/v4/letter/p/f14d63/32.png) [@philippe](https://forums.percona.com/u/philippe)\
**Post date:** [December 14, 2007, 3:33pm UTC](https://forums.percona.com/t/innodb-auto-increment-multi-row-insert-guaranteed-sequential-ids/568/1 "2007-12-14T15:33:24Z")

</div>

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 ?

---

<div class="post-metadata">

**Author:** ![scoundrel](https://avatars.discourse-cdn.com/v4/letter/s/3ec8ea/32.png) [@scoundrel](https://forums.percona.com/u/scoundrel)\
**Post date:** [December 15, 2007, 2:28am UTC](https://forums.percona.com/t/innodb-auto-increment-multi-row-insert-guaranteed-sequential-ids/568/2 "2007-12-15T02:28:00Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![philippe](https://avatars.discourse-cdn.com/v4/letter/p/f14d63/32.png) [@philippe](https://forums.percona.com/u/philippe)\
**Post date:** [December 15, 2007, 3:24am UTC](https://forums.percona.com/t/innodb-auto-increment-multi-row-insert-guaranteed-sequential-ids/568/3 "2007-12-15T03:24:51Z")

</div>

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. )