MySQL Bugs: #34696: auto increment gaps when using multi row insert with explicit values in InnoDB

Bug #34696	auto increment gaps when using multi row insert with explicit values in InnoDB
Submitted:	20 Feb 2008 16:27	Modified:	20 Feb 2008 18:14
Reporter:	Mattias Jonsson	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S3 (Non-critical)
Version:	5.1	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	auto_increment

Description:
Possible to get gaps when using multi row insert in InnoDB and not using the innodb_autoinc_lock_mode = 0 (1 is default). See 'How to repeat'.

The work around is to use innodb_autoinc_lock_mode=0.

Found it when fixing Bug#33479.

How to repeat:
SELECT @@global.innodb_autoinc_lock_mode;
@@global.innodb_autoinc_lock_mode
0
CREATE TABLE t1 (c1 BIGINT AUTO_INCREMENT PRIMARY KEY) ENGINE = InnoDB;
INSERT INTO t1 VALUES (50), (NULL);
INSERT INTO t1 VALUES (NULL), (20), (NULL);
INSERT INTO t1 VALUES (NULL);
SELECT * FROM t1 ORDER BY c1;
c1
20
50
51
52
53
54
DROP TABLE t1;
SELECT @@global.innodb_autoinc_lock_mode;
@@global.innodb_autoinc_lock_mode
1
CREATE TABLE t1 (c1 BIGINT AUTO_INCREMENT PRIMARY KEY) ENGINE = InnoDB;
INSERT INTO t1 VALUES (50), (NULL);
INSERT INTO t1 VALUES (NULL), (20), (NULL);
INSERT INTO t1 VALUES (NULL);
SELECT * FROM t1 ORDER BY c1;
c1
20
50
51
53
54
56
DROP TABLE t1;
SELECT @@global.innodb_autoinc_lock_mode;
@@global.innodb_autoinc_lock_mode
2
CREATE TABLE t1 (c1 BIGINT AUTO_INCREMENT PRIMARY KEY) ENGINE = InnoDB;
INSERT INTO t1 VALUES (50), (NULL);
INSERT INTO t1 VALUES (NULL), (20), (NULL);
INSERT INTO t1 VALUES (NULL);
SELECT * FROM t1 ORDER BY c1;
c1
20
50
51
53
54
56
DROP TABLE t1;

Suggested fix:
Implement ha_innobase::release_auto_increment (I guess that
release_auto_increment is always called after an insert statement).
If ha_innobase::release_auto_increment was implemented, it would
lower the maximum reserved auto_increment value to the highest used
unless there has been another thread has reserved a higher number.
And this would solve the problem.

One can probably use the handlers variable next_insert_id and
auto_inc_interval_for_cur_row in ha_innobase::release_auto_increment
to compare with the highest reserved auto_increment in innodb to tell if some
other thread have reserved a value, and if not, one should lower the reserved
auto_increment value to the highest used.

Maybe also change handler::update_auto_increment to decrease the nb_desired_values before calling get_auto_increment with the number of initial values that are explicitly set. (e.g. INSERT INTO (3), (8), (NULL), (NULL) should only request 2 values, not 4)

Also see bug#33479 where a proposed patch will show how it was solved in partitioning.

Changing this could also break replication.

Could be related to BUG#31612, I don't know.

Thank you for taking the time to write to us, but this is not a bug. Please double-check the documentation available at http://dev.mysql.com/doc/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

This is documented behavior of innodb_autoinc_lock_mode influence. See also http://dev.mysql.com/doc/refman/5.1/en/innodb-auto-increment-handling.html:

* innodb_autoinc_lock_mode = 1  (“consecutive” lock mode)
...
The exception is for “mixed-mode inserts”, where the user provides explicit values for an AUTO_INCREMENT column for some, but not all, rows in a multiple-row “simple insert.” For such inserts, InnoDB will allocate more auto-increment values than the number of rows to be inserted. However, all values automatically assigned are consecutively generated (and thus higher than) the auto-increment value generated by the most recently executed previous statement. “Excess” numbers are lost.
...
* innodb_autoinc_lock_mode = 2  (“interleaved” lock mode)
...
If the only statements executing are “simple inserts” where the number of rows to be inserted is known ahead of time, there will be no gaps in the numbers generated for a single statement, except for “mixed-mode inserts.