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.
