同一条UPDATE为何死锁？锁轨迹揭示变长字段与Include索引的陷阱

简介一份聚焦 SQL Server 死锁Deadlock问题的技术分析文档适合数据库管理员、开发人员以及需要排查事务并发异常的进阶学习者。文档从一次奇怪死锁现象入手深入讲解聚集索引、非聚集索引与行锁机制如何共同诱发死锁并结合可复现的测试场景完整记录了从问题复现、信息收集、锁等待分析到最终定位原因的过程。资源包仅含1个docx格式文档压缩包大小694KB内容集中、便于直接阅读与归档。目前已有326人浏览学习。文档中的示例表结构、更新语句以及死锁图谱解读能帮助读者理解 non-clustered index 的 include 选项和 varchar(max) 字段类型对锁行为的影响同时通过 SQL Trace、DBCC TRACEON(1222) 与 sp_readerrorlog 的配合使用读者可掌握一套系统化分析 SQL Server 死锁的典型方法对日常数据库性能调优与故障排查具有直接参考价值。1. 死锁现场两条相同 UPDATE 怎么会锁成环死锁Deadlock在 SQL Server 里有一种让人上头的形态两条语句一模一样更新的是同一条记录分开跑谁都没问题两个会话一交错就卡死而只要把索引里某个include列去掉或者把字段类型从varchar(max)改成varchar(200)死锁又像没发生过一样消失。这个案例之所以值得拆是因为它把 SQL Server 死锁的真相摊开在面前锁不是跟语句走而是跟执行计划走同一条 UPDATE 可以在同一个索引上先申请 U 锁再申请 X 锁两个会话各自卡在不同的申请阶段死锁环就闭环了。搞懂它的分析路径等于拿到了一套“锁轨迹 执行计划 索引结构”的死锁排查方法论。2. 抓现场靠 1222 和 SQL Trace 还原这只看不见的手2.1 复现环境一张表、两个 include 索引、一条 UPDATE问题可以从一张很简单的表复现出来。表上有聚集主键两个非聚集索引并且都include了一个varchar(max)字段CREATE TABLE tt ( id INT IDENTITY PRIMARY KEY, a CHAR(36), b CHAR(36), d VARCHAR(MAX) ); CREATE INDEX ix_a_bc ON tt(a) INCLUDE (d); CREATE INDEX ix_b_cd ON tt(b) INCLUDE (d);注意ix_a_bc这个名字有迷惑性它建立在a上INCLUDE (d)只是把d打包进索引叶行ix_b_cd类似建立在b上。接着插入一万行a用NEWID()生成随机 GUIDb和d固定INSERT INTO tt (a, b, d) SELECT NEWID(), bbb, ddd GO 10000;然后查询id 10那行的a值SELECT * FROM tt WHERE id 10;假设得到一个形如EF211985-EA72-4A40-81DA-0AAB076E7AA3的 GUID。接下来两个会话同时跑下面这个循环WHILE 1 1 UPDATE tt WITH (ROWLOCK) SET d cd WHERE a EF211985-EA72-4A40-81DA-0AAB076E7AA3;这组操作几乎每次都能稳定触发死锁。WITH (ROWLOCK)的作用是把锁粒度压到行/键级别让冲突集中在索引 KEY 锁上循环语句制造连续的竞争窗口死锁的复现就变得确定。为了对比还需要另外两个变体测试表结构是否死锁基线d varchar(max)两个 NCIINCLUDE(d)必现测试 2去掉两个 NCI 的INCLUDE(d)不死锁测试 3d varchar(200)仍保留INCLUDE(d)不死锁这三个测试的结果合在一起已经能排除“并发量高”“锁粒度太小”这类表面解释。真正的问题藏在执行计划和索引维护里。2.2 开启 1222 并固定 SPID分析死锁官方路径是两条开 trace flag 1222 把死锁图写进错误日志再用 SQL Profiler 抓锁申请与释放的时序。对于能稳定复现的死锁两个都开最划算。先打开 1222DBCC TRACEON(1222, -1);-1表示对所有会话生效。然后在两个执行 UPDATE 的会话里分别执行SELECT SPID;假设得到 54 和 60。接着在 Profiler 里新建 Trace勾选 Show all events 和 Show all columns在 Locks 事件组里选上Lock:Acquired、Lock:Released、Lock:Deadlock等事件在 TSQL 事件组里选上SQL:BatchCompleted、SQL:StmtCompleted在 Column Filters 中按 SPID 过滤只保留 54、60、6 和 20。有人会问为什么放上 6 和 20那是后台进程避免漏掉与死锁检测相关的上下文。两个会话跑出死锁后用下面的指令读错误日志EXEC sp_readerrorlog;日志里能看到最近一次死锁的完整记录关键结构如下deadlock-list deadlock victimprocess5e27708 process-list process idprocess5e27708 ... spid60 ... lockModeX executionStack UPDATE [tt] WITH(rowlock) SET [d]1 WHERE [a]2 process idprocess5e09dc8 ... spid54 ... lockModeU executionStack UPDATE [tt] WITH(rowlock) SET [d]1 WHERE [a]2 resource-list keylock hobtid72057594066108416 ... indexnameix_a_bc modeU owner-list: process5e09dc8 modeU waiter-list: process5e27708 modeX requestTypewait keylock hobtid72057594065518592 ... indexnamePK__tt__... modeX owner-list: process5e27708 modeX waiter-list: process5e09dc8 modeU requestTypewait这段输出就是死锁图的骨架。它告诉我们SPID 54 拿着ix_a_bc上的 U 锁正在等待聚集索引PK__tt__...上的 U 锁SPID 60 拿着聚集索引上的 X 锁正在等待ix_a_bc上的 X 锁。两个等待方向完全相反形成标准的资源环。注意hobtid的含义这是索引/堆在某个分区里的物理对象 ID。KEY: 8:72057594066108416中的8是数据库 ID后面那串是指定索引行的定位信息。这里的 KEY 锁表示锁在索引叶行上和可序列化隔离级别里的 range lock 不是一个东西。通过sys.indexes可以把hobtid反查成索引名SELECT o.name AS table_name, i.name AS index_name, i.type FROM sys.indexes i JOIN sys.objects o ON i.object_id o.object_id JOIN sys.partitions p ON p.object_id i.object_id AND p.index_id i.index_id WHERE p.partition_id 72057594065518592;由此得到三段映射72057594065518592→ 聚集索引PK__tt__...72057594066108416→ix_a_bc72057594066173952→ix_b_cd也就是说锁不在整张表上而在三个索引的特定 KEY 行上。多索引表的死锁经常发生在“索引维护”而不是“用户数据”上只是 KEY 锁的名字让初学者下意识以为是键范围冲突。2.3 锁环成立的前提U 与 X 互不兼容这里需要把锁模式的关系摆出来。SQL Server 的 UUpdate锁是 S 与 X 之间的折中它表达“我准备更新这一行但在真正改之前先以读的模式占用它”。关键兼容性如下已持锁请求 U请求 XU兼容不兼容X不兼容不兼容因为 U 与 U 兼容两个会话可以同时在“不同索引”或“不同阶段”持有 U 锁但一旦有一方想升级成 X另一方又需要 U等待就不可避免。本例中 SPID 54 持有ix_a_bc的 U想拿聚集索引的 USPID 60 持有聚集索引的 X想拿ix_a_bc的 X于是两边互相不兼容锁环就闭环了。到这里1222 只回答了“谁等谁”还没回答“为什么同一条 UPDATE 会同时出现 U 和 X”。要回答这个问题必须把锁轨迹摊开。3. 锁轨迹拆解一条 UPDATE 为什么在同一个索引上加两次锁3.1 把 Lock:Acquired / Lock:Released 变成时间线Profiler 输出之所以难读是因为字段太多。分析锁轨迹时把Mode、ObjectID、ObjectID2、Type拖到最前面其余字段留作辅助。ObjectID2正是 1222 输出里的hobtid把它和 2.2 里的映射表对应起来就能得到每个锁实际落在哪个索引上。测试 1有死锁成功完成一次 UPDATE 时的锁轨迹按时间顺序如下顺序资源锁模式动作1ix_a_bcUAcquired2PK__tt__...UAcquired3PK__tt__...XAcquiredU 升级为 X4ix_a_bcUReleased5ix_a_bcXAcquired6ix_b_cdXAcquired7ix_b_cdXReleased8ix_a_bcXReleased9PK__tt__...XReleased这条轨迹非常有信息量。前半段是“第一步”SQL Server 通过ix_a_bc的 U 锁定位聚集索引键随后在聚集索引叶行上申请 U 并升级为 X完成对聚集索引行的更新。到第 4 步ix_a_bc上的 U 锁被释放注意聚集索引上的 X 锁一直没释放。后面是“第二步”对ix_a_bc和ix_b_cd各申请一次 X 锁最后统一释放。为什么要更新这两个非聚集索引因为它们的叶行都include了d字段而d从ddd变成了cd索引里的冗余列必须同步改写。重点在第 1 步和第 5 步同一个ix_a_bc在语句前半段持 U后半段持 X中间被释放了一次。一条 UPDATE 把自己劈成了两次锁操作这是后面一切死锁的源头。3.2 两个会话交错的位置假设 SPID 54 和 SPID 60 同时运行同一条 UPDATE。死锁发生的完整时序可以这样描述SPID 60 率先完成第一步持有聚集索引上的 X并释放了ix_a_bc上的 U。此时它准备进入第二步需要申请ix_a_bc上的 X。SPID 54 在 SPID 60 释放ix_a_bc的 U 锁之后进来先拿到了ix_a_bc上的 U准备定位聚集键。SPID 54 接着向聚集索引申请 U 锁准备更新聚集行但聚集索引的 X 锁在 SPID 60 手里于是 SPID 54 被阻塞。SPID 60 在第二步申请ix_a_bc的 X 锁时发现这把锁被 SPID 54 的 U 锁占着于是 SPID 60 也被阻塞。环就出来了54 等 6060 等 54。死锁检测器随即介入选择牺牲事务回滚更轻的那个会话。这个交错点非常精确SPID 60 已经“离开”了ix_a_bc的定位阶段但还没有“进入”维护阶段SPID 54 恰好插入到这个空档里。如果两个会话完全同步进入同一阶段它们会先互相看到同一把 U 锁反而不会死锁。死锁往往发生在“一个刚走一个刚来”的临界窗口这也是它看起来“很奇怪”的原因。3.3 为什么同样的 UPDATE一个拿 U 一个拿 X这是整个案例最反直觉的地方。两条 UPDATE 文本完全一样但 SQL Server 的锁不是整条语句统一加而是随执行计划里算子的执行逐步申请、逐步释放。SPID 60 已经完成了聚集索引更新处在“维护非聚集索引”阶段SPID 54 还在“用非聚集索引定位”阶段。两个会话处在同一个算子的不同生命周期里自然拿到不同索引、不同模式的锁。关键现象是ix_a_bc在语句内被“先读后写”这种对同一资源的重复访问使得一个会话在不同时刻表现出两种锁身份。很多时候大家以为相同语句死锁必然是行数太多或并发太高其实只需要两个会话交错在同一个语句的不同阶段就能稳定复现。SQL Server 里这种问题不算罕见但每次出现都很有迷惑性因为表面证据和直觉完全对不上。4. 根因在计划里三个 Index Update 与一个问题4.1 用 SET STATISTICS PROFILE 看计划要解释为什么锁轨迹会裂成两段必须回到执行计划。在 UPDATE 语句前打开文本计划输出SET STATISTICS PROFILE ON; GO UPDATE tt WITH (ROWLOCK) SET d cd WHERE a EF211985-EA72-4A40-81DA-0AAB076E7AA3; GO SET STATISTICS PROFILE OFF;三个测试给出的计划差异非常明显测试计划特征Index Update 算子对 ix_a_bc 的锁操作基线三个 Index Update 分阶段执行3U 申请 → 释放 → X 申请去掉 include只有一个 Clustered Index Update1只有 Ud 改 varchar(200)一个 Update 包含三个对象1合并只有 U基线计划里有三个Index Update算子分别对应聚集索引、ix_a_bc、ix_b_cd。这才解释了锁轨迹里为什么会有三次 X 申请。第二个计划里只有一个Clustered Index Update因为d没有被非聚集索引 includeSQL Server 只需要更新聚集行本身。第三个计划最值得玩味它只有一个Update算子但StmtText里同时包含了三个对象。也就是说SQL Server 把“更新聚集索引 维护两个非聚集索引”压缩成了一步锁轨迹上对ix_a_bc只出现 U不再出现第二步的 X。这一步合并直接消灭了死锁环的一边。4.2 varchar(max) 为什么逼着 SQL Server 拆开维护核心原因和d的存储类型有关。varchar(max)虽然在小值时可能被存在行内但它属于 LOB 类型更新时的物理路径比普通变长字段重得多可能需要写行外存储、调整 LOB 指针甚至触发行迁移。当两个非聚集索引都include(d)时索引叶行里保存了d的值或其定位信息d一变SQL Server 必须同步维护每个包含它的非聚集索引。常见实现思路是对于普通varchar更新可以在原地完成优化器可以把“更新聚集索引 维护非聚集索引”合并成一个 Update 算子但对于varchar(max)这类字段行内指针可能变化优化器不敢假设原地更新于是选择退化成三个独立的 Index Update 算子先更新聚集索引再分别更新两个非聚集索引。这不是 bug而是成本模型基于存储类型的保守选择。这里要强调一个容易误读的点测试 2 证明“非聚集索引不 include d”时死锁消失不是因为索引少了而是因为更新不再触碰非聚集索引的叶行。测试 3 证明“字段类型改成 varchar(200)”时死锁也消失因为类型变普通后优化器选择了合并计划。两条路殊途同归最终都让ix_a_bc从“先 U 后 X”变成“只有 U”。4.3 varchar(200) 不是装傻而是让优化器转身d改成varchar(200)后这个列变成普通的行内变长列更新d不再涉及 LOB 指针迁移优化器重新选择了一个“一次扫描更新所有索引”的计划。在锁轨迹上只剩下ix_a_bc的 U 申请、聚集索引的 U/X 申请、ix_a_bc的 U 释放和聚集索引的 X 释放。整个 UPDATE 对非聚集索引只读不写写入全部收敛在聚集索引上。这也解释了为什么 include 不是免费午餐它让覆盖查询免回表却让每次写操作承担额外的索引维护锁和 IO。索引维护锁的顺序一旦与定位锁交叠死锁就会在不经意间找上门。5. 死锁分析避坑指南这些坑我都踩过5.1 只凭死锁资源图就下手改索引现象看到两个 KEY 锁一个 U 一个 X第一反应是“两个索引键冲突”于是删索引或改索引键顺序。原因KEY 锁只是索引行锁的载体真正的问题在于“同一个索引先用于定位、后用于维护”的两阶段执行。1222 输出只给了最终快照没有给锁的申请顺序单看资源图很容易把因果搞反。解决先把 1222 输出和 Lock:Acquired / Lock:Released 轨迹对齐确认锁环发生在哪个索引的哪个阶段再决定动索引。凡是没看过锁轨迹就改索引的判断都先缓一缓。5.2 死锁发生后用动态管理视图考古现象死锁已经发生跑sys.dm_tran_locks却什么都查不到看起来像误报。原因死锁检测介入后牺牲会话的锁会被回滚释放动态管理视图只反映当前瞬态抓不到案发时的锁。尤其是偶发死锁等你接到报警再连上去现场早就没了。解决把 1222 trace 常开事后读sp_readerrorlog对需要精确时序的场景用 Profiler 或扩展事件抓Lock:Deadlock、Lock:Acquired、Lock:Released。先有现场再谈分析。5.3 复现时没固定执行计划现象测试时加ROWLOCK死锁必现去掉就永远不死或者换个数据量、换一台机器就复现不了。原因SQL Server 的优化器会随索引、统计信息、参数值甚至表大小选择不同计划加锁顺序和锁数量全都会变。ROWLOCK在这里不是死锁的直接原因而是把锁粒度固定下来、让问题可复现的手段。解决复现前先看执行计划确认两个会话走的是同一个计划并把表结构、数据量、索引和锁提示都固定住。如果计划都不一样讨论锁环没有意义。5.4 用 NOLOCK 或删 include 来“解决”问题现象有人给 UPDATE 加WITH (NOLOCK)或者直接删掉 include 列死锁消失但业务查询开始回表性能明显下降。原因NOLOCK只影响读锁UPSERT 和写入仍然要 X 锁删 include 会让原本的覆盖查询失去免回表优势等于用读性能换写性能。解决先量化该索引对读查询的实际收益。如果覆盖查询高频且重要可以考虑把d改成合适长度的普通 varchar如果索引本来就很鸡肋才值得删。不要让“死锁没了”成为唯一标准。5.5 忽略 HOLDLOCK 这类锁提示的副作用现象给 UPDATE 加WITH (HOLDLOCK)后死锁消失了但并发一高阻塞链变长其他查询开始大面积超时。原因HOLDLOCK会把定位阶段持有的 U 锁保持到事务结束实际上是把两个会话的并发执行改成了串行执行。它绕开了死锁但没有消除锁竞争只是转移了等待。解决HOLDLOCK适合低并发、高优先级的关键更新高并发场景下调整索引结构或字段类型通常比加锁提示更划算。死锁分析的目标不是让锁消失而是让锁的数量和持有时间回到合理区间。6. 这个案例的设计取舍include 列不是白送的把三个测试放到一起看核心矛盾很清楚INCLUDE(d)给读查询省了一次书签查找代价是让 UPDATE 的写路径多出两个索引维护算子并引入了同一索引上“先 U 后 X”的两阶段锁。死锁只是这个代价的极端表现形式。实际设计时要权衡几个方案方案对读查询对更新/锁适用场景保持INCLUDE(d)原样免回表锁多、风险高读多写少且d很少更新去掉INCLUDE(d)查询可能回表锁少、写路径短写频繁覆盖查询要求不高d改成varchar(200)仍免回表锁少、写路径短d长度可控且业务允许用HOLDLOCK或计划指南不变锁被拉长代码不能动追求稳定优先我看到这个案例时最深的感受是SQL Server 的优化器是成本驱动的任何结构改动都会影响加锁计划索引设计的收益模型如果只算读不算写迟早会在 DML 路径上还债。死锁分析走到最后不是比谁记的锁矩阵更熟而是比谁能更快地从锁轨迹反推出执行计划里的Index Update算子长什么样。从那以后我每次设计索引都会强制把这张表的 UPDATE/DELETE 执行计划翻出来数一数里面有几个Index Update算子超过两个就会问自己这个 include 列到底值不值得。希望帮到你。本文还有配套的精品资源点击获取