企业官网建设流程全解析

简介：围绕SQL Server中一例看似奇怪、实则很有分析价值的Deadlock，这份docx技术文档面向数据库管理员与后端开发人员，聚焦索引与锁机制引发的死锁排查。内容给出可稳定重现的实验：表tt上clustered index与两个non-clustered index的include(d)，以及把d从varchar(max)改为varchar(200)后死锁消失的对比，帮助读者理解执行计划、键锁与rowlock之间的交互。文档还整理了两类典型分析路径：开启1222跟踪标记收集死锁信息，结合SQL Profiler按SPID过滤Locks与TSQL事件，并读取errorlog中的deadlock-list进行进程、等待资源与执行栈解读。资源包仅含1个docx，约694KB，轻量便于查阅；目前已有326人学习。适合希望掌握死锁复现、证据收集与根因定位方法的中高级SQL Server从业者。

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

在 SQL Server 的 Deadlock 排查里，最难缠的从来不是“两个事务更新顺序相反”这种人尽皆知的模型，而是两条字节级完全相同的语句、走同一个索引、连显式事务都没写，却在几秒内把对方拖进 1205（Transaction (Process ID xx) was deadlocked on lock resources with another process and has been chosen as the deadlock victim）。我遇到的这个案例就是这样：两个连接对着同一张表跑同一条update ... with(rowlock)，逻辑上互不相干，跑起来却必死。这类故障在“覆盖索引 + 大字段”的组合上尤其常见，线上表现是偶发 1205、业务侧反复重试，日志里只有一张看不出因果的死锁图。下面这套从复现、抓现场、读锁时序到反推执行计划的路径，适合手上正卡在死锁图看不懂的后端开发与 DBA。

2. 复现：三组对照实验，把“偶发”变成“必然”

死锁之所以难查，很大一部分原因是线上没法稳定重现。但这个案例有一个非常好的性质：只要表结构和数据类型对得上，两个连接各跑一个循环，死锁就一定发生。所以在动手分析之前，先把“能稳定复现”这件事做扎实，后面所有推断才有对照组可依。

2.1 建表：一个聚簇索引加两个带 include 的非聚簇索引

整个问题的物理基础就三张索引：一个聚簇主键，两个带include(d)的非聚簇索引，而d又是varchar(max)。这三样东西凑齐，坑就挖好了。

create table tt( id int identity primary key, -- 聚簇索引，后面日志里显示为 PK__tt__3213E83F... a char(36), b char(36), d varchar(max) -- 大字段，被 include 进了两个非聚簇索引 ); go create index ix_a_bc on tt(a) include(d); create index ix_b_cd on tt(b) include(d);

先说清三个索引的定位：PK__tt__...是聚簇索引，存的是整行数据；ix_a_bc和ix_b_cd是非聚簇索引，本来只存a（或b）加主键，因为写了include(d)，才把这个varchar(max)也一并塞进去。这意味着d一旦被修改，这两个非聚簇索引都得跟着改，而d又是 LOB 类型，维护成本比普通列高得多。这两个事实，是后面死锁和执行计划的共同起点。

2.2 造数据，取出那条一定会互锁的 UPDATE

先灌一万行，让表有足够的页和随机键值，避免所有行挤在一页上掩盖问题。

insert into tt select NEWID(), 'bbb', 'ddd'; go 10000

go 10000表示把上一条语句重复执行一万次，这是构造测试数据的常用写法，比单条insert ... select循环更省事。然后随便挑一行，把它的a值取出来作为更新条件：

select * from tt where id = 10; -- 假设返回的 a 是 'EF211985-EA72-4A40-81DA-0AAB076E7AA3' -- 这是 NEWID() 生成的值，每次重建表都不一样，照抄会导致复现失败

拿到a值之后，开两个查询窗口，各自执行下面这个死循环：

while 1 = 1 update tt with(rowlock) set d = 'cd' where a = 'EF211985-EA72-4A40-81DA-0AAB076E7AA3';

