Why INSERT IGNORE increments the auto_increment primary key?

Question

I wrote a java program that accesses a MySQL innodb database.

Whenever an INSERT IGNORE statement encounters a duplicate entry the Auto Increment primary key is incremented.

Is this behaviour the expected? I think it shouldn't happen with IGNORE. That means that IGNORE actually incurs an extra overhead for writing the new primary key value.

The table is the following:

CREATE TABLE `tablename` (   `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,   `rowname` varchar(50) NOT NULL,   PRIMARY KEY (`id`),   UNIQUE KEY `rowname` (`rowname`) ) ENGINE=InnoDB  DEFAULT CHARSET=latin1; 

Thank you!

Answer 1

This has been the default behaviour since MySQL 5.1.22.

You can set the configuration variable innodb_autoinc_lock_mode to 0 (a.k.a “traditional” lock mode) If you'd like to avoid gaps in your auto-increment columns. It may incur a performance penalty, though, as this mode has the effect of holding a table lock until the INSERT completes.

From the docs on InnoDB AUTO_INCREMENT Lock Modes:

innodb_autoinc_lock_mode = 0 (“traditional” lock mode)

The traditional lock mode provides the same behavior that existed before the innodb_autoinc_lock_mode configuration parameter was introduced in MySQL 5.1. The traditional lock mode option is provided for backward compatibility, performance testing, and working around issues with “mixed-mode inserts”, due to possible differences in semantics.

In this lock mode, all “INSERT-like” statements obtain a special table-level AUTO-INC lock for inserts into tables with AUTO_INCREMENT columns. This lock is normally held to the end of the statement (not to the end of the transaction) to ensure that auto-increment values are assigned in a predictable and repeatable order for a given sequence of INSERT statements, and to ensure that auto-increment values assigned by any given statement are consecutive.

Answer 2

I believe this is a configurable setting in InnoDB. See: AUTO_INCREMENT Handling in InnoDB

You'd want to go with

innodb_autoinc_lock_mode = 0