未提交的数据库事务和自动增量列
我今天遇到了一些奇怪的行为，想知道这是预期的还是标准的。我们使用 Hibernate 来对抗 MySQL5。在编码过程中，我忘记关闭交易，我想其他人也能体会到。
当我最终关闭交易、运行代码并检查表格时，我注意到以下内容。每次我都错误地在没有关闭事务的情况下运行代码，因此不会导致插入实际行，但会增加自动增量代理主键值，因此我有一个间隙（即没有 id 字段值为 的行） 751 至 762）。
这是预期行为还是标准行为？可能会因数据库而异？和/或 Hibernate 自己的事务抽象对此有一些可能的影响吗？
I encountered some curious behavior today and was wondering if it is expected or standard. We are using Hibernate against MySQL5. During the course of coding I forgot to close a transaction, I presume others can relate.
When I finally closed the transaction, ran the code and checked the table, I noticed the following. All the times I mistakenly ran my code without closing the transaction, which therefore did not result in actual rows being inserted, nevertheless incremented the auto-increment surrogate primary key value, so that I have a gap (i.e. no rows with id field value of 751 to 762).
Is this expected or standard behavior? Might it vary depending on the database? And/or does Hibernate's own transaction abstraction have some possible effect on this?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
是的，这是预期的。
如果你想一想：数据库还能做什么？如果您增加列，然后将其用作同一事务中其他插入中的外键，并且当您这样做时，其他人会提交，那么他们将无法使用您的值。你就会得到一个间隙。
Oracle 等数据库中的序列的工作方式大致相同。一旦请求特定值，无论是否提交都无关紧要。它永远不会被重复使用。而且序列也不是绝对有序的。
Yes that's expected.
If you think about it: what else can the database do? If you increment the column and then use that as a foreign key in other inserts within the same transaction and while you're doing that someone else commits then they can't use your value. You'll get a gap.
Sequences in databases like Oracle work much the same way. Once a particular value is requested, whether or not it's then committed doesn't matter. It'll never be reused. And sequences are loosely not absolutely ordered too.
这几乎是预期的行为。如果没有它，数据库将必须等待每个已插入记录的事务完成，然后才能为下一个插入分配新的 id。
It's pretty much expected behaviour. With out it the db would have to wait for each transaction that has inserted a record to complete before assigning a new id to the next insert.
是的，这是预期的行为。 本文档解释得很好。
从5.1.22开始，实际上有三种不同的锁模式来控制并发事务如何获取自增值。但这三者都会导致回滚事务出现间隙（回滚事务使用的自增值将被丢弃）。
Yes, this is expected behaviour. This documentation explains it very well.
Beginning with 5.1.22, there are actually three different lock modes that control how concurrent transactions get auto-increment values. But all three will cause gaps for rolled-back transactions (auto-increment values used by the rolled-back transaction will be thrown away).
数据库序列不保证 id 序列没有间隙。它们被设计为事务无关的，只有这样才能实现非阻塞。
如果你想要没有间隙，你必须编写自己的存储过程来以事务方式增加列，但是这样的代码会阻塞其他事务，所以你必须小心。
您执行 SELECT CURRVAL FROM SEQUENCE_TABLE WHERE TYPE = :YOUR_SEQ_NAME FOR UPDATE;
更新 SEQUENCE_TABLE 设置 CURRVAL = :INCRMENTED_CURRVAL，其中 TYPE = :YOUR_SEQ。
Database sequences are not to guarantee id sequence without gaps. They are designed to be transaction-independent, only in such way can be non-blocking.
You want no gaps, you must write your own stored procedure to increase column transactionally, but such code will block other transactions, so you must be carrefull.
You do SELECT CURRVAL FROM SEQUENCE_TABLE WHERE TYPE = :YOUR_SEQ_NAME FOR UPDATE;
UPDATE SEQUENCE_TABLE SET CURRVAL = :INCREMENTED_CURRVAL WHERE TYPE = :YOUR_SEQ.