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.

I find your concern about these gaps a way more undesirable. One sholdn't be concerned of auto_oncrement numbers at all. You are probably misunderstood it's concept. — Your Common Sense
I understand that the actual values are meaningless, but if you waste too many of them, you can run out of space and need to go to a bigger data type, and then all of the rows will take up that much more space. Also, I find the gaps inelegant, although I suppose that's not the biggest deal. — Joshua Frank
MySQL supports SERIAL type which is an alias for BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE. BIGINT can hold values up to 18,446,744,073,709,551,615. I'm leaving a nice metaphorical comparison (like, to the age of the universe etc.) as an exercise for the reader. And it's only 8 bytes, too. — Quassnoi
Fair enough, although using the extra 4 bytes seems wasteful, but I guess it's not a big deal in most applications. — Joshua Frank

Quassnoi Quassnoi · Accepted Answer · 2010-09-09T19:07:15

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).

MySql upsert and auto-increment causes gaps

1 Answers