主页 / dba / 问题 / 271480
Accepted
Pavel Zv
Asked: 2020-07-24 00:38:29 +0800 CST

更新冲突不清楚

  • 772

我有两个问题：

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) 2018 年 3 月 18 日 09:11:49 版权所有 (c) Microsoft Corporation Developer Edition (64-bit) o​​n Windows 10 Pro 10.0 (Build 18363:) (Hypervisor) )

我在 SQL Server 2019 上检查了这个示例，该服务器上的行为与我预期的一样：第二个会话被锁定。这是一个错误还是我做错了什么？

sql-server sql-server-2016

2 个回答

  1. Best Answer

    为什么在这种情况下我会遇到更新冲突而不是阻塞

    这是产品缺陷，已在 SQL Server 2019 中修复。

    当快照事务尝试修改已由在快照事务开始后提交的另一个事务修改的行时，会发生快照写入冲突。

    您的示例中行为不正确的原因有些深奥。更新计划使用称为Rowset Sharing的东西。这意味着聚集索引查找和聚集索引更新共享一个公共行集。

    这是一种优化，因此聚集索引更新不需要通过正常的查找操作来定位要更新的行。公用行集已由Clustered Index Seek正确定位。更新运算符对行集中的“当前行”执行其工作。

    这会导致错误消息，因为查找所看到的行的版本（未提交更改之前的行）与更新运算符共享。更新发现它尝试更新的行已更改，并得出（错误地）发生更新冲突的结论。

    可以通过多种方式获得正确的行为。重写更新以便无法共享行集的一种方法是强制搜索使用不同的索引。使用不同的访问方法，没有通用的行集可以共享：

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

    不同的指标

    更直接的方法是使用未记录且不受支持的跟踪标志来禁用行集共享优化（这仅用于演示目的，请勿在真实数据库上使用它）：

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

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

    您还可以通过强制执行宽（每个索引）更新计划来避免错误（并为Clustered Index Update保留行集共享）：

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

    广泛的更新计划

    遇到该错误需要行集共享和基表更新，该更新还维护二级索引（窄或每行更新）。

    如果此行为导致您出现实际问题，您应该向 Microsoft 提出支持案例。

    乔希正确回答了你的第二个问题。我将补充一点，您可以在 SSMS 中的聚簇索引更新运算符上看到非聚簇索引维护——您需要查看“属性”窗口并展开“对象”节点：

    SSMS

    • 18
    1. 第二个理论问题：

    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 在更新图标上放置了一个漂亮的小指示器，让您知道非聚集索引正在“幕后”更新：

    显示 NC 索引更新的计划资源管理器的屏幕截图

    这被称为“狭义的更新计划”，至少通俗地说（我在任何地方的官方文档中都没有看到）。您可以在 Paul White 的这篇博文中看到窄更新计划和宽更新计划之间差异的示例：优化更改数据的 T-SQL 查询

    • 10

相关问题