sql-server - SQL Server INSERT、Scope_Identity() 和物理写入光盘

Question

我有一个存储过程，除其他外，它还可以在循环内的不同表中插入一些内容。请参阅下面的示例以更清楚地理解:

INSERT INTO T1 VALUES ('something')

SET @MyID = Scope_Identity()

... some stuff go here

INSERT INTO T2 VALUES (@MyID, 'something else')

... The rest of the procedure

这两个表(T1 和 T2)每一个都有一个 IDENTITY(1, 1) 列，我们称它们为 ID1 和 ID2；然而，在我们的生产数据库(非常繁忙的数据库)中运行该过程并且每个表中有超过 6250 条记录后，我注意到 ID1 与 ID2 不匹配的事件!虽然通常情况下，对于 T1 中插入的每条记录，T2 中都会插入一条记录，并且两者中的标识列都会一致递增。

“错误”的记录是这样的:

ID1     Col1
----    ---------
4709    data-4709
4710    data-4710

ID2     ID1     Col1
----    ----    ---------
4709    4710    data-4710
4710    4709    data-4709

注意第二个表中的“倒置”ID1。

由于对 SQL Server 底层操作了解不多，我提出了以下“理论”，也许有人可以纠正我。

我认为，由于循环比物理写入表更快，和/或可能有其他原因延迟了写入过程，因此记录被缓冲。当需要编写它们时，它们的编写顺序没有特定的顺序。

这是否可能，如果不可能，如何解释上述场景？

如果是，那么我还有一个问题要提出。如果第一次插入(来自上面的代码)被延迟怎么办？这是否意味着我无法将正确的 IDENTITY 插入到第二个表中？如果答案也是肯定的，我该怎么做才能确保两个表中的插入将以正确的 IDENTITY 顺序发生？

感谢任何有助于我理解这一点的评论和信息。

提前致谢。

Answer 1

您无法依靠 IDENTITY 来解决第二个表的问题。如果您关心该行生成的主键值，您应该生成它自己。

IDENTITY 是一种表达“我不想自己生成 key 的麻烦，只需为我生成 key ，并且在需要时我会询问生成的值”的一种方式。

这里可能发生的情况是两个线程同时插入行，但它们都尚未提交，因此您会遇到这种情况:

Thread 1                      Thread 2
get id for table 1 = 4709
                              get id for table 1 = 4710
insert row for table 1
                              insert row for table 1
                              get id for table 2 = 4709
get id for table 2 = 4710
                              insert row for table 2
insert row for table 1

您有两种方法可以解决您的问题:

1. 删除第二个表中主键的 IDENTITY
2. 使用 SET IDENTITY_INSERT ON 允许您为其提供 key ，同时保留 IDENTITY 设置

但是，在这种情况下，我将使用方法 nbr。 1. 方法编号。 2 通常在将数据导入到空表时使用。您不希望数据库自动生成您稍后想要使用的 ID 的风险(因为它来自第一个表)，因此您应该禁用第二个表的主键上的 IDENTITY 设置。

或者您可以尝试完全避免依赖该表的键，因为您有外键引用，您真的需要键值相同吗？