SQL Server - 即使在回滚的情况下，如何确保标识字段正确递增 [英] SQL Server - how to ensure identity fields increment correctly even in case of rollback

问题描述

在 SQL Server 中，如果涉及插入新行的事务被回滚，则会在标识字段中跳过一个数字.

In SQL Server, if a transaction involving the inserting of a new row gets rolled back, a number is skipped in the identity field.

例如，如果 Foos 表中的最高 ID 是 99，那么我们尝试插入一个新的 Foo 记录但回滚，然后 ID 100 被 'usedup' 并且下一个 Foo 行将被编号为 101.

For example, if the highest ID in the Foos table is 99, then we try to insert a new Foo record but roll back, then ID 100 gets 'used up' and the next Foo row will be numbered 101.

有什么办法可以改变这种行为，从而保证身份字段是连续的?

Is there any way this behaviour can be changed so that identity fields are guaranteed to be sequential?

推荐答案

您所追求的永远不会使用标识列.

What you are after will never work with identity columns.

它们被设计为发出"和忘记，通过设计，这样它们就不会导致等待或死锁等.该属性允许 IDENTITY 列在高度事务性的系统中用作序列，没有延迟或瓶颈.

They are designed to "give out" and forget, by-design so that they don't cause waits or deadlocks etc. The property allows IDENTITY columns to be used as a sequence within a highly transactional system with no delay or bottlenecks.

确保没有间隙意味着无法实现每秒 100 次插入的系统，因为要确定第 1 次插入是否要回滚，将会有很长的队列.

To make sure that there are no gaps means that there is NO WAY to implement a 100-insert per second system because there would be a very long queue to figure out if the 1st insert was going to be rolled back.

出于同样的原因，您通常不希望这种行为，也不希望大容量表有这样的数字序列.但是，对于非常少见的单进程表(例如每月单个进程的发票编号)，可以将事务放在MAX(number)+1 或类似查询中，例如

For the same reason, you normally do not want this behaviour, nor such a number sequence for a high volume table. However, for very infrequent, single-process tables (such as invoice number by a single process monthly), it is acceptable to put a transaction around a MAX(number)+1 or similar query, e.g.

declare @next int
update sequence_for_tbl set @next=next=next+1
.. use @next

这篇关于SQL Server - 即使在回滚的情况下，如何确保标识字段正确递增的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！