同一UPDATE死锁：执行计划与INCLUDE陷阱

简介：这是一份围绕 SQL Server 死锁诊断的实战文档，面向数据库开发、运维及性能排障人员，重点拆解一个由非聚集索引 include 选项与 varchar(max) 字段引发的“奇怪”死锁案例。压缩包内仅含 1 个 docx 文件，大小约 694KB，内容集中，便于按案例顺序阅读。文档还原了在 SQL Server 2008 上创建含聚集索引和两个非聚集索引的 tt 表、插入万条记录并以 rowlock 循环更新复现死锁的过程，同时对比删除 include(d) 或改用 varchar(200) 后死锁消失的现象；分析部分涵盖 SQL Trace、1222 跟踪开关、SQL Profiler 的 Locks/TSQL 事件筛选，以及借助 sp_readerrorlog 查看 deadlock-list、process-list、waitresource、lockMode 等信息定位等待链路的思路。文档还展示了用 SPID 过滤连接、收集锁事件与 TSQL 事件的具体做法，并从错误日志中的 victim、executionStack 等节点理解死锁牺牲者与执行语句。已有 326 人学习，适合需要理解索引、锁粒度与死锁输出、提升排错能力的读者参考。

1. 两条一模一样的 UPDATE 打成死锁：问题不在锁提示，而在计划

两个会话跑同一条语句，update tt with(rowlock) set d='cd' where a='...'，一模一样，没有交叉访问，没有外键，没有触发器，结果 ERRORLOG 里躺着一张死锁图。很多人第一反应是去加nolock、把隔离级别调低、或者怀疑rowlock提示本身，方向全错。真正决定这两条语句会不会撞车的东西，是执行计划里有几个Index Update算子，以及由此决定的加锁条数和加锁顺序。这个案例适合正在写 T-SQL、做过索引优化、被死锁图折磨过的人看：它把 Deadlock 的因果链缩到一张表、三个索引、一条 UPDATE 上，能完整走一遍从复现到定性的全过程，也顺手说明了一件事——把varchar(max)塞进INCLUDE，代价不只是存储。

2. 把死锁稳定造出来：建表、灌数与三组对照实验

复现门槛很低，一张表、一万行数据、两个查询窗口就够。关键在于三个要素必须同时在场：被更新的列d存在两个非聚集索引里、d是varchar(max)、两条 UPDATE 走的是同一个索引定位路径。少一个，死锁就不出现——这既是复现条件，也是后面分析的对照组。

动手前建议单独建一个测试库，或者至少用一张一次性的表，别在生产库上开while 1=1循环。这个循环每秒能跑出几十次更新，CPU 和日志写入都是实打实的压力。

2.1 建表：一个聚簇索引 + 两个带 INCLUDE 的非聚集索引

-- 测试库中执行：id 是自增主键，默认建在聚簇索引上 create table tt ( id int identity primary key, a char(36), b char(36), d varchar(max) ); go -- 两个非聚集索引，都把 d 放进 INCLUDE -- 等于让 d 的内容在这两个索引里各存一份副本 create index ix_a_bc on tt(a) include(d); create index ix_b_cd on tt(b) include(d);

a、b用char(36)是为了装NEWID()转成字符串后的定长 GUID，长度固定 36，定长类型在索引里的存储更可控。d是varchar(max)，属于 LOB 类型，超过 8000 字节会走行外存储，这一点后面会直接影响执行计划的形状。

那两个INCLUDE(d)是本案例的核心。UPDATE 一旦动了d，SQL Server 就不能只改聚簇索引——两个非聚集索引里的副本同样要改，否则索引和基表数据就不一致了。而每一次“改一个索引”都是一次独立的加锁动作。

2.2 灌数与定位：为什么 10000 行就够

-- 灌一万行，a、b 先用固定值占位，d 用 'ddd' insert into tt select NEWID(), 'bbb', 'ddd'; go 10000

go 10000是 SSMS 里做批量重复执行的常用写法，会把这句insert反复提交一万次，最终表里有一万行。行数不是关键，一万行足以让优化器选择走非聚集索引查找而不是全表扫描，也就够了。

