sql-server – 为什么这个MERGE语句导致会话被杀死?

我有针对数据库发出的以下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.*;

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

Msg 0,Level 11,State 0,Line 67 A severe error occurred on the
current command. The results,if any,should be discarded.

Msg 0,Level 20,Line 67 A severe error occurred on the current
command. The results,should be discarded.

我把一个简短的测试脚本放在一起产生错误

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,"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","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",deleted.*;

如果我删除OUTPUT子句,则不会发生错误.此外,如果我删除删除的引用,则不会发生错误.所以我查看了MSTP文档中的OUTPUT子句,其中说明:

DELETED cannot be used with the OUTPUT clause in the INSERT statement.

这对我来说很有意义,但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,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,deleted.*;
ROLLBACK TRAN;
GO

此外,我还有其他查询使用OUTPUT的方式与抛出错误的方式相同,并且它们完美地工作 – 它们之间的唯一区别是参与MERGE的表.

这对我们来说是生产中的主要问题.我已经在VM2014和sql2016上在128和RAM,12 x 2.2GHZ内核,Windows Server 2012 R2的VM和物理上重现了这个错误.

可以在此处找到从查询生成的估计执行计划:

Estimated Execution Plan

解决方法

这是一个错误.

它与MERGE特定的填孔优化有关,用于避免显式万圣节保护和消除连接,以及这些与其他更新计划功能的交互方式.

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

赠品是Insert,后跟同一个表上的Merge:

解决方法

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

>使用未记录的跟踪标志强制显式万圣节保护:

OPTION (QUERYTRACEON 8692);

>将ON子句更改为:

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

>更改表类型PointTable以将主键替换为:

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

CHECK约束部分是可选的,包括用于保留主键的原始空拒绝属性.

“简单”更新查询处理(外键检查,唯一索引维护和输出列)的复杂性足以开始.使用MERGE为其添加了几个附加层.将它与上面提到的特定优化相结合,你就可以很好地遇到像这样的边缘案例错误.

还有一个增加了MERGE报告的长线错误.

Connect bug report

相关文章

(一)日志传送架构 (1.1)相关服务器 主服务器 :用于生产的服务器,上面运行这生产SQL Server数据库...
(一)事故背景 最近在SQL Server 2012生产数据库上配置完事物复制(发布订阅)后,生产数据库业务出现了...
(一)测试目的 目前公司使用的SQL SERVER 2012高可用环境为主备模式,其中主库可执行读写操作,备库既...
(一)背景个人在使用sql server时,用到了sql server的发布订阅来做主从同步,类似MySQL的异步复制。在...
UNION和OR谓词 找出 product 和 product2 中售价高于 500 的商品的基本信息. select * from product wh...
datawhale组队学习task03