Why Doesn't MySQL's AUTO_INCREMENT Reset After a Rollback?

Why MySQL AUTO_INCREMENT Is Not Affected by Rollbacks

MySQL's AUTO_INCREMENT field maintains unique values regardless of transaction status. This behavior stems from the following scenario:

Scenario:

1. Program one inserts a record into table FOO, generating the value 557 for its auto-incremented primary key.
2. Program two inserts a record into table FOO, retrieving the value 558.
3. Program two inserts the 558 value into table BAR as a foreign key.
4. Program two commits.
5. Program one rolls back.

Problem:

Reclaiming the 557 value after a rollback creates several unresolved issues:

• Decrementing primary key values in FOO would affect other records with higher values.
• Modifying BAR would require removing the foreign key, which could impact other data.
• Erasing the printed report from program three is practically impossible.

Consequences:

For this reason, Oracle's sequence numbers also operate independently of transactions.

Workaround:

If maintaining gapless auto-increment values is crucial, transactional rollbacks cannot be used. Instead, a status flag can be added to records:

• Upon initial insertion, set the status to "Incomplete."
• During a transaction, update the status to "Complete" when operations are successful.
• Commit the transaction to mark the record as live.
• If the transaction rolls back, the incomplete record remains for auditing purposes.

This solution introduces its own challenges but provides a way to retain audit trails while avoiding auto-increment gaps.

The above is the detailed content of Why Doesn't MySQL's AUTO_INCREMENT Reset After a Rollback?. For more information, please follow other related articles on the PHP Chinese website!