简介：围绕SQL Server中一例表现特殊、排查过程颇具代表性的死锁案例，这份文档面向数据库开发与运维人员，尤其是需要定位锁等待、索引与并发更新问题的技术人员。内容从可稳定重现的实验入手，给出含聚集索引与两个非聚集索引的建表脚本、批量插入及循环update场景，并对比去掉include(d)或把d字段由varchar(max)改为varchar(200)后死锁消失的现象，帮助读者理解索引包含列、行锁与执行计划之间的关联。分析部分结合1222跟踪开关、SQL Trace和SQL Profiler按SPID过滤Locks与TSQL事件，再通过sp_readerrorlog读取死锁图，梳理从现象复现到根因判断的完整思路。资源包共1个docx文件，约694KB，便于直接查阅与归档。已有326人学习，适合希望积累SQL Server死锁诊断经验、对照案例练习排错方法的读者。

1. 两条一模一样的 UPDATE 为什么会互相锁死

同一个表、同一条 where 条件、同一句update ... with(rowlock)，两个连接各跑一遍，SQL Server 直接把其中一个选为 Deadlock victim 杀掉。更玄学的是：把 non-clustered index 上的include(d)去掉，死锁消失；把 d 从varchar(max)改成varchar(200)，死锁也消失。三个变量里只要动一个，问题就没了——这种“三选一就好”的现象最容易被归成玄学，然后靠加holdlock蒙混过去。

真正的原因藏在一条 UPDATE 到底被拆成几步执行这个层面上。rowlock只决定锁落在行上还是页上，决定不了加锁的顺序；而死锁从来是顺序问题。把这个顺序搞明白，你就能从“事后看死锁图”走到“事前看执行计划就能预判它会不会死锁”。

2. 从 1222 输出到锁时序：一次 UPDATE 到底加了几把锁

死锁的直接原因写在 1222 里，但 1222 只告诉你“谁在等谁”，不告诉你“为什么会走到这一步”。完整链条需要三样东西拼起来：死锁图、锁的申请释放轨迹、执行计划。少任何一样，结论都是猜的。

2.1 读 deadlock-list：U 锁与 X 锁的环形等待

先按惯例打开 1222，让每次死锁都把完整过程写进 ERRORLOG。这个开关对全局生效，事后不用再抓现场：

-- 1222：死锁发生时输出完整的 deadlock-list（比 1204 详细） DBCC TRACEON(1222, -1); GO -- 出问题后读 ERRORLOG，里面会多出一段 XML EXEC sp_readerrorlog; GO

拿到的那段 XML 化简后大致长这样（主机名、登录名这类信息贴出来之前记得删掉）：

<deadlock-list> <deadlock victim="process5e27708"> <process-list> <process id="process5e27708" spid="60" lockMode="X" waitresource="KEY: 8:72057594066108416 (ef8a9edf5a1e)"> <executionStack> <frame procname="adhoc" line="2"> update tt with(rowlock) set d='cd' where a='EF21...7AA3' </frame> </executionStack> </process> <process id="process5e09dc8" spid="54" lockMode="U" waitresource="KEY: 8:72057594065518592 (d08358b1108f)"> </process> </process-list> <resource-list> <keylock hobtid="72057594066108416" indexname="ix_a_bc" mode="U"> <owner id="process5e09dc8" mode="U"/> <waiter id="process5e27708" mode="X"/> </keylock> <keylock hobtid="72057594065518592" indexname="PK__tt__3213E83F" mode="X"> <owner id="process5e27708" mode="X"/> <waiter id="process5e09dc8" mode="U"/> </keylock> </resource-list> </deadlock> </deadlock-list>

waitresource的格式是KEY: 数据库ID:分区ID (键值哈希)，中间那个大数字就是hobtid（heap or B-tree ID），本质上是sys.partitions.partition_id。真正要读的是resource-list：它是把死锁环拆成了两条边。

• 边一：spid 54 在ix_a_bc上持有 U 锁，spid 60 想在同一个资源上要 X 锁，排队中。
• 边二：spid 60 在聚集索引上持有 X 锁，spid 54 想在聚集索引上要 U 锁，排队中。

两条边首尾相接，环成立，victim选了回滚代价小的那个（logused=0，几乎没写过日志）。到这一步只能得出“U 锁和 X 锁交叉等待”这个层面的结论，再往下 1222 就说不出来了：为什么两个连接会以相反的顺序碰同一批资源？

2.2 用 SQL Trace 还原加锁顺序：hobtid 怎么对上索引名

第二步要拿到锁的时间顺序，只有 Trace（或扩展事件）能给。做法是先在两个连接里确认自己的 SPID，然后用 SPID 过滤，只抓这两个会话的锁事件：

