Why does MySQL autoincrement increase on failed inserts?

by

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

More Related Contents:

  1. MySQL AUTO_INCREMENT does not ROLLBACK
  2. Prevent auto increment on MySQL duplicate insert
  3. Defining Composite Key with Auto Increment in MySQL
  4. MyISAM versus InnoDB [closed]
  5. How to reset AUTO_INCREMENT in MySQL
  6. How to set initial value and auto increment in MySQL?
  7. How do I quickly rename a MySQL database (change schema name)?
  8. Is there a REAL performance difference between INT and VARCHAR primary keys?
  9. Get current AUTO_INCREMENT value for any table
  10. 1114 (HY000): The table is full
  11. Restore table structure from frm and ibd files
  12. MySQL InnoDB not releasing disk space after deleting data rows from table
  13. What’s the difference between MyISAM and InnoDB? [duplicate]
  14. How to handle fragmentation of auto_increment ID column in MySQL
  15. Why is MySQL InnoDB insert so slow?
  16. MySql: MyISAM vs. Inno DB! [closed]
  17. UUID performance in MySQL?
  18. Add Auto-Increment ID to existing table?
  19. How to fill in the “holes” in auto-increment fields?
  20. MySQL: #1075 – Incorrect table definition; autoincrement vs another key?
  21. auto_increment by group
  22. Is there any performance gain in indexing a boolean field?
  23. How does MySQL Auto Increment work?
  24. How to force MySQL to take 0 as a valid auto-increment value
  25. MySQL Integer vs DateTime index
  26. How do I lock on an InnoDB row that doesn’t exist yet?
  27. mysql Failed to read auto-increment value from storage engine
  28. “Unknown column in ‘field list'”, but column does exist
  29. Should I always prefer MySQL InnoDB over MyISAM?
  30. make an ID in a mysql table auto_increment (after the fact)

Leave a Comment