Snapshot isolation transaction aborted due to update conflict in SQL Server when removing rows

Question

I now have a simple example of this issue following on from: Snapshot isolation transaction aborted due to update conflict in SQL Server

This is the script to create the database tables:

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 NONCLUSTERED INDEX [IX_tblPPObjectChildObjectList_ChildInsertionID_INC_InsertionID_UpdateID_SortText_SortIndex_UpdateTime_RemovalThreshold] ON [dbo].[tblPPObjectChildObjectList]
(
    [ChildInsertionID] ASC
)
INCLUDE([InsertionID],[UpdateID],[SortText],[SortIndex],[UpdateTime],[RemovalThreshold]) 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_InsertionID_UpdateID_INC_SortText_SortIndex_UpdateTime_RemovalThreshold] ON [dbo].[tblPPObjectChildObjectList]
(
    [InsertionID] ASC,
    [UpdateID] ASC
)
INCLUDE([SortText],[SortIndex],[UpdateTime],[RemovalThreshold]) 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 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].[udtPPInsertionIDList] AS TABLE(
    [InsertionID] [bigint] NOT NULL,
    PRIMARY KEY CLUSTERED 
(
    [InsertionID] ASC
)WITH (IGNORE_DUP_KEY = OFF)
)
GO

Snapshot isolation must be on in the database and read committed snapshot must be on.

This script should be used to populate the table:

declare @i int
set @i = 1

while (@i < 200)
begin

    insert into [dbo].[tblPPObjectChildObjectList]
    select -1, GetUTCDate(), @i, @i * 1000, null, 1, null

    set @i = @i + 1

end
GO

There are then two scripts which must be run at the same time. This is the update script:

SET TRANSACTION ISOLATION LEVEL READ COMMITTED
GO 

declare @insertionid bigint
set @insertionid = 1

while (1 = 1)
begin

    BEGIN TRY
     
        BEGIN TRANSACTION;  

        WAITFOR DELAY '00:00:01';

        declare @updatetime datetime
        set @updatetime = GetUTCDate()

        declare @values dbo.udtPPChildObjectList
        delete from @values

        insert into @values select 1, 1000, -1, 1, null, null
        insert into @values select 2, 2000, -1, 1, null, null
        insert into @values select 3, 3000, -1, 1, null, null
        insert into @values select 4, 4000, -1, 1, null, null
        insert into @values select 5, 5000, -1, 1, null, null
        insert into @values select 6, 6000, -1, 1, null, null
        insert into @values select 7, 7000, -1, 1, null, null
        insert into @values select 8, 8000, -1, 1, null, null
        insert into @values select 9, 9000, -1, 1, null, null
        insert into @values select 10, 10000, -1, 1, null, null
                    
        update t 
        set t.UpdateTime = @updatetime
        from tblPPObjectChildObjectList as t 
        join @values as s
        on s.ChildInsertionId = t.ChildInsertionID

        select t.ChildInsertionID
        from tblPPObjectChildObjectList as t with (updlock, rowlock) 
        left join @values as s
        on s.InsertionId = t.InsertionID and s.ChildInsertionId = t.ChildInsertionID
        where (t.InsertionID in (select InsertionId from @values)) and (s.ChildInsertionId is null)

        COMMIT TRANSACTION; 

    END TRY
    BEGIN CATCH 

        ROLLBACK TRANSACTION;

        print 'ERROR :' + ERROR_MESSAGE()
        break;

    END CATCH

end 
GO  

and this is the delete script:

SET TRANSACTION ISOLATION LEVEL SNAPSHOT
GO  

while (1 = 1)
begin

    BEGIN TRY

        WAITFOR DELAY '00:00:01';

        declare @insertionids dbo.udtPPInsertionIDList

        declare @i int
        set @i = 1

        while (@i < 150)
        begin

            insert into @insertionids
            select 90000000 + @i

            set @i = @i + 1

        end

        set deadlock_priority low
        set nocount on

        -- Create the required temporary tables
        declare @LocalInsertionIDs table (InsertionID bigint, PRIMARY KEY (InsertionID))
        
        delete from @LocalInsertionIDs
        insert into @LocalInsertionIDs 
        select InsertionID from @insertionids

        if ((select count(*) from @LocalInsertionIDs) > 0)
        begin
        
            declare @c4 int
            select @c4 = count(*) 
            from tblPPObjectChildObjectList as pocol
            join @LocalInsertionIDs as ii
            on pocol.InsertionID = ii.InsertionID

            delete from pocol with (rowlock, updlock) 
            from tblPPObjectChildObjectList as pocol with (rowlock, updlock) 
            join @LocalInsertionIDs as ii
            on pocol.InsertionID = ii.InsertionID
            
            declare @c5 int
            select @c5 = count(*) 
            from tblPPObjectChildObjectList as pocol
            join @LocalInsertionIDs as ii
            on pocol.ChildInsertionID = ii.InsertionID

            delete from pocol with (rowlock, updlock) 
            from tblPPObjectChildObjectList as pocol with (rowlock, updlock) 
            join @LocalInsertionIDs as ii
            on pocol.ChildInsertionID = ii.InsertionID

        end

        delete from @insertionids

    END TRY
    BEGIN CATCH 
        
        print 'ERROR :' + ERROR_MESSAGE()
        break;

    END CATCH

end
GO  

After 10-15 minutes the delete script will fail with the update error even though the rows being removed are not being updated (in fact they do not even exist).

Can anyone see why this exception is being raised?

Answer 1

After 10-15 minutes the delete script will fail with the update error even though the rows being removed are not being inserted or updated.

The query below to get the rows to be deleted performs a full scan of the tblPPObjectBlobProperty table because no index exists on UpdateTime. The UPDLOCK lock will fail when rows outside the range to be deleted are accessed and the row has been modified by another transaction.

    insert into @InsertionIDs               
    select distinct InsertionID, UpdateTime from tblPPObjectBlobProperty as poco with (rowlock, updlock) 
    where UpdateTime < @thresholddatetime

Add an index on the UpdateTime column so that only the rows to be deleted are touched. This should avoid the update conflict error.

CREATE INDEX idx_tblPPObjectBlobProperty_UpdateTime ON dbo.tblPPObjectBlobProperty(UpdateTime);

On a side note, I suggest you use THROW to facilitate troubleshooting. The error message will include the line number of the problem statement in the script. Also, add SET XACT_ABORT ON; to scripts/procs with explicit transactions to ensure the transaction is rolled back immediately after an error, client timeout, or query cancel. Below is the standard catch block I use.

BEGIN CATCH 

    IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION;
    THROW;

END CATCH;