Bug #30767 AUTO_INCREMENT on rollback
Submitted: 3 Sep 2007 10:39 Modified: 3 Sep 2007 11:02
Reporter: Jan Barkhed Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S3 (Non-critical)
Version:4.1.22-log OS:MacOS
Assigned to: CPU Architecture:Any

[3 Sep 2007 10:39] Jan Barkhed 
Description:
When you perform a rollback, the Auto Incremented index is not rolled back. This may actually not be a bug, it may be designed that way, but the index is spent and cannot be found in the table. It's not a big deal for me, but it can be for others.

How to repeat:
Create a table with an AUTO_INCREMENT index. The table must be of engine InnoDB. Write a script with SQL BEGIN and SQL COMMIT but force a rollback.

Suggested fix:
Rollback the auto incremented index.
[3 Sep 2007 11:02] Hartmut Holzgraefe 
This is the defined behavior, we guarantee that each auto_increment id that is handed out is greater than all previous ones. There is no (ant can't be) any guarantee that it is gapless at the same time.

So once an auto_increment value is taken by a transaction all following transactions have to get a value larger than that, and if the first transaction is rolled back the value is lost.