这里有两个细节必须说清。第一，where a = ...会走ix_a_bc这个非聚簇索引来定位行，而不是走聚簇索引扫表，这决定了后续加锁的顺序。第二，with(rowlock)只是把锁粒度钉在行级，它不改变加锁的先后次序，也不阻止键锁升级，所以它在这里形同虚设——这一点是很多人的第一层误解，后面避坑章节还会展开。两个窗口同时跑，几秒内就会有一边收到 1205。

2.3 两组反证：去掉 include、改字段类型，死锁就消失

光有“会死锁”还不够，得有两个“不死锁”的对照组，才能把变量锁定。第一组，把include(d)去掉，索引只保留键列：

drop index ix_a_bc on tt; drop index ix_b_cd on tt; create index ix_a_bc on tt(a); -- 不再 include(d) create index ix_b_cd on tt(b); -- 不再 include(d)

重建完成后，用同样的两个窗口跑同样的死循环，死锁不再出现。第二组，把include(d)加回来，但把d的类型从varchar(max)改成一个有长度上限的varchar(200)：

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);

同样跑循环，死锁也不出现了。注意这两组实验改的是两个完全不同的变量——一个是“索引里有没有这一列”，一个是“这一列是什么类型”，它们都能让死锁消失，说明触发条件不是单点，而是“大字段被 include 进非聚簇索引”这个组合。

2.4 先把三组结果钉死，再谈解释

把三组实验的结果整理成一张表，后面每一步分析都要能对上这张表：

这三个结果本身并不能解释任何东西，它们只是把“玄学”压缩成了一个可以验证的命题：当d同时满足“是 LOB 类型”和“被 include 进非聚簇索引”两个条件时，同一条 UPDATE 的并发执行就会互锁。要回答“为什么”，靠猜没用，必须拿到死锁发生瞬间的锁信息和锁的申请释放时序。这就是下一步要做的事。

3. 抓现场：1222 开关与 Profiler 锁事件怎么配才抓得全

死锁的分析材料有两种来源，各有所长：1222 跟踪标记给你一份死锁发生瞬间的“快照”，告诉你谁持有什么锁、谁在等谁；SQL Trace 给你一条“时间线”，告诉你这些锁是按什么顺序申请和释放的。只拿其中一样，往往会卡在“知道结果不知道为什么”上。这个案例能稳定复现，所以两种材料我都收了。

3.1 开 1222，让死锁自己写进 errorlog

dbcc traceon (1222, -1);

1222的作用是让 SQL Server 在发生死锁时，把详细死锁信息写进错误日志；-1表示对所有连接全局生效，而不是只对当前会话。它比老式的1204输出更完整，会带上resource-list，也就是每个被争用的资源的持有者和等待者，这正是定位的关键。两个注意点：这个开关只在实例重启前有效，要持久化得改成启动参数-T1222；另外它只在死锁真的发生时才有输出，不能替代时序跟踪。

顺带说一个现在更省事的做法：system_health会话默认就在捕获xml_deadlock_report，不用额外开任何东西，直接从环形缓冲区里捞就行。

-- 从默认的 system_health 会话里取出最近的死锁报告 WITH x AS ( SELECT CAST(t.target_data AS XML) AS target_xml FROM sys.dm_xe_session_targets t JOIN sys.dm_xe_sessions s ON s.address = t.event_session_address WHERE s.name = 'system_health' AND t.target_name = 'ring_buffer' ) SELECT evt.value('@timestamp', 'datetime') AS event_time, evt.query('data/value/deadlock') AS deadlock_graph FROM x CROSS APPLY target_xml.nodes('//event[@name="xml_deadlock_report"]') AS n(evt);

target_data是一段 XML，nodes()按事件名切出每一条死锁记录，query()再把里面的<deadlock>节点原样取出来。这个方法的好处是不用提前准备，坏处是ring_buffer容量有限、实例重启就清空，长期观测还是得配一个文件目标的扩展事件会话。两种方式拿到的死锁图内容是一致的，只是封装不同。

3.2 Profiler 里选哪些事件、哪些列

开 Trace 之前，先在两个跑循环的连接里各执行一次select @@spid，把两个 SPID 记下来（案例中是 54 和 60），这是后面做过滤器用的。新建 Trace 时，先把 “Show all events” 和 “Show all columns” 都勾上，否则很多关键列根本不出现在列表里。事件方面，Locks分类下至少要选Lock:Acquired、Lock:Released、Lock:Deadlock、Lock:Deadlock Chain，这几条合起来才能还原锁的生命周期；TSQL分类下选语句开始类的事件，用来把锁事件对回具体语句。然后在 Column Filters 里把 SPID 限制为 54、60 以及几个系统 SPID，避免把整个实例的锁流量都灌进来。

