sql - Sql Server 2016 中 INSERT SELECT UNION ALL 的原子性

Question

我们最近升级到 SQL Server 2016，我们开始看到以前从未见过的错误。

该错误与升级完全无关，但我想值得一提。

我们的场景如下(以更简单的查询方式显示):

DROP TABLE IF EXISTS dbo.TestTable

CREATE TABLE dbo.TestTable
(
    AsAt DATETIME,
    Key1 INT,
    Key2 INT
)

CREATE CLUSTERED INDEX ixc_TestTable ON dbo.TestTable (AsAt) 
GO

CREATE UNIQUE NONCLUSTERED INDEX ixu_TestTable ON dbo.TestTable (AsAt, Key1, Key2) 
GO




DELETE FROM dbo.TestTable WHERE Key1 = 1 AND AsAt = '20180131'

INSERT INTO dbo.TestTable
        ( AsAt, Key1, Key2 )
VALUES
        ( '20180131',
            1, -- Key1 - int
            3  -- Key2 - int
            )

INSERT INTO dbo.TestTable
        ( AsAt, Key1, Key2 )
SELECT
    AsAt,
    Key1,
    130
FROM
    dbo.TestTable AS tt
UNION ALL
SELECT
    AsAt,
    Key1,
    129
FROM
    dbo.TestTable AS tt

遗憾的是这个小例子并没有失败

在现实世界场景中，第一个 INSERT正在插入几千条记录，并且 SELECT在UNION ALL涉及多个JOINS但他们的关键是他们SELECT从我们插入的表中。

我们看到的是，在某些情况下 IXU约束失败，尝试为 Key2 = 129 插入重复项.

如果我们继续运行查询，我们会在一些运行成功的地方运行，在一些运行失败的地方运行。

这让我们认为问题在于第一个 SELECT正在插入然后 SELECTED由第二个插入，导致重复。

有两个修复看起来每次都能使查询正常工作:

• 添加 WHERE Key2 <> 130在第二个 SELECT
• SELECT UNION ALL进入#temp表，然后 INSERT目标表中的结果

我们会认为 SELECT UNION ALL INSERT 的一部分应该以原子方式执行，然后结果 INSERT在表中。我们的想法错了吗？

即。 UNION ALL 的第二个选择将看不到第一个 SELECT 中插入的记录。

额外信息:

• 我们的隔离级别是 READ COMMITED
• 运行查询的存储过程在 TRANSACTION 中运行
• 添加 MAXDOP 1查询不会改变任何东西
• 失败运行的执行计划只显示一个INSERT节点
• 我们最近添加了一些 COLUMN STORE所涉及表的索引

更新:我们一直在尝试在没有列存储索引的情况下进行查询，尽管它们速度较慢但不会失败。当我们再次将列存储索引放回原处时，我们会在合理的迭代次数 (<200)

中得到错误

Answer 1

通过包装 UNION ALL 语法，这可以消除观察到的任何执行计划异常。我无法复制，所以我无法确定。但它肯定具有“无害”级别的影响。

INSERT INTO dbo.TestTable
        ( AsAt, Key1, Key2 )
SELECT u.*
FROM (
    SELECT AsAt, Key1, 130 As Key2
    FROM dbo.TestTable AS tt
    UNION ALL
    SELECT AsAt, Key1, 129
    FROM dbo.TestTable AS tt
) u