Not the answer you need?
Register and ask your own question!
Many Forum changes were implemented on Tue 22 Sep. Read about new Ranks, Scoring, and Reactions.
Email [email protected] for any comments or concerns.

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

philippephilippe EntrantInactive User Role Beginner
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
<table border="0" align="center" width="90%" cellpadding="3" cellspacing="1"><tr><td class="SmallText">Quote:</td></tr><tr><td class="quote">
If you insert multiple rows using a single INSERT statement, LAST_INSERT_ID() returns the value generated for the first inserted row only. The reason for this is to make it possible to reproduce easily the same INSERT statement against some other server.
</td></tr></table>

Sweet ! But...

Multi-row INSERTs
<table border="0" align="center" width="90%" cellpadding="3" cellspacing="1"><tr><td class="SmallText">Quote:</td></tr><tr><td class="quote">
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.
</td></tr></table>

<table border="0" align="center" width="90%" cellpadding="3" cellspacing="1"><tr><td class="SmallText">Quote:</td></tr><tr><td class="quote">
I'm surprised that nobody knows the answer on that for sure...
</td></tr></table>

Any idea ?

Comments

  • scoundrelscoundrel Contributor Inactive User Role Beginner
    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.
  • philippephilippe Entrant Inactive User Role Beginner
    Found it. Thanks for the tip !

    How AUTO_INCREMENT Handling Works in InnoDB

    <table border="0" align="center" width="90%" cellpadding="3" cellspacing="1"><tr><td class="SmallText">Quote:</td></tr><tr><td class="quote">
    With innodb_autoinc_lock_mode set to 0 (“traditional”) or 1 (“consecutive”), the auto-increment values generated by any given statement will be consecutive, without gaps, 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.
    </td></tr></table>

    MySQL 5.1 documentation is much clearer than 5.0. )
Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.