-- 取一行，把它的 a 值抄下来，后面两个连接都用这个值做条件 select id, a from tt where id = 10;

拿到的a是一个随机 GUID 字符串，形如EF211985-EA72-4A40-81DA-0AAB076E7AA3。这个值每次重建表都会变，所以脚本里必须手工替换成你自己查出来的那个值，照抄示例值会一行都更新不到，自然不会死锁——这是新手最常卡住的第一步。

2.3 两个连接同时循环 UPDATE

开两个查询窗口，先把a值替换好，各自记下自己的 SPID，然后同时执行：

-- 会话 1（会话 2 里执行完全相同的这段） select @@spid as my_spid; -- 记下这个号，第三、四章做 Trace 过滤要用 while 1 = 1 begin update tt with(rowlock) set d = 'cd' where a = 'EF211985-EA72-4A40-81DA-0AAB076E7AA3'; end

with(rowlock)只是告诉优化器“尽量在行粒度上申请锁”，它并不承诺“只锁一行”，更不承诺“锁一次就结束”。真正申请了几把锁、按什么顺序申请，仍然由执行计划决定。两条 UPDATE 都命中同一个a值，走的是同一个非聚集索引ix_a_bc，命中同一条记录，锁请求必然重叠。

几秒之内，其中一个连接就会报错：事务(进程 ID xx)与另一个进程被死锁在 锁 资源上，并且已被选作死锁牺牲品。稳定的、可重复的，这很重要——后面要反复做几轮采集，能稳定重现比偶发重现省太多时间。

2.4 三组对照实验，锁定变量

只复现一次说明不了任何问题，得把变量一个一个拿掉。三组实验的差异和现象如下：

测试 2 的脚本：

-- 测试 2：两个非聚集索引都不再 INCLUDE(d) drop index ix_a_bc on tt; drop index ix_b_cd on tt; create index ix_a_bc on tt(a); create index ix_b_cd on tt(b);

注意这里必须真的不写INCLUDE(d)。有版本流传的复现脚本在这一步把建索引语句原样贴回来、仍然带着INCLUDE(d)，照着跑会得到“去掉 INCLUDE 也没用”的错误结论，然后怀疑人生。

测试 3 的脚本：

-- 测试 3：d 从 LOB 类型收窄成有长度上限的 varchar drop index ix_a_bc on tt; drop index ix_b_cd on tt; -- 被索引引用的列不能直接改类型，必须先删索引 alter table tt alter column d varchar(200); create index ix_a_bc on tt(a) include(d); create index ix_b_cd on tt(b) include(d);

alter column之前必须先丢掉索引，否则会报“索引依赖于此列”的错。顺序不能颠倒。

三组跑完，结论已经很硬：死锁不是 UPDATE 语句本身的问题，是索引结构 + 数据类型共同决定的加锁行为问题。行锁提示、隔离级别、执行频率都不是变量。接下来的任务就变成把这个“加锁行为”从黑匣子里拽出来。

3. 抓证据：1222 跟踪标记 + SQL Trace 锁事件的双通道采集

死锁一旦报错，客户端能看到的只有一行牺牲品提示。想知道谁在等谁、等的哪把锁、当时在干什么，必须让 SQL Server 主动把现场留下来。两条通道各管一段：跟踪标记 1222 负责留下“死锁那一刻的快照”，SQL Trace 负责留下“从申请到释放的完整时间线”。两条都要，缺一条就只能猜。

3.1 打开 1222：把死锁图落进 ERRORLOG

-- -1 表示全局范围：对当前和之后新建的连接都生效 dbcc traceon (1222, -1);

那个-1是这套动作里最容易漏的参数。只写dbcc traceon (1222)，跟踪标记只作用于当前这一个连接，一旦把 SSMS 窗口关掉、重开一个，标记就没了，下一个复现出来的死锁照样什么都不会记录。

死锁触发后，把死锁图读回来：

-- 参数依次是：0=当前日志，1=ERRORLOG 类型，第三个是内容匹配 exec sp_readerrorlog 0, 1, N'deadlock';

sp_readerrorlog会把当前 ERRORLOG 里包含deadlock的段落打出来，一段一段往下翻，就能看到完整的deadlock-list。这一段通常很长，包含process-list（进程表）、resource-list（资源表）两大块，读法在第四章展开。

