即使事务回滚，SQL 标识（自动编号）也会增加
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/282451/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL Identity (autonumber) is Incremented Even with a Transaction Rollback
提问by muhan
I have a .net transaction with a SQL insert to a SQL Server 2005 database. The table has an identity primary key.
我有一个带有 SQL 插入到 SQL Server 2005 数据库的 .net 事务。该表有一个身份主键。
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?
当事务中发生错误时，Rollback()调用。行插入被正确回滚，但是下次我将数据插入表时，标识会增加，就好像回滚从未发生过一样。所以本质上在身份序列中存在差距。有没有办法让该Rollback()方法收回丢失的身份？
Am I not approaching this the right way?
我不是以正确的方式解决这个问题吗？
回答by Jason Hymanson
If you think about it, the auto-increment number should notbe 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:
如果您考虑一下，自动增量编号不应该是事务性的。如果其他事务必须等待查看自动编号是否将被使用或“回滚”，则它们将被使用自动编号的现有事务阻止。例如，考虑下面我的伪代码，表 A 使用 ID 列的自动编号字段：
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.
如果用户 2 的事务在用户 1 的一毫秒后开始，那么他们插入表 A 将不得不等待用户 1 的整个事务完成，以查看是否使用了第一次插入 A 的自动编号。
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.
这是一个功能，而不是一个错误。如果您需要它们紧密连续，我建议使用另一种方案来生成自动编号。
回答by Mark Brackett
If you depend on your identity values being gapless, then yes - you are doing it wrong. The whole point of a surrogate keyto is to have no business meaning.
如果你依赖于你的身份价值观是无缝的，那么是的——你做错了。一个整点代理键来是有没有商业意义。
And, no, there is no way to change this behaivor (short of rolling your own autoincrement, and suffering the performance consequences of blocking other inserts).
而且，不，没有办法改变这种行为（除了滚动你自己的自动增量，并遭受阻塞其他插入的性能后果）。
回答by Bill Karwin
You get gaps in your sequence if you DELETEa row too.
如果您也DELETE排成一排，您的序列就会出现间隙。
Sequences are required to be unique, but they are not required to be sequential. The fact that they are monotonically increasing is just a fluke of implementation.
序列需要是唯一的，但它们不需要是连续的。它们单调增加的事实只是实现的侥幸。
回答by Ian Varley
All the other posters who say not to worry about it, and that you shouldget gaps, are right. If there's business meaning to the number, and that meaning doesn't jive with gaps, then don't use an identity column.
所有其他说不用担心，你应该得到间隙的海报都是对的。如果数字具有商业意义，并且该意义不存在间隙，则不要使用标识列。
FYI, if for whatever reason you DO want to remove the gaps, most databases have a way to reseed the auto-numbering to the number of your choice. It's a pain in the arse, and if you find yourself needing to do it regularly, you definitely shouldn't be using an autonumber / identity field, as noted above. But here's the code to do it in SQL server:
仅供参考，如果您出于某种原因确实想要消除间隙，大多数数据库都有一种方法可以将自动编号重新设定为您选择的编号。这是一种痛苦，如果您发现自己需要定期执行此操作，则绝对不应该使用自动编号/身份字段，如上所述。但这是在 SQL Server 中执行此操作的代码：
DBCC CHECKIDENT('Product', RESEED, 0)
DBCC CHECKIDENT('产品', RESEED, 0)
That sets the product table to start back at 1 (though if you have records in the table, it'll obviously skip the ID values that are already taken.) Other RDBMS vendors have their own syntax, but the effect is roughly the same, so look up "reseed identity" or "reseed autonumber" in the system help files or internets.
这将产品表设置为从 1 开始（尽管如果表中有记录，它显然会跳过已经采用的 ID 值。）其他 RDBMS 供应商有自己的语法，但效果大致相同，所以在系统帮助文件或互联网中查找“reseed identity”或“reseed autonumber”。
Again: this is for special occasions, not regular use. Don't put it in a stored procedure and make us all come over there.
再次：这是特殊场合，不是经常使用。不要把它放在一个存储过程中，让我们都过来。
回答by Gavin Miller
As far as I know the rows for insertion claim the autonumber and on rollback that number is lost for good. If you're depending on that autonumber being in sequencing you might want to consider the approach you're using.
据我所知，插入行要求自动编号，并且在回滚时该编号将永远丢失。如果您依赖于排序的自动编号，您可能需要考虑您正在使用的方法。
回答by BCS
I don't think there is any requirement that autonumbered keys be sequential. In fact, I don't think they can be required to be:
我认为没有任何要求自动编号的键是顺序的。事实上，我不认为他们可以被要求是：
- transaction a starts and inserts
- transaction b starts and inserts
transaction a aborts
you get a hole. nothing to do about it.
- 事务 a 开始并插入
- 事务 b 开始并插入
事务a中止
你有一个洞。无事可做。
回答by Eric Sabine
Muhan try to think of it in the context of many simultaneous connections executing this transaction and not one at a time. Some will fail and some will succeed. You want SQL Server to concentrate on running the new requests as they come in and not on maintaining a gap-less identity column. IMO it (gaps in the values) is definitely something not worth spending time on.
Muhan 尝试在许多同时执行此事务的连接的上下文中考虑它，而不是一次一个。有些会失败，有些会成功。您希望 SQL Server 专注于运行新请求，而不是维护无间隙标识列。IMO 它（价值观的差距）绝对是不值得花时间的事情。
回答by Brian
No. Sequence implmentations use an autonomous transaction. In Oracle, the autonomous transaction was once internal to the dbms, but is now exposed for your own use (and is often used incorrectly)
否。序列实现使用自治事务。在 Oracle 中，自治事务曾经是 dbms 的内部事务，但现在公开供您自己使用（并且经常被错误地使用）
PRAGMA AUTONOMOUS_TRANSACTION;'
