简介针对SQL Server中一类看似怪异、实则与索引及锁机制密切相关的死锁问题本资源提供完整的问题重现步骤与系统化排查思路适合数据库管理员、开发人员以及希望深入理解锁机制的中高级SQL Server使用者。包内含1个docx文档大小约694KB文档以实际案例方式展开从创建含clustered与non-clustered索引的测试表、复现死锁到对比删除include选项或调整varchar(max)字段类型后死锁消失的现象逐步剖析锁冲突的根源。分析环节重点讲解如何通过开启1222跟踪标记、结合SQL Profiler按SPID筛选事件并借助sp_readerrorlog提取死锁日志最终定位资源等待链与死锁牺牲者。对需要掌握真实生产环境死锁诊断方法、提升SQL Server故障排查能力的读者这份文档提供了可复现的实验脚本和典型分析路径兼具理论解释与实操参考价值。已有326人学习适合作为死锁专题学习与排错方法演练的参考资料。1. 两条一模一样的 UPDATE 互相死锁这个案例到底怪在哪两条完全相同的 UPDATE 语句各自在独立的连接里并发执行竟然会稳定地互相死锁。第一次看到这个现场时绝大多数人的第一反应都是这怕不是玄学。可它就是在一个普普通通的 SQL Server 2008 测试库上复现了而且浑身都是反直觉的点——只要把非聚集索引里的INCLUDE(d)去掉死锁就消失或者把d的类型从VARCHAR(MAX)改成VARCHAR(200)死锁也消失。索引定义和字段类型这两个看起来八竿子打不着的改动居然同时决定着一个 Deadlock 出不出现。这篇文章完整复盘我对这个案例的整个排查过程从稳定重现、开启 1222 追踪、读锁等待环到用 SQL Trace 还原锁申请顺序、对照执行计划锁定根因最后落到可抄作业的解法与预防手段。正被同类死锁卡住的开发者按这套流程走一遍能少折腾好几个晚上。2. 稳定复现三步造出必现死锁三种改动让死锁消失排错最怕「偶发」死锁尤其怕。偶发意味着你没法验证修复有没有生效只能靠猜。这个案例幸运的地方在于它有一个极其简单的重现路径一张表、两个索引、一万行数据、两个连接死锁必然出现。我把复现过程拆成三步每一步都能解释「为什么这么设计」。2.1 建表与数据准备一张表、三个索引、一万条记录先看原始表结构。这个表设计得很克制没有任何花活却精准地踩中了死锁的触发条件。CREATE TABLE tt ( id INT IDENTITY PRIMARY KEY, -- 聚集索引所在列 a CHAR(36), b CHAR(36), d VARCHAR(MAX) ); GO CREATE INDEX ix_a_bc ON tt(a) INCLUDE(d); CREATE INDEX ix_b_cd ON tt(b) INCLUDE(d); GO这个表有一个显式的主键同时就是聚集索引另有两个非聚集索引ix_a_bc和ix_b_cd分别建立在a、b两列上并且都通过INCLUDE把d字段包含进了索引的叶子节点。d是VARCHAR(MAX)一个可以容纳 2GB 文本的大对象类型。-- 插入一万条数据a 用 NEWID() 保证每个值唯一 INSERT INTO tt SELECT NEWID(), bbb, ddd; GO 10000复制这段脚本时有个细节GO 10000表示把上面的INSERT语句连续执行 10000 次靠IDENTITY自增让id从 1 到 10000同时每条记录的a都是不同的NEWID()随机值。这一步是为了让WHERE a 某个具体值能精确命中一条记录避免同一值被多条记录共享而干扰锁行为分析。-- 找到第 10 条记录记下它的 a 字段值 SELECT * FROM tt WHERE id 10;这里得到的a值每次重建表都不一样比如我当时拿到的是EF211985-EA72-4A40-81DA-0AAB076E7AA3。后面所有 UPDATE 语句都只会更新这一条记录——单行更新注意是单行避免有人第一反应是「是不是更新范围太大扫了太多锁」。2.2 三个对照实验改一处必现死锁就变成不死锁复现场景准备好后用两个查询窗口分别跑同一个循环 UPDATE死锁在几秒内必然出现。-- 连接 1 与连接 2 同时运行这段循环 WHILE 1 1 UPDATE tt WITH (ROWLOCK) SET d cd WHERE a EF211985-EA72-4A40-81DA-0AAB076E7AA3;先解释这段循环在做什么。WHILE 1 1是无限循环目的是让 UPDATE 语句在很短的时间内反复执行把锁竞争的概率无限放大——现实中不会有哪个业务这么写但复现死锁时循环是最高效的「压力放大器」。WITH (ROWLOCK)是锁粒度提示告诉 SQL Server 尽量在行级别加锁而不是升级到页锁或表锁保证两个连接竞争的是同一个 Key 上的锁而不是被大粒度锁掩盖问题。同样的数据、同样的 UPDATE 语句只改动一处定义死锁立刻消失。三个测试的对照关系如下。测试索引定义d 类型死锁测试 1ix_a_bc(a) INCLUDE(d)、ix_b_cd(b) INCLUDE(d)VARCHAR(MAX)必现测试 2ix_a_bc(a)、ix_b_cd(b)去掉 INCLUDEVARCHAR(MAX)不出现测试 3保留INCLUDE(d)VARCHAR(200)不出现这两个改动看似一个动索引、一个动类型指向的却是同一个变量——UPDATE 语句的执行计划。SQL Server 加多少把锁、按什么顺序加锁不是由表结构直接决定的而是由优化器选出来的执行计划决定的。索引叶子节点里有没有d、d是不是大对象类型都会改写执行计划的分支进而改写整个加锁顺序。这就是「差一处定义、差一个死锁」的本质。3. 根因定位从 1222 输出到锁顺序表还原死锁全过程现象摆清楚之后接下来就是深挖根因。这个案例的分析过程非常有代表性它同时用上了两种死锁分析手段一个是DBCC TRACEON(1222)输出的死锁报告一个是 SQL Trace 里逐条记录的Lock:Acquired与Lock:Released。前者回答「死锁长什么样」后者回答「死锁为什么会变成这样」。3.1 先开 1222 开关把死锁信息写进错误日志SQL Server 自带的死锁捕获开关有两个1204和1222。1204输出格式偏老信息密度低1222的输出是结构化的 XML 文本包含完整的deadlock-list、process-list、resource-list是当前分析死锁的主流选择。-- 开启 1222 追踪-1 表示对所有数据库生效 DBCC TRACEON(1222, -1);执行后后续发生的每一次死锁都会在 SQL Server 错误日志里留下一份完整报告。注意-1是全局开关意味着影响整个实例生产环境要谨慎使用确认分析完毕后用DBCC TRACEOFF(1222, -1)及时关闭。在测试环境复现死锁时这个是必开的开关没有它就没有第一手证据。-- 在两个连接里分别执行记录各自的 SPID SELECT spid;假设两个连接返回的 SPID 分别是 54 和 60。记下这两个号后续做 SQL Trace 过滤要用。死锁发生后用sp_readerrorlog就能读到错误日志里的死锁记录。3.2 读 1222 报告锁等待环直接告诉我「谁在等谁」1222 输出里最关键的是resource-list段它描述了死锁那一刻两个进程互相等待的锁资源。当时报告里的关键信息整理成锁等待环是这样连接 SPID 60 已经持有PK__tt__3213E83F10E07F16主键索引上的X锁正请求ix_a_bc索引上的X锁等待中。连接 SPID 54 已经持有ix_a_bc索引上的U锁正请求PK__tt__3213E83F10E07F16上的U锁等待中。两个进程各自持有一把对方想要的锁等待关系首尾相连形成了闭环。从executionStack看两个进程执行的确实是同一条语句UPDATE tt WITH(rowlock) set dcd where a...。两个进程、同一条 SQL、互相等对方手里的锁SQL Server 的锁管理器无法打破这个环只能选一个牺牲者回滚——deadlock victimprocess5e27708就是被选中的那个。但是1222 报告只回答到「是怎么死锁的」这一层。它没有回答更根本的问题为什么一次简单的单行 UPDATE 需要同时申请两个不同索引上的锁为什么持有ix_a_bc的U锁还不够还要去拿主键索引上的X锁这个问题1222 已经无能为力了必须去看更细粒度的锁申请记录。3.3 用 SQL Trace 还原锁顺序一次 UPDATE 竟然分两步执行打开 SQL Profiler新建一个 Trace事件选择Locks分类下的Lock:Acquired、Lock:Released、Lock:Deadlock再选上TSQL分类下的SQL:BatchCompleted等事件。在Column Filters里按 SPID 过滤只保留 54、60、6、20 这几个会话——6 和 20 通常是后台系统会话保留它们是为了观察是否有系统层面的干扰。然后跑两个连接的 UPDATE 循环让死锁发生。Lock:Acquired和Lock:Released默认展示的字段很乱要把Mode锁模式、ObjectID2资源编号、Type锁类型拖到前面。这里有个关键技巧ObjectID2字段里的值就是 1222 报告里hobtid的值它不是一个容易读懂的对象编号需要换算回索引名。-- 把 hobtid / ObjectID2 换算成具体的索引名 SELECT o.name, i.name, i.type FROM sys.indexes i INNER JOIN sys.objects o ON i.object_id o.object_id INNER 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我当时查到的对应关系是72057594065518592对应主键索引PK__tt__3213E83F10E07F1672057594066108416对应ix_a_bc72057594066173952对应ix_b_cd。有了这张对照表Trace 里那一长串数字立即变成了可读的锁序列。测试 1必现死锁的锁顺序如下。索引锁类型连接 1SPID 54连接 2SPID 60ix_a_bcU申请主键U申请主键X申请ix_a_bcU释放ix_a_bcX申请ix_b_cdX申请ix_b_cdX释放ix_a_bcX释放主键X释放这张表暴露了真相这条 UPDATE 在测试 1 下是「两步走」的。第一步先拿ix_a_bc上的U锁定位记录再拿主键上的U锁和X锁完成对聚集索引记录的修改第二步因为两个非聚集索引的INCLUDE(d)里存了d字段的副本聚集索引上的d一改这两个索引必须跟着同步更新于是又要申请ix_a_bc和ix_b_cd上的X锁。问题就出在「第一步结束后、第二步开始前」连接 1 释放了ix_a_bc的U锁连接 2 立刻拿到了ix_a_bc的U锁并开始它的第一步而连接 2 此时已经持有了主键的X锁连接 1 又在等主键锁——环在那一瞬间闭合了。再看测试 2去掉INCLUDE(d)和测试 3VARCHAR(200)的锁顺序两条路径收敛到了同一个结果。索引锁类型申请/释放ix_a_bcU申请主键U申请主键X申请ix_a_bcU释放主键X释放两步变成一步UPDATE 只需要通过ix_a_bc定位到主键记录并完成修改不再需要回头同步两个非聚集索引。锁的持有时间大幅缩短第二个连接根本等不到「对方持有我想要的锁」的窗口死锁自然消失。3.4 执行计划对照是谁决定了「走两步还是走一步」锁顺序的变化只是表象真正的幕后推手是执行计划。在 UPDATE 前加上SET STATISTICS PROFILE ON可以看到语句实际使用的计划。SET STATISTICS PROFILE ON; GO UPDATE tt WITH (ROWLOCK) SET d cd WHERE a EF211985-EA72-4A40-81DA-0AAB076E7AA3;三个测试的执行计划差异非常直观测试 1计划里有三个Index Update操作。一个负责主键索引两个分别负责ix_a_bc和ix_b_cd。对应到锁行为就是要申请三把X锁且分散在两步完成。测试 2只有一个Index Update。因为d没有被任何非聚集索引包含SQL Server 只需要更新聚集索引。测试 3同样只有一个Update操作但这个Update内部包含了三个 Object。意思是SQL Server 把三个索引的更新合并进了一次操作一次性完成。测试 3 是最有意思的对照组索引定义没变INCLUDE(d)还在但因为d从VARCHAR(MAX)变成了定长容量可控的VARCHAR(200)优化器认为可以把它放进一次原子更新里完成而不是拆成多次索引维护。VARCHAR(MAX)之所以被特殊对待是因为它属于大对象LOB类型在索引维护、日志记录、页分裂行为上和普通变长字段完全不同优化器对它的处理路径也更复杂。一个类型变更改写了整个加锁路径——这就是「玄学」背后真正的机制。到这里根因链条完整闭合了INCLUDE(d)VARCHAR(MAX)→ 执行计划选择两步更新 → 锁的申请与释放在中途出现「间隙」 → 两个并发的同语句 UPDATE 在这个间隙里互相咬住 → 死锁。4. 死锁排查避坑清单这些常见误判会让方向跑偏这个案例排查过程中踩过的、以及平时答疑时常看到别人踩的坑我整理成一份避坑清单。每条都按「现象 → 原因 → 解决」讲清楚能帮你少走弯路。4.1 只看报错编号就搜解决方案忽略锁等待环现象拿到的是Error 1205死锁报错直接复制编号去搜索引擎找答案照着别人给的方案改完死锁依然复现。原因1205 只是 SQL Server 通知客户端「你被选为牺牲者」的通用错误本身不含任何锁资源信息。两个完全不同的死锁场景可能报同一个 1205靠错误号反推原因基本是盲人摸象。解决必须先拿到死锁报告本身。测试环境用DBCC TRACEON(1222, -1)生产环境可以在AlwaysOn里用扩展事件抓取deadlock_report。没有resource-list和owner-list/waiter-list就别动手改代码。4.2 把死锁当成阻塞来处理现象看到process-list里有statussuspended、waittime字段就以为这是普通的锁阻塞等其中一个事务结束就恢复。于是只加了索引、调了超时死锁还是反复出现。原因阻塞是两个进程一前一后、单向等待死锁是等待关系成环、互不相让。处理阻塞的思路是「让持有锁的人尽快提交」处理死锁的思路是「打破环」。把死锁当阻塞治方向就反了。解决拿到 1222 报告后第一件事就是画锁等待环——谁持有谁想要的锁、谁又持有另一个人想要的锁。环画出来死锁的性质就定了后续才谈得上改执行计划、改隔离级别。4.3 拿到 1222 就当结论不去还原锁的申请顺序现象1222 报告已经把死锁瞬间的两个锁和两个进程列得很清楚于是直接在这份报告上做文章改隔离级别、加WITH (NOLOCK)问题却时好时坏。原因1222 记录的是「死锁那一帧」的资源状态不是「死锁如何演化到这个状态」的全过程。这个案例里如果只看 1222只能看到两个进程各持一把锁、互相等待但看不到「为什么一次 UPDATE 要先释放ix_a_bc的 U 锁再去申请同索引上的 X 锁」——这个关键演化过程来自 Trace 里逐条锁记录的时序。解决遇到「看不懂」的死锁养成开 SQL Trace或扩展事件捕获lock_acquired/lock_released的习惯分析锁的申请与释放顺序再结合执行计划下结论。1222 给结论Trace 给证据链。4.4 一遇到死锁就全局加 NOLOCK现象查不出根因干脆在 SELECT 语句里全加WITH (NOLOCK)UPDATE 上加不了就用READ UNCOMMITTED隔离级别指望躲开锁竞争。原因NOLOCK防的是「读被写阻塞」这个案例里的死锁发生在两个UPDATE之间全是写锁之间的互斥NOLOCK完全无效。而且NOLOCK还会引入脏读风险属于掩盖问题而不是解决问题。解决先分清死锁发生在读-写之间还是写-写之间。写-写死锁的出路是让每条语句申请更少的锁、更短地持有锁手段是改执行计划、改索引设计、缩短事务边界而不是给 SELECT 加提示。4.5 忽略了INCLUDE字段对 UPDATE 锁行为的影响现象表上非聚集索引带了INCLUDE大字段UPDATE 这个小字段时频繁死锁DBA 检查执行计划发现索引都被正常使用「索引没问题」就被放过了。原因INCLUDE不在WHERE/JOIN/ORDER BY里出现时优化器会认为它对查询「有用」但它出现在UPDATE的SET列上时每次修改都会触发索引叶子节点的同步写。索引维护的成本和锁的范围就这样悄悄叠加到了每条 UPDATE 上。这个案例的测试 2 证明去掉INCLUDE(d)后死锁立即消失。解决审查表上每个非聚集索引的INCLUDE列时把「这些列会不会被 UPDATE 修改」作为必问问题。大对象字段放进INCLUDE尤其要警惕——优化器对 LOB 类型走的是另一条维护路径锁更重、计划更复杂。5. 解决方案从改索引到控制执行计划三条路都能消死锁根因清楚了解法其实就摆在桌面上。这个案例给了三条路每条路对应一个层面的干预改索引结构、改字段类型、改语句行为。实际项目中按成本和风险选择。5.1 方案一从非聚集索引中移除INCLUDE(d)字段这是最直接的做法对应测试 2。既然死锁源于非聚集索引要跟着更新d那把d从索引里拿掉就斩断了第二步的根源。DROP INDEX ix_a_bc ON tt; DROP INDEX ix_b_cd ON tt; -- 重建时不带 INCLUDE(d) CREATE INDEX ix_a_bc ON tt(a); CREATE INDEX ix_b_cd ON tt(b); GO这个方案的逻辑是ix_a_bc和ix_b_cd的定位是加速WHERE a ?、WHERE b ?这类查找d字段并不参与索引查找。当初把d放进去多半是想做覆盖索引让查询不需要回表。但如果d是一个频繁被 UPDATE 的大字段这个「覆盖」收益会被索引维护的代价吃掉死锁风险更是火上浇油。实际业务里如果确实需要覆盖查询更合理的做法是评估d的更新频率高频更新就不该进INCLUDE低频更新也要控制字段长度。5.2 方案二给大字段一个有界的长度对应测试 3保留INCLUDE(d)但改变类型。VARCHAR(MAX)换成VARCHAR(200)UPDATE 从两步合并成一步死锁消失。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); GO注意一个细节修改列类型前必须先删掉依赖这个列的索引否则ALTER TABLE会因为有索引引用而失败。这个方案的适用前提是业务上d的最长值确实可控——比如它存的是状态描述、短备注这类数据。把VARCHAR(MAX)收敛成VARCHAR(200)不只是省存储更重要的是让优化器把这条路径当普通变长字段处理不再为 LOB 类型选择特殊的两步更新计划。5.3 方案三保持表结构不变改变锁的持有行为如果索引结构不能动、字段类型也不能动还有第三条路用锁提示或计划指南干预语句行为。比如用HOLDLOCK把第一步的U锁一直持有到语句结束。WHILE 1 1 UPDATE tt WITH (ROWLOCK, HOLDLOCK) SET d cd WHERE a EF211985-EA72-4A40-81DA-0AAB076E7AA3;HOLDLOCK等价于SERIALIZABLE级别下的锁语义它会让U锁一直保持到事务结束而不是中途释放。这听起来反而「锁更久」为什么能解死锁因为这个案例的死锁间隙恰恰出现在「第一步结束后释放了ix_a_bc的 U 锁、第二步又去申请同索引的 X 锁」这个空档。HOLDLOCK把这个空档堵住了第二个连接永远拿不到ix_a_bc的U锁也就不可能和第一个连接形成环。当然代价是并发度下降——它用「串行化」换「不死锁」适用场景有限。也有同行指出对这种「一条 UPDATE 只有一个热行」的场景用计划指南把执行计划固定成测试 3 那样的单步更新也能根治但维护成本高一般作为最后的备选。5.4 预防思路把执行计划当成「加锁路线图」来审这个案例真正的价值不在某一条解法而在于一个可复用的认知SQL Server 的加锁数量、顺序、持续时间是由执行计划决定的。索引越多、数据类型越复杂计划分支越复杂锁就越容易在多个索引之间「来回切换」阻塞和死锁的窗口就越大。所以预防死锁不能只盯着死锁那一瞬间的锁报告要养成顺手审执行计划的习惯。看完 1222 之后下一步就是把出问题的语句执行一遍打开STATISTICS PROFILE或SET SHOWPLAN_ALL看它到底有几个Index Update、锁是集中在一个对象上还是散在多个对象上。凡是看到计划里对同一语句存在多阶段锁申请就要警惕并发下的锁间隙。这个习惯比背一百条死锁处理口诀都管用。三种方案适用场景对比如下。方案改动面风险适用场景移除INCLUDE(d)索引定义覆盖查询可能回表d高频更新索引覆盖收益低VARCHAR(MAX)→VARCHAR(200)字段类型需确认业务长度上限d长度天然有界HOLDLOCK/ 计划指南语句行为并发度下降维护成本高表结构无法改动时的兜底6. 快速定位死锁三个动作照做十分钟拿到结论死锁问题的排查效率取决于你能不能快速拿到「正在演化的锁过程」而不是「死锁后的遗照」。我把这个案例里用到的流程压缩成三个动作任何死锁问题都按这个顺序过一遍。第一步开 1222 并把死锁信息落盘。测试环境直接DBCC TRACEON(1222, -1)生产环境用扩展事件抓deadlock_report事件名sqlserver.deadlock_report文件目标配成.xel。这一步唯一要记住的原则是宁可先开追踪再复现也不要等复现了再回头找证据。死锁是瞬时事件错过就是错过。第二步记录现场连接与锁资源的换算关系。用SELECT spid拿到参与会话的 SPID在 Trace 过滤里只保留这些 SPID同时把 1222 输出里的hobtid用sys.partitions关联sys.indexes换算成索引名。没有这张换算表后面对锁顺序的分析就是一堆数字猜谜。第三步对照执行计划画锁等待环。拿到 Trace 里Lock:Acquired/Lock:Released的时序后把每个进程的锁申请顺序和释放顺序列成表再用执行计划解释「为什么是这个顺序」。只要锁等待环画出来、两阶段锁申请找到对应的Index Update节点根因基本就浮出水面了。验证是否根治的方法也很简单保留原来的两个连接在改动后的表结构或语句上重跑同样的循环 UPDATE。持续跑几分钟不死锁才算真正闭环。如果改动后死锁频率降低但仍有偶发说明锁的路径被压缩了但没有完全消除回到第二步把新的锁顺序表再画一次通常会发现剩下的是另一条锁路径。这个案例给我留下的教训是那句已经被验证了很多次的话死锁分析没有捷径但一定有方法——先拿全证据再谈解决方案。从那以后我每次遇到死锁都强制自己走一遍「1222 Trace 执行计划」的流程没做完前两步绝不动代码。这套流程救过我不少次希望帮到你。本文还有配套的精品资源点击获取