重构SQL以避免使用TABLOCKX

Question

我有两个这样的表：

Table1                   Table2
----------------------------------
Table1Id IDENTITY        Table2Id
Table2Id NOT NULL        SomeStuff
                         SomeOtherStuff

在它们之间有一个外键约束，Table2Id是关键。这意味着，在插入其相关的Table1行之前，需要先插入Table2行（这是一种“先有鸡还是先有蛋”的问题）。加载两个表的过程是通过批量集合操作完成的，这意味着我有一个@temp表中包含大量Table1和Table2的数据，该表使用一个IDENTITY列来跟踪这些数据。目前，我的插入操作如下所示（为简洁起见省略了事务和错误处理）：

DECLARE @currentTable2Id INT
SET @currentTable2Id = IDENT_CURRENT('dbo.Table2')
INSERT INTO dbo.Table2 WITH (TABLOCKX)
    ( SomeStuff, 
      SomeOtherStuff
    )
    SELECT WhateverStuff, 
           WhateverElse
    FROM @SomeTempTable
    ORDER BY SomeTempTableId

INSERT INTO dbo.Table1 
    ( Table2Id )
    SELECT @currentTable2Id + SomeTempTableId
    FROM @SomeTempTable
    ORDER BY SomeTempTableId

这个工作很好，插入后关系都正常。但是由于使用了，我们经常会遇到人们等待对方查询结束的情况，无论是这个“加载”查询还是其他的UPDATES和INSERTS（在selects上我使用）。项目的性质要求加载大量数据，因此有时这个过程可能运行20-30分钟。关于性能，我无能为力，相信我，我已经尝试过。

我不能使用，因为DBAs不允许用户在生产中发出此命令，而且我认为使用也需要。有没有办法在不使用的情况下进行这种插入？

- AJ.

---

如果你使用SQL Server 2008，你可以使用merge和output来完成任务。在SQL Server 2005中，我会使用循环（一次处理Table2中的一行）或者在Table2中添加一个额外的列来保存暂存表中的ID，然后在插入到Table1时使用该值进行连接。https://dev59.com/MW435IYBdhLWcg3whQg5 - Mikael Eriksson

@Mikael：听起来是个不错的答案。 - Paul Williams

@Mikael - 这个过程的原始版本使用游标和循环。我被告知将游标重构掉。 - AJ.

你能否在table2中添加一列，还是DBA也禁止这样做？ - Mikael Eriksson

你在考虑将Table1Id添加到Table2中吗？嗯...那可能行。你应该把它发布为一个答案，这样我就可以点赞了 :-) - AJ.

Answer 1

我猜想你使用tablockx是为了防止其他东西在Table2中插入数据（从而增加标识值）在你的过程执行期间。试试这个方法：

DECLARE @t TABLE (Table2Id int), @currentTable2Id int

INSERT INTO dbo.Table2
    ( SomeStuff, 
      SomeOtherStuff
    )
OUTPUT INSERTED.Table2Id into @t
    SELECT WhateverStuff, 
           WhateverElse
    FROM @SomeTempTable
    ORDER BY SomeTempTableId

SELECT @currentTable2Id = Table2Id FROM @t

INSERT INTO dbo.Table1 
    ( Table2Id )
    SELECT @currentTable2Id + SomeTempTableId
    FROM @SomeTempTable
    ORDER BY SomeTempTableId

DELETE @t

- Ben Thul

---

啊哈。我很快就会尝试这个，并告诉你结果。谢谢！ - AJ.

我不确定这会不会有效。如果没有锁定，@t 中的 ID 值不一定是严格顺序的。也就是说，它们可能是 1、5、6、9 等等。SomeTempTableId 总是直接连续的。此外，如果不使用 order by，我怎么知道 @currentTable2Id 是 @t 序列中的第一个呢？ - AJ.

@AJ - 你说得对。我进行了一些测试，在高并发情况下，不能保证 @T 中的 id 是没有间隙的。 - Mikael Eriksson

我最初误读了你的代码，以为是一次插入一行。如果你把@t临时表改成除了Table2Id之外还有SomeTempTableId的话，那么你就可以使用映射来插入table1了。 - Ben Thul

Answer 2

请确保在@SomeTempTable中有一个ID字段。在Table2中创建一个新列TempID。向Table2添加行时，将@SomeTempTable中的ID插入到TempID中。在插入到Table1时，使用TempID列进行连接以获取自动递增的Table2ID。可以像这样操作：

alter table Table2 add TempID int

go

declare @SomeTempTable table(ID int identity, WhateverStuff int, WhateverElse int)

insert into @SomeTempTable values(1, 1)
insert into @SomeTempTable values(2, 2)

insert into Table2(SomeStuff, SomeOtherStuff, TempID)
select WhateverStuff, WhateverElse, ID
from @SomeTempTable

insert into Table1(Table2Id)
select Table2ID
from @SomeTempTable as S
  inner join Table2 as T2
    on S.ID = T2.TempID

go

alter table Table2 drop column TempID    

不要添加和删除TempID列，但是您可以将其放在其中，但需要在每次运行之前清除它，以便先前运行的旧值不会混淆您的连接。

- Mikael Eriksson

---

我认为这会起作用。如果我有两个相同查询的实例在运行，每个实例都在自己的事务中，那么临时ID是否会重叠？ - AJ.

@AJ - 这取决于您在临时表中为ID设置的值。它们必须在同时运行的所有实例中保持唯一。 - Mikael Eriksson