MySQL AUTO_INCREMENT does not ROLLBACK

by

It can’t work that way. Consider:

  • program one, you open a transaction and insert into a table FOO which has an autoinc primary key (arbitrarily, we say it gets 557 for its key value).
  • Program two starts, it opens a transaction and inserts into table FOO getting 558.
  • Program two inserts into table BAR which has a column which is a foreign key to FOO. So now the 558 is located in both FOO and BAR.
  • Program two now commits.
  • Program three starts and generates a report from table FOO. The 558 record is printed.
  • After that, program one rolls back.

How does the database reclaim the 557 value? Does it go into FOO and decrement all the other primary keys greater than 557? How does it fix BAR? How does it erase the 558 printed on the report program three output?

Oracle’s sequence numbers are also independent of transactions for the same reason.

If you can solve this problem in constant time, I’m sure you can make a lot of money in the database field.

Now, if you have a requirement that your auto increment field never have gaps (for auditing purposes, say). Then you cannot rollback your transactions. Instead you need to have a status flag on your records. On first insert, the record’s status is “Incomplete” then you start the transaction, do your work and update the status to “compete” (or whatever you need). Then when you commit, the record is live. If the transaction rollsback, the incomplete record is still there for auditing. This will cause you many other headaches but is one way to deal with audit trails.

More Related Contents:

  1. How to debug Lock wait timeout exceeded on MySQL?
  2. Prevent auto increment on MySQL duplicate insert
  3. How to enable INNODB in mysql
  4. Why does MySQL autoincrement increase on failed inserts?
  5. Defining Composite Key with Auto Increment in MySQL
  6. How to reset AUTO_INCREMENT in MySQL
  7. MySQL foreign key constraints, cascade delete
  8. How do I see all foreign keys to a table or column?
  9. How do I quickly rename a MySQL database (change schema name)?
  10. Get the new record primary key ID from MySQL insert query?
  11. TINYTEXT, TEXT, MEDIUMTEXT, and LONGTEXT maximum storage sizes
  12. Howto: Clean a mysql InnoDB storage engine?
  13. MySQL InnoDB not releasing disk space after deleting data rows from table
  14. How to convert all tables from MyISAM into InnoDB?
  15. MySQL InnoDB foreign key between different databases
  16. Bogus foreign key constraint fail
  17. How to insert data to MySQL with auto-incremented column(field)?
  18. How to optimize COUNT(*) performance on InnoDB by using index
  19. How do I repair an InnoDB table?
  20. auto_increment by group
  21. How to display open transactions in MySQL
  22. How to disable index in innodb
  23. How to force MySQL to take 0 as a valid auto-increment value
  24. Database corruption with MariaDB : Table doesn’t exist in engine
  25. Too many auto increments with ON DUPLICATE KEY UPDATE
  26. MySQL DROP all tables, ignoring foreign keys
  27. “Unknown column in ‘field list'”, but column does exist
  28. MySQL ‘select for update’ behaviour
  29. make an ID in a mysql table auto_increment (after the fact)
  30. How can I rebuild indexes and update stats in MySQL innoDB?

Leave a Comment