简介一份SQL Server死锁排查实战文档围绕一张带聚集索引和两个包含列索引的示例表重现两条相同update语句交叉更新时反复出现的‘奇怪Deadlock’。资源从索引include(d)、varchar(max)字段类型两个变量切入说明非聚集索引包含列与大值类型如何影响索引页维护和键范围锁的获取同时用测试1、测试2、测试3对比锁范围与键锁冲突的差异并展示如何开启1222跟踪标记、结合SQL Trace与SQL Profiler按SPID过滤收集事件再借助sp_readerrorlog逐段阅读deadlock graph中的process-list、waitresource、executionStack等关键输出定位真正持锁与申请锁的资源。内容适合已有SQL Server基础、希望系统提升死锁分析与索引设计能力的DBA、运维和开发人员。包内为1个docx文档约694KB结构完整、步骤可复现。已有326人浏览学习通过该资源可获得从复现、采集到解读死锁图的完整排查思路减少生产环境锁等待与死锁影响。1. 一个十分钟能复现的死锁没改业务改两个字符就消失了遇到死锁时第一反应往往是谁先拿数据、谁后拿数据但这个案例反直觉得有点玄学。一张带聚集索引和两个非聚集索引的测试表两条一模一样的单行 UPDATE 循环用 with(rowlock) 锁同一行并发跑起来死锁必现。更奇怪的是把任一非聚集索引的 INCLUDE(d) 去掉或者把 d 从 varchar(max) 改成 varchar(200)死锁就彻底消失。业务 SQL 没动、数据没动、并发方式没动矛盾的根源全在索引和类型上。这篇文章会把一个十分钟能复现的死锁拆开从 1222 死锁日志、SQL Trace 锁序列到执行计划里的 Index Update 算子数量逐层还原死锁如何被索引配置一步步喂出来。适合被死锁缠住的后端开发、索引优化方向的 DBA以及想搞懂锁机制的人。2. 复现试验台一张表、两个索引、三种测试路径2.1 建表与基础数据把现场搬到你自己的测试库先说明表结构的设计意图id identity primary key生成聚集索引同时也是主键两个非聚集索引分别以a和b作为键列并且都include了d。关键点是d一开始就被设计成varchar(max)后面你会看到这就是整个死锁的核心变量。-- 建立测试表id 自增并作为聚集索引主键 CREATE TABLE t_deadlock_demo ( id INT IDENTITY PRIMARY KEY, a CHAR(36), -- 非聚集索引键列用来定位目标行 b CHAR(36), -- 第二个非聚集索引的键列 d VARCHAR(MAX) -- LOB类型将被 include 进两个非聚集索引 ); GO -- 两个非聚集索引都 include 了 d CREATE INDEX ix_a_bc ON t_deadlock_demo(a) INCLUDE(d); CREATE INDEX ix_b_cd ON t_deadlock_demo(b) INCLUDE(d); GO -- 批量插入 10000 行a 列用 NEWID() 生成随机的 36 字符 GUID INSERT INTO t_deadlock_demo SELECT NEWID(), bbb, ddd GO 10000参数说明INCLUDE(d)表示把 d 列作为非键列放进索引叶子页。它不参与索引排序却能实现窄索引加覆盖查询的效果。但 d 是 LOB 类型时叶子页里放的不是完整值而是指向 LOB 数据的指针这个细节就是后面死锁的引信。GO 10000是 SQLCMD 和 SSMS 的批处理计数语法等价于把 INSERT 语句重复执行一万次并批量提交。接下来要定位一条目标记录。先取 id10 的 a 值SELECT * FROM t_deadlock_demo WHERE id 10;假设得到的 a 是EF211985-EA72-4A40-81DA-0AAB076E7AA3每次测试随机。把这个值替换进下面的循环就可以在两个连接里并发跑WHILE 1 1 BEGIN UPDATE t_deadlock_demo WITH(ROWLOCK) SET d cd WHERE a EF211985-EA72-4A40-81DA-0AAB076E7AA3; -- 替换成你查到的真实值 END;注意WITH(ROWLOCK)是锁粒度提示作用是把锁粒度压到 KEY/RID 级防止 SQL Server 因为表格扩大而自动升级成页锁或表锁。锁粒度越细锁序列越容易被观察。在这个实验里把 ROWLOCK 去掉死锁依然存在只是锁范围变大后表现会不一样。所以别以为是 ROWLOCK 导致的死锁它是为了把现场看得更清楚而加上的。再补充一个关键前提每次切换到另一组测试前最好重建表或者确认两个会话都已经停止。两个 WHILE 循环在死锁发生后SQL Server 会自动回滚受害者事务并重试所以测试连接本身不会退出但上一轮的锁残留可能影响下一轮结果。我会在每组测试前执行一次CHECKPOINT和DBCC DROPCLEANBUFFERS让缓存页和日志状态尽量一致。2.2 测试2与测试3去掉 include / 收窄类型死锁消失的两个变体测试2 的意图是把两个非聚集索引重建为不带INCLUDE(d)的版本DROP INDEX ix_a_bc ON t_deadlock_demo; DROP INDEX ix_b_cd ON t_deadlock_demo; CREATE INDEX ix_a_bc ON t_deadlock_demo(a); CREATE INDEX ix_b_cd ON t_deadlock_demo(b);执行同样的 UPDATE 循环两个会话并发跑死锁不再出现。测试3 则保留include(d)但先把字段类型从varchar(max)收窄到varchar(200)DROP INDEX ix_a_bc ON t_deadlock_demo; DROP INDEX ix_b_cd ON t_deadlock_demo; ALTER TABLE t_deadlock_demo ALTER COLUMN d VARCHAR(200); CREATE INDEX ix_a_bc ON t_deadlock_demo(a) INCLUDE(d); CREATE INDEX ix_b_cd ON t_deadlock_demo(b) INCLUDE(d);这两组变体的意义在哪里测试2 把“更新 include 列”这个动作从执行计划里彻底摘掉了测试3 把 LOB 列从行外存储变成行内存储改变了索引行的物理格式。二者都能消灭死锁说明死锁与 UPDATE 语句本身无关与 ROWLOCK 无关与并发连接同时更新同一行也无关。真正相关的变量只有一个索引维护路径。我一般会再补一个测试4 用来排除“长度变化”的干扰把 d 改成varchar(8000)保留 include。你猜结果如何死锁同样不会发生。这个现象在 SQL Server 2012 早期版本上也能稳定复现。原因后面第 4 章会细讲max_length -1的 LOB 类型走的是行外指针维护而varchar(8000)的max_length 8000属于普通行内类型可以合并进索引行的更新。所以关键判断不是“长不长”而是“是不是 LOB”。对照实验到这里已经能给出一个初步结论两个非聚集索引同时对同一个 LOB 字段做 include会让一条原本简单的单行 UPDATE 变成需要维护三份索引的工作。SQL Server 在执行计划里把它们拆成了多个独立步骤每个步骤都有自己的锁申请窗口死锁就是在这个窗口里钻出来的。3. 取证用 1222 和 SQL Trace 还原死锁现场3.1 打开死锁抓取开关dbcc traceon(1222, -1)要理解死锁第一件事是取证。SQL Server 提供了几种方式SQL Trace、1222 开关、扩展事件。在 SQL Server 2008 时代最顺手的是 1222 开关加 SQL ProfilerSQL Server 2012 之后我一般习惯用扩展事件但 1222 输出的文本仍然是理解死锁细节最完整的资料因为它会把 owner、waiter、锁资源、SPID、事务、调用栈一股脑写进错误日志。DBCC TRACEON(1222, -1); GO -- 确认开关生效 DBCC TRACESTATUS(1222, -1); GO参数说明1222 是死锁文本报告跟踪标记每次死锁发生后会把完整的锁资源列表和等待关系写入错误日志。-1 表示全局开启对所有数据库生效。生产环境开 1222 有轻微性能开销取证完成后要立刻关掉DBCC TRACEOFF(1222, -1); GO有个常见问题是只写DBCC TRACEON(1222)忘记加-1结果只有当前会话能看到死锁报告错误日志里什么都没有。排查时先DBCC TRACESTATUS确认状态这一步能省掉很多无效等待。3.2 SQL Trace 过滤只盯两个出事的 SPID因为死锁能稳定复现信息收集可以做到很全。先在两个 UPDATE 连接里分别执行SELECT SPID;假设得到的结果是 54 和 60。接下来打开 SQL Profiler新建一个 TraceEvents 勾选 Show all events 后在 Locks 分类下选择 Lock:Acquired、Lock:Released、Lock:Deadlock在 TSQL 分类下选择 SQL:BatchStarting、SQL:BatchCompletedColumn Filters 的 SPID 设置里只包含 54、60并额外加上 6 和 20 两个后台 SPID。为什么要额外带上 6 和 20因为死锁报告里经常会出现后台工作会话比如日志写入、检查点相关的进程。只过滤业务 SPID 会漏掉这些参与者导致锁序列分析时发现资源对不上号。至于具体是哪几个后台 SPID以你环境里sys.dm_exec_requests查询到的实际值为准。配置文件准备好后两个会话同时开始跑 WHILE 循环等死锁发生。这里需要注意如果过了几分钟还是没有 deadlock 事件多半是并发窗口没触发不要盲目加大循环次数先确认两个连接的 WHERE 条件指向的是同一条记录的同一个 a 值。3.3 用 sys.indexes 反向映射 hobtidKEY锁到底在哪个索引上1222 输出里 waitresource 长这样KEY: 8:72057594066108416 (ef8a9edf5a1e)冒号后面的数字是数据库 ID紧接着的 18 位数字是分区 IDhobtid括号里是 key hash。如果不做映射这一行对排查的人来说就是个黑匣子。要把它翻译成索引名用下面的查询SELECT o.name AS table_name, i.name AS index_name, i.type_desc, p.partition_id 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 IN (72057594066108416, 72057594065518592);在我本次运行的测试环境里映射结果如下你的环境生成的值会不同务必重新查询partition_id索引72057594065518592PK__t_deadlock_demo...聚集索引72057594066108416ix_a_bc72057594066173952ix_b_cd这个映射是后续所有锁序列分析的基础。我在实际工作中会提前把生产环境的索引名和 partition_id 全部导出备查遇到死锁时报出任何一个 KEY 资源五分钟内就能定位到具体索引。3.4 errorlog 里的死锁报告owner 与 waiter 是谁开启 1222 后用sp_readerrorlog查看错误日志关键段落如下deadlock-list deadlock victimprocess5e27708 process idprocess5e27708 spid60 ... transactionnameUPDATE waitresourceKEY: 8:72057594066108416 ... owner-list owner idprocess5e09dc8 modeU waiter-list waiter idprocess5e27708 modeX process idprocess5e09dc8 spid54 ... waitresourceKEY: 8:72057594065518592 ... owner-list owner idprocess5e27708 modeX waiter-list waiter idprocess5e09dc8 modeU这段输出信息量很大。spid54 持有一个 U 锁在 ix_a_bc 上正在等待聚集索引上的 U 锁spid60 持有聚集索引上的 X 锁正在等待 ix_a_bc 上的 X 锁。executionStack 里会显示 SQL 文本和 InputBuf确认死锁指令就是那两条完全相同的 UPDATE。到这里证据链已经闭合死锁不是两条相反顺序的 SELECT 加 UPDATE而是同一个索引上的 U 锁与 X 锁交叉等待。4. 机理拆解一行UPDATE为什么要申请三次X锁4.1 一次成功的UPDATE锁序列长什么样先看单会话下的一次成功 UPDATESQL Server 是怎么申请和释放锁资源的。把 SQL Trace 里 Lock:Acquired 和 Lock:Released 按时间整理得到下面这张表d 为 varchar(max)两个非聚集索引都 include 了 d阶段锁资源锁类型动作1ix_a_bcUAcquired2PK聚集索引UAcquired3PK聚集索引XAcquired4ix_a_bcUReleased5ix_a_bcXAcquired6ix_b_cdXAcquired7ix_b_cdXReleased8ix_a_bcXReleased9PKXReleased注意第 4 步ix_a_bc 上的 U 锁在聚集索引更新完成后就被释放了过一会儿又回来申请同一个索引的 X 锁。这个“先 U、放掉、再 X”的空档就是整个死锁的关键窗口。为什么需要回头申请 X 锁更新 ix_a_bc因为 d 被 include 在这个索引里而且 d 是 varchar(max) LOB 类型。索引行里不能直接保存 LOB 值只能保存指向 LOB 数据的指针。UPDATE d 时SQL Server 不仅要改聚集索引里的 LOB 数据页还要改两个非聚集索引叶子页里的指针字段。三个索引维护任务被拆成了“定位并更新聚集行”然后“更新两个非聚集索引”两个阶段锁序列自然被拉长。4.2 两个连接交错U锁与X锁的握手变成死锁把 SPID 54 和 SPID 60 放回同一条时间线上是这个案例最精华的部分时序连接1SPID 54连接2SPID 60t1申请并持有 ix_a_bc 上的 U申请并持有 ix_a_bc 上的 Ut2申请 PK 上的 U申请 PK 上的 U随后升级为 X 并完成更新t3等待 PK 锁释放 ix_a_bc 上的 Ut4趁空档拿到 ix_a_bc 上的 U进入第二步回头申请 ix_a_bc 上的 Xt5继续等待 PK 上的 U被连接1阻塞等待 ix_a_bc 的 Xt6持有 ix_a_bc 的 U等待 PK持有 PK 的 X等待 ix_a_bc 的 Xt5 到 t6 形成了完整的循环等待连接1握着 ix_a_bc 的 U 锁等待 PK连接2握着 PK 的 X 锁等待 ix_a_bc。SQL Server 死锁检测器发现这个环以后会选一个牺牲者回滚报 1205 错误。这里有个容易被忽略的点为什么连接2 在完成第一步后要释放 ix_a_bc 的 U 锁因为 SQL Server 默认在 read committed 隔离级别下单条语句内部的中间锁会在语句执行到不需要时立即释放不需要等语句结束。它以为 ix_a_bc 的定位任务已经完成U 锁可以放掉没想到下一步又要回去申请 X 锁。这个间隙给了连接1 趁虚而入的机会。如果不想让这个间隙出现最直接的优化是让连接2 在定位阶段就把 U 锁保持住比如加上WITH(HOLDLOCK)让它持有到事务结束。但这是打补丁不是根治。根治方向是把非聚集索引对 LOB include 列的多步维护拆掉。4.3 执行计划差异三个 Index Update 与一个 Update 的区别要验证上面的推断最直接的办法是看执行计划。在 UPDATE 语句前加一行SET STATISTICS PROFILE ON; GO UPDATE t_deadlock_demo WITH(ROWLOCK) SET d cd WHERE a EF211985-EA72-4A40-81DA-0AAB076E7AA3; GO SET STATISTICS PROFILE OFF; GO测试1varchar(max) INCLUDE(d)的算子摘要|--Clustered Index Update(OBJECT:([t_deadlock_demo].[PK...])) |--Index Update(OBJECT:([t_deadlock_demo].[ix_a_bc])) |--Index Update(OBJECT:([t_deadlock_demo].[ix_b_cd]))测试2去掉 INCLUDE(d)的算子摘要|--Clustered Index Update(OBJECT:([t_deadlock_demo].[PK...]))测试3varchar(200) INCLUDE(d)的算子摘要|--Clustered Index Update(OBJECT:([t_deadlock_demo].[PK...])) (内部同时包含两个非聚集索引的更新目标)三组计划差异和锁序列完全对得上。测试1 物理上有三个 Index Update 算子SQL Server 要分别申请三个目标索引上的 X 锁分步执行中间暴露释放 U 锁的窗口测试2 的 d 没有在非聚集索引中出现UPDATE 只动聚集索引锁序列只有一次握手测试3 的 varchar(200) 可以内联存储SQL Server 用一个 Update 算子合并维护三个索引一步完成没有“释放 U 后再要 X”的间隙。关于测试3 为什么能合并关键在 LOB 类型不能内联。varchar(max)、nvarchar(max)、varbinary(max)、text这些类型的max_length -1在行式索引里无法作为普通字段存进叶子页。把它们放进 INCLUDE 列时索引叶子页保存的是指向 LOB 堆的引用。更新 d 时引用本身也要写回非聚集索引SQL Server 就得为两个非聚集索引单独维护一次。改成varchar(200)后列值直接放进索引行索引维护可以合并到聚集索引更新那一拍执行计划从三算子分步变成一算子合并。顺带解释一个反直觉结论varchar(8000)也不会死锁因为它不是 LOBmax_length 8000同样可以内联。这就是“索引越多、数据类型越复杂死锁概率越高”的最直白注释。5. 死锁排查避坑指南四个最容易翻车的判断5.1 “同一行两条UPDATE语句”不等于“同一把锁”现象代码里只 update 一行业务也没有嵌套事务但并发压测时两条一模一样的语句死锁。原因同一行会被聚集索引和非聚集索引分别维护。本案例里一条 UPDATE 被拆成定位、聚集索引更新、两个非聚集索引更新锁对象是一串不是一个。锁序列存在跨索引的多段握手单纯说“单行更新不会死锁”在这里不成立。解决先跑一遍SET STATISTICS XML ON数执行计划里 Index Update 算子的数量。超过 1 个就说明锁序列存在多段维护必须把索引设计纳入排查范围。5.2 deadlock graph 里看不懂 KEY: 8:72057594066108416现象错误日志只给了KEY: 8:72057594066108416 (ef8a9edf5a1e)不知道是哪个表哪个索引。原因死锁报告里 KEY 锁显示的是 hobtid不是索引名。直接搜数字搜不出索引因为它是一个分区内部标识。解决用第 3.3 节的 sys.indexes 加 sys.partitions 映射脚本把 partition_id 翻译成索引名。我在生产环境会定期导出一份全库索引与 partition_id 对照表出问题时五分钟就能对上号。5.3 Trace 事件开了但死锁就是不进日志现象SQL Profiler 在跑1222 也开了压测跑出去就是没有 deadlock 事件。原因常见两个。一是 Filter 里只包含业务 SPID漏掉了死锁报告中出现的后台 SPID导致部分锁等待片段没被记录二是 1222 被会话级开启没加-1只有那个会话能看到报告。解决DBCC TRACEON(1222, -1)必须带-1Profiler 的 SPID 过滤里除了正在跑的业务 SPID把错误日志里出现过的所有 SPID 都放进去。另外确认死锁真的发生了如果始终没有双向阻塞多半是并发窗口没触发适当增大循环批次再试。5.4 一看死锁就上 NOLOCK结果事故更大现象加 NOLOCK 解决了某一次的 1205但报表读出中间状态或者又冒出其他死锁。原因NOLOCK 只是让读操作不去申请 S 锁它改变不了跨索引多算子更新自身的 U/X 锁重叠。本例中 U 锁参与的死锁NOLOCK 完全无效还会引入脏读、重复读风险。解决先定位执行计划和锁序列确认是本例的“索引 include LOB 字段导致多步索引维护”后正确做法是改索引设计从非聚集索引中移除不必要的 INCLUDE 字段或者把 LOB 字段改成有界的 varchar/nvarchar。临时止血可以用WITH(HOLDLOCK)保持定位阶段 U 锁到事务结束但这是补丁不是根治。6. 把死锁消灭在变更前执行计划与锁序列检查法这个案例最值钱的不是“死锁怎么解”而是“下次上线前怎么提前发现”。从那以后我只要在表结构变更里看到 INCLUDE 了一列就会强制自己走一遍下面的检查流程。第一件事跑一次 XML 执行计划数 Index Update 算子的个数。这一步专门用来发现多步索引维护SET STATISTICS XML ON; GO UPDATE t_deadlock_demo WITH(ROWLOCK) SET d cd WHERE a EF211985-EA72-4A40-81DA-0AAB076E7AA3; GO SET STATISTICS XML OFF; GO第二件事把包含 LOB 类型 INCLUDE 列的非聚集索引捞出来。下面的脚本直接查 sys.index_columns 和 sys.typesmax_length -1就是 LOBSELECT i.name AS index_name, i.type_desc, c.name AS included_column, t.name AS column_type FROM sys.indexes i INNER JOIN sys.index_columns ic ON i.object_id ic.object_id AND i.index_id ic.index_id INNER JOIN sys.columns c ON ic.object_id c.object_id AND ic.column_id c.column_id INNER JOIN sys.types t ON c.system_type_id t.system_type_id WHERE ic.is_included_column 1 AND t.name IN (varchar, nvarchar, varbinary) AND c.max_length -1;第三件事如果变更前时间允许开 1222 做一轮双会话并发循环专门验证同一行的同一条 UPDATE 是否稳定报错。这个动作我习惯写进压测脚本里不靠人工肉眼看概率。三条检查按顺序做完这类死锁基本可以在上线前暴露。如果已经上线了在救火我优先选改索引设计而不是 HOLDLOCK 或 PLAN GUIDE。让 SQL Server 少维护一个索引比事后想办法固化执行计划要稳得多。还有一个收尾技巧值得记住测试环境里把 UPDATE 的 WHERE 键从 a 换成 b或者换成聚集索引主键 id锁序列会完全不同。用主键定位时第一步直接命中聚集索引非聚集索引的维护动作排在后面死锁窗口也会变化。所以做复现实验时尽量模拟真实业务里的定位路径别为了方便改 WHERE 条件。这个案例给我的教训一直留到现在遇到死锁先别急着骂业务或者加提示先把执行计划的算子数和锁序列画出来大多数死锁都是索引设计埋的雷。希望帮到你。本文还有配套的精品资源点击获取 
长河君 · 2026/10/14 1:46:25 · 阅读 1.2k 非聚集索引INCLUDE LOB字段引发的死锁案例剖析
简介一份SQL Server死锁排查实战文档围绕一张带聚集索引和两个包含列索引的示例表重现两条相同update语句交叉更新时反复出现的‘奇怪Deadlock’。资源从索引include(d)、varchar(max)字段类型两个变量切入说明非聚集索引包含列与大值类型如何影响索引页维护和键范围锁的获取同时用测试1、测试2、测试3对比锁范围与键锁冲突的差异并展示如何开启1222跟踪标记、结合SQL Trace与SQL Profiler按SPID过滤收集事件再借助sp_readerrorlog逐段阅读deadlock graph中的process-list、waitresource、executionStack等关键输出定位真正持锁与申请锁的资源。内容适合已有SQL Server基础、希望系统提升死锁分析与索引设计能力的DBA、运维和开发人员。包内为1个docx文档约694KB结构完整、步骤可复现。已有326人浏览学习通过该资源可获得从复现、采集到解读死锁图的完整排查思路减少生产环境锁等待与死锁影响。1. 一个十分钟能复现的死锁没改业务改两个字符就消失了遇到死锁时第一反应往往是谁先拿数据、谁后拿数据但这个案例反直觉得有点玄学。一张带聚集索引和两个非聚集索引的测试表两条一模一样的单行 UPDATE 循环用 with(rowlock) 锁同一行并发跑起来死锁必现。更奇怪的是把任一非聚集索引的 INCLUDE(d) 去掉或者把 d 从 varchar(max) 改成 varchar(200)死锁就彻底消失。业务 SQL 没动、数据没动、并发方式没动矛盾的根源全在索引和类型上。这篇文章会把一个十分钟能复现的死锁拆开从 1222 死锁日志、SQL Trace 锁序列到执行计划里的 Index Update 算子数量逐层还原死锁如何被索引配置一步步喂出来。适合被死锁缠住的后端开发、索引优化方向的 DBA以及想搞懂锁机制的人。2. 复现试验台一张表、两个索引、三种测试路径2.1 建表与基础数据把现场搬到你自己的测试库先说明表结构的设计意图id identity primary key生成聚集索引同时也是主键两个非聚集索引分别以a和b作为键列并且都include了d。关键点是d一开始就被设计成varchar(max)后面你会看到这就是整个死锁的核心变量。-- 建立测试表id 自增并作为聚集索引主键 CREATE TABLE t_deadlock_demo ( id INT IDENTITY PRIMARY KEY, a CHAR(36), -- 非聚集索引键列用来定位目标行 b CHAR(36), -- 第二个非聚集索引的键列 d VARCHAR(MAX) -- LOB类型将被 include 进两个非聚集索引 ); GO -- 两个非聚集索引都 include 了 d CREATE INDEX ix_a_bc ON t_deadlock_demo(a) INCLUDE(d); CREATE INDEX ix_b_cd ON t_deadlock_demo(b) INCLUDE(d); GO -- 批量插入 10000 行a 列用 NEWID() 生成随机的 36 字符 GUID INSERT INTO t_deadlock_demo SELECT NEWID(), bbb, ddd GO 10000参数说明INCLUDE(d)表示把 d 列作为非键列放进索引叶子页。它不参与索引排序却能实现窄索引加覆盖查询的效果。但 d 是 LOB 类型时叶子页里放的不是完整值而是指向 LOB 数据的指针这个细节就是后面死锁的引信。GO 10000是 SQLCMD 和 SSMS 的批处理计数语法等价于把 INSERT 语句重复执行一万次并批量提交。接下来要定位一条目标记录。先取 id10 的 a 值SELECT * FROM t_deadlock_demo WHERE id 10;假设得到的 a 是EF211985-EA72-4A40-81DA-0AAB076E7AA3每次测试随机。把这个值替换进下面的循环就可以在两个连接里并发跑WHILE 1 1 BEGIN UPDATE t_deadlock_demo WITH(ROWLOCK) SET d cd WHERE a EF211985-EA72-4A40-81DA-0AAB076E7AA3; -- 替换成你查到的真实值 END;注意WITH(ROWLOCK)是锁粒度提示作用是把锁粒度压到 KEY/RID 级防止 SQL Server 因为表格扩大而自动升级成页锁或表锁。锁粒度越细锁序列越容易被观察。在这个实验里把 ROWLOCK 去掉死锁依然存在只是锁范围变大后表现会不一样。所以别以为是 ROWLOCK 导致的死锁它是为了把现场看得更清楚而加上的。再补充一个关键前提每次切换到另一组测试前最好重建表或者确认两个会话都已经停止。两个 WHILE 循环在死锁发生后SQL Server 会自动回滚受害者事务并重试所以测试连接本身不会退出但上一轮的锁残留可能影响下一轮结果。我会在每组测试前执行一次CHECKPOINT和DBCC DROPCLEANBUFFERS让缓存页和日志状态尽量一致。2.2 测试2与测试3去掉 include / 收窄类型死锁消失的两个变体测试2 的意图是把两个非聚集索引重建为不带INCLUDE(d)的版本DROP INDEX ix_a_bc ON t_deadlock_demo; DROP INDEX ix_b_cd ON t_deadlock_demo; CREATE INDEX ix_a_bc ON t_deadlock_demo(a); CREATE INDEX ix_b_cd ON t_deadlock_demo(b);执行同样的 UPDATE 循环两个会话并发跑死锁不再出现。测试3 则保留include(d)但先把字段类型从varchar(max)收窄到varchar(200)DROP INDEX ix_a_bc ON t_deadlock_demo; DROP INDEX ix_b_cd ON t_deadlock_demo; ALTER TABLE t_deadlock_demo ALTER COLUMN d VARCHAR(200); CREATE INDEX ix_a_bc ON t_deadlock_demo(a) INCLUDE(d); CREATE INDEX ix_b_cd ON t_deadlock_demo(b) INCLUDE(d);这两组变体的意义在哪里测试2 把“更新 include 列”这个动作从执行计划里彻底摘掉了测试3 把 LOB 列从行外存储变成行内存储改变了索引行的物理格式。二者都能消灭死锁说明死锁与 UPDATE 语句本身无关与 ROWLOCK 无关与并发连接同时更新同一行也无关。真正相关的变量只有一个索引维护路径。我一般会再补一个测试4 用来排除“长度变化”的干扰把 d 改成varchar(8000)保留 include。你猜结果如何死锁同样不会发生。这个现象在 SQL Server 2012 早期版本上也能稳定复现。原因后面第 4 章会细讲max_length -1的 LOB 类型走的是行外指针维护而varchar(8000)的max_length 8000属于普通行内类型可以合并进索引行的更新。所以关键判断不是“长不长”而是“是不是 LOB”。对照实验到这里已经能给出一个初步结论两个非聚集索引同时对同一个 LOB 字段做 include会让一条原本简单的单行 UPDATE 变成需要维护三份索引的工作。SQL Server 在执行计划里把它们拆成了多个独立步骤每个步骤都有自己的锁申请窗口死锁就是在这个窗口里钻出来的。3. 取证用 1222 和 SQL Trace 还原死锁现场3.1 打开死锁抓取开关dbcc traceon(1222, -1)要理解死锁第一件事是取证。SQL Server 提供了几种方式SQL Trace、1222 开关、扩展事件。在 SQL Server 2008 时代最顺手的是 1222 开关加 SQL ProfilerSQL Server 2012 之后我一般习惯用扩展事件但 1222 输出的文本仍然是理解死锁细节最完整的资料因为它会把 owner、waiter、锁资源、SPID、事务、调用栈一股脑写进错误日志。DBCC TRACEON(1222, -1); GO -- 确认开关生效 DBCC TRACESTATUS(1222, -1); GO参数说明1222 是死锁文本报告跟踪标记每次死锁发生后会把完整的锁资源列表和等待关系写入错误日志。-1 表示全局开启对所有数据库生效。生产环境开 1222 有轻微性能开销取证完成后要立刻关掉DBCC TRACEOFF(1222, -1); GO有个常见问题是只写DBCC TRACEON(1222)忘记加-1结果只有当前会话能看到死锁报告错误日志里什么都没有。排查时先DBCC TRACESTATUS确认状态这一步能省掉很多无效等待。3.2 SQL Trace 过滤只盯两个出事的 SPID因为死锁能稳定复现信息收集可以做到很全。先在两个 UPDATE 连接里分别执行SELECT SPID;假设得到的结果是 54 和 60。接下来打开 SQL Profiler新建一个 TraceEvents 勾选 Show all events 后在 Locks 分类下选择 Lock:Acquired、Lock:Released、Lock:Deadlock在 TSQL 分类下选择 SQL:BatchStarting、SQL:BatchCompletedColumn Filters 的 SPID 设置里只包含 54、60并额外加上 6 和 20 两个后台 SPID。为什么要额外带上 6 和 20因为死锁报告里经常会出现后台工作会话比如日志写入、检查点相关的进程。只过滤业务 SPID 会漏掉这些参与者导致锁序列分析时发现资源对不上号。至于具体是哪几个后台 SPID以你环境里sys.dm_exec_requests查询到的实际值为准。配置文件准备好后两个会话同时开始跑 WHILE 循环等死锁发生。这里需要注意如果过了几分钟还是没有 deadlock 事件多半是并发窗口没触发不要盲目加大循环次数先确认两个连接的 WHERE 条件指向的是同一条记录的同一个 a 值。3.3 用 sys.indexes 反向映射 hobtidKEY锁到底在哪个索引上1222 输出里 waitresource 长这样KEY: 8:72057594066108416 (ef8a9edf5a1e)冒号后面的数字是数据库 ID紧接着的 18 位数字是分区 IDhobtid括号里是 key hash。如果不做映射这一行对排查的人来说就是个黑匣子。要把它翻译成索引名用下面的查询SELECT o.name AS table_name, i.name AS index_name, i.type_desc, p.partition_id 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 IN (72057594066108416, 72057594065518592);在我本次运行的测试环境里映射结果如下你的环境生成的值会不同务必重新查询partition_id索引72057594065518592PK__t_deadlock_demo...聚集索引72057594066108416ix_a_bc72057594066173952ix_b_cd这个映射是后续所有锁序列分析的基础。我在实际工作中会提前把生产环境的索引名和 partition_id 全部导出备查遇到死锁时报出任何一个 KEY 资源五分钟内就能定位到具体索引。3.4 errorlog 里的死锁报告owner 与 waiter 是谁开启 1222 后用sp_readerrorlog查看错误日志关键段落如下deadlock-list deadlock victimprocess5e27708 process idprocess5e27708 spid60 ... transactionnameUPDATE waitresourceKEY: 8:72057594066108416 ... owner-list owner idprocess5e09dc8 modeU waiter-list waiter idprocess5e27708 modeX process idprocess5e09dc8 spid54 ... waitresourceKEY: 8:72057594065518592 ... owner-list owner idprocess5e27708 modeX waiter-list waiter idprocess5e09dc8 modeU这段输出信息量很大。spid54 持有一个 U 锁在 ix_a_bc 上正在等待聚集索引上的 U 锁spid60 持有聚集索引上的 X 锁正在等待 ix_a_bc 上的 X 锁。executionStack 里会显示 SQL 文本和 InputBuf确认死锁指令就是那两条完全相同的 UPDATE。到这里证据链已经闭合死锁不是两条相反顺序的 SELECT 加 UPDATE而是同一个索引上的 U 锁与 X 锁交叉等待。4. 机理拆解一行UPDATE为什么要申请三次X锁4.1 一次成功的UPDATE锁序列长什么样先看单会话下的一次成功 UPDATESQL Server 是怎么申请和释放锁资源的。把 SQL Trace 里 Lock:Acquired 和 Lock:Released 按时间整理得到下面这张表d 为 varchar(max)两个非聚集索引都 include 了 d阶段锁资源锁类型动作1ix_a_bcUAcquired2PK聚集索引UAcquired3PK聚集索引XAcquired4ix_a_bcUReleased5ix_a_bcXAcquired6ix_b_cdXAcquired7ix_b_cdXReleased8ix_a_bcXReleased9PKXReleased注意第 4 步ix_a_bc 上的 U 锁在聚集索引更新完成后就被释放了过一会儿又回来申请同一个索引的 X 锁。这个“先 U、放掉、再 X”的空档就是整个死锁的关键窗口。为什么需要回头申请 X 锁更新 ix_a_bc因为 d 被 include 在这个索引里而且 d 是 varchar(max) LOB 类型。索引行里不能直接保存 LOB 值只能保存指向 LOB 数据的指针。UPDATE d 时SQL Server 不仅要改聚集索引里的 LOB 数据页还要改两个非聚集索引叶子页里的指针字段。三个索引维护任务被拆成了“定位并更新聚集行”然后“更新两个非聚集索引”两个阶段锁序列自然被拉长。4.2 两个连接交错U锁与X锁的握手变成死锁把 SPID 54 和 SPID 60 放回同一条时间线上是这个案例最精华的部分时序连接1SPID 54连接2SPID 60t1申请并持有 ix_a_bc 上的 U申请并持有 ix_a_bc 上的 Ut2申请 PK 上的 U申请 PK 上的 U随后升级为 X 并完成更新t3等待 PK 锁释放 ix_a_bc 上的 Ut4趁空档拿到 ix_a_bc 上的 U进入第二步回头申请 ix_a_bc 上的 Xt5继续等待 PK 上的 U被连接1阻塞等待 ix_a_bc 的 Xt6持有 ix_a_bc 的 U等待 PK持有 PK 的 X等待 ix_a_bc 的 Xt5 到 t6 形成了完整的循环等待连接1握着 ix_a_bc 的 U 锁等待 PK连接2握着 PK 的 X 锁等待 ix_a_bc。SQL Server 死锁检测器发现这个环以后会选一个牺牲者回滚报 1205 错误。这里有个容易被忽略的点为什么连接2 在完成第一步后要释放 ix_a_bc 的 U 锁因为 SQL Server 默认在 read committed 隔离级别下单条语句内部的中间锁会在语句执行到不需要时立即释放不需要等语句结束。它以为 ix_a_bc 的定位任务已经完成U 锁可以放掉没想到下一步又要回去申请 X 锁。这个间隙给了连接1 趁虚而入的机会。如果不想让这个间隙出现最直接的优化是让连接2 在定位阶段就把 U 锁保持住比如加上WITH(HOLDLOCK)让它持有到事务结束。但这是打补丁不是根治。根治方向是把非聚集索引对 LOB include 列的多步维护拆掉。4.3 执行计划差异三个 Index Update 与一个 Update 的区别要验证上面的推断最直接的办法是看执行计划。在 UPDATE 语句前加一行SET STATISTICS PROFILE ON; GO UPDATE t_deadlock_demo WITH(ROWLOCK) SET d cd WHERE a EF211985-EA72-4A40-81DA-0AAB076E7AA3; GO SET STATISTICS PROFILE OFF; GO测试1varchar(max) INCLUDE(d)的算子摘要|--Clustered Index Update(OBJECT:([t_deadlock_demo].[PK...])) |--Index Update(OBJECT:([t_deadlock_demo].[ix_a_bc])) |--Index Update(OBJECT:([t_deadlock_demo].[ix_b_cd]))测试2去掉 INCLUDE(d)的算子摘要|--Clustered Index Update(OBJECT:([t_deadlock_demo].[PK...]))测试3varchar(200) INCLUDE(d)的算子摘要|--Clustered Index Update(OBJECT:([t_deadlock_demo].[PK...])) (内部同时包含两个非聚集索引的更新目标)三组计划差异和锁序列完全对得上。测试1 物理上有三个 Index Update 算子SQL Server 要分别申请三个目标索引上的 X 锁分步执行中间暴露释放 U 锁的窗口测试2 的 d 没有在非聚集索引中出现UPDATE 只动聚集索引锁序列只有一次握手测试3 的 varchar(200) 可以内联存储SQL Server 用一个 Update 算子合并维护三个索引一步完成没有“释放 U 后再要 X”的间隙。关于测试3 为什么能合并关键在 LOB 类型不能内联。varchar(max)、nvarchar(max)、varbinary(max)、text这些类型的max_length -1在行式索引里无法作为普通字段存进叶子页。把它们放进 INCLUDE 列时索引叶子页保存的是指向 LOB 堆的引用。更新 d 时引用本身也要写回非聚集索引SQL Server 就得为两个非聚集索引单独维护一次。改成varchar(200)后列值直接放进索引行索引维护可以合并到聚集索引更新那一拍执行计划从三算子分步变成一算子合并。顺带解释一个反直觉结论varchar(8000)也不会死锁因为它不是 LOBmax_length 8000同样可以内联。这就是“索引越多、数据类型越复杂死锁概率越高”的最直白注释。5. 死锁排查避坑指南四个最容易翻车的判断5.1 “同一行两条UPDATE语句”不等于“同一把锁”现象代码里只 update 一行业务也没有嵌套事务但并发压测时两条一模一样的语句死锁。原因同一行会被聚集索引和非聚集索引分别维护。本案例里一条 UPDATE 被拆成定位、聚集索引更新、两个非聚集索引更新锁对象是一串不是一个。锁序列存在跨索引的多段握手单纯说“单行更新不会死锁”在这里不成立。解决先跑一遍SET STATISTICS XML ON数执行计划里 Index Update 算子的数量。超过 1 个就说明锁序列存在多段维护必须把索引设计纳入排查范围。5.2 deadlock graph 里看不懂 KEY: 8:72057594066108416现象错误日志只给了KEY: 8:72057594066108416 (ef8a9edf5a1e)不知道是哪个表哪个索引。原因死锁报告里 KEY 锁显示的是 hobtid不是索引名。直接搜数字搜不出索引因为它是一个分区内部标识。解决用第 3.3 节的 sys.indexes 加 sys.partitions 映射脚本把 partition_id 翻译成索引名。我在生产环境会定期导出一份全库索引与 partition_id 对照表出问题时五分钟就能对上号。5.3 Trace 事件开了但死锁就是不进日志现象SQL Profiler 在跑1222 也开了压测跑出去就是没有 deadlock 事件。原因常见两个。一是 Filter 里只包含业务 SPID漏掉了死锁报告中出现的后台 SPID导致部分锁等待片段没被记录二是 1222 被会话级开启没加-1只有那个会话能看到报告。解决DBCC TRACEON(1222, -1)必须带-1Profiler 的 SPID 过滤里除了正在跑的业务 SPID把错误日志里出现过的所有 SPID 都放进去。另外确认死锁真的发生了如果始终没有双向阻塞多半是并发窗口没触发适当增大循环批次再试。5.4 一看死锁就上 NOLOCK结果事故更大现象加 NOLOCK 解决了某一次的 1205但报表读出中间状态或者又冒出其他死锁。原因NOLOCK 只是让读操作不去申请 S 锁它改变不了跨索引多算子更新自身的 U/X 锁重叠。本例中 U 锁参与的死锁NOLOCK 完全无效还会引入脏读、重复读风险。解决先定位执行计划和锁序列确认是本例的“索引 include LOB 字段导致多步索引维护”后正确做法是改索引设计从非聚集索引中移除不必要的 INCLUDE 字段或者把 LOB 字段改成有界的 varchar/nvarchar。临时止血可以用WITH(HOLDLOCK)保持定位阶段 U 锁到事务结束但这是补丁不是根治。6. 把死锁消灭在变更前执行计划与锁序列检查法这个案例最值钱的不是“死锁怎么解”而是“下次上线前怎么提前发现”。从那以后我只要在表结构变更里看到 INCLUDE 了一列就会强制自己走一遍下面的检查流程。第一件事跑一次 XML 执行计划数 Index Update 算子的个数。这一步专门用来发现多步索引维护SET STATISTICS XML ON; GO UPDATE t_deadlock_demo WITH(ROWLOCK) SET d cd WHERE a EF211985-EA72-4A40-81DA-0AAB076E7AA3; GO SET STATISTICS XML OFF; GO第二件事把包含 LOB 类型 INCLUDE 列的非聚集索引捞出来。下面的脚本直接查 sys.index_columns 和 sys.typesmax_length -1就是 LOBSELECT i.name AS index_name, i.type_desc, c.name AS included_column, t.name AS column_type FROM sys.indexes i INNER JOIN sys.index_columns ic ON i.object_id ic.object_id AND i.index_id ic.index_id INNER JOIN sys.columns c ON ic.object_id c.object_id AND ic.column_id c.column_id INNER JOIN sys.types t ON c.system_type_id t.system_type_id WHERE ic.is_included_column 1 AND t.name IN (varchar, nvarchar, varbinary) AND c.max_length -1;第三件事如果变更前时间允许开 1222 做一轮双会话并发循环专门验证同一行的同一条 UPDATE 是否稳定报错。这个动作我习惯写进压测脚本里不靠人工肉眼看概率。三条检查按顺序做完这类死锁基本可以在上线前暴露。如果已经上线了在救火我优先选改索引设计而不是 HOLDLOCK 或 PLAN GUIDE。让 SQL Server 少维护一个索引比事后想办法固化执行计划要稳得多。还有一个收尾技巧值得记住测试环境里把 UPDATE 的 WHERE 键从 a 换成 b或者换成聚集索引主键 id锁序列会完全不同。用主键定位时第一步直接命中聚集索引非聚集索引的维护动作排在后面死锁窗口也会变化。所以做复现实验时尽量模拟真实业务里的定位路径别为了方便改 WHERE 条件。这个案例给我的教训一直留到现在遇到死锁先别急着骂业务或者加提示先把执行计划的算子数和锁序列画出来大多数死锁都是索引设计埋的雷。希望帮到你。本文还有配套的精品资源点击获取