Why does MySQL autoincrement increase on failed inserts?

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

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.

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 

OROpen your mysql.ini and add following line :

innodb_autoinc_lock_mode=0