Snapshot isolation transaction aborted due to update conflict when removing rows from the database



  • I have spent some time on this issue and finally have an example which reproduces the issue (even with the appropriate non-clustered indexes on all FKs).

    This a brief overview of the database operation:

    a) The updates/inserts are done under read committed (snapshot) isolation.

    b) The removal of items is done under snapshot isolation.

    c) There are indexes for all FKs.

    The purge process removes rows that are old from the database. After between 20-60 minutes the delete script below will fail with the snapshot error. I have been told that FK checks revert to read committed isolation but even this would not explain what we are seeing below.

    Steps to reproduce:

    a) Create a database and ensure that the snapshot option is True. We called the database SnapshotTest.

    b) Create the database using this script below:

    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

    c) Run this update/insert query in one session:

    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) Run this update/insert query in another session (a few seconds after the query in (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) Finally, run the delete query in another session:

    SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
    

    while (1 = 1)
    begin

    WAITFOR DELAY '00:00:05'
    
    exec spPurge 0 
    

    end

    So this is constantly inserting/updating rows in the tables and the spPurge procedure is removing them when they are no longer in use and they are at least 2 minutes old.

    The issue happens in the final part of the part procedure i.e.

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

    Taking off the FKs resolves the issue but this is not a solution to the problem. Since the rows accessed for the FK check do not clash with those being inserted/updated this should not be an issue should it?

    What I do not understand is that the error is occurring on the delete from tbPPObject part of the purge procedure. The purge call is not in a transaction so my understanding is that each statement executed is in its own transaction so previous scans etc should make no difference? The rows being removed are not being updated at the same time so this error makes no sense.

    And also this error is very intermittent - it will take between 20 and 60 mins to fail with scripts constantly inserting, updating and deleting rows. If this was an issue with the purge procedure it would happen much faster than that.



  • The https://www.brentozar.com/pastetheplan/?id=SkT72_WRY show merge joins with full scans so all rows are touched. The culprit is the table variable in the dbo.spPurge procedure:

    declare @RedundantInsertionIDs table (InsertionID bigint, UpdateTime DateTime)
    

    This lacks an index to help optimize the delete queries. I observed that a primary key on the table variable https://www.brentozar.com/pastetheplan/?id=r1BGtvZAt such that the target tables are accessed with a seek instead of scan, touching only those rows to be deleted rather than the active rows too.

    declare @RedundantInsertionIDs table (InsertionID bigint primary key, UpdateTime DateTime);
    

    If you still experience issues, a temporary table instead of table variable may help. You could also implment a retry in the purge proc as a last resort.




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2