选完事件之后还有一步很容易被忽略：把Mode、ObjectID、ObjectID2、Type这四个列拖到列显示区的前面。因为Lock:Acquired和Lock:Released的默认列顺序非常难读，一屏几十个字段，锁模式和对象 ID 全藏在后面，不调整的话根本没法按时间顺序扫一遍。

3.3 从 executionStack 确认两条语句是同一句

打开 1222 写进 errorlog 的那段内容，重点看两处。第一处是process-list，两个进程各自带着spid、waitresource、lockMode三个关键字段：

process id=process5e27708 ... waitresource=KEY: 8:72057594066108416 (ef8a9edf5a1e) lockMode=X ... spid=60 ... process id=process5e09dc8 ... waitresource=KEY: 8:72057594065518592 (d08358b1108f) lockMode=U ... spid=54 ...

waitresource的格式是“资源类型: 数据库 ID: 对象 ID (哈希)”，这里的对象 ID 就是后面要翻译的 hobtid。第二处是executionStack，它把出问题的那条语句原样贴了出来，两条都是update tt with(rowlock) set d='cd' where a='...'，可以确认死锁确实发生在两条完全相同的 UPDATE 上，而不是中间夹了触发器或者别的语句。

3.4 把 ObjectID2 翻译成索引名

拿到72057594065518592和72057594066108416这两个数字之后，得知道它们分别是哪张索引，否则没法判断谁在锁谁。

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;

这里用partition_id去匹配，是因为单分区表下它和 hobt_id 数值一致，是最省事的写法。如果是分区表，partition_id和 hobt_id 就对不上了，得改用sys.partitions.hobt_id去关联，否则查出来的索引名是错的——这个坑我踩过一次，白白多花两小时。跑完对照关系就清楚了：72057594065518592是聚簇索引PK__tt__...，72057594066108416是ix_a_bc，72057594066173952是ix_b_cd。

3.5 推一遍加锁时序：U 锁释放的那一刻就是缺口

把resource-list的结论翻译成人话：连接 1（SPID 54）在ix_a_bc上持有 U 锁，正在等聚簇索引上的 U 锁；连接 2（SPID 60）在聚簇索引上持有 X 锁，正在等ix_a_bc上的 X 锁。这就是标准的环形等待，但关键疑问还没解决——两条一模一样的语句，怎么会一个持 U 一个持 X，还正好错开？

答案在 Profiler 抓到的Lock:Acquired/Lock:Released时序里。把一次正常 update 的锁轨迹摊开，可以看到 SQL Server 其实分了两步：第一步用ix_a_bc定位到行，拿到ix_a_bc上的 U 锁和聚簇索引上的 U 锁，再升级成 X 锁把聚簇索引更新掉；第二步，因为d被 include 进了两个非聚簇索引，还要分别去更新ix_a_bc和ix_b_cd，各申请一次 X 锁。而第一步结束时，ix_a_bc上的 U 锁被释放了——正是这个释放，把连接 1 放了进来。

所以真正的问题不是“谁先开始”，而是“第一步和第二步之间存在一个锁释放的窗口”。只要更新被拆成两步，这个窗口就必然存在，两条相同的语句就必然有机会一前一后错开，形成“我等你手里的聚簇索引、你等我手里的非聚簇索引”。反过来，如果更新只有一步，锁在语句结束前一直不松手，就不会有这个交叉点。

4. 执行计划：为什么加锁从一步变成了三步

到这一步，1222 的输出已经把“怎么锁的”讲清楚了，但“为什么要分两步”还没答案。而这个问题不解决，就没法给出真正管用的改法——把 include 去掉确实能解决，但代价是牺牲覆盖索引带来的查询性能，得先弄清楚有没有更优的选择。

4.1 用 statistics profile 拿到文字版执行计划

最轻量的方式是直接在 update 前加一句开关，让执行计划以文本形式输出到消息窗口：

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

