SQL身份（自动编号）与事务回滚甚至递增 [英] SQL Identity (autonumber) is Incremented Even with a Transaction Rollback

问题描述

我有一个.NET的事务与SQL插入到MS SQL 2005数据库。该表有标识的​​主键。

I have a .net transaction with a SQL insert to a MS SQL 2005 DB. The table has an identity primary key.

在一个错误的事务中发生，回滚（）被调用。该行插入被回滚正确，但是下一次我将数据插入到表，身份递增，如果回滚从未发生过。所以基本上有在身份序列空位。有什么办法有rollback（）方法回收丢失的身份证？

When an error occurs within the transaction, Rollback() is called. The row inserts are rolled back correctly, however the next time I insert data to the table, the identity is incremented as if the rollback never occurred. So essentially there are gaps in the identity sequence. Is there any way to have the Rollback() method reclaim the missing identity?

我这不是处理这个正确的方式？

Am I not approaching this the right way?

推荐答案

如果你仔细想想，自动递增号不应该是事务性的。如果其它事务必须等待，看是否自动号码将要被使用的或回退，它们将通过使用自动号码现有事务被阻止。例如，使用自动编号字段为ID列考虑我下面的伪code与表答：

If you think about it, the auto-increment number should not be transactional. If other transactions had to wait to see if the auto-number was going to be used or "rolled back", they would be blocked by the existing transaction using the auto-number. For example, consider my psuedo code below with table A using an auto-number field for the ID column:

User 1
------------
begin transaction
insert into A ...
insert into B ...
update C ...
insert into D ...
commit


User 2
-----------
begin transaction
insert into A ...
insert into B ...
commit

如果用户2的事务处理开始后用户1的1毫秒，然后其插入到表A将不得不等待用户1的整个事务完成只是为了看看以前用于从第一插入到A中的自动编号。

If user 2's transaction starts a millisecond after user 1's, then their insert into table A would have to wait for user 1's entire transaction to complete just to see if the auto-number from the first insert into A was used.

这是一个特点，不是一个错误。我会建议使用另一种方案，如果你需要他们紧紧地连续生成自动编号。

This is a feature, not a bug. I would recommend using another scheme to generate auto-numbers if you need them to be tightly sequential.

这篇关于SQL身份（自动编号）与事务回滚甚至递增的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！