MySQL Bugs: #63128: explanation of the behavior of innodb_autoinc_lock

Bug #63128	explanation of the behavior of innodb_autoinc_lock_mode = 1 with INSERT IGNORE
Submitted:	7 Nov 2011 9:40	Modified:	26 Jun 2014 22:27
Reporter:	Miguel Angel Nieto	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S3 (Non-critical)
Version:	5.1 5.5	OS:	Any
Assigned to:	Daniel Price	CPU Architecture:	Any
Tags:	auto_increment, documentation

Description:
The default Auto-Increment Locking method on 5.1 and 5.5 is innodb_autoinc_lock_mode = 1. When you execute a INSERT IGNORE with a duplicated value, the query is not inserted, but the auto_increment value is incremented by one. This is a "simple insert" and not a "mixed-mode insert". It is not clearly documented why with a simple insert that fails to write data the allocated auto_increment is not rolled back.

How to repeat:
mysql> CREATE TABLE `y` (
-> `id` INT(11) NOT NULL AUTO_INCREMENT,
-> `val` INT(11) DEFAULT NULL,
-> PRIMARY KEY (`id`),
-> UNIQUE KEY `NewIndex1` (`val`)
-> ) ENGINE=INNODB DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.03 sec)

mysql> INSERT IGNORE INTO Y (val) VALUES (1),(2);
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0

mysql> INSERT IGNORE INTO Y (val) VALUES (1),(2);
Query OK, 0 rows affected (0.00 sec)
Records: 2 Duplicates: 2 Warnings: 0

mysql> SHOW CREATE TABLE y;
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| y | CREATE TABLE `y` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`val` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `NewIndex1` (`val`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1 |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

The same will occur by using this:

INSERT INTO y (val) VALUES (1),(2) ON DUPLICATE KEY UPDATE val=VALUES(val);

Indeed, http://dev.mysql.com/doc/refman/5.5/en/innodb-auto-increment-handling.html does NOT mention IGNORE at all (unlike INSERT ... ON DUPLICATE KEY UPDATE, that is considered "mixed-mode insert"). So there is something to fix/improve for sure.

The same behavior ("lost" autoinc values) occurs without specifying the IGNORE clause, so the IGNORE clause does not appear to be a factor.

As noted by the development team:

"In the given scenario, the behavior is correct
and it is as per our expectation.  And it is as documented.  When a
multi value insert statement is used we reserve as many values as will
be needed for the insert statement (before we actually do the insert),
and this cannot be given back.  Either it is used or lost.  If any error
happens, the reserved values are lost."

The documentation that explains why a "simple insert" that fails results in "lost" autoinc values can be found on this page:
http://dev.mysql.com/doc/refman/5.5/en/innodb-auto-increment-configurable.html

Here is the relevant information:

(1) “Simple inserts: Statements for which the *number of rows to be inserted can be determined in advance* (when the statement is initially processed). This includes single-row and multiple-row INSERT and REPLACE statements that do not have a nested subquery, but not INSERT ... ON DUPLICATE KEY UPDATE.:

(2) "For INSERT statements where the number of rows to be inserted is known at the beginning of processing the statement, InnoDB quickly allocates the required number of auto-increment values.."

(3) "The auto-increment locking modes provided by innodb_autoinc_lock_mode have several usage implications:"

“Lost” auto-increment values and sequence gaps: In all lock modes (0, 1,
and 2), if a transaction that generated auto-increment values rolls
back, those auto-increment values are “lost”. Once a value is generated
for an auto-increment column, it cannot be rolled back, whether or not
the “INSERT-like” statement is completed, and whether or not the
containing transaction is rolled back. Such lost values are not reused.
Thus, there may be gaps in the values stored in an AUTO_INCREMENT column
of a table.

Here is same scenario without the IGNORE clause:

mysql> show variables like 'innodb_autoinc_lock_mode';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| innodb_autoinc_lock_mode | 1     |
+--------------------------+-------+
1 row in set (0.00 sec)

mysql> CREATE TABLE `y` (
    -> `id` INT(11) NOT NULL AUTO_INCREMENT,
    -> `val` INT(11) DEFAULT NULL,
    -> PRIMARY KEY (`id`),
    -> UNIQUE KEY `NewIndex1` (`val`)
    -> ) ENGINE=INNODB DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.14 sec)

mysql> INSERT INTO y (val) VALUES (1),(2);
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> SHOW CREATE TABLE y\G
*************************** 1. row ***************************
       Table: y
Create Table: CREATE TABLE `y` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `val` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `NewIndex1` (`val`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> INSERT INTO y (val) VALUES (1),(2);
ERROR 1062 (23000): Duplicate entry '1' for key 'NewIndex1'
mysql> SHOW CREATE TABLE y\G
*************************** 1. row ***************************
       Table: y
Create Table: CREATE TABLE `y` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `val` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `NewIndex1` (`val`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

Thank you for the bug report.