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