•  

Announcement

Announcement Module
Collapse
No announcement yet.

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

Page Title Module
Move Remove Collapse
Conversation Detail Module
Collapse
  • Page of 1
  • Filter
  • Time
  • Show
Clear All
new posts

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

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


    Sweet ! But...

    Multi-row INSERTs
    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.


    Quote:

    I'm surprised that nobody knows the answer on that for sure...


    Any idea ?
    Tags: None

  • #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.

    Comment


    • #3
      Found it. Thanks for the tip !

      How AUTO_INCREMENT Handling Works in InnoDB

      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.


      MySQL 5.1 documentation is much clearer than 5.0. )

      Comment

      Working...
      X