不明确的更新冲突

Question

不明确的更新冲突

sql-serversql-server-2016lockingdatabase-internalssnapshot-isolation

14

我有两个问题：

1. 在这种情况下，为什么我会遇到更新冲突而不是仅仅阻止：

-- prepare
drop database if exists [TestSI];
go
create database [TestSI];
go
alter database [TestSI] set READ_COMMITTED_SNAPSHOT ON;
alter database [TestSI] set ALLOW_SNAPSHOT_ISOLATION ON;
go
use [TestSI];
go
drop table if exists dbo.call_test;
create table dbo.call_test ( Id bigint CONSTRAINT [PK_Call] PRIMARY KEY CLUSTERED ( [Id] ASC ), additional int, incl int );
create index ix_Call on dbo.call_test ( additional ) include( incl );
insert into dbo.call_test select 1, 2, 3;
go

第一個會議：

use [TestSI];
go
set transaction isolation level snapshot
begin tran

   UPDATE dbo.call_test SET additional = 22 WHERE [Id] = 1

还有第二个会议：

use [TestSI];
go
set transaction isolation level snapshot

   UPDATE dbo.call_test SET additional = 222 WHERE [Id] = 1

在第二个会话中，我立即遇到了以下问题：

错误消息 3960，级别 16，状态 3，行 3 由于更新冲突，快照隔离事务已中止。您不能使用快照隔离来直接或间接访问数据库“TestSI”中的表“dbo.call_test”以更新、删除或插入已被另一个事务修改或删除的行。请重试该事务或更改更新/删除语句的隔离级别。

如果我更新包含列 incl 而不是非聚集索引键，我也会遇到这种情况。

在这种情况下，非聚集索引对更新冲突有什么影响？为什么在这种情况下不使用锁定机制？

2. 第二个理论问题：

SQL Server 如何处理包含列的更新？

当我们更新此值时，SQL Server 如何更新所有具有包含列的非聚集索引？我在查询计划中没有看到任何相关信息。

select @@version

Microsoft SQL Server 2016 (SP2) (KB4052908) - 13.0.5026.0 (X64) Mar 18 2018 09:11:49 版权所有 (c) Microsoft Corporation 开发者版 (64位) 在 Windows 10 Pro 10.0 上 (版本 18363: ) (Hypervisor) 我在 SQL Server 2019 上检查了这个示例，行为和我预期的一样：第二个会话被锁定。这是一个错误还是我做错了什么？

- Pavel Zv

2个回答

10

第二个理论问题是：SQL Server如何处理包含列的更新？当我们更新这个值时，SQL Server如何更新所有具有包含列的非聚集索引？我在查询计划中没有看到任何相关的信息。对于第一个问题，我不确定自己是否理解了情况，并且我发现SQL Server 2017和2019之间的行为差异更加有趣，但是我可以帮助解开这个谜团。非聚集索引的更新在SSMS图形执行计划中不会显示，但是你可以在XML中看到它被提及。

  <Update DMLRequestSort="false">
    <Object Database="[TestSI]" Schema="[dbo]" Table="[call_test]" Index="[PK_Call]" IndexKind="Clustered" Storage="RowStore" />
    <Object Database="[TestSI]" Schema="[dbo]" Table="[call_test]" Index="[ix_Call]" IndexKind="NonClustered" Storage="RowStore" />

此外，Sentry One Plan Explorer在更新图标上放置了一个巧妙的小指示器，以告诉您非聚集索引正在“幕后”进行更新。

这被称为“窄更新计划”，至少在口语中是这样说的（我在官方文档中没有看到这个）。你可以在Paul White的博客文章中看到窄更新计划和宽更新计划之间的区别的例子：优化改变数据的T-SQL查询。

- Josh Darnell

- Paul White · Accepted Answer

为什么我在这种情况下会遇到更新冲突而不是阻止呢？这是一个产品缺陷，在SQL Server 2019中已经修复。当快照事务尝试修改已被另一个提交于快照事务开始之后的事务修改的行时，会发生快照写冲突。你的示例中出现错误行为的原因有些微妙。更新计划使用了Rowset Sharing。这意味着Clustered Index Seek和Clustered Index Update共享一个公共的行集。这是一种优化，使得Clustered Index Update不需要通过正常的查找操作来定位要更新的行。公共的行集已经被Clustered Index Seek正确地定位。更新运算符在行集中的“当前行”上执行其工作。这会导致错误的消息，因为seek所看到的行的版本（未提交更改之前的行）与更新操作符共享。更新操作符看到要更新的行已经发生了变化，并错误地认为发生了更新冲突。可以通过多种方式获得正确的行为。一种方法是重写更新操作，使得无法进行行集共享，可以强制seek使用不同的索引。通过使用不同的访问方法，就没有共享的常规行集了。

UPDATE CT
SET CT.additional = 222
FROM dbo.call_test AS CT WITH (INDEX(ix_Call))
WHERE CT.Id = 1;

更直接的方法是使用一个未记录和不支持的跟踪标志来禁用Rowset Sharing优化（这仅用于演示目的，请勿在真实数据库上使用）：

UPDATE dbo.call_test 
SET additional = 222 
WHERE [Id] = 1
OPTION (QUERYTRACEON 8746);

计划看起来与原始计划相同（默认情况下不公开行集共享属性），但它将正确阻止而不是抛出更新冲突错误。

您还可以通过强制使用宽（按索引）更新计划来避免错误（并保留对于聚集索引更新的行集共享）：

UPDATE dbo.call_test 
SET additional = 222 
WHERE [Id] = 1
OPTION (QUERYTRACEON 8790);

遇到这个错误需要行集共享和同时维护二级索引（窄或逐行更新）的基表更新。如果这种行为给你带来了现实世界的问题，你应该向微软开启一个支持案例。

Josh已经正确回答了你的第二个问题。我只想补充一点，你可以在SSMS中查看Clustered Index Update操作符上的非聚集索引维护情况 - 你需要打开属性窗口并展开对象节点。