Why does mysql skip some auto increment ids?

Question

I saw a lot of explanations why mysql skips some auto increment values ​​(and that this is not an error, since mysql never states that they should be sequential). I'm not sure why any of them apply to this simple test case. I don't know if the results will be the same in all recent mysql versions or not. Also add:

ALTER TABLE test_table2 AUTO_INCREMENT = 1; 

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

Does anyone know why this simple case skipped identifiers 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 of my mysql version:

 '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

This is an example of blocking an automatic increment in InnoDB: since you execute 2 statements simultaneously in one session: automatic blocking of a lock is obtained at the first request, and the generation of the auto-increment value does not alternate between operators - this is the whole point of the transaction.

This will always happen by design: if it’s not, then how transactions work in InnoDB, well, it won’t work. Scalability under OLTP loads would be terrible, since each insert would have to wait for each other insert to complete, whether it would be completed or worse - rollback.

Ie: If your first insertion works 5 times longer than your second, and it crashes and rolls back, the second one still completes and runs. Otherwise, you have to wait for ea. The request will be completed after another.

If you need consecutive and absolutely unique ID #s, get them from another source. AutoInc columns simply guarantee a unique value - not necessarily a single sequence - it's a serialization point and a bottleneck.

One way if it is otherwise required:

set innodb_autoinc_lock_mode = 0 to my.cnf / mysql.ini

Description of Auto Lock in InnoDB and Options