statistics profile on输出的是语句级实际执行计划，每一行带rows、executes、st（算子自身的开销）等统计，比图形化计划更容易对比。三种表结构下各跑一遍，把输出留着逐行比对——图形界面切换来切换去，反而容易看漏Index Update的个数。

4.2 三份计划的差异：Index Update 的数量

测试 1（varchar(max)+ include）的计划里有三个Index Update算子，分别对应聚簇索引和两个非聚簇索引。这直接解释了为什么一次 UPDATE 要申请三次 X 锁：每个Index Update都要对自己的目标索引加锁，而且是分开加的，不是一次性打包。

测试 2（varchar(max)、不 include）的计划里只剩一个Index Update，只更新聚簇索引。因为d不在两个非聚簇索引里，改d完全不影响它们，自然不需要额外加锁。这也是它不死锁的原因。

测试 3（varchar(200)+ include）的计划最有意思：结构上仍然只有一次 update，但计划里这一个算子同时带了三个对象。也就是说，SQL Server 把三个索引的更新合并成了一个步骤完成，中间没有释放锁的间隙，所以同样不死锁。

4.3 为什么 varchar(max) 会把更新拆成三步

从计划反推，合理的解释是：varchar(max)属于 LOB 类型，它的值不保证能塞在数据页的行内，可能放在单独的 LOB 页上，还涉及text in row之类的存储细节。当这样一个列同时存在于聚簇索引和两个非聚簇索引中时，优化器没法把三处的更新合并成一次原子操作，只能为每个索引各生成一个Index Update，顺序执行。而varchar(200)是定长上限的行内类型，三处更新可以在同一个算子里一起完成。这个机理在死锁日志里是看不到的，只能从执行计划的形态倒推——但它足够解释三组实验的全部差异。

提示：执行计划的形态会随统计信息、数据分布、SQL Server 版本变化，不能只凭一次计划就下“一定是这个原因”的结论。稳妥的做法是像上面这样，用三组对照实验把计划形态和锁行为一一对上。

4.4 改法：从索引里摘掉 d，或者给 d 一个长度上限

回到解法。第一种，也是这个案例里最推荐的：把d从两个非聚簇索引的 include 里摘掉。

drop index ix_a_bc on tt; drop index ix_b_cd on tt; create index ix_a_bc on tt(a); create index ix_b_cd on tt(b);

代价是这两个索引不再是覆盖索引，原本只查a、b却要带出d的查询，会退化成键查找，多一次回表。所以动手前先用sys.dm_db_index_usage_stats看一眼这两个索引的 seek 和 scan 次数，确认它们主要服务的是“只要 a、b 两列”的查询，而不是真的在靠 include 省回表。

-- 看两个非聚簇索引的实际使用情况，再决定要不要拆 include select i.name, s.user_seeks, s.user_scans, s.user_lookups, s.user_updates from sys.indexes i left join sys.dm_db_index_usage_stats s on s.object_id = i.object_id and s.index_id = i.index_id and s.database_id = db_id() where i.object_id = object_id('tt') and i.type = 2;

user_seeks和user_scans是读的次数，user_updates是因写操作维护这个索引的次数。如果某个非聚簇索引的user_updates远大于读次数，说明它的维护成本已经超过收益，那 included就是纯负担。

第二种改法：如果业务上d确实有长度上限，把类型收窄到varchar(n)。

alter table tt alter column d varchar(200);

这个改动会让 UPDATE 重新合并成一步，死锁消失，同时保留覆盖索引的读性能。但前提是得先确认现有数据没有超过这个长度，否则alter column会直接报截断错误：

-- 改类型之前先量一下最长值，避免 alter 失败 select max(len(d)) as max_len from tt;

如果历史数据里混进了超长值，得先清洗再改类型，或者干脆走第一种方案。两种改法没有绝对优劣，取决于这张表的读写比例和d的真实用途。

5. 避坑：这几条经验都是用死锁换来的

5.1 加了 WITH (rowlock) 就以为万事大吉

现象：语句里明明写了with(rowlock)，死锁照样发生，于是开始怀疑是不是提示没生效。

