SQL Server SCOPE_IDENTITY()-线程安全 [英] SQL Server SCOPE_IDENTITY() - thread safety

问题描述

我们必须修改数据库并自己管理IDENTITY列(而不是依赖于auto-inc字段).

We have to modify our database and manage the IDENTITY column ourselves (rather than relying on an auto-inc field).

我们的解决方案是拥有一个生成器"表，该表具有一个自动增量字段.我们插入到此表中，然后读取SCOPE_IDENTITY值以获取新ID，例如

Our solution is to have a "Generator" table, which has an auto-inc field. We insert to this table and then read the SCOPE_IDENTITY value to get our new ID, e.g.

insert into NewIDEntity
        (CreationDate)
        select
        GetDate()

select @EntityID = SCOPE_IDENTITY()

我们主要关注以下情况:

We are mainly concerned with the following scenario:

•事务1插入到NewID中，并接收101作为要插入到Entity表中的新ID.
•在提交之前，事务2将插入到NEWID中，并且还接收101作为新ID(这是因为SCOPE_IDENTITY()将在当前作用域的上下文中返回该ID.原始行尚未提交，因此我们希望值为101)

• Transaction 1 INSERTS into NewID and receives 101 as the new ID to insert into the Entity table.
• Before it can commit, Transaction 2 INSERTS into NEWID and also receives 101 as the new ID (this is because SCOPE_IDENTITY() will return the ID within the context of the current scope. The original row has not been committed yet so we would expect the value to be 101)

•事务1提交并写入行.
•事务2尝试提交，但已经写入101，导致主键冲突并中止了事务.

• Transaction 1 commits and the row is written.
• Transaction 2 attempts to commit but 101 has already been written and results in a Primary Key violation and aborts the transaction.

但是，运行此命令似乎由SQL Server处理了SCOPE_IDENTITY()，即使在READ UNCOMMITTED隔离级别下运行，我们也不会发生冲突.

However on running this it seems that SCOPE_IDENTITY() is handled by SQL Server, even if running under READ UNCOMMITTED isolation level we get no conflicts.

这还好吗，或者我们还没有找到陷阱?

Is this okay or are there gotchas that we haven't been able to find?

谢谢 邓肯

推荐答案

身份生成本质上不在任何交易的范围之内-ID计数器总是在生成ID后立即增加，而不仅是在提交事务时.这会导致身份序列在事务回滚时有缺口，但这是因为它在您所描述的那种情况下都保证了安全性.

Identity generation is essentially outside the scope of any transaction- the ID counter is always bumped as soon as an ID is generated, not just when the transaction is committed. This leads to identity sequences having gaps when transactions roll back, but that's because it is guaranteeing safety in exactly the sort of situation you describe.

这篇关于SQL Server SCOPE_IDENTITY()-线程安全的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！