CODEWITHSUNDEEP
mysqlinnodb
BlueSword
BlueSword

Reputation: 1346

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.

Upvotes: 1

Views: 187

Answers (1)

Rick James
Rick James

Reputation: 142278

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

Upvotes: 2

Related Questions