MySQL table with AUTO_INCREMENT primary id does not release the number after a rollback

I have a table with bills. Every bill has an id that comes from the DB after I insert a new record. The field is an INTEGER with AUTO_INCREMENT set.

If I insert a new record as part of a transaction and I have to rollback this transaction, the ID is taken and gone. So the next record becomes the ID one higher, even though this 开发者_开发问答ID is not in use.

It would be better for the bills to have a linear numbering, so the accounting can figure out if something is wrong.

For concurrency reasons, the auto increment value cannot "rollback" with your transaction. If another process had inserted records while your transaction was in process, you'd risk a collision with their IDs later.

As an example, let's say your transaction in process "A" grabs IDs 1,2 and 3. Another process "B" runs and gets IDs 4 and 5. If the identity rolled back with your transaction and the next process "C" needed 5 IDs, it would get 1,2,3,4,5 but 4 and 5 were already taken by process "B".