SQL Server 标识列递增而不插入
我有一个 C# 控制台应用程序，它使用存储过程将行插入 SQL Server 数据库。目前数据库大约有 1m 行，但是 ID 列达到了 26m！
最后一次成功插入时，ID 从 16m 变为 26m。该应用程序并行运行多个线程，因此它有可能尝试同时插入两个内容。
除了事务回滚之外，还有什么会导致标识列增加而不添加行吗？
两个线程尝试同时写入冲突行的次数似乎不可能达到 1000 万次。
我正在使用一个临时表变量，该变量是通过将 XML 传递到存储过程中来填充的：
-- insert data if it doesn-t already exist
insert into DataStore (DataText, DataSourceID)
select distinct td.dataText, td.dataSource
from @tempData td
where not exists ( select 'x'
from DataStore ds
where ds.DataText = td.dataText );
I have a C# console app which is using a stored procedure to insert rows into a SQL Server database. Currently the database has about 1m rows, however the ID column is up at 26m!
On the last successful insert the ID went from 16m to 26m. The app runs multiple threads in parallel so there is a chance it could be trying to insert two things at the same time.
Is there anything other than a transaction rollback that would cause the identity column to increment without adding a row?
It seems unlikely that there were 10 million times that two threads tried to write a conflicting row at the same time.
I'm using a temporary table variable which is populated by passing XML into the stored proc:
-- insert data if it doesn-t already exist
insert into DataStore (DataText, DataSourceID)
select distinct td.dataText, td.dataSource
from @tempData td
where not exists ( select 'x'
from DataStore ds
where ds.DataText = td.dataText );
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
什么可以留下缺口？
...
我还会使用 IDENT_INCR 检查增量值，以防万一确实是500万左右
What can leave gaps?
...
I'd also check the increment value using IDENT_INCR in case it is really is 5 million or such
插入和删除的记录会增加身份计数器，
正如 @gbn 所提到的 - 回滚和 c-violations 也有，但还有两个选项：
Inserted and deleted record increments identity counter,
as mentioned by @gbn - Rollbacks and c-violations also, but there is two more options: