简介围绕 SQL Server 中一个看似反常的 Deadlock 案例这份资源面向数据库开发、运维与性能优化人员系统梳理了死锁的产生条件与完整分析路径。文档从一张同时带有聚集索引和两个非聚集索引的示例表出发先复现 update 语句在 rowlock 下的互相阻塞再对比取消非聚集索引中的 include(d) 字段、将 d 列由 varchar(max) 改为 varchar(200) 两种调整从而解释索引键长度、锁粒度与死锁触发之间的关系。分析环节覆盖 DBCC TRACEON(1222) 开关、SQL Profiler 按 SPID 筛选并捕获 Locks/TSQL 事件、sp_readerrorlog 读取死锁报告以及通过执行堆栈定位等待资源等典型方法能让读者形成一套可复用的死锁排查思路。压缩包内为 1 个 docx 文档容量约 694KB内容紧凑便于对照学习。该资源已有 326 人学习下载适合想深入理解 SQL Server 索引、锁等待与死锁机制的从业者参考。1. 这个死锁怪在哪儿两条一模一样的 UPDATE锁死了自己人先说结论SQL Server 里两条完全相同的 UPDATE 语句在并发执行时发生了死锁。更诡异的是把索引里的include(d)去掉或者把d从varchar(max)改成varchar(200)死锁就消失了。这听起来像是索引或者数据类型的“玄学”但背后是 SQL Server 执行计划对锁申请顺序的决定性影响。这个案例是我见过最典型的“死锁不是等行锁而是等执行计划里那几步锁”的教材。本文从复现步骤开始完整走一遍 1222 跟踪、SQL Profiler 锁事件分析、hobtid 定位索引、执行计划对比这几板斧最终解释清楚死锁形成的完整链路。适合正在处理 SQL Server 死锁、或者被“同样的语句为什么这里锁那里不锁”困扰的 DBA 和开发人员。2. 复现这个死锁从表结构到并发触发脚本2.1 三张索引的物理布局为什么它是死锁的温床原案例在 SQL 2008 上复现我这边在 SQL Server 2019 兼容级别 100 的库上也跑通了。核心在于这张表的结构——它刻意制造了一个“更新一条记录但索引链条很长”的场景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这里有两个关键设计。第一id上的主键是 clustered index所以表本身按id物理排序更新d字段时基础数据行的位置是确定的。第二ix_a_bc和ix_b_cd是 non-clustered index而且都在叶子节点里include(d)了。这就意味着当UPDATE修改d时SQL Server 不仅要改 clustered index 里的数据行还得改这两个 non-clustered index 的叶子页——因为它们的叶子节点里存了这个字段的副本。varchar(max)的存在也很微妙。max 类型在大对象处理上有特殊的存储和锁行为它不会老老实实地把所有数据塞进索引页而是可能采用单独的大对象分配单元。这一步给后面的问题埋了个大雷。所以在动手复现之前先确认自己的环境里表和索引的实际物理布局而不是看逻辑结构想当然。2.2 插入一万行并定位一条“幸运”记录insert into tt select NEWID(),bbb,ddd go 10000这个go 10000在 SSMS 里表示执行 10000 次插入用NEWID()生成a列的随机值。a列是char(36)数据库会用空格补齐尾部的差异但查询时自动截断所以不影响匹配。插入完成后取第 10 条记录的a值作为后续 UPDATE 的定位条件select * from tt where id 10这里有个值得说明的点这一万条数据中a列的值全部通过NEWID()生成是唯一的而且因为随机分布ix_a_bc索引的键值排序会比较分散。这种离散性直接导致后续 UPDATE 语句在索引上寻址时会经历 B-tree 的逐层定位而不是聚集扫描。我一般会顺手跑一下DBCC SHOW_STATISTICS看看直方图确保a列的分布能让优化器选择 Index Seek否则整个复现路径就变了。2.3 两条死锁循环rowlock 与“平均分配”的假象-- 连接 1 while 1 1 update tt with(rowlock) set d cd where a EF211985-EA72-4A40-81DA-0AAB076E7AA3 -- 连接 2 while 1 1 update tt with(rowlock) set d cd where a EF211985-EA72-4A40-81DA-0AAB076E7AA3两个连接各自开一个查询窗口同时执行这段循环死锁在几秒内必然出现。with(rowlock)是刻意加上的它限定 SQL Server 只在行级加锁避免锁升级到页锁或表锁后掩盖掉真正的问题。while 1 1让 UPDATE 无限循环重复执行同一行的更新。在实际复现时不要两个窗口都手动点执行然后碰运气。更稳妥的做法是先用一个连接把循环跑起来确认单连接不会自锁再启动第二个连接。批量测试时我会用两个sqlcmd会话加上START时间对齐尽量模拟同一时刻的并发提交。这个场景下循环体没有事务包裹每条 UPDATE 是隐式事务但死锁检测器依然能捕捉到锁等待环。2.4 对比实验改动索引或类型死锁为什么消失原案例里另外两个测试构成了对照。测试二把两个 non-clustered index 里的include(d)去掉测试三把d改为varchar(200)。两者都不再死锁。在执行计划层面这两次改动的共同点是UPDATE 不再需要对三个索引分别做 X 锁申请SQL Server 只更新 clustered index 主数据行。提示include(d)不是“附带一个字段”这么简单它让索引叶子节点物理包含这个字段的副本。UPDATE 修改该字段时所有包含该字段的索引对应的叶子页都要维护。维护的索引越多锁的生命周期越长死锁概率越高。把varchar(max)换成varchar(200)不再死锁的原因稍微不同后续章节会详细展开执行计划的差异。复现阶段把这个对照组跑一遍能很快让新手体会到“死锁不是靠猜是靠执行计划锁序”。3. 收集死锁证据打开 1222 开关与 SQL Trace 抓锁事件3.1 开启 1222 跟踪死锁报告的“黑匣子”SQL Server 默认把死锁信息写到错误日志但只记录受害者会话的部分信息。开启 1222 跟踪后日志里会输出完整的死锁图包括两个参与进程的锁等待链、资源模式、SQL 文本和事务时间线。操作如下dbcc traceon (1222, -1)-1表示全局开启对所有会话生效而不是只对当前会话。开启后死锁一旦发生错误日志会记录一段以deadlock-list开头的文本。关闭跟踪用dbcc traceoff (1222, -1)。注意生产环境开启 1222 会有轻微性能开销建议只在复现窗口期内开启用完即关。sp_readerrorlog是读错误日志最快的入口sp_readerrorlog 0, 1, deadlock-list第三个参数可以过滤关键字。实际输出里的死锁报告关键部分长这样子deadlock victimprocess5e27708 process idprocess5e27708 ... lockModeX ... spid60 process idprocess5e09dc8 ... lockModeU ... spid54 resource-list keylock hobtid72057594066108416 ... indexnameix_a_bc ... modeU owner idprocess5e09dc8 modeU waiter idprocess5e27708 modeX keylock hobtid72057594065518592 ... indexnamePK__tt__3213E83F10E07F16 ... modeX owner idprocess5e27708 modeX waiter idprocess5e09dc8 modeU这段内容能直接看到锁等待方向spid 60 持有主键上的 X 锁等待ix_a_bc上的 X 锁spid 54 持有ix_a_bc上的 U 锁等待主键上的 U 锁。这就是一个典型的环状等待。但 1222 不会告诉你锁的申请顺序——为什么两个进程各自持有第一个锁之后会去申请对方的锁3.2 通过 SPID 过滤 SQL Profiler 的 Locks 事件要还原锁的申请顺序SQL Trace 是更好的工具。SQL Profiler 新建一个 Trace在事件选择里勾上 Show all events 和 Show all columns然后从 Locks 类别选Lock:Acquired、Lock:Released、Lock:Deadlock等事件从 TSQL 类别选SQL:BatchStarting和SQL:BatchCompleted。Column Filters 里按 SPID 过滤只保留死锁涉及的两个连接外加后台进程的 SPID。select spid在死锁发生前分别到两个连接里执行这条语句拿到各自 SPID。原案例里一个是 54一个是 60。过滤时只选这两个 SPID 加系统进程的 ID。Trace 输出里Lock:Acquired 和 Lock:Released 的 Mode、ObjectID、ObjectID2 是判断锁类型的入口。ObjectID 是索引对象 IDObjectID2 是 hobtid也就是 1222 报告里的那个 15 位数字。3.3 hobtid 到索引名的精确映射1222 输出的keylock hobtid72057594066108416一串数字难以直接看出是哪个索引。常规做法是查sys.indexes、sys.objects和sys.partitions三张系统视图的关联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 ( 72057594065518592, 72057594066108416, 72057594066173952 )这条查询把跟踪日志里的hobtid转成表名和索引名。partition_id就是 hobtid每个索引的每个分区有独立值。注意 SQL Server 2008 以后一个索引对应一个分区时hobtid和partition_id一一对应如果表做了分区同一个索引会有多个hobtid排查时需要结合partition_number去辨别实际命中了哪个分区。提示把这条查询里的partition_id换成object_id也适用于sys.dm_db_index_operational_stats这类 DMV 的排查场景能直接看到每个索引上的等待统计。3.4 锁申请时间线谁先 U 锁谁后 X 锁从 Profiler 拉出来的 Lock:Acquired 和 Lock:Released 记录按时间排序后一次成功的 UPDATE 的锁生命周期清晰可见表 3-1一次成功 UPDATE 的锁申请顺序时间序索引锁类型会话动作1ix_a_bcUAcquired2PKclusteredUAcquired3PKclusteredXAcquired4ix_a_bcUReleased5ix_a_bcXAcquired6ix_b_cdXAcquired7ix_b_cdXReleased8ix_a_bcXReleased9PKclusteredXReleased从这张时间线能看到两个关键阶段。第一阶段UPDATE 语句通过ix_a_bc的 Index Seek 定位到符合条件的记录期间对ix_a_bc加 U 锁确认记录存在后再对 clustered index 的主键行加 U 锁然后升级成 X 锁执行更新。第二阶段SQL Server 发现d字段被两个 non-clustered index 的叶子页包含于是回过头来为两个索引补 X 锁维护索引数据。这个“先更新主表再回来更新索引”的二次寻路过程就是死锁的温床。4. 死锁形成链路执行计划里的 Index Update 与两步走的陷阱4.1 锁环怎么套上的连接 A 等主键、连接 B 等辅助索引结合 1222 报告和上表死锁的直接因果关系能描述得很具体连接 Aspid 54完成第一阶段在ix_a_bc上持 U 锁做了 Index Seek然后准备对 clustered index 上的目标行申请 U 锁时发现该行已被连接 B 加了 X 锁。连接 Bspid 60恰好走完第一阶段正在第二阶段对ix_a_bc补 X 锁但ix_a_bc上有连接 A 的 U 锁。于是连接 A 等主键连接 B 等辅助索引形成完整环形等待SQL Server 死锁检测器挑一个牺牲品回滚。这里有个耐人寻味的细节连接 B 在第一阶段结束时释放了ix_a_bc的 U 锁连接 A 才申请到 U 锁然后连接 B 在第二阶段又回来抢 X 锁。也就是说同一个索引、同一个键的锁在同一个事务里被释放后又被不同模式重新申请。这个释放后重申请的窗口恰恰是另一个连接插入的时机。4.2 锁申请与释放的“两步走”Index Update 与 Lazy Index Maintenanceset statistics profile on go update tt with(rowlock) set d cd where a EF211985-EA72-4A40-81DA-0AAB076E7AA3set statistics profile on输出文字形式的执行计划每行代表一个物理操作。测试一中能看到三个Index Update它们的父节点是同一个Update说明 SQL Server 将主键更新、ix_a_bc更新、ix_b_cd更新拆成了三个独立的物理操作。测试二中辅助索引不包含d字段所以只有一个Index Update锁申请链路缩短死锁消失。测试三中d为varchar(200)时执行计划里只有一个 Update 节点但它的 child 节点同时包含三个对象的操作说明引擎在一步内完成了三处索引维护。测试一这种“先更新主表、再逐个更新二级索引”的行为在内部叫作 Lazy Index Maintenance它把索引维护推迟到了主表更新之后带来的代价就是多阶段锁申请。varchar(200)之所以能避免死锁本质是优化器计算出大对象不再需要单独的行外存储索引叶子页可以容纳该字段于是选择了 eager index maintenance——在一个算子内完成全部更新锁生命周期被压缩到极小。4.3 为什么 varchar(max) 放进 include 会催化这个锁环varchar(max)与普通varchar在存储上有本质区别max 类型默认使用 large object 分配单元数据可能存储在行外。当它被include进非聚集索引后索引维护算子需要处理行外数据的定位和搬迁这一步比普通定长字符串的原地更新昂贵得多。优化器在代价估算中发现行外存储的更新不值得交付给单个算子做于是把辅助索引的更新拆成独立算子形成了那三个 Index Update。注意不要只关注死锁本身。把varchar(max)放进索引的叶子节点本身就意味着每一次UPDATE都要维护 LOB 指针写入放大效应明显。死锁只是暴露了这个问题真正的改进方向是索引设计不合理。4.4 执行计划对比三个方案的锁数量与死锁概率把三次测试的执行计划并排看差异非常直观表 4-1三次测试的执行计划与锁行为对比场景d 列类型索引是否 include(d)执行计划中的 Update 节点死锁是否发生测试一varchar(max)是1 个 Update 3 个 Index Update是测试二varchar(max)否1 个 Update 1 个 Index Update否测试三varchar(200)是1 个 Update内部合并更新三个索引否测试二的锁少是因为辅助索引里没有dSQL Server 根本不需要碰它们。测试三的锁数量和测试一相同但申请顺序从“两步走”变成了“一步到位”锁等待窗口消失。这个对比印证了一件事死锁的关键变量不是锁的数量而是锁的申请持续时间和顺序。5. 避坑指南分析死锁时最容易翻车的五个细节5.1 只看 1222 报告就下结论漏掉锁申请顺序是最大的坑现象1222 报告清楚显示了两个进程各自持有什么锁、等待什么锁但看完了还是不知道为什么持有这些锁也没法解释死锁成因。原因1222 是死锁发生瞬间的静态快照它不包含锁的申请时间线。解决必须配合 SQL Profiler 的 Lock:Acquired 和 Lock:Released 事件按时间排序还原锁生命周期才能真正理解死锁形成链路。我从这案例里学到的习惯是所有死锁分析都至少保留一份锁事件跟踪否则只能停留在“看到环”的层面。5.2 用 spid 过滤 Trace 时漏掉系统进程现象Trace 里只看到两个死锁会话的锁事件但关键的中间环节缺失锁的时序断档。原因SQL Server 的锁管理和死锁检测还涉及多个后台系统会话比如 LOCK_MONITOR 等这些进程的锁事件也应该被捕获。解决在原案例中过滤条件里除了 54 和 60还应该加上 6 和 20 这类关键系统 SPID。一般做法是先启用 Locks 事件并不过滤发生死锁后用小范围的重放来定位避免第一次 Trace 就漏数据。5.3 用 ObjectID 而不是 ObjectID2 关联索引现象Trace 里 ObjectID 是索引的 object_id去看 SysIndexes 时匹配到的是表而不是具体索引定位错误。原因SQL Server 在锁事件里的 ObjectID 指的是对象 ID而 ObjectID2 才是 hobtid也就是 1222 报告里 keylock 后面那串数字。解决直接用 ObjectID2 去关联 sys.partitions.partition_id。这是我做死锁排查时用的固定脚本每次 Trace 之后立即执行避免靠记忆硬编码。5.4 把 rowlock 提示当成解决死锁的银弹现象给 UPDATE 加上 with(rowlock) 后死锁依然发生。原因rowlock 只是告诉优化器行级锁是允许的它不改变执行计划的形状也不改变锁申请顺序。解决用它复现问题时是为了防止锁升级掩盖掉真实冲突解决死锁还是得靠执行计划调优、索引调整或事务边界改造。不要在产品代码里依赖锁提示来“修死锁”后续维护是噩梦。5.5 测试“去掉 include(d)” 后顺手删了索引破坏了原有查询性能现象去掉 include(d) 后死锁消失但原有查询变慢甚至从 Index Seek 变成 RID Lookup。原因include 列本来是为了覆盖常用查询移除后 leaf 页不包含 d 字段查询定位到索引条目后还要回表。解决这个场景下更稳妥的改法是把varchar(max)改成varchar(8000)或者直接改成varchar(200)保留 include 的结构同时消除大对象的行外存储问题。要记住死锁是索引设计不合理的最外层症状不要只治症状。6. 从执行计划预判死锁用 Include/Exclude 索引反向检查锁序这个案例给了我一个很实用的方法论在代码评审阶段用set statistics profile on或set statistics xml on看 UPDATE 语句的输出数一下Index Update算子数量。一个 UPDATE 如果对应多个Index Update就意味着多个物理操作分散在事务时间轴上锁的持有时间会被拉长死锁风险随之上升。这个方法在表结构变更前就能发现问题不需要等死锁真的发生。具体的操作流程是先拿到目标 UPDATE 语句在测试库上执行set statistics profile on把输出保存下来。然后手工统计 Update 节点下的 Index Update 数量以及涉及的索引名。如果数量大于等于 2就要警惕。下一步结合索引定义检查看这些索引的叶子节点是否包含了 UPDATE 会修改的字段。如果包含就得评估这些索引是否可以去掉或者把大字段从 include 里移除。这里可以套用我在这个案例里沉淀下的检查清单数 Index Update 算子数量超过 2 个标黄。检查涉及更新的索引里有没有 include 正在被修改的列。检查被修改列的数据类型varchar(max)、nvarchar(max)优先标红。模拟两个并发会话用循环 UPDATE 验证是否存在锁等待不要只测单会话执行。把varchar(max)从索引里移除或者改成nvarchar(4000)这类带长度限制的类型通常是最直接的优化。如果业务需要长文本字段的覆盖索引考虑改用nvarchar(4000)加上全文索引或者把长文本拆表存放。那以后我每次做表结构评审都会把“这张表有哪些索引包含了大对象列”当成必查项目。这个案例也让我养成了一个习惯所有新增索引的脚本都先跑一次set statistics profile on验证 UPDATE 和 DELETE 的执行计划形状确认没有多算子索引维护再进入发布流程。对于死锁这种问题事后排查的能力很重要但从执行计划里提前看见风险才是更省时间的做法。希望帮到你。本文还有配套的精品资源点击获取