MySQL table with basic AUTO_INCREMENT identifier does not free the number after rollback

Question

I have a table with bills. Each account has an identifier that comes from the database after entering a new record. The field is an INTEGER with a set of AUTO_INCREMENT.

If I insert a new record as part of a transaction, and I have to roll back this transaction, the identifier is taken and leaves. Thus, the next record becomes the identifier one above, although this identifier is not used.

It would be better if the accounts had linear numbering, so accounting can determine if something is wrong.

Answer 1

For concurrency reasons, the auto-increment value cannot "roll back" with your transaction. If another process inserted records during your transaction, you run the risk of encountering your identifiers later.

As an example, suppose that your transaction in process “A” captures identifiers 1,2 and 3. Another process “B” starts and receives identifiers 4 and 5. If the person rolled back with your transaction and the next process “C” 5 identifiers were required, it would have turned out 1,2,3,4,5, but 4 and 5 were already occupied by process "B".