-- 在两个跑 update 循环的连接里分别执行，记下两个 SPID SELECT @@spid; -- 假设得到 54 和 60 -- 顺便打开 1222，双保险 DBCC TRACEON(1222, -1); GO

Profiler 侧的关键动作有三个，缺一个都会导致后面读不懂数据：在 Events Selection 里勾上Show all events和Show all columns，选 Locks 分类下的Lock:Acquired、Lock:Released、Lock:Deadlock，再选 TSQL 分类下的SQL:BatchStarting、SQL:StmtStarting；然后在 Column Filters 里把 SPID 限定成 54 和 60，避免被系统会话的噪声淹掉。

Lock:Acquired默认列里最没用的那几列恰恰最关键：Mode、ObjectID、ObjectID2、Type。把它们拖到前面来再排序，Mode是数字编码的，记住三个就够读数了：

ObjectID2就是死锁图里的hobtid，光看数字不知道对应哪个索引，得反查：

-- 把 hobtid 翻译成 表名 / 索引名 SELECT o.name AS table_name, i.name AS index_name, i.type_desc 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 = 72057594065518592; -- 换成 trace 里的 ObjectID2

跑完对照，三个 ID 分别落在聚集索引和两个非聚集索引上。把两个连接的事件按时间轴并排铺开，一次正常 update 的加锁序列就浮出来了：

这张表透露的信息比死锁图多得多：UPDATE 分两段执行。第一段用ix_a_bc定位到目标行（where 条件是 a，走的就是这个索引），在索引上拿 U 锁，在聚集索引上拿 U 再升 X，然后把 ix_a_bc 上的 U 锁释放掉。第二段回头去改两个非聚集索引，因为它们的 include 里都有 d，d 变了索引页就必须重写，于是要重新申请ix_a_bc和ix_b_cd的 X 锁。

死锁正是卡在第 5 步：连接2 已经走完第一段、释放了ix_a_bc的 U 锁，正要回头申请 X；这个空档被连接1 钻进来拿走了 U 锁。连接2 的 X 撞上连接1 的 U，连接1 的 U 又撞上连接2 在聚集索引上还没释放的 X。释放得太早，反而制造了死锁窗口——这是整个案例里最反直觉的一点，也是holdlock这类“一把梭”方案能歪打正着的原因。

提示：U 锁之间是兼容的，多个会话可以同时持有同一个 key 的 U 锁；但 U 与 X 不兼容，而且同一资源上只能有一个 U 升格成 X。U 锁存在的意义就是避免“两个会话都拿 S 锁、然后都想升 X”的转换死锁，结果在这个场景里被“申请—释放—再申请”的节奏绕过去了。

2.3 执行计划里数 Index Update 算子：三把 X 锁的出处

锁轨迹解释了“怎么锁的”，还差“为什么是两段”。答案在执行计划里，用set statistics profile on拿文本输出最省事：

SET STATISTICS PROFILE ON; GO UPDATE tt WITH(ROWLOCK) SET d = 'cd' WHERE a = 'EF211985-EA72-4A40-81DA-0AAB076E7AA3'; GO SET STATISTICS PROFILE OFF;

三个测试的执行计划形态差别非常直接：

• 测试1（d 为varchar(max)、被 include）：出现三个 Index Update算子，分别对应聚集索引和两个非聚集索引。三个算子就是三段执行，每段各自申请、各自释放，段与段之间有窗口。
• 测试2（去掉 include）：只有一个 Index Update。d 不在任何非聚集索引里，改 d 不影响索引内容，SQL Server 只改聚集索引就完事，没有第二段。
• 测试3（d 为varchar(200)、仍被 include）：仍然只有一次 update，但这一次更新同时覆盖了三个对象。三个索引被塞进同一个算子，锁在一次连续动作里拿齐，中间不松手。

所以死锁概率的真正自变量不是“有没有 rowlock”，也不是“有几个索引”，而是索引更新被拆成了几个算子、算子之间有没有把锁放掉。rowlock只是把锁的粒度压到行级，让冲突更容易精确发生；如果锁粒度是页级，两个连接很可能连第一段都进不去，反而“看起来没问题”——问题被掩盖了而已。

3. varchar(max) 如何把一步更新拆成两步：LOB 存储与宽窄计划

到这里还剩最后一个问题没回答：为什么varchar(max)和varchar(200)会产生两种完全不同形态的执行计划？这两个类型在用户眼里都是变长字符串，差别只是长度上限，但在存储引擎和优化器眼里是两类东西。

3.1 MAX 类型与 include 列：为什么两个条件必须同时出现

先看 d 被谁“持有”。表 tt 上有三个索引：聚集索引在 id 上，ix_a_bc在 a 上并 include d，ix_b_cd在 b 上并 include d。d 同时存在于三个物理结构里：表本身（聚集索引的叶子页）一份，两个非聚集索引的叶子页各一份。

