8

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.

Joshua Frank
  • 13,120
  • 11
  • 46
  • 95
  • 1
    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 Sep 09 '10 at 18:55
  • 2
    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 Sep 09 '10 at 19:32
  • 1
    `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 Sep 09 '10 at 19:53
  • Fair enough, although using the extra 4 bytes seems wasteful, but I guess it's not a big deal in most applications. – Joshua Frank Sep 10 '10 at 16:16
  • I wrote up an innodb gap answer [Over Here](http://stackoverflow.com/a/38363271) – Drew Jul 14 '16 at 00:14

1 Answers1

5

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

Quassnoi
  • 413,100
  • 91
  • 616
  • 614
  • Thanks for the detailed information about why this behavior is as intended. I'll accept the answer, but I'd still like to know if there's any high-performance way to accomplish upsert without gaps. – Joshua Frank Sep 09 '10 at 19:35
  • @Joshua: you could create a single-field, single-record "sequence" table and update it in a trigger. However, that would severely hurt the concurrency of your table. – Quassnoi Sep 09 '10 at 19:45
  • @Quassnoi : I'm running MyISAM and got the same problem – hornetbzz Mar 18 '11 at 22:21
  • @hornetbzz: please post it as another question. – Quassnoi Mar 18 '11 at 22:29
  • @Quassnoi: Just to say I do not believe this problem is not strictly Innodb related, as I solved my problem just adding a unique constraint on the key field (unlinking the AI field). As I guess you're french, you'll be able to see what/how [here](http://www.developpez.net/forums/d1052735/bases-donnees/mysql/mysql-5-0-upsert/#post5847149) – hornetbzz Mar 18 '11 at 22:45
  • @hornetbzz: I'm Russian, but with little Google translating I can see that the problem you posted on that forum has little to do with this. A key violation on `INSERT IGNORE` or `INSERT ON DUPLICATE KEY UPDATE` will increment the `AUTO_INCREMENT` column on `InnoDB` but not on `MyISAM`. In your original query, there were no key violation at all. – Quassnoi Mar 18 '11 at 22:52