主页 / dba / 问题 / 140880
Accepted
World Wide DBA
Asked: 2016-06-10 11:13:48 +0800 CST

为什么此 MERGE 语句会导致会话被终止？

  • 772

我有以下MERGE针对数据库发出的声明：

MERGE "MySchema"."Point" AS t
USING (
       SELECT "ObjectId", "PointName", z."Id" AS "LocationId", i."Id" AS "Region"
         FROM @p1 AS d
         JOIN "MySchema"."Region" AS i ON i."Name" = d."Region"
    LEFT JOIN "MySchema"."Location" AS z ON z."Name" = d."Location" AND z."Region" = i."Id"
       ) AS s
   ON s."ObjectId" = t."ObjectId"
 WHEN NOT MATCHED BY TARGET 
    THEN INSERT ("ObjectId", "Name", "LocationId", "Region") VALUES (s."ObjectId", s."PointName", s."LocationId", s."Region")
 WHEN MATCHED 
    THEN UPDATE 
     SET "Name" = s."PointName"
       , "LocationId" = s."LocationId"
       , "Region" = s."Region"
OUTPUT $action, inserted.*, deleted.*;

但是，这会导致会话终止并出现以下错误：

消息 0，级别 11，状态 0，第 67 行当前命令发生严重错误。结果，如果有的话，应该被丢弃。

消息 0，级别 20，状态 0，第 67 行当前命令发生严重错误。结果，如果有的话，应该被丢弃。

我已经将一个简短的测试脚本放在一起，它会产生错误：

USE master;
GO
IF DB_ID('TEST') IS NOT NULL
DROP DATABASE "TEST";
GO
CREATE DATABASE "TEST";
GO
USE "TEST";
GO

SET NOCOUNT ON;

IF SCHEMA_ID('MySchema') IS NULL
EXECUTE('CREATE SCHEMA "MySchema"');
GO

IF OBJECT_ID('MySchema.Region', 'U') IS NULL
CREATE TABLE "MySchema"."Region" (
"Id" TINYINT IDENTITY NOT NULL CONSTRAINT "PK_MySchema_Region" PRIMARY KEY,
"Name" VARCHAR(8) NOT NULL CONSTRAINT "UK_MySchema_Region" UNIQUE
);
GO

INSERT [MySchema].[Region] ([Name]) 
VALUES (N'A'), (N'B'), (N'C'), (N'D'), (N'E'), ( N'F'), (N'G');

IF OBJECT_ID('MySchema.Location', 'U') IS NULL
CREATE TABLE "MySchema"."Location" (
"Id" SMALLINT IDENTITY NOT NULL CONSTRAINT "PK_MySchema_Location" PRIMARY KEY,
"Region" TINYINT NOT NULL CONSTRAINT "FK_MySchema_Location_Region" FOREIGN KEY REFERENCES "MySchema"."Region" ("Id"),
"Name" VARCHAR(128) NOT NULL,
CONSTRAINT "UK_MySchema_Location" UNIQUE ("Region", "Name") 
);
GO

IF OBJECT_ID('MySchema.Point', 'U') IS NULL
CREATE TABLE "MySchema"."Point" (
"ObjectId" BIGINT NOT NULL CONSTRAINT "PK_MySchema_Point" PRIMARY KEY,
"Name" VARCHAR(64) NOT NULL,
"LocationId" SMALLINT NULL CONSTRAINT "FK_MySchema_Point_Location" FOREIGN KEY REFERENCES "MySchema"."Location"("Id"),
"Region" TINYINT NOT NULL CONSTRAINT "FK_MySchema_Point_Region" FOREIGN KEY REFERENCES "MySchema"."Region" ("Id"),
CONSTRAINT "UK_MySchema_Point" UNIQUE ("Name", "Region", "LocationId")
);
GO

-- CONTAINS HISTORIC Point DATA
IF OBJECT_ID('MySchema.PointHistory', 'U') IS NULL
CREATE TABLE "MySchema"."PointHistory" (
"Id" BIGINT IDENTITY NOT NULL CONSTRAINT "PK_MySchema_PointHistory" PRIMARY KEY,
"ObjectId" BIGINT NOT NULL,
"Name" VARCHAR(64) NOT NULL,
"LocationId" SMALLINT NULL,
"Region" TINYINT NOT NULL
);
GO

CREATE TYPE "MySchema"."PointTable" AS TABLE (
"ObjectId"      BIGINT          NOT NULL PRIMARY KEY,
"PointName"     VARCHAR(64)     NOT NULL,
"Location"      VARCHAR(16)     NULL,
"Region"        VARCHAR(8)      NOT NULL,
UNIQUE ("PointName", "Region", "Location")
);
GO

DECLARE @p1 "MySchema"."PointTable";

