我现在有一个关于这个问题的简单例子，来自：由于SQL Server 中的更新冲突，快照隔离事务中止
这是创建数据库表的脚本：
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
数据库中的快照隔离必须打开，读取提交的快照必须打开。
此脚本应用于填充表：
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
然后有两个脚本必须同时运行。这是更新脚本：
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
这是删除脚本：
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
10-15分钟后，删除脚本将失败并出现更新错误，即使被删除的行没有被更新(事实上它们甚至不存在(。
有人知道为什么会提出这个例外吗？
10-15分钟后，删除脚本将失败并出现更新错误即使被移除的行没有被插入或更新。
由于UpdateTime上不存在索引，下面获取要删除的行的查询将对tblPPObjectBlobProperty表执行完全扫描。当访问要删除的范围之外的行并且该行已被另一个事务修改时，UPDLOCK锁将失败。
insert into @InsertionIDs
select distinct InsertionID, UpdateTime from tblPPObjectBlobProperty as poco with (rowlock, updlock)
where UpdateTime < @thresholddatetime
在UpdateTime列上添加一个索引，以便只触摸要删除的行。这样可以避免更新冲突错误。
CREATE INDEX idx_tblPPObjectBlobProperty_UpdateTime ON dbo.tblPPObjectBlobProperty(UpdateTime);
附带说明一下，我建议您使用THROW来方便故障排除。错误消息将包括脚本中问题语句的行号。此外，将SET XACT_ABORT ON;添加到具有显式事务的脚本/进程中，以确保在出现错误、客户端超时或查询取消后立即回滚事务。下面是我使用的标准挡块。
BEGIN CATCH
IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION;
THROW;
END CATCH;