如果要做长期监控，常见做法是把-T1222写进实例的启动参数，重启后一直生效，不用每次手动开。代价是 ERRORLOG 会持续变大，需要有轮转和清理的约定。

3.2 用 @@spid 锁定目标，再让 Profiler 只跟这几个连接

死锁图只给“那一刻”，中间的锁申请与释放过程得靠 SQL Trace 补。而实例上的活动连接可能成百上千，不加过滤的 Trace 会被无关事件淹没，所以第一步是定位目标 SPID。

-- 在每一个参与复现的连接里执行，把结果记下来 select @@spid as my_spid;

假设两个复现连接分别拿到 54 和 60。接下来在 SQL Server Profiler 里新建 Trace，事件和列筛选按下面这张表配：

ObjectID2这个列名很容易被忽略，但它就是死锁图和锁事件里出现的hobtid，是“分区级对象标识”，不是我们熟悉的object_id。没有它，你看到的只是一串无意义的数字。

采集流程：Trace 开始运行 → 两个会话同时启动 UPDATE 循环 → 等到死锁报错 → 停止 Trace → 保存成 trc 文件。整段录下来的一两千条锁事件，就是还原时间线的原始材料。

3.3 把 hobtid 翻译成索引名：一次 sys.indexes 反查

Trace 里出现的72057594065518592这类数字，需要翻译成“哪张表、哪个索引”。做法是用partition_id反查，因为hobtid就是分区的hobt_id：

-- 把 Trace / 死锁图里出现的 hobtid 填进 in 列表 select o.name as table_name, i.name as index_name, i.type_desc, p.partition_id from sys.indexes i join sys.objects o on i.object_id = o.object_id join sys.partitions p on p.index_id = i.index_id and p.object_id = i.object_id where p.partition_id in (72057594065518592, 72057594066108416, 72057594066173952);

执行后就能把三个 hobtid 对上号，得到这样一张映射表：

这一步做完，Trace 里所有ObjectID2都能读成索引名，后面的分析才谈得上“看得懂”。很多人卡在这里就放弃了，其实就是一个 join 的事。

4. 读懂死锁图：process-list 与 resource-list 逐行对照

拿到 1222 输出和 Trace 之后，真正的分析工作是“把两张表对照着读”。process-list说的是每个进程当时在等什么，resource-list说的是每把锁现在归谁。两边一交叉，死锁的闭环就浮出来了。

4.1 process-list：两个进程死在了同一条语句上

1222 输出经过删减，关键字段如下（登录名、主机名之类的环境信息已去掉）：

deadlock-list deadlock victim=process5e27708 process-list process id=process5e27708 spid=60 lockMode=X waitresource=KEY: 8:72057594066108416 (ef8a9edf5a1e) waittime=1750 trancount=2 isolationlevel=read committed (2) executionStack frame procname=adhoc line=2 update tt with(rowlock) set d='cd' where a='EF211985-EA72-4A40-81DA-0AAB076E7AA3' process id=process5e09dc8 spid=54 lockMode=U waitresource=KEY: 8:72057594065518592 (d08358b1108f) waittime=1750 trancount=2 isolationlevel=read committed (2) executionStack frame procname=adhoc line=2 update tt with(rowlock) set d='cd' where a='EF211985-EA72-4A40-81DA-0AAB076E7AA3' resource-list keylock hobtid=72057594066108416 indexname=ix_a_bc mode=U owner id=process5e09dc8 mode=U waiter id=process5e27708 mode=X requestType=wait keylock hobtid=72057594065518592 indexname=PK__tt__3213E83F10E07F16 mode=X owner id=process5e27708 mode=X waiter id=process5e09dc8 mode=U requestType=wait

几个字段值得逐个抠：

waitresource里的KEY: 8:72057594066108416 (ef8a9edf5a1e)，8是 dbid，中间那串是 hobtid，括号里是具体的键哈希值。两个进程等的都是键锁，说明冲突发生在索引的键上，不是页锁也不是表锁。

lockMode是当前进程正在等待的锁模式，不是它已经持有的。SPID 60 在等 X，SPID 54 在等 U，这个细节决定了闭环的方向。