insert into @p1 values(10001769996,N'ABCDEFGH',N'N/A',N'E')

MERGE "MySchema"."Point" AS t
USING (
       SELECT "ObjectId", "PointName", z."Id" AS "LocationId", i."Id" AS "Region"
         FROM @p1 AS d
         JOIN "MySchema"."Region" AS i ON i."Name" = d."Region"
    LEFT JOIN "MySchema"."Location" AS z ON z."Name" = d."Location" AND z."Region" = i."Id"
       ) AS s
   ON s."ObjectId" = t."ObjectId"
 WHEN NOT MATCHED BY TARGET 
    THEN INSERT ("ObjectId", "Name", "LocationId", "Region") VALUES (s."ObjectId", s."PointName", s."LocationId", s."Region")
 WHEN MATCHED 
    THEN UPDATE 
     SET "Name" = s."PointName"
       , "LocationId" = s."LocationId"
       , "Region" = s."Region"
OUTPUT $action, inserted.*, deleted.*;

如果我删除该OUTPUT子句，则不会发生错误。另外，如果我删除deleted引用，则不会发生错误。所以我查看了 MSDN 文档中的OUTPUT条款：

DELETED 不能与 INSERT 语句中的 OUTPUT 子句一起使用。

这对我来说很有意义，但是重点MERGE是您可能事先不知道。

此外，无论采取何种操作，以下脚本都可以正常工作：

USE tempdb;
GO
CREATE TABLE dbo.Target(EmployeeID int, EmployeeName varchar(10), 
     CONSTRAINT Target_PK PRIMARY KEY(EmployeeID));
CREATE TABLE dbo.Source(EmployeeID int, EmployeeName varchar(10), 
     CONSTRAINT Source_PK PRIMARY KEY(EmployeeID));
GO
INSERT dbo.Target(EmployeeID, EmployeeName) VALUES(100, 'Mary');
INSERT dbo.Target(EmployeeID, EmployeeName) VALUES(101, 'Sara');
INSERT dbo.Target(EmployeeID, EmployeeName) VALUES(102, 'Stefano');

GO
INSERT dbo.Source(EmployeeID, EmployeeName) Values(103, 'Bob');
INSERT dbo.Source(EmployeeID, EmployeeName) Values(104, 'Steve');
GO
-- MERGE statement with the join conditions specified correctly.
USE tempdb;
GO
BEGIN TRAN;
MERGE Target AS T
USING Source AS S
ON (T.EmployeeID = S.EmployeeID) 
WHEN NOT MATCHED BY TARGET AND S.EmployeeName LIKE 'S%' 
    THEN INSERT(EmployeeID, EmployeeName) VALUES(S.EmployeeID, S.EmployeeName)
WHEN MATCHED 
    THEN UPDATE SET T.EmployeeName = S.EmployeeName
WHEN NOT MATCHED BY SOURCE AND T.EmployeeName LIKE 'S%'
    THEN DELETE 
OUTPUT $action, inserted.*, deleted.*;
ROLLBACK TRAN;
GO

此外，我还有其他查询以OUTPUT与抛出错误的查询相同的方式使用，并且它们工作得很好——它们之间的唯一区别是参与MERGE.

这给我们的生产造成了重大问题。我已经在具有 128GB RAM、12 x 2.2GHZ 内核、Windows Server 2012 R2 的虚拟机和物理机上的 SQL2014 和 SQL2016 中重现了这个错误。

从查询生成的估计执行计划可以在这里找到：

预计执行计划

sql-server sql-server-2014

1 个回答

  1. Best Answer

    这是一个错误。

    它与MERGE用于避免明确的万圣节保护和消除连接的特定漏洞填充优化有关，以及这些优化如何与其他更新计划功能交互。

    在我的文章The Halloween Problem – Part 3中有关于这些优化的详细信息。

    赠品是插入，然后是同一张表上的合并：

    计划片段

    解决方法

    有几种方法可以打败这种优化，从而避免错误。

    1. 使用未记录的跟踪标志强制执行显式万圣节保护：

      OPTION (QUERYTRACEON 8692);

    2. 将条款更改ON为：

      ON s."ObjectId" = t."ObjectId" + 0

    3. 更改表类型PointTable以将主键替换为：

      ObjectID bigint NULL UNIQUE CLUSTERED CHECK (ObjectId IS NOT NULL)

      约束部分是可选的CHECK，包括在内以保留主键的原始拒绝空属性。

    “简单”更新查询处理（外键检查、唯一索引维护和输出列）已经足够复杂了。使用MERGE为此添加了几个附加层。将其与上面提到的特定优化结合起来，你就有了一个很好的方法来遇到像这样的边缘情况错误。

    另一个要添加到已报告的一长串错误MERGE中。

    • 21

相关问题