Search code examples
mysqlinnodb

Erratic increment in Mysql Auto increment column

I am seeing AUTO_INCREMENT column of MySql table having erratically large values. Somehow I am trying to understand how can this situation occur so as to avoid it.

Current scenarios:

  1. Multiple tables are residing each with single AUTO_INCREMENT columns. All are having this issue.
  2. I am doing frequent batch commit across tables. Can that be culprit ?
  3. There are 2 databases on same mysql server. Can that interfere ?

I tried to read and understand mysql auto_increment doc and figured out batch commit can be problem. But I am not sure as what innodb_autoinc_lock_mode should I switch to ?

Snapshot of erratic increment in values.

enter image description here

All MySql system variables are default and unchanged.

Solution

  • Are there really gaps of thousands?

    I have seen such in a "normalization" table that used INSERT IGNORE to either add an entry or silently do nothing. The trouble is that "doing nothing" includes allocating an auto_inc id, but not using it.

    IODKU, ROLLBACK, and a few other things also "burn" ids.

    http://mysql.rjweb.org/doc.php/staging_table discusses a non-burning way to do normalization at a high rate.

    Another thing that can happen... Since the id is not visible to other threads until COMMIT is done, other threads may see a gap, then later see the missing id. This can be annoying for using MySQL as a queuing mechanism and you are "continuing where you left off".