logo标签列表

由于从数据库中删除行时发生更新冲突，快照隔离事务被中止。

sql-serversql-server-2019snapshot-isolation

我在这个问题上花了一些时间，最终得到了一个能够重现该问题的示例（即使在所有外键上都有适当的非聚集索引）。

以下是数据库操作的简要概述：

a) 更新/插入操作使用读提交（快照）隔离级别进行。

b) 删除项目使用快照隔离级别进行。

c) 所有外键都有索引。

清除过程会从数据库中删除旧的行。在20-60分钟后，下面的删除脚本将因为快照错误而失败。我被告知FK检查会恢复到读提交隔离级别，但即使如此，也无法解释我们下面所看到的情况。

复现步骤：

a) 创建一个数据库，并确保快照选项为True。我们将数据库称为SnapshotTest。

b) 使用下面的脚本创建数据库：

ALTER DATABASE [SnapshotTest] SET READ_COMMITTED_SNAPSHOT ON
GO
CREATE TYPE [dbo].[udtPPChildObject] AS TABLE(
    [InsertionId] [bigint] NOT NULL,
    [ChildInsertionId] [bigint] NOT NULL,
    [PropertyMapNameId] [int] NOT NULL,
    [UpdateId] [bigint] NULL,
    PRIMARY KEY CLUSTERED 
(
    [ChildInsertionId] ASC
)WITH (IGNORE_DUP_KEY = OFF)
)
GO
CREATE TYPE [dbo].[udtPPChildObjectList] AS TABLE(
    [InsertionId] [bigint] NOT NULL,
    [ChildInsertionId] [bigint] NOT NULL,
    [SortIndex] [int] NULL,
    [UpdateId] [bigint] NULL,
    [SortText] [nvarchar](260) NULL,
    [RemovalThreshold] [bigint] NULL,
    PRIMARY KEY CLUSTERED 
(
    [ChildInsertionId] ASC
)WITH (IGNORE_DUP_KEY = OFF)
)
GO
CREATE TYPE [dbo].[udtPPGuidList] AS TABLE(
    [Guid] [uniqueidentifier] NOT NULL,
    PRIMARY KEY CLUSTERED 
(
    [Guid] ASC
)WITH (IGNORE_DUP_KEY = OFF)
)
GO
CREATE TYPE [dbo].[udtPPObject] AS TABLE(
    [InsertionId] [bigint] NOT NULL,
    [ObjectId] [uniqueidentifier] NOT NULL,
    [TypeNameId] [int] NOT NULL,
    [UpdateId] [bigint] NULL,
    [SourceInterpreterID] [uniqueidentifier] NULL,
    [LevelID] [nvarchar](max) NULL,
    [SearchParentInsertionID] [bigint] NULL,
    PRIMARY KEY CLUSTERED 
(
    [InsertionId] ASC
)WITH (IGNORE_DUP_KEY = OFF)
)
GO
CREATE TYPE [dbo].[udtPPObjectBlobProperty] AS TABLE(
    [InsertionId] [bigint] NOT NULL,
    [PropertyMapNameId] [int] NOT NULL,
    [BlobValue] [varbinary](max) NULL,
    [UpdateId] [bigint] NULL,
    PRIMARY KEY CLUSTERED 
(
    [InsertionId] ASC,
    [PropertyMapNameId] ASC
)WITH (IGNORE_DUP_KEY = OFF)
)
GO
CREATE TYPE [dbo].[udtPPObjectProperty] AS TABLE(
    [InsertionId] [bigint] NOT NULL,
    [PropertyMapNameId] [int] NOT NULL,
    [UpdateId] [bigint] NOT NULL,
    [BitValue] [bit] NULL,
    [UIDValue] [uniqueidentifier] NULL,
    [FloatValue] [float] NULL,
    [BigIntValue] [bigint] NULL,
    [IntValue] [int] NULL,
    [NVarCharValue] [nvarchar](max) NULL,
    [SearchText] [nvarchar](max) NULL,
    [TypeIndex] [tinyint] NOT NULL,
    PRIMARY KEY CLUSTERED 
(
    [InsertionId] ASC,
    [PropertyMapNameId] ASC
)WITH (IGNORE_DUP_KEY = OFF)
)
GO
CREATE TABLE [dbo].[tblPPObject](
    [ObjectID] [uniqueidentifier] NOT NULL,
    [UpdateTime] [datetime] NULL,
    [InsertionID] [bigint] NOT NULL,
    [SourceInterpreterID] [uniqueidentifier] NULL,
    [LevelID] [nvarchar](260) NULL,
    [UpdateID] [bigint] NULL,
    [TypeNameID] [int] NOT NULL,
    [SearchParentInsertionID] [bigint] NULL,
 CONSTRAINT [PK_tblPPObject] PRIMARY KEY CLUSTERED 
(
    [InsertionID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 85, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[tblPPObjectBlobProperty](
    [Value] [varbinary](max) NULL,
    [UpdateTime] [datetime] NULL,
    [InsertionID] [bigint] NOT NULL,
    [UpdateID] [bigint] NULL,
    [PropertyMapNameID] [int] NOT NULL,
 CONSTRAINT [PK_tblPPObjectBlobProperty] PRIMARY KEY CLUSTERED 
(
    [InsertionID] ASC,
    [PropertyMapNameID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 85, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
CREATE TABLE [dbo].[tblPPObjectChildObject](
    [UpdateTime] [datetime] NULL,
    [InsertionID] [bigint] NOT NULL,
    [ChildInsertionID] [bigint] NOT NULL,
    [UpdateID] [bigint] NULL,
    [PropertyMapNameID] [int] NULL,
 CONSTRAINT [PK_tblPPObjectChildObject] PRIMARY KEY CLUSTERED 
(
    [InsertionID] ASC,
    [ChildInsertionID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 85, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[tblPPObjectChildObjectList](
    [SortIndex] [int] NOT NULL,
    [UpdateTime] [datetime] NULL,
    [InsertionID] [bigint] NOT NULL,
    [ChildInsertionID] [bigint] NOT NULL,
    [SortText] [nvarchar](260) NULL,
    [UpdateID] [bigint] NULL,
    [RemovalThreshold] [bigint] NULL,
 CONSTRAINT [PK_tblPPObjectChildObjectList] PRIMARY KEY CLUSTERED 
(
    [InsertionID] ASC,
    [ChildInsertionID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 85, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[tblPPObjectProperty](
    [UpdateTime] [datetime] NULL,
    [InsertionID] [bigint] NOT NULL,
    [BitValue] [bit] NULL,
    [UIDValue] [uniqueidentifier] NULL,
    [FloatValue] [float] NULL,
    [BigIntValue] [bigint] NULL,
    [IntValue] [int] NULL,
    [NVarCharValue] [nvarchar](max) NULL,
    [TypeIndex] [tinyint] NOT NULL,
    [SearchText] [nvarchar](max) NULL,
    [UpdateID] [bigint] NULL,
    [PropertyMapNameID] [int] NOT NULL,
 CONSTRAINT [PK_tblPPObjectProperty] PRIMARY KEY CLUSTERED 
(
    [InsertionID] ASC,
    [PropertyMapNameID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 85, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
CREATE TABLE [dbo].[tblPPPropertyMapName](
    [PropertyMapName] [varchar](max) NOT NULL,
    [PropertyMapNameID] [int] IDENTITY(1,1) NOT NULL,
 CONSTRAINT [PK_tblPPPropertyMapName] PRIMARY KEY CLUSTERED 
(
    [PropertyMapNameID] 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] TEXTIMAGE_ON [PRIMARY]
GO
CREATE TABLE [dbo].[tblPPRedundantObjects](
    [InsertionID] [bigint] NOT NULL,
    [UpdateTime] [datetime] NULL,
 CONSTRAINT [PK_tblPPRedundantObjects] PRIMARY KEY CLUSTERED 
(
    [InsertionID] 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].[tblPPTypeName](
    [TypeName] [varchar](max) NOT NULL,
    [TypeNameID] [int] IDENTITY(1,1) NOT NULL,
 CONSTRAINT [PK_tblPPTypeName] PRIMARY KEY CLUSTERED 
(
    [TypeNameID] 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] TEXTIMAGE_ON [PRIMARY]
GO
SET ANSI_PADDING ON
GO
CREATE NONCLUSTERED INDEX [IX_tblPPObject_LevelID_TypeNameID_InsertionID_INC_ObjectID] ON [dbo].[tblPPObject]
(
    [LevelID] ASC,
    [TypeNameID] ASC,
    [InsertionID] ASC
)
INCLUDE([ObjectID]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 85, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
GO
CREATE UNIQUE NONCLUSTERED INDEX [IX_tblPPObject_ObjectID_INC_InsertionID_UpdateID_TypeNameID_SourceInterpreterID_LevelID] ON [dbo].[tblPPObject]
(
    [ObjectID] ASC
)
INCLUDE([InsertionID],[UpdateID],[TypeNameID],[SourceInterpreterID],[LevelID]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 85, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_tblPPObject_SearchParentInsertionID_SourceInterpreteID_InsertionID_INC_ObjectID_LevelID_UpdateID] ON [dbo].[tblPPObject]
(
    [SearchParentInsertionID] ASC,
    [SourceInterpreterID] ASC,
    [InsertionID] ASC
)
INCLUDE([ObjectID],[LevelID],[UpdateID]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 85, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_tblPPObjectBlobProperty_InsertionID] ON [dbo].[tblPPObjectBlobProperty]
(
    [InsertionID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 85, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_tblPPObjectChildObject_ChildInsertionID] ON [dbo].[tblPPObjectChildObject]
(
    [ChildInsertionID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 85, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_tblPPObjectChildObjectList_ChildInsertionID] ON [dbo].[tblPPObjectChildObjectList]
(
    [ChildInsertionID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 85, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_tblPPObjectProperty_InsertionID] ON [dbo].[tblPPObjectProperty]
(
    [InsertionID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 85, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_tblPPObjectProperty_InsertionID_TypeIndex_INC_SearchText] ON [dbo].[tblPPObjectProperty]
(
    [InsertionID] ASC,
    [TypeIndex] ASC
)
INCLUDE([SearchText]) 
WHERE ([TypeIndex]=(6))
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 85, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tblPPObjectProperty] ADD  CONSTRAINT [DF_ObjectProperty_SearcText]  DEFAULT (NULL) FOR [SearchText]
GO
ALTER TABLE [dbo].[tblPPObject]  WITH CHECK ADD  CONSTRAINT [FK_tblPPObject_tblPPTypeName] FOREIGN KEY([TypeNameID])
REFERENCES [dbo].[tblPPTypeName] ([TypeNameID])
GO
ALTER TABLE [dbo].[tblPPObject] CHECK CONSTRAINT [FK_tblPPObject_tblPPTypeName]
GO
ALTER TABLE [dbo].[tblPPObjectBlobProperty]  WITH CHECK ADD  CONSTRAINT [FK_tblPPObjectBlobProperty_tblPPObject] FOREIGN KEY([InsertionID])
REFERENCES [dbo].[tblPPObject] ([InsertionID])
GO
ALTER TABLE [dbo].[tblPPObjectBlobProperty] CHECK CONSTRAINT [FK_tblPPObjectBlobProperty_tblPPObject]
GO
ALTER TABLE [dbo].[tblPPObjectBlobProperty]  WITH CHECK ADD  CONSTRAINT [FK_tblPPObjectBlobProperty_tblPPPropertyMapName] FOREIGN KEY([PropertyMapNameID])
REFERENCES [dbo].[tblPPPropertyMapName] ([PropertyMapNameID])
GO
ALTER TABLE [dbo].[tblPPObjectBlobProperty] CHECK CONSTRAINT [FK_tblPPObjectBlobProperty_tblPPPropertyMapName]
GO
ALTER TABLE [dbo].[tblPPObjectChildObject]  WITH CHECK ADD  CONSTRAINT [FK_tblPPObjectChildObject_tblPPObject] FOREIGN KEY([ChildInsertionID])
REFERENCES [dbo].[tblPPObject] ([InsertionID])
GO
ALTER TABLE [dbo].[tblPPObjectChildObject] CHECK CONSTRAINT [FK_tblPPObjectChildObject_tblPPObject]
GO
ALTER TABLE [dbo].[tblPPObjectChildObject]  WITH CHECK ADD  CONSTRAINT [FK_tblPPObjectChildObject_tblPPPropertyMapName] FOREIGN KEY([PropertyMapNameID])
REFERENCES [dbo].[tblPPPropertyMapName] ([PropertyMapNameID])
GO
ALTER TABLE [dbo].[tblPPObjectChildObject] CHECK CONSTRAINT [FK_tblPPObjectChildObject_tblPPPropertyMapName]
GO
ALTER TABLE [dbo].[tblPPObjectChildObjectList]  WITH CHECK ADD  CONSTRAINT [FK_tblPPObjectChildObjectList_tblPPObject] FOREIGN KEY([ChildInsertionID])
REFERENCES [dbo].[tblPPObject] ([InsertionID])
GO
ALTER TABLE [dbo].[tblPPObjectChildObjectList] CHECK CONSTRAINT [FK_tblPPObjectChildObjectList_tblPPObject]
GO
ALTER TABLE [dbo].[tblPPObjectProperty]  WITH CHECK ADD  CONSTRAINT [FK_tblPPObjectProperty_tblPPObject] FOREIGN KEY([InsertionID])
REFERENCES [dbo].[tblPPObject] ([InsertionID])
GO
ALTER TABLE [dbo].[tblPPObjectProperty] CHECK CONSTRAINT [FK_tblPPObjectProperty_tblPPObject]
GO
ALTER TABLE [dbo].[tblPPObjectProperty]  WITH CHECK ADD  CONSTRAINT [FK_tblPPObjectProperty_tblPPPropertyMapName] FOREIGN KEY([PropertyMapNameID])
REFERENCES [dbo].[tblPPPropertyMapName] ([PropertyMapNameID])
GO
ALTER TABLE [dbo].[tblPPObjectProperty] CHECK CONSTRAINT [FK_tblPPObjectProperty_tblPPPropertyMapName]
GO
CREATE NONCLUSTERED INDEX [IX_tblPPObjectChildObject_UpdateID] ON [dbo].[tblPPObjectChildObject]
(
    [UpdateID] ASC
)
INCLUDE([ChildInsertionID]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 85, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_tblPPObjectChildObjectList_UpdateID] ON [dbo].[tblPPObjectChildObjectList]
(
    [UpdateID] ASC
)
INCLUDE([ChildInsertionID]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 85, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
GO
CREATE procedure [dbo].[spMergePPObject]
(
    @values dbo.udtPPObject readonly
)
as
begin

    begin try

        declare @updatetime datetime
        set @updatetime = GetUTCDate()

        update t
        set t.UpdateTime = @updatetime, t.UpdateId = s.UpdateId, t.TypenameId = s.TypeNameId, t.SourceInterpreterID = s.SourceInterpreterID, t.LevelID = s.LevelID, t.SearchParentInsertionID = s.SearchParentInsertionID           
        from tblPPObject as t
        join @values as s
        on s.InsertionId = t.InsertionID and (s.UpdateID is not null)

        insert into tblPPObject (InsertionId, ObjectId, TypeNameId, UpdateId, UpdateTime, SourceInterpreterID, LevelID, SearchParentInsertionID)  
        select s.InsertionId, s.ObjectId, s.TypeNameId, s.UpdateId, @updatetime, s.SourceInterpreterID, s.LevelID, s.SearchParentInsertionID
        from @values as s
        left join tblPPObject as t
        on s.InsertionID = t.InsertionID
        where t.InsertionID is null

    end try
    begin catch

        declare @errormessage varchar(256)
        select @errormessage = ERROR_MESSAGE()
        raiserror('Error updating entries in the tblPPObject Table. %s', 16, 1, @errormessage)

    end catch

end
GO
CREATE procedure [dbo].[spMergePPObjectBlobProperty]
(
    @values dbo.udtPPObjectBlobProperty readonly
)
as
begin

    begin try

        declare @updatetime datetime
        set @updatetime = GetUTCDate()

        update t
        set t.UpdateTime = @updatetime, t.[Value] = s.BlobValue, t.UpdateId = s.UpdateId
        from tblPPObjectBlobProperty as t
        join @values as s
        on s.InsertionId = t.InsertionID and t.PropertyMapNameId = s.PropertyMapNameID

        insert into tblPPObjectBlobProperty (PropertyMapNameId, Value, UpdateId, UpdateTime, InsertionId) 
        select s.PropertyMapNameId, s.BlobValue, s.UpdateId, @updatetime, s.InsertionId
        from @values as s
        left join tblPPObjectBlobProperty as t
        on s.InsertionID = t.InsertionID and t.PropertyMapNameId = s.PropertyMapNameID
        where t.InsertionID is null and t.PropertyMapNameID is null

    end try
    begin catch

        declare @errormessage varchar(256)
        select @errormessage = ERROR_MESSAGE()
        raiserror('Error updating entries in the tblPPObjectBlobProperty Table. %s', 16, 1, @errormessage)

    end catch

end
GO
create procedure [dbo].[spMergePPObjectChildObject]
(
    @values dbo.udtPPChildObject readonly
)
as
begin

    begin try

        declare @updatetime datetime
        set @updatetime = GetUTCDate()

        update t
        set t.UpdateID = s.UpdateID, t.UpdateTime = @updatetime
        from tblPPObjectChildObject as t
        join @values as s
        on s.InsertionId = t.InsertionID and t.ChildInsertionId = t.ChildInsertionID

        insert into tblPPObjectChildObject (InsertionID, ChildInsertionID, PropertyMapNameID, UpdateID, UpdateTime) 
        select s.InsertionID, s.ChildInsertionID, s.PropertyMapNameID, s.UpdateID, @updatetime
        from @values as s
        left join tblPPObjectChildObject as t
        on s.InsertionId = t.InsertionID and s.ChildInsertionID = t.ChildInsertionID
        where t.ChildInsertionID is null

    end try
    begin catch

        declare @errormessage varchar(256)
        select @errormessage = ERROR_MESSAGE()
        raiserror('Error updating entries in the tblPPObjectChildObject Table. %s', 16, 1, @errormessage)

    end catch

end
GO
create procedure [dbo].[spMergePPObjectChildObjectList]
(
    @values dbo.udtPPChildObjectList readonly
)
as
begin

    begin try

        declare @updatetime datetime
        set @updatetime = GetUTCDate()

        update t
        set t.UpdateID = isnull(s.UpdateID, t.UpdateID), t.UpdateTime = @updatetime, t.RemovalThreshold = isnull(s.RemovalThreshold, t.RemovalThreshold), t.SortIndex = isnull(s.SortIndex, t.SortIndex), t.SortText = isnull(s.SortText, t.SortText)
        from tblPPObjectChildObjectList as t
        join @values as s
        on s.InsertionID = t.InsertionID and s.ChildInsertionId = t.ChildInsertionID

        insert into tblPPObjectChildObjectList (InsertionID, ChildInsertionID, SortIndex, UpdateID, UpdateTime, SortText, RemovalThreshold) 
        select s.InsertionID, s.ChildInsertionID, isnull(s.SortIndex, -1), s.UpdateID, @updatetime, s.SortText, s.RemovalThreshold
        from @values as s
        left join tblPPObjectChildObjectList as t
        on s.InsertionId = t.InsertionID and s.ChildInsertionID = t.ChildInsertionID
        where t.ChildInsertionID is null

    end try
    begin catch

        declare @errormessage varchar(256)
        select @errormessage = ERROR_MESSAGE()
        raiserror('Error updating entries in the tblPPObjectChildObjectList Table. %s', 16, 1, @errormessage)

    end catch

end
GO
CREATE procedure [dbo].[spMergePPObjectProperty]
(
    @values dbo.udtPPObjectProperty readonly
)
as
begin

    begin try

        declare @updatetime datetime
        set @updatetime = GetUTCDate()

        update t 
        set t.UpdateTime = @updatetime, t.BitValue = s.BitValue, t.UIDValue = s.UIDValue, t.FloatValue = s.FloatValue, t.IntValue = s.IntValue, t.NVarCharValue = s.NVarCharValue, t.BigIntValue = s.BigIntValue, t.UpdateId = s.UpdateId, t.TypeIndex = s.TypeIndex, t.SearchText = s.SearchText
        from tblPPObjectProperty as t
        join @values as s
        on s.InsertionId = t.InsertionID and t.PropertyMapNameId = s.PropertyMapNameID

        insert into tblPPObjectProperty (InsertionId, PropertyMapNameId, UpdateID, UpdateTime, BitValue, UIDValue, FloatValue, IntValue, NVarCharValue, BigIntValue, TypeIndex, SearchText)
        select s.InsertionId, s.PropertyMapNameId, s.UpdateId, @updatetime, s.BitValue, s.UidValue, s.floatValue, s.IntValue, s.NVarCharValue, s.BigIntValue, s.TypeIndex, s.SearchText
        from @values as s
        left join tblPPObjectProperty as t
        on s.InsertionID = t.InsertionID and t.PropertyMapNameId = s.PropertyMapNameID
        where t.InsertionID is null and t.PropertyMapNameID is null

    end try
    begin catch

        declare @errormessage varchar(256)
        select @errormessage = ERROR_MESSAGE()
        raiserror('Error updating entries in the tblPPObjectProperty Table. %s', 16, 1, @errormessage)

    end catch

end
GO
create procedure [dbo].[spPurge](@modelinsertionid bigint)
as
begin

    set deadlock_priority low
    set nocount on

    print 'Start Purge'

    -- Work out what can be removed
    begin transaction

    declare @RedundantInsertionIDs table (InsertionID bigint PRIMARY KEY, UpdateTime DateTime)
        
    declare @currenttime datetime
    set @currenttime = GetUTCDate()

    insert into @RedundantInsertionIDs 
    select pocol.ChildInsertionId, @currenttime from tblPPObjectChildObjectList as pocol
    where pocol.UpdateID = -1

    insert into @RedundantInsertionIDs 
    select poco.ChildInsertionId, @currenttime from tblPPObjectChildObject as poco 
    where poco.UpdateID = -1

    delete from tblPPRedundantObjects
    from @RedundantInsertionIDs as ri
    right join tblPPRedundantObjects as ro
    on ri.InsertionID = ro.InsertionID
    where (ri.InsertionID is null) and (ro.UpdateTime is not null)

    insert into tblPPRedundantObjects
    select ri.InsertionID, @currenttime from @RedundantInsertionIDs as ri
    left join tblPPRedundantObjects as ro
    on ri.InsertionID = ro.InsertionID
    where ro.InsertionID is null

    commit transaction;

    -- Carry out the removal
    declare @RedundantInsertionIDsToRemove table (InsertionID bigint, PRIMARY KEY (InsertionID))

    declare @thresholddatetime datetime
    set @thresholddatetime = DATEADD(mi, -2, @currenttime)

    declare @purgecount int = 500
    declare @removalcount int

    delete top (@purgecount) from tblPPRedundantObjects
    output deleted.InsertionID into @RedundantInsertionIDsToRemove
    from tblPPRedundantObjects as ro
    where (ro.UpdateTime is null) or (ro.UpdateTime < @thresholddatetime)

    declare @ObjectIDs table (ObjectID uniqueidentifier, PRIMARY KEY (ObjectID))

    insert into @ObjectIDs 
    select ObjectID from tblPPObject as po
    join @RedundantInsertionIDsToRemove as ii
    on po.InsertionID = ii.InsertionID

    insert into tblPPRedundantObjects
    select ChildInsertionID, null from tblPPObjectChildObjectList as pocol 
    join @RedundantInsertionIDsToRemove as ii on pocol.InsertionID = ii.InsertionID
    where not exists (select InsertionID from tblPPRedundantObjects where InsertionID = ChildInsertionID)

    insert into tblPPRedundantObjects
    select ChildInsertionID, null from tblPPObjectChildObject as poco 
    join @RedundantInsertionIDsToRemove as ii on poco.InsertionID = ii.InsertionID
    where not exists (select InsertionID from tblPPRedundantObjects where InsertionID = ChildInsertionID)

    delete from op  
    from tblPPObjectProperty as op 
    join @RedundantInsertionIDsToRemove as ii
    on op.InsertionID = ii.InsertionID

    delete from bop 
    from tblPPObjectBlobProperty as bop 
    join @RedundantInsertionIDsToRemove as ii
    on bop.InsertionID = ii.InsertionID

    delete from poco 
    from tblPPObjectChildObject as poco 
    join @RedundantInsertionIDsToRemove as ii
    on poco.ChildInsertionID = ii.InsertionID

    delete from pocol  
    from tblPPObjectChildObjectList as pocol 
    join @RedundantInsertionIDsToRemove as ii
    on pocol.ChildInsertionID = ii.InsertionID

    print 'Remove objects'

    delete from po 
    from tblPPObject as po  
    join @RedundantInsertionIDsToRemove as ii
    on po.InsertionID = ii.InsertionID

    print 'End Purge'

    select ObjectID
    from @ObjectIDs
    
end
GO

在一个会话中运行此更新/插入查询：

delete from tblPPObjectBlobProperty
delete from tblPPObjectProperty
delete from tblPPObjectChildObject
delete from tblPPObjectChildObjectList
delete from tblPPObject
delete from tblPPPropertyMapName
delete from tblPPRedundantObjects
delete from tblPPTypeName
go

DBCC CHECKIDENT (tblPPPropertyMapName, RESEED, 0)
DBCC CHECKIDENT (tblPPTypeName, RESEED, 0)

declare @index int = 1
while (@index <= 1000)
begin

    insert into [dbo].tblPPPropertyMapName select 'Test ' + cast(@index as varchar)
    insert into [dbo].tblPPTypeName select 'Test ' + cast(@index as varchar)
    set @index = @index + 1

end
go
    
DECLARE @values1 dbo.udtPPObject
DECLARE @values2 dbo.udtPPChildObject
DECLARE @values3 dbo.udtPPChildObjectList
DECLARE @values4 dbo.udtPPObjectProperty
DECLARE @values5 dbo.udtPPObjectBlobProperty

declare @insertionid bigint = 0
declare @childobjectinsertionid bigint
declare @childobjectlistinsertionid bigint

while (1 = 1)
begin

    WAITFOR DELAY '00:00:01'

    SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
    BEGIN TRANSACTION;

    declare @updatetime datetime
    set @updatetime = GetUTCDate()

    set @insertionid = @insertionid + 1
    set @childobjectinsertionid = @insertionid + 1
    set @childobjectlistinsertionid = @childobjectinsertionid + 1

    delete from @values1
    delete from @values2
    delete from @values3
    delete from @values4
    delete from @values5

    insert into @values1 select @insertionid, NEWID(), 1, 1, null, null, null
    insert into @values1 select @childobjectinsertionid, NEWID(), 1, 2, null, null, null
    insert into @values1 select @childobjectlistinsertionid, NEWID(), 1, 3, null, null, null

    insert into @values2 select @insertionid, @childobjectinsertionid, 1, -1
    insert into @values3 select @insertionid, @childobjectlistinsertionid, -1, -1, null, null

    -- Updates
    if ((@insertionid - 20) > 0)
    begin
    
        declare @i1 bigint = (((@insertionid - 20)/ 3) * 3) + 1

        print 'Updating child object insertion id ' + cast((@i1 + 1) as varchar(max))
        insert into @values2 select @i1, @i1 + 1, 1, -1

        print 'Updating child object list insertion id ' + cast((@i1 + 2) as varchar(max))
        insert into @values3 select @i1, @i1 + 2, -1, -1, null, null

    end

    declare @index int = 1
    while (@index <= 100)
    begin

        insert into @values4 select @childobjectinsertionid, @index, -1, 1, null, null, null, null, null, null, 1
        insert into @values5 select @childobjectlistinsertionid, @index, null, -1

        set @index = @index + 1

    end

    exec spMergePPObject @values1
    exec spMergePPObjectChildObject @values2
    exec spMergePPObjectChildObjectList @values3
    exec spMergePPObjectProperty @values4
    exec spMergePPObjectBlobProperty @values5

    set @insertionid = @childobjectlistinsertionid

    commit transaction;

end
d) 在另一个会话中运行此更新/插入查询（在（c）的查询几秒钟后）： 
DECLARE @values1 dbo.udtPPObject
DECLARE @values2 dbo.udtPPChildObject
DECLARE @values3 dbo.udtPPChildObjectList
DECLARE @values4 dbo.udtPPObjectProperty
DECLARE @values5 dbo.udtPPObjectBlobProperty

declare @insertionid bigint = 900000000
declare @childobjectinsertionid bigint
declare @childobjectlistinsertionid bigint

while (1 = 1)
begin

    WAITFOR DELAY '00:00:01'

    SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
    BEGIN TRANSACTION;

    declare @updatetime datetime
    set @updatetime = GetUTCDate()

    set @insertionid = @insertionid + 1
    set @childobjectinsertionid = @insertionid + 1
    set @childobjectlistinsertionid = @childobjectinsertionid + 1

    delete from @values1
    delete from @values2
    delete from @values3
    delete from @values4
    delete from @values5

    insert into @values1 select @insertionid, NEWID(), 1, 1, null, null, null
    insert into @values1 select @childobjectinsertionid, NEWID(), 1, 2, null, null, null
    insert into @values1 select @childobjectlistinsertionid, NEWID(), 1, 3, null, null, null

    insert into @values2 select @insertionid, @childobjectinsertionid, 1, -1
    insert into @values3 select @insertionid, @childobjectlistinsertionid, -1, -1, null, null

    -- Updates
    if ((@insertionid - 900000020) > 0)
    begin
    
        declare @i1 bigint = (((@insertionid - 20)/ 3) * 3) + 1

        print 'Updating child object insertion id ' + cast((@i1 + 1) as varchar(max))
        insert into @values2 select @i1, @i1 + 1, 1, -1

        print 'Updating child object list insertion id ' + cast((@i1 + 2) as varchar(max))
        insert into @values3 select @i1, @i1 + 2, -1, -1, null, null

    end

    declare @index int = 1
    while (@index <= 100)
    begin

        insert into @values4 select @childobjectinsertionid, @index, -1, 1, null, null, null, null, null, null, 1
        insert into @values5 select @childobjectlistinsertionid, @index, null, -1

        set @index = @index + 1

    end

    exec spMergePPObject @values1
    exec spMergePPObjectChildObject @values2
    exec spMergePPObjectChildObjectList @values3
    exec spMergePPObjectProperty @values4
    exec spMergePPObjectBlobProperty @values5

    set @insertionid = @childobjectlistinsertionid

    commit transaction;

end

e) 最后，在另一个会话中运行删除查询：

SET TRANSACTION ISOLATION LEVEL SNAPSHOT;

while (1 = 1)
begin

    WAITFOR DELAY '00:00:05'

    exec spPurge 0 

end

所以这个过程一直在表中插入/更新行数据，而spPurge过程则会在它们不再使用并且至少2分钟旧时将其删除。

问题发生在过程的最后部分，即：

delete from po 
from tblPPObject as po  
join @RedundantInsertionIDsToRemove as ii
on po.InsertionID = ii.InsertionID
解除外键约束可以解决问题，但这不是问题的解决办法。由于进行外键检查时访问的行与正在插入/更新的行不冲突，所以这应该不是一个问题，对吗？ 我不明白的是，错误发生在清理过程中从tbPPObject表删除数据的部分。清理调用不在一个事务中，所以我的理解是每个执行的语句都在自己的事务中，因此之前的扫描等操作不应该有影响。被删除的行不会同时被更新，所以这个错误毫无意义。 而且这个错误非常间歇性 - 在不断插入、更新和删除行的脚本下，它将花费20到60分钟才会失败。如果这是清理过程的问题，那么它会比这快得多。
1个回答

这些删除执行计划显示了使用全表扫描的合并连接，因此会触及到所有行。罪魁祸首是dbo.spPurge过程中的表变量：

declare @RedundantInsertionIDs table (InsertionID bigint, UpdateTime DateTime)
这个缺少一个索引来优化删除查询。我观察到在表变量上创建一个主键可以改善计划，使得目标表通过查找而不是扫描进行访问，只触及需要删除的行而不是活动行。 
declare @RedundantInsertionIDs table (InsertionID bigint primary key, UpdateTime DateTime);
如果您仍然遇到问题，可以尝试使用临时表替代表变量。在清除过程中，您还可以作为最后的手段实施重试机制。
我也在使用SQL Server 2019。从我所看到的情况来看，新的计划仍然显示在tblPPObjectChildObject和tblPPObjectChildObjectList表上进行扫描，我猜这可能是导致问题的原因？我已经在tblPPObjectChildObject和tblPPObjectChildObjectList表的UpdateID列（包括ChildInsertionID列）上添加了新的非聚集索引。请参考https://www.brentozar.com/pastetheplan/?id=SkTlNd-Ct现在主要表格上没有表扫描，但问题仍然存在。 - Sputnik
@Sputnik，我忽略了在改进计划中还有几个扫描。解决这个问题将归结为查询/索引调优。 - Dan Guzman