executionStack把出事的语句原样打了出来，两条一模一样。这一点很关键——它排除了“两条不同语句互相踩”这种常见怀疑，把问题锁死在“同一条语句的两个并发实例”上。

isolationlevel=read committed (2)说明不是隔离级别太高惹的祸，默认的读已提交一样会死。

4.2 resource-list：谁拿着什么、谁在等什么

resource-list才是闭环的直接证据。把它拆成两句话：

• 资源一：ix_a_bc上有一把 U 键锁，持有者是 SPID 54，等待者是 SPID 60，想升级成 X。
• 资源二：聚簇索引PK__tt__3213E83F10E07F16上有一把 X 键锁，持有者是 SPID 60，等待者是 SPID 54，想拿 U。

于是闭环成立：54 拿着ix_a_bc的 U 不放，等 60 释放聚簇索引的 X；60 拿着聚簇索引的 X 不放，等 54 释放ix_a_bc的 U。两边都不肯先松手，SQL Server 挑一个当牺牲品回滚。

但这里有个用 1222 解释不了的问题：为什么恰好是这个时候撞上？如果 UPDATE 是“一次性把三把锁全申请完”，两边要么都拿不到、要么一个先拿完，不该出现这种交错。答案藏在 Trace 记录的申请释放时间线里。

4.3 锁的申请与释放顺序：成功 UPDATE 的标准动作

把 Trace 里Lock:Acquired和Lock:Released按时间排序，提取索引名和锁模式，测试 1（会死锁的那组）的顺序是这样的：

看到第 4 行和第 5 行就明白了。UPDATE 是分两步执行的：第一步通过ix_a_bc定位记录、在聚簇索引上完成更新，顺手把ix_a_bc上的 U 锁释放掉；第二步回头去改两个非聚集索引里的副本，这时候需要在ix_a_bc和ix_b_cd上分别申请 X 锁。

死锁就发生在这个“松手再握手”的空档：连接 2 刚把ix_a_bc的 U 锁放掉、正走向第二步要 X 锁；连接 1 恰好挤进这个空档拿到 U，随后要在聚簇索引上申请 U，而聚簇索引的 X 锁还压在连接 2 手上。两边互相卡死。

对照组测试 2 和测试 3 的锁顺序，只有前四行：

只剩一步，没有“第二步再去申请 X”的动作，自然也不会出现 U 锁提前释放留下的窗口。死锁的充分条件不是“锁多”，而是“同一行数据上有两次独立的加锁机会”。这句话是整轮分析里最值钱的一条经验。

5. 避坑与排查：五个把分析带偏的现场习惯

整个流程走下来，真正耗时间的不是 SQL 本身，而是几个看起来不起眼的操作习惯。下面几条都是这个案例里实际踩到的，按“现象 → 原因 → 解决”记一遍。

坑一：复现脚本照抄，测试 2 跑出和测试 1 一样的死锁

现象：把两个索引重建一遍，想着“这回没 INCLUDE 了”，结果跑起来照样死锁，误以为 INCLUDE 跟死锁无关。

原因：复现文章里的建索引语句在这一步仍然带着INCLUDE(d)，直接复制粘贴就等于什么都没改，实际执行的还是测试 1 的结构。

解决：改结构之后，一定用sp_helpindex tt或者查sys.indexes+sys.index_columns确认is_included_column的真实状态，别相信脚本看起来像什么。改完先验证结构，再跑压力循环。

坑二：只开了dbcc traceon(1222)，没加-1

现象：复现了好几轮死锁，sp_readerrorlog里干干净净，一条deadlock-list都没有。

原因：跟踪标记默认只在当前会话生效，关掉查询窗口就失效了。复现连接和采集连接往往不是同一个。

解决：统一用dbcc traceon(1222, -1)开全局，开完用一个新连接执行dbcc tracestatus确认这个标记确实处于开启状态，再去复现。

坑三：Profiler 里没勾 Show all columns，找不到 ObjectID2

现象：Trace 录下来了，但锁事件里只有一堆ObjectID，翻遍列名也找不到那个能对上hobtid的字段。

