具有AUTO_INCREMENT主ID的MySQL表在回滚后不会释放该数字 [英] MySQL table with AUTO_INCREMENT primary id does not release the number after a rollback

问题描述

我有一张有帐单的桌子.在插入新记录后，每个账单都有一个来自数据库的ID.该字段是设置了AUTO_INCREMENT的INTEGER.

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.

如果我将新记录作为事务的一部分插入，并且必须回滚此事务，那么ID就会丢失.因此，即使未使用该ID，下一条记录也将成为更高的ID.

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.

推荐答案

由于并发原因，自动增量值无法随事务回滚".如果在交易过程中另一个进程插入了记录，那么您稍后可能会与它们的ID发生冲突.

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.

作为示例，假设您的进程"A"中的事务获取ID 1,2和3.另一个进程"B"运行并获得ID 4和5.如果身份随您的事务回滚而下一个进程"C"需要5个ID，它将获得1,2,3,4,5，但是4和5已被进程"B"占用.

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".

这篇关于具有AUTO_INCREMENT主ID的MySQL表在回滚后不会释放该数字的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！