这就决定了“改 d”的代价：不是改一行，是改三处。测试2 之所以没有死锁，不是因为 d 的类型变了，而是因为 d 从两个非聚集索引里被请出去了，写放大从 3 降到 1。反过来说，只有“d 被 include 进非聚集索引”这个条件成立，才可能出现“第二段回头改索引”的动作；只有“d 是 MAX 类型”这个条件成立，第二段才会被拆成独立的算子。两个条件缺一不可，这就是为什么动其中任何一个都能让死锁消失。

很多人对 include 的理解停留在读侧：“where 里没用到 d，但 select 要 d，include 进去就不用回表了。”这只说对了一半。include 在写侧的代价是：被 include 的列每变一次，索引就要重写一次，锁也要多拿一次。读侧的收益和写侧的代价，天平往哪边倒取决于列的更新频率，而不取决于它有多大。

3.2 窄计划的 Split-Sort-Collapse 为什么带不动 LOB 列

SQL Server 更新多索引时有两种计划形态，业内通常叫窄计划和宽计划。

窄计划是把“要改哪些行、每个索引要改成什么样”先算出来，然后 Split（把一行拆成每个索引一份）、Sort（按索引键排序，让写索引时顺序扫叶子页）、Collapse（合并重复键）、最后交给一个Update 算子，由这个算子按顺序把每个索引写完。因为写动作集中在一个算子里，锁的申请和释放节奏是紧凑的，不存在“放掉再回来”的空档。

宽计划则是 per-index 的：每个索引一个 Update 算子，各自定位、各自加锁、各自释放。算子越多，段数越多，段与段之间的窗口越多。

常见的解释是：Split-Sort-Collapse 这条路需要对数据行做排序比较，而 MAX 类型（varchar(max)、nvarchar(max)、varbinary(max)、xml）的值可能根本不在行内，行里只有一个 16 字节的 LOB 指针，排序算子没法拿它当普通定长/变长字段来比较。所以只要更新语句涉及把 MAX 列写进索引，优化器就只能退回到 per-index 的宽计划，一个索引一个算子。我一般会这么理解：不是varchar(max)本身慢，是它让优化器失去了“批量一次写完”的选项。

而varchar(200)是普通变长类型，值永远在行内，排序比较没有任何障碍，走窄计划顺理成章。这也解释了测试3 那个“只有一次 update 但包含三个 Object”的奇怪计划——不是三个索引变成了一个，是一个算子管了三个目标。

3.3 三个测试的执行计划对比：一步与两步的分界

把三个测试放在一起看，分界线非常清晰：

测试1 的三段执行对应的正是锁轨迹里的“申请—释放—再申请”。测试2 只有一段，写完就结束。测试3 虽然要写三处，但三处在一个算子里，不松手。

这也顺带解释了为什么把 d 改成varchar(8000)同样不会死锁——8000 字节以内仍是行内类型，优化器照样能走窄计划。真正的分水岭不是“长不长”，而是“是不是 MAX 类型”。

注意：不能由“测试1 有死锁”反推“include 大字段一定会死锁”。死锁需要两个连接以相反顺序遇到同一批资源，单连接反复跑只会互相阻塞，不会进入死锁检测。测试脚本里必须两个连接同时循环，才能稳定复现。

4. 排查这类死锁的常见坑：五个踩过的具体场景

这类“看起来莫名其妙、其实执行计划写得明明白白”的死锁，排查过程里的坑比死锁本身还多。下面五条都是血泪经验，每一条都会让你在错误的方向上多花几个小时。

4.1 坑一：只拿到死锁图就下结论

现象：1222 输出拿在手上，resource-list里两个 keylock 交叉等待，看起来明明白白，于是直接给出“把隔离级别调低”或“加重试”的方案，问题隔天复现。

原因：死锁图只描述“谁持有、谁等待”这个瞬时快照，不描述“为什么持有者会在这里持锁”。本例里，如果只看图，会以为两个连接是“同时”申请两把锁；实际上连接2 是先拿后放、再重新申请，中间隔了一个完整的执行段。这个差异决定了修复方向：如果是同时申请，缩小事务范围有效；如果是先放后拿，缩事务范围没用，得减少段数。

解决：死锁图、锁 trace、执行计划三件套一起收，缺一件就不算定位完成。收集顺序建议是“先开 1222 保证有现场，再开 trace 拿时序，最后单独跑一次statistics profile拿计划”。

4.2 坑二：Lock 事件里没把 ObjectID2 拖出来

现象：Trace 抓了一堆Lock:Acquired，ObjectID全是数字，翻了半天不知道在说哪张表哪个索引，最后放弃。