原因：Profiler 默认只显示常用列，ObjectID2属于被折叠起来的那个。

解决：新建 Trace 时先勾Show all events和Show all columns，选完事件之后把Mode、ObjectID、ObjectID2、Type拖到列首。这一步一次做对，后面每次分析都省事。

坑四：把死锁图当成最终答案

现象：看到一张漂亮的红蓝箭头图，立刻下结论“就是锁升级/就是隔离级别问题”，然后去改隔离级别，改完发现死锁概率没什么变化。

原因：死锁图只回答“谁在等谁”，不回答“为什么会在这个时间点等”。加锁顺序上的那个时间差，只有把Lock:Acquired/Lock:Released按时间排开才看得见。

解决：死锁图定性，Trace 定时序，两步都做完再动手改。少一步，改出来的方案大概率是猜的。

坑五：在生产库上直接跑while 1=1复现

现象：测试脚本在手边顺手，连着业务库就跑了，几秒钟内 CPU 打满，业务查询开始排队。

原因：无退出条件的循环 UPDATE 每秒能提交几十次事务，日志写入和锁竞争都是实打实的负载。

解决：复现只在隔离环境做；实在要在共用实例上验证，给循环加计数器上限（比如while @i < 200），并在事务外留好kill的退路。测试完记得把表删掉，别把 10000 行的垃圾表留在库里。

6. 摊开执行计划：Index Update 的个数才是那把钥匙

锁顺序的差异不是凭空来的，它是执行计划的直接后果。要看清这一点，最省事的办法是在 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;

SET STATISTICS PROFILE会把每个算子的StmtText、Rows、Executes连同实际执行情况一起返回。新版环境里更常看到的是SET STATISTICS XML ON，拿到的是可点击的图形计划，思路完全一样：看Update类算子有几个、每个算子更新了哪些对象。

6.1 三个计划，差在算子数量

把三组实验的执行计划拉出来对比，差异非常直观：

测试 1 那三个Index Update算子，就是第三章 Trace 里那两轮加锁的物理来源：每个Index Update对应一次独立的锁申请，算子之间可以交错，交错就有窗口。

测试 2 只有一个算子——因为d不在非聚集索引里，改d不涉及那两份副本，只改聚簇索引就完事。锁申请只有一轮，没有第二轮，也就没有“U 锁提前释放”的机会。

测试 3 最有意思：更新范围其实和测试 1 完全一样（三个索引都要改），但优化器能把它折叠进一个算子，三个对象在一次操作里同步更新完。锁的申请和释放被压成一气呵成的动作，中间不留缝。

6.2 为什么varchar(max)会挡住宿主计划折叠

varchar(max)是 LOB 类型，数据可能不在行内，需要额外的 LOB 读取和写入路径。我一般把它理解成：涉及 LOB 列的更新，优化器更倾向于拆成更保守的执行方式，因为一次性同步多个索引上的 LOB 副本在实现上更复杂。补一个旁证——把d从varchar(max)改成varchar(8000)（仍在行内上限之内），这个死锁同样不出现。变的是类型的存储形态，不是数据本身。

所以对这类问题的处理顺序，我习惯这样排：

1. 先问“这个列真的需要进INCLUDE吗”。如果它只是偶尔被查询带出来，覆盖索引带来的读性能收益，未必抵得上每次更新多改一个索引的写代价。
2. 如果确实要进INCLUDE，就别用varchar(max)、nvarchar(max)、varbinary(max)这类 LOB 类型。给一个合理的长度上限，让数据留在行内。
3. 索引数量也要克制。表上每多一个包含被更新列的索引，UPDATE 的加锁条数就多一份，死锁的组合数也跟着上去。
4. 短期止血可以用HOLDLOCK或者把两条更新串行化，但那是压住症状，不是改结构。根子还在计划上。

从那以后我每次看到“同一条语句自己跟自己死锁”这种报障，都不先去翻死锁图，而是先set statistics profile on把计划打出来，数一遍Index Update有几个。两个以上，基本就能锁定是加锁窗口的问题；只有一个，再回头去看隔离级别和访问路径。这个顺序帮我省掉过不少来回。希望帮到你。

本文还有配套的精品资源，点击获取