由于FK检查而导致Server中更新冲突而中止的快照隔离事务-第2部分

Question

这项工作是由于FK检查导致Server中的更新冲突，快照隔离事务中止的后续工作。

看过这篇优秀的文章(https://sqlperformance.com/2021/06/sql-performance/foreign-keys-blocking-update-conflicts#comment-167645)之后

我运行这段代码来创建数据库：

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Parent]
(
    [ParentID] [int] NOT NULL,
    [UpdateTime] [datetime] NOT NULL,

    CONSTRAINT [PK dbo.Parent ParentID] 
        PRIMARY KEY CLUSTERED ([ParentID] ASC)
                WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, 
                      IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, 
                      ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Child]
(
    [ChildID] [int] NOT NULL,
    [ParentID] [int] NULL,
    [UpdateTime] [datetime] NULL,

    CONSTRAINT [PK dbo.Child ChildID] 
        PRIMARY KEY CLUSTERED ([ChildID] ASC)
                WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, 
                      IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, 
                      ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Child] WITH CHECK 
    ADD CONSTRAINT [FK dbo.Child to dbo.Parent] 
        FOREIGN KEY([ParentID]) REFERENCES [dbo].[Parent] ([ParentID])
GO

ALTER TABLE [dbo].[Child] CHECK CONSTRAINT [FK dbo.Child to dbo.Parent]
GO

CREATE TABLE [dbo].[Dummy]
(
    [x] [int] NULL
) ON [PRIMARY]
GO

数据库必须允许快照隔离，并且启用了已提交的快照。

按以下方式填充数据库：

DELETE FROM dbo.Child
DELETE FROM dbo.Parent
GO

-- Insert parent rows
INSERT INTO dbo.Parent (ParentID, UpdateTime) VALUES (1, GETUTCDATE());
INSERT INTO dbo.Parent (ParentID, UpdateTime) VALUES (2, GETUTCDATE());
INSERT INTO dbo.Parent (ParentID, UpdateTime) VALUES (3, GETUTCDATE());

-- Insertion a child row
INSERT INTO dbo.Child select 101, 2, GetUTCDate()
go

然后运行以下两个脚本(按指示运行)：

-- Session 1 - part one (1st bit to run)
SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
BEGIN TRANSACTION;
 
-- Ensure snapshot transaction is started
SELECT COUNT_BIG(*) FROM dbo.Dummy AS D;

-- Session 1 - part two (3rd bit to run)
DELETE FROM dbo.Parent WHERE ParentID = 3

-- Session 2 - part one (2nd bit to run)
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
BEGIN TRANSACTION;

UPDATE dbo.Child
SET UpdateTime = GETUTCDATE()
WHERE ParentID = 1

INSERT INTO dbo.Child 
    SELECT 201, 2, GetUTCDate()

-- Session 2 - part two (4th bit to run)
COMMIT TRANSACTION;

会话1按预期生成更新错误：

Msg 3960，16级，状态1，10号线 由于更新冲突，快照隔离事务中止。不能使用快照隔离直接或间接地访问数据库“已更改的示例”中的表“dbo.Child”，以更新、删除或插入已被其他事务修改或删除的行。重新尝试事务或更改update/delete语句的隔离级别。

根据本文，应该通过将PK更改为非聚集并添加唯一的聚集索引来消除这种情况，但是会产生相同的错误。这样做应该可以避免FK问题，但似乎并不是这样，尽管执行计划意味着应该这样做。

为什么现在是这样？

谢谢伊恩

Answer 1

您的问题是外键ParentID 没有索引，所以Parent上的每个DELETE都需要扫描整个Child表，以确保没有FK一致性问题。这会导致SNAPSHOT隔离中的锁定冲突，而在其他隔离级别则会导致死锁。

将索引添加到Child

CREATE INDEX IX_Parent ON Child (ParentID);

你会看到锁定冲突已经消失了。

所有主键和外键都有一个索引(以这些列作为前导键)，这是 essential 。如果缺少外键上的索引，那么将在UPDATE和DELETE上针对父表的主键获得锁定问题。如果主键上缺少一个索引，那么您将在子表中获得INSERT和UPDATE上的锁定问题。

您可以添加其他列作为键的一部分或作为INCLUDE，但是PK或FK必须是索引中的前导列。

您可以在这把小提琴中看到索引的效果。

将Parent上的主键更改为非聚集键，并在相同列上添加另一个聚集键是一个坏主意，完全没有意义。

这篇文章中的特定示例引用了这样一种情况:每个表上有两个唯一的键，一个集合之间有一个外键。在这种情况下，有些情况下创建两个单独的索引是明智的。但是，您应该始终对所有主键、唯一键和外键都有索引。