AskOverflow.Dev

AskOverflow.Dev Logo AskOverflow.Dev Logo

AskOverflow.Dev Navigation

  • 主页
  • 系统&网络
  • Ubuntu
  • Unix
  • DBA
  • Computer
  • Coding
  • LangChain

Mobile menu

Close
  • 主页
  • 系统&网络
    • 最新
    • 热门
    • 标签
  • Ubuntu
    • 最新
    • 热门
    • 标签
  • Unix
    • 最新
    • 标签
  • DBA
    • 最新
    • 标签
  • Computer
    • 最新
    • 标签
  • Coding
    • 最新
    • 标签
主页 / dba / 问题 / 271480
Accepted
Pavel Zv
Pavel Zv
Asked: 2020-07-24 00:38:29 +0800 CST2020-07-24 00:38:29 +0800 CST 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 2 个回答
  • 1208 Views

2 个回答

  • Voted
  1. Best Answer
    Paul White
    2020-07-24T07:17:55+08:002020-07-24T07:17:55+08:00

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

    这是产品缺陷,已在 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
  2. Josh Darnell
    2020-07-24T06:19:42+08:002020-07-24T06:19:42+08:00
    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

相关问题

  • SQL Server - 使用聚集索引时如何存储数据页

  • 我需要为每种类型的查询使用单独的索引,还是一个多列索引可以工作?

  • 什么时候应该使用唯一约束而不是唯一索引?

  • 死锁的主要原因是什么,可以预防吗?

  • 如何确定是否需要或需要索引

Sidebar

Stats

  • 问题 205573
  • 回答 270741
  • 最佳答案 135370
  • 用户 68524
  • 热门
  • 回答
  • Marko Smith

    连接到 PostgreSQL 服务器:致命:主机没有 pg_hba.conf 条目

    • 12 个回答
  • Marko Smith

    如何让sqlplus的输出出现在一行中?

    • 3 个回答
  • Marko Smith

    选择具有最大日期或最晚日期的日期

    • 3 个回答
  • Marko Smith

    如何列出 PostgreSQL 中的所有模式?

    • 4 个回答
  • Marko Smith

    列出指定表的所有列

    • 5 个回答
  • Marko Smith

    如何在不修改我自己的 tnsnames.ora 的情况下使用 sqlplus 连接到位于另一台主机上的 Oracle 数据库

    • 4 个回答
  • Marko Smith

    你如何mysqldump特定的表?

    • 4 个回答
  • Marko Smith

    使用 psql 列出数据库权限

    • 10 个回答
  • Marko Smith

    如何从 PostgreSQL 中的选择查询中将值插入表中?

    • 4 个回答
  • Marko Smith

    如何使用 psql 列出所有数据库和表?

    • 7 个回答
  • Martin Hope
    Jin 连接到 PostgreSQL 服务器:致命:主机没有 pg_hba.conf 条目 2014-12-02 02:54:58 +0800 CST
  • Martin Hope
    Stéphane 如何列出 PostgreSQL 中的所有模式? 2013-04-16 11:19:16 +0800 CST
  • Martin Hope
    Mike Walsh 为什么事务日志不断增长或空间不足? 2012-12-05 18:11:22 +0800 CST
  • Martin Hope
    Stephane Rolland 列出指定表的所有列 2012-08-14 04:44:44 +0800 CST
  • Martin Hope
    haxney MySQL 能否合理地对数十亿行执行查询? 2012-07-03 11:36:13 +0800 CST
  • Martin Hope
    qazwsx 如何监控大型 .sql 文件的导入进度? 2012-05-03 08:54:41 +0800 CST
  • Martin Hope
    markdorison 你如何mysqldump特定的表? 2011-12-17 12:39:37 +0800 CST
  • Martin Hope
    Jonas 如何使用 psql 对 SQL 查询进行计时? 2011-06-04 02:22:54 +0800 CST
  • Martin Hope
    Jonas 如何从 PostgreSQL 中的选择查询中将值插入表中? 2011-05-28 00:33:05 +0800 CST
  • Martin Hope
    Jonas 如何使用 psql 列出所有数据库和表? 2011-02-18 00:45:49 +0800 CST

热门标签

sql-server mysql postgresql sql-server-2014 sql-server-2016 oracle sql-server-2008 database-design query-performance sql-server-2017

Explore

  • 主页
  • 问题
    • 最新
    • 热门
  • 标签
  • 帮助

Footer

AskOverflow.Dev

关于我们

  • 关于我们
  • 联系我们

Legal Stuff

  • Privacy Policy

Language

  • Pt
  • Server
  • Unix

© 2023 AskOverflow.DEV All Rights Reserve