即使事务回滚，SQL 标识(自动编号)也会增加 [英] SQL Identity (autonumber) is Incremented Even with a Transaction Rollback

问题描述

我有一个 .net 事务，其中包含对 SQL Server 2005 数据库的 SQL 插入.该表有一个身份主键.

当事务中发生错误时，会调用Rollback().行插入正确回滚，但是下次我将数据插入表时，标识会增加，就好像回滚从未发生过一样.所以本质上在身份序列中存在差距.有没有办法让 Rollback() 方法回收丢失的身份?

我这样做的方式不对吗?

解决方案

如果你仔细想想，自增数不应该是事务性的.如果其他事务必须等待查看自动编号是否将被使用或回滚"，则它们将被使用自动编号的现有事务阻止.例如，考虑下面我的伪代码，其中表 A 使用 ID 列的自动编号字段:

用户 1------------开始交易插入 A ...插入 B ...更新 C ...插入 D ...犯罪用户 2-----------开始交易插入 A ...插入 B ...犯罪

如果用户 2 的事务在用户 1 的一毫秒后开始，那么他们插入表 A 将不得不等待用户 1 的整个事务完成，以查看是否使用了第一次插入 A 的自动编号.

这是一项功能，而不是错误.如果您需要它们紧密连续，我建议使用另一种方案来生成自动编号.

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

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?

解决方案

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

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屋！