A Little Noise

Why are there gaps in my auto_increment sequence, even if there are no deletes or rolled back transactions?

Is it a bug?

The manual says, “For lock modes 1 or 2, gaps may occur between successive statements because for bulk inserts the exact number of auto-increment values required by each statement may not be known and overestimation is possible.”

Where does that overestimation come from?

An example to illustrate:

DROP TABLE IF EXISTS t;
CREATE TABLE t (a bigint unsigned auto_increment primary key) ENGINE=InnoDB SELECT NULL AS a;
/* #1 */ INSERT INTO t SELECT NULL FROM t;
/* #2 */ INSERT INTO t SELECT NULL FROM t;
/* #3 */ INSERT INTO t SELECT NULL FROM t;
/* #4 */ INSERT INTO t SELECT NULL FROM t;
SELECT * FROM t;

+----+
| a |
+----+
| 1 |
| 2 |
| 3 |
| 4 |
| 6 |
| 7 |
| 8 |
| 9 |
| 13 |
| 14 |
| 15 |
| 16 |
| 17 |
| 18 |
| 19 |
| 20 |
+----+
16 rows in set (0.02 sec)

Notice that 5 and 10-12 are missing. If we did another insert, we’d be missing 21-27 (try it and see!)

Here’s a model of what MySQL is doing:

Create the table and simultaneously insert a single row. That is the row where a=1.

#1: Insert as many rows as there are in the table (it’s one row, but MySQL doesn’t know that.)
– Grab a chunk of auto_increment values. How many in the chunk? One – the value ‘2’. Insert it (one row inserted).
– No more rows to insert, so all done.

#2: Insert as many rows as there are in the table (it’s two rows, but MySQL doesn’t know that.)
– Grab a chunk of auto_increment values. How many in the chunk? One – the value ‘3’. Insert it (one row inserted).
– Still more rows to insert. Grab another chunk, twice as big as before – two values, ‘4’ and ‘5’. Insert the ‘4’ (two rows inserted).
– No more rows to insert. Discard the left over ‘5’.

#3: Insert as many rows as there are in the table (it’s four rows, but MySQL doesn’t know that.)
– Grab a chunk of auto_increment values. How many in the chunk? One – the value ‘6’. Insert it (one row inserted).
– Still more rows to insert. Grab another chunk, twice as big as before – two values, ‘7’ and ‘8’. Insert them (three rows inserted).
– Still more rows to insert. Grab another chunk, twice as big as before – four values, ‘9’, ’10’, ’11’, ’12’. Insert the ‘9’ (four rows inserted).
– No more rows to insert. Discard the left over ’10’, ’11’, and ’12’.

#4: Insert as many rows as there are in the table (it’s eight rows, but MySQL doesn’t know that.)
– Grab a chunk of auto_increment values. How many in the chunk? One – the value ’13’. Insert it (one row inserted).
– Still more rows to insert. Grab another chunk, twice as big as before – two values, ’14’ and ’15’. Insert them (three rows inserted).
– Still more rows to insert. Grab another chunk, twice as big as before – four values, ’16’, ’17’, ’18’, ’19’. Insert them (seven rows inserted).
– Still more rows to insert. Grab another chunk, twice as big as before – eight values, ’20’, ’21’, ’22’, …, ’27’. Insert the ’20’ (eight rows inserted).
– No more rows to insert. Discard the left over ’21’, ’22’, etc.

The gap can get as big as 65535 (I didn’t look in the code to confirm that, it’s just what running the test above a few more times seems to suggest).

When innodb_autoinc_lock_mode=1, there can be gaps between statements, but not within a statement, because there’s a lock on the auto_increment until we’re done. #4 above is guaranteed to get 8 consecutive values, you just might not be sure where they are going to start (well, now you are, because you read this post).

When innodb_autoinc_lock_mode=2, there can be gaps within a statement, because the auto_increment is not locked. Imagine we’re in the middle of #4 above. Our statement is inserting the ’14’ and ’15’, when another statement comes along wanting just a single auto_increment value. It gets the ’16’. Now it’s our turn to ask for another chunk, and we get ’17’, ’18’, ’19’, ’20’. While we’re doing those, another statement comes along and steals our ’21’. So the last row for our statement is ’22’.