企业官网建设流程全解析

简介：围绕 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 victim=process5e27708 process id=process5e27708 ... lockMode=X ... spid=60 process id=process5e09dc8 ... lockMode=U ... spid=54 resource-list keylock hobtid=72057594066108416 ... indexname=ix_a_bc ... mode=U owner id=process5e09dc8 mode=U waiter id=process5e27708 mode=X keylock hobtid=72057594065518592 ... indexname=PK__tt__3213E83F10E07F16 ... mode=X owner id=process5e27708 mode=X waiter id=process5e09dc8 mode=U

这段内容能直接看到锁等待方向：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 是索引对象 ID，ObjectID2 是 hobtid，也就是 1222 报告里的那个 15 位数字。

3.3 hobtid 到索引名的精确映射

1222 输出的keylock hobtid=72057594066108416一串数字难以直接看出是哪个索引。常规做法是查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 的锁申请顺序

从这张时间线能看到两个关键阶段。第一阶段，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 报告和上表，死锁的直接因果关系能描述得很具体：

• 连接 A（spid 54）完成第一阶段，在ix_a_bc上持 U 锁做了 Index Seek，然后准备对 clustered index 上的目标行申请 U 锁时，发现该行已被连接 B 加了 X 锁。
• 连接 B（spid 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 Maintenance

set statistics profile on go update tt with(rowlock) set d = 'cd' where a = 'EF211985-EA72-4A40-81DA-0AAB076E7AA3'

set 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，SQL 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 里移除。

这里可以套用我在这个案例里沉淀下的检查清单：

1. 数 Index Update 算子数量，超过 2 个标黄。
2. 检查涉及更新的索引里有没有 include 正在被修改的列。
3. 检查被修改列的数据类型，varchar(max)、nvarchar(max)优先标红。
4. 模拟两个并发会话，用循环 UPDATE 验证是否存在锁等待，不要只测单会话执行。

把varchar(max)从索引里移除，或者改成nvarchar(4000)这类带长度限制的类型，通常是最直接的优化。如果业务需要长文本字段的覆盖索引，考虑改用nvarchar(4000)加上全文索引，或者把长文本拆表存放。

那以后我每次做表结构评审，都会把“这张表有哪些索引包含了大对象列”当成必查项目。这个案例也让我养成了一个习惯：所有新增索引的脚本，都先跑一次set statistics profile on验证 UPDATE 和 DELETE 的执行计划形状，确认没有多算子索引维护，再进入发布流程。对于死锁这种问题，事后排查的能力很重要，但从执行计划里提前看见风险，才是更省时间的做法。希望帮到你。

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