Why does MySQL autoincrement increase on failed inserts?

Question

A co-worker just made me aware of a very strange MySQL behavior.

Assuming you have a table with an auto_increment field and another field that is set to unique (e.g. a username-field). When trying to insert a row with a username thats already in the table the insert fails, as expected. Yet the auto_increment value is increased as can be seen when you insert a valid new entry after several failed attempts.

For example, when our last entry looks like this...

ID: 10
Username: myname

...and we try five new entries with the same username value on our next insert we will have created a new row like so:

ID: 16
Username: mynewname

While this is not a big problem in itself it seems like a very silly attack vector to kill a table by flooding it with failed insert requests, as the MySQL Reference Manual states:

"The behavior of the auto-increment mechanism is not defined if [...] the value becomes bigger than the maximum integer that can be stored in the specified integer type."

Is this expected behavior?

Answer 1

InnoDB is a transactional engine.

This means that in the following scenario:

1. Session A inserts record 1
2. Session B inserts record 2
3. Session A rolls back

, there is either a possibility of a gap or session B would lock until the session A committed or rolled back.

InnoDB designers (as most of the other transactional engine designers) chose to allow gaps.

From the documentation:

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

…

InnoDB uses the in-memory auto-increment counter as long as the server runs. When the server is stopped and restarted, InnoDB reinitializes the counter for each table for the first INSERT to the table, as described earlier.

If you are afraid of the id column wrapping around, make it BIGINT (8-byte long).

Answer 2

Without knowing the exact internals, I would say yes, the auto-increment SHOULD allow for skipped values do to failure inserts. Lets say you are doing a banking transaction, or other where the entire transaction and multiple records go as an all-or-nothing. If you try your insert, get an ID, then stamp all subsequent details with that transaction ID and insert the detail records, you need to ensure your qualified uniqueness. If you have multiple people slamming the database, they too will need to ensure they get their own transaction ID as to not conflict with yours when their transaction gets committed. If something fails on the first transaction, no harm done, and no dangling elements downstream.

Answer 3

Old post, but this may help people, You may have to set innodb_autoinc_lock_mode to 0 or 2.

System variables that take a numeric value can be specified as --var_name=value on the command line or as var_name=value in option files.

Command-Line parameter format:

--innodb-autoinc-lock-mode=0 

OR Open your mysql.ini and add following line :

innodb_autoinc_lock_mode=0

Answer 4

I know that this is an old article but since I also couldn't find the right answer, I actually found a way to do this. You have to wrap your query within an if statement. Its usually insert query or insert and on duplicate querys that mess up the organized auto increment order so for regular inserts use:

$check_email_address = //select query here\\

if ( $check_email_address == false ) {
    your query inside of here
}

and instead of INSERT AND ON DUPLICATE use a UPDATE SET WHERE QUERY in or outside an if statement doesn't matter and a REPLACE INTO QUERY also does seem to work