Why does mysql skip some auto increment ids?

Question

I have seen many explanations why mysql skips some auto increment values (and that it is not a bug as mysql never states that they should be consecutive). I am not sure why any of them would be applicable to this simple test case. I don't know if the results would be the same on all recent versions of mysql or not. Also adding:

ALTER TABLE test_table2 AUTO_INCREMENT = 1;

between the 2 INSERT INTO test_table2 lines makes the order as expected.

Does anyone know why this simple case would skip ids 6 and 7?

CREATE TABLE test_table1 (
  `id` INT NOT NULL AUTO_INCREMENT,
  `test` TEXT NOT NULL,
  PRIMARY KEY (`id`)
);
INSERT INTO test_table1(`test`) VALUES('value 1');
INSERT INTO test_table1(`test`) VALUES('value 2');
INSERT INTO test_table1(`test`) VALUES('value 3');
INSERT INTO test_table1(`test`) VALUES('value 4');
INSERT INTO test_table1(`test`) VALUES('value 5');
CREATE TABLE test_table2 (
  `id` INT NOT NULL AUTO_INCREMENT,
  `test` TEXT NOT NULL,
  PRIMARY KEY (`id`)
);
INSERT INTO test_table2(`test`) SELECT `test` FROM test_table1;
INSERT INTO test_table2(`test`) SELECT `test` FROM test_table1;
SELECT * FROM test_table2;

Results on my version of mysql:

'1', 'value 1'
'2', 'value 2'
'3', 'value 3'
'4', 'value 4'
'5', 'value 5'
'8', 'value 1'
'9', 'value 2'
'10', 'value 3'
'11', 'value 4'
'12', 'value 5'

Thanks in advance.

Answer 1

It's an example of auto increment locking in InnoDB: As you are executing 2 statements concurrently in the same session: the auto inc lock is obtained by the first query, and the autoincrement value generation is not interleaved between the statements - that is the whole point of transaction.

This will always happen by design: If it didn't, the way the transactions in InnoDB work, well, wouldn't work. Scalability under OLTP type loads would be horrible as every insert would have to wait for every other insert to finish, be committed, or worse - rolled back.

I.e.: If your first insert runs 5x longer than your second, and fails and is rolled back, the second one still completes and has been committed. Otherwise, you would have to wait for ea. query to be complete after the other.

If you require sequential and absolutely unique ID #s, pull them from another source. AutoInc columns simply guarantee a unique value - not necessarily a monosequence - that is a point of serialization and a bottleneck.

One way around this if otherwise required:

set innodb_autoinc_lock_mode = 0 in your my.cnf/mysql.ini

Description of auto inc locking in InnoDB and Options

Answer 2

One reason is that you have a UNIQUE INDEX that is triggered.

An id is skipped when you try to insert a field that is already inside your unique index.