原因：Profiler 默认列是按“人肉友好度”排的，Mode、Type这种语义列反而在后面。ObjectID2（也就是分区 ID）根本不在默认视图里，不拖出来就永远对不上死锁图里的hobtid。

解决：固定一套模板——Show all columns，然后把SPID、EventClass、Mode、Type、ObjectID、ObjectID2、TextData拖到最前，按 SPID、StartTime 排序。拿到ObjectID2后用sys.partitions反查索引名，把 ID 换成名字再看，可读性差别巨大。

4.3 坑三：以为 WITH(ROWLOCK) 是解药

现象：语句里加了with(rowlock)，以为锁粒度细了就不容易冲突，结果死锁照旧，甚至更频繁。

原因：rowlock改的是锁的粒度，不改锁的顺序。本例里，正是行级锁让两个连接能精确地在同一个索引键上撞车；如果粒度是页级，第二个连接可能在第一段就被挡住，反而“不出死锁”——但那是用更长的阻塞换来的假象。

解决：先判断死锁是“顺序型”还是“转换型”。顺序型（本例）要减少算子段数或统一访问路径；转换型才考虑调整锁粒度或隔离级别。盲目加锁提示词属于碰运气。

4.4 坑四：把大字段塞进 include，以为只是索引变大

现象：为了消掉一个查询的 key lookup，把varchar(max)的正文列 include 进两个索引，查询快了，写入开始随机死锁、日志增长变快、索引重建时间翻倍。

原因：include 的写放大是 1:N 的，被 include 的列每变一次，N 个索引全要重写；MAX 类型还会把计划从窄计划逼成宽计划，段数增加，锁窗口增加。读侧省下的那点 IO，在写侧被成倍还回去。

解决：include 只放“小、稳定、查询要读”的列。大文本要么留在表里让查询回表，要么单独拆一张扩展表按需 JOIN。判断标准很简单：这一列的更新频率乘以索引个数，就是你要付的写代价。

4.5 坑五：用 HOLDLOCK 或 NOLOCK 一把梭

现象：有人在语句上加holdlock让锁一直持有到事务结束，死锁确实不出现了；换个场景，阻塞时间从毫秒级变成秒级。也有人加nolock读，脏读出来了，写侧的竞争一点没变。

原因：holdlock相当于把 U 锁的持有期拉长到事务末尾，代价是并发度下降、阻塞范围扩大。它治的是症状——把“放掉再回来”的窗口堵上了，但没有解决“为什么要回来”这个问题。nolock更离谱，它只影响读，对这个更新死锁无效。

解决：holdlock可以用来做验证——如果加上它死锁消失，就证实了“先放后拿”的判断，然后回到根因去减少索引更新段数。用它做长期方案，等于用并发度换稳定，迟早要在另一个地方还债。

5. 设计取舍：include 列的边界，以及怎么自己验证一步还是两步

这个案例最后落在一个很朴素的取舍上：varchar(max)该不该进 include。答案是不该，但推理过程比结论有用。include 的设计初衷是覆盖查询，让只读路径不回表；它对写路径的影响在设计时几乎是隐形的，因为它不改变索引键、不影响 seek 逻辑，只在“被 include 的列发生变更”时才露出獠牙。而“被 include 的列会不会变、变得多频繁”这个问题，在加索引的那一刻往往没人问。

我给自己的判据是三条，按顺序过：这列是不是 MAX 类型（是就直接排除，不管读侧多想要）；这列是不是频繁更新（更新频率乘以 include 它的索引个数，就是每次写入要多付的锁和日志）；这个覆盖查询的收益是不是真的大到值得（用sys.dm_db_index_usage_stats看 user_seeks 和 user_updates 的比值，比值低于 10 的基本不值得）。三条里有一条不过，就把列请出 include，让查询回表。

验证方法也比想象中简单，不需要 trace 也能做初筛：跑一次set statistics profile on，数输出里的 Index Update 算子个数。一个算子是窄计划，多个是宽计划，后者就意味着多段加锁、多段窗口。如果再加上set statistics io on看逻辑读的次数，能进一步确认写放大到底有几倍。这两个开关加起来两行代码，比抓一次 trace 的成本低一个数量级，适合放在改索引之前的必查清单里。

再往深一层，这个案例真正的教训不是“别把大字段 include 进索引”，而是执行计划的形态本身就是并发行为的一部分。同样一条 SQL，走窄计划和走宽计划，逻辑结果一模一样，锁的行为却完全不同。做索引设计、做语句调优的时候，如果只看“快不快”不看“锁几次”，就等于只测了功能没测并发。

从那以后我每次给表加 include 之前，都会先跑一遍statistics profile数算子数，再问一句“这列会变吗”。这个习惯救过我不少次，也希望帮到你。

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