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?