原因：rowlock只约束锁的粒度是行级，它既不改语句访问索引的顺序，也不改语句内部的执行步骤。这个案例里，加锁顺序完全由执行计划决定，rowlock对此毫无影响。

解决：不要指望用锁提示消死锁。要么从执行计划入手减少加锁次数，要么在应用层对 1205 做捕获加重试，同时接受“偶发 1205 是并发写下的正常现象”这个前提。

5.2 只看 1222 的输出就下结论

现象：resource-list里清清楚楚写着谁持有谁等待，但照着改完还是复现，或者根本不知道该改哪。

原因：1222 是快照，只有死锁那一刻的状态，没有“锁是什么时候申请的、什么时候释放的”。而这个案例的根因恰恰藏在“第一步释放 U 锁”这个时间点上。

解决：1222 和Lock:Acquired/Lock:Released必须配套用。快照负责指出环形等待的两端，时序负责解释这个环是怎么形成的，缺一不可。

5.3 用 partition_id 去匹配分区表的 hobt_id

现象：按日志里的数字查索引名，单机测试一切正常，换到分区表环境查出来的索引名对不上。

原因：单分区表下partition_id和hobt_id数值相同，多分区表下就不是一回事了。

解决：查 hobtid 时统一用sys.partitions.hobt_id关联，别图省事写partition_id。脚本写一次就能复用，没必要为省几个字符埋雷。

5.4 把大字段塞进覆盖索引当“性能优化”

现象：某个查询加了 include 之后确实快了，但上线后写入侧开始零星报 1205，索引体积和维护时间也明显上涨。

原因：varchar(max)这类 LOB 列出现在非聚簇索引里，不只是体积问题，还会改变 UPDATE 的执行计划形态，把一步更新拆成多步，人为制造锁释放窗口。

解决：include 里放行内类型的小列是常规操作，放 LOB 大字段要格外谨慎。真要放，先评估读写比例，再考虑能不能用长度受限的类型替代。

5.5 本地只跑一两条 UPDATE 就断定“复现不了”

现象：本地新建同样的表，手动执行两三次 UPDATE，一切正常，于是认为线上是玄学。

原因：死锁需要两个连接在极窄的时间窗口内交叉，单次执行几乎不可能撞上。

解决：用while 1 = 1死循环加上两个并发连接去压，几秒内就能稳定复现。这套循环脚本本身就是最好的回归用例，改完表结构之后重跑一遍，能立刻验证修复是否真的有效。

6. 快速定位：拿到一张死锁图之后该按什么顺序看

死锁排查最耗时间的不是修，而是前十分钟不知道该看哪里。我的习惯是把步骤固定下来，不管多复杂的图，先按这个顺序过一遍。

第 3 步常被跳过，但它其实最省时间——一旦发现争用的是“某个带 include 的非聚簇索引”，八成就是覆盖索引带来的问题；如果争用的是聚簇索引本身，方向就要转向访问顺序或索引缺失。第 4 步和第 5 步是连着的：时序告诉你“中途有没有松手”，执行计划告诉你“为什么会松手”，两者对得上，结论才算成立。

验证修复是否生效，也别只看“跑一会儿没报错”。我一般会用两个连接各跑 5000 次循环，同时用扩展事件挂一个xml_deadlock_report的会话，跑完统计记录条数，必须是 0 才算通过。

-- 建一个临时的扩展事件会话，专门盯死锁，验证阶段用 create event session catch_deadlock on server add event sqlserver.xml_deadlock_report add target package0.ring_buffer(set max_memory = 4096); go alter event session catch_deadlock on server state = start;

max_memory = 4096单位是 KB，验证阶段够用；跑完alter event session ... state = stop再drop掉，别留在生产实例上。这个会话的好处是能拿到完整的死锁图 XML，配合前面的步骤 3、4 一起看，比翻 errorlog 快得多。

从那以后，我每次看到“两条一样的语句互锁”这种报告，都会先要求对方提供三样东西：1222 或扩展事件的死锁图、Profiler 的锁申请释放轨迹、以及那条语句的执行计划。少一样我都不下结论，因为缺任何一块，都可能把“执行计划拆成了两步”误判成“索引建错了”，然后花半天改一个根本不是根因的地方。希望帮到你。

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