sql中发生回滚时如何重置自动增量ID [英] How to reset Autoincremented Id when rollback occurs in sql

问题描述

当我尝试在表中插入新行时，如果此事务发生任何异常，则数据回滚.

When I try to insert a new row in the table, if there is any exception occurs in this transaction then data is rollback.

现在，当下次成功插入新条目时，AutoIncrement id 将更新为下一个值.表示表中两个连续的唯一 ID 之间存在差距.

Now when a new entry is succesfully inserted next time, AutoIncrement id is updated with next value. Means there is Gap between two consequetive Unique Id in the table.

有什么有效的方法可以克服这个问题吗?

Is there any valid way to overcome this problem?

提前致谢

推荐答案

答案不得不说 - 否.

IDENTITY 列的整个想法是不有意义，并且与事务无关 - 这样就可以在不关心其他事务回滚与否的情况下抛出数字.想象一下，每秒 1000 次插入的系统为每个事务(插入)等待 10 毫秒，以决定它是否会提交！(仅供参考 10ms * 100 = 1s)

The whole idea of IDENTITY columns is to not be meaningful, and to be transaction agnostic - so that the numbers can be dished out without care of other transactions rolling back or not. Imagine a 1000 insert per second system being held up for 10ms for each transaction (insert) to decide whether it will commit! (fyi 10ms * 100 = 1s)

注意:在 SQL Server 2012(撰写本文时最新的 SP/补丁级别)中，此处注明了一个功能"on Connect 与身份相关.

Note: In SQL Server 2012 (latest SP/patch level at time of writing), there is a "feature" noted here on Connect related to identities.

即使在 2012 年之前，您甚至不需要回滚到使用一个 IDENTITY 值 - 请参阅此处https://stackoverflow.com/a/16156419/573261

Also even prior to 2012, you don't even need to rollback to consume an IDENTITY value - see here https://stackoverflow.com/a/16156419/573261

出于同样的原因，这也适用于其他主要的 RDBMS，例如

This applies to other major RDBMS as well for the same reasons, e.g.

PostgreSQL 序列

重要:为了避免阻塞从同一序列中获取数字的并发事务，nextval 操作永远不会回滚；也就是说，一旦获取了一个值，它就被认为是已使用的，即使执行 nextval 的事务后来中止.这意味着中止的事务可能会在分配的值序列中留下未使用的漏洞".

Important: To avoid blocking concurrent transactions that obtain numbers from the same sequence, a nextval operation is never rolled back; that is, once a value has been fetched it is considered used, even if the transaction that did the nextval later aborts. This means that aborted transactions might leave unused "holes" in the sequence of assigned values.

^{(强调我的)}

这篇关于sql中发生回滚时如何重置自动增量ID的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！