MySql upsert and auto-increment causes gaps

Question

I've got a MySql table with an auto-increment primary key, and it seems that all of the various upsert methods (INSERT IGNORE and ON DUPLICATE KEY UPDATE) suffer from the, uh, feature that the auto-increment field increments, even if a row is updated and not inserted. This means that gaps are introduced into the table, which I find undesirable.

So the question is: is there any way to upsert records in a table with an auto-increment field without auto-incrementing that field, if the upsert in fact merely updates the row. To my mind, this is the way upsert should behave, but it doesn't seem to.

Answer 1

This "problem" is only in InnoDB.

It is by design, and intended to improve concurrency: another thread can use an AUTO_INCREMENT without having to wait for the results of an UPSERT operation.

From the docs:

After a server startup, for the first insert into a table t, InnoDB executes the equivalent of this statement:

SELECT MAX(ai_col) FROM t FOR UPDATE;

…

InnoDB initializes but does not increment the value and stores it for use by later inserts

…

When accessing the auto-increment counter, InnoDB uses a special table-level AUTO-INC lock that it keeps to the end of the current SQL statement, not to the end of the transaction. The special lock release strategy was introduced to improve concurrency for inserts into a table containing an AUTO_INCREMENT column. Nevertheless, two transactions cannot have the AUTO-INC lock on the same table simultaneously, which can have a performance impact if the AUTO-INC lock is held for a long time. That might be the case for a statement such as INSERT INTO t1 ... SELECT ... FROM t2 that inserts all rows from one table into another.

MyISAM does not exhibit this behavior, since it's AUTO_INCREMENT algorithm is implemented differently (due to its limited ability to support concurrent DML).