CODEWITHSUNDEEP
mysql
abiku
abiku

Reputation: 2246

Why mysql insert ignore increase autoincrement field?

have this simple table:

id - key auto_increment int, 
mail - primary_key - varchar ,
type - primary_key - int,

Have query like: insert ignore into table (id, mail , type )...

When adding records that have the same mail and type this record is not added, this is ok. But in the same time id field is auto increment even if no record is added. Is there any way to prevent this?

Upvotes: 2

Views: 3041

Answers (1)

Bill Karwin
Bill Karwin

Reputation: 562270

You can set the configuration variable innodb_autoinc_lock_mode=0 to change the way InnoDB discards id's if the insert fails.

See http://dev.mysql.com/doc/refman/5.6/en/innodb-auto-increment-configurable.html

  • innodb_autoinc_lock_mode = 0 (“traditional” lock mode)

    This lock mode provides the same behavior as before innodb_autoinc_lock_mode existed. For all “INSERT-like” statements, a special table-level AUTO-INC lock is obtained and held to the end of the statement. This assures that the auto-increment values assigned by any given statement are consecutive.

(emphasis mine)

Note that this lock mode also has the effect of holding a table lock until the INSERT completes (either by success of failure), and that can hinder throughput if you have many concurrent threads inserting rows to the same table.

This won't necessarily have any real impact on your application, if you have a more modest rate of concurrent inserts.

So to answer your original question, the auto-inc value increases even when the insert fails in default InnoDB behavior, because that allows MySQL to release the table-lock more promptly, i.e. right after it has incremnted the auto-inc, but before it has attempted the insert. But this means if the insert fails, some other thread may have allocated the next auto-inc value, so your thread can't un-allocate its id.

Upvotes: 4

Related Questions