企业官网建设流程全解析

简介：一份面向SQL Server开发与运维人员的死锁排查实战笔记，围绕一个由非聚集索引include列与varchar(max)字段引发的怪异Deadlock展开，完整还原问题重现、锁竞争机理与两种典型分析方法。内容涵盖SQL Trace收集、1222跟踪开关、sp_readerrorlog读取死锁信息等步骤，适合需要提升SQL Server锁机制理解与故障诊断能力的中高级技术人员。文档以示例表tt、聚集索引、两个非聚集索引及rowlock更新语句为载体，直观展示索引include选项和字段类型对死锁产生的影响；通过对照测试，读者可掌握排查同类死锁问题的完整思路。包内共1个文件，为docx文档，压缩后大小694KB，便于本地查阅与打印。目前已有326人学习浏览，适合按现象复现、原因剖析、分析工具、结论验证的顺序逐步演练。

1. 一个“奇怪”的死锁：从现象到分析框架

去年秋天我接手了一套老旧的进销存系统，SQL Server 2016，业务量不大，却每周固定出现两三次死锁。最让人头疼的是死锁的双方都是极其简单的 UPDATE 语句，单条执行都在毫秒级，索引也都存在。按常理，这种语句不该互相卡死。更诡异的是，死锁发生的时间点毫无规律，有时在凌晨，有时在中午，而且每次死锁图里都带着一个不大显眼的 Writelog 资源。那时候我意识到，SQL Server 死锁分析不能只看语句本身，锁的申请顺序、事务边界、甚至日志写入都可能成为隐藏的元凶。这篇文章就从一个真实发生过的“奇怪 Deadlock”出发，把 SQL Server 上定位死锁的完整方法拆开讲透，包括如何抓取现场、如何解读死锁图、如何从五个常见方向排查，以及最后怎么用工程手段把死锁发生率压下去。

2. 死锁的产生条件与 SQL Server 的锁模型：为什么两个 UPDATE 会互相卡住

2.1 锁的基本粒度与兼容矩阵

SQL Server 的锁管理器以资源为单位分配锁，资源粒度从小到大分别是行（RID/KEY）、页（PAGE）、表（TABLE）、对象（OBJECT）、数据库（DB）等。两个事务发生死锁，不是因为锁的数量多，而是因为它们在至少两个资源上以相反的顺序申请锁。要判断一个死锁是否“奇怪”，第一步就是确认两个事务到底在争抢哪些资源、以什么锁模式申请。

锁的兼容矩阵是分析的基础。共享锁（S）与共享锁兼容，排他锁（X）与任何其他锁都不兼容，更新锁（U）与共享锁兼容但与其他更新锁不兼容。对于 UPDATE 语句，SQL Server 通常先申请 U 锁或 X 锁定位行，然后在真正修改时升级为 X 锁。这个“先 U 后 X”的切换点经常被忽略，但它恰恰是许多死锁的温床。

我用一个实际案例来说明。会话 A 执行：

UPDATE Orders SET Status = 'Shipped' WHERE OrderID = 100

会话 B 执行：

UPDATE Orders SET Status = 'Paid' WHERE OrderID = 100

这两个语句争的是同一行，理论上只会产生阻塞，不会死锁，因为资源只有一个。真正导致死锁的是第二个资源。比如 Orders 表上有一个外键指向 Customers，更新 OrderID=100 时，SQL Server 需要先验证外键约束，于是会在 Customers 表上申请 S 锁；另一个事务同时更新了 Customers 的某一行并持有 X 锁，再反过来去更新 Orders。两个事务都持有对方需要的资源，死锁形成。

这里的要点是：分析死锁时不要只盯着死锁图里的两条语句，还要把外键、触发器、级联操作、索引维护这些“隐形的锁申请者”全部纳入视野。我一般会先列出每个事务完整执行路径上涉及的所有表，再逐个确认锁模式。

2.2 从锁升级到死锁：四个必要条件

死锁的形成需要四个条件同时满足：互斥条件、持有并等待、不可剥夺、循环等待。SQL Server 的锁管理器不会主动剥夺已授予的锁，也不会参与超时协商，因此一旦循环等待形成，死锁检测机制才会介入，每隔几十毫秒做一次检测，发现后选择牺牲者回滚。

理解这四个条件，能帮我们快速判断一个死锁是否“合理”。比如互斥条件，如果两个事务都在申请 S 锁，就不会死锁。持有并等待条件，如果某个事务的锁全部申请完成后再执行更新，死锁概率会大幅下降。循环等待条件，则和语句访问表的顺序直接相关。

我见过一个最典型的“非奇怪”死锁：两个事务都先更新 OrderHeader，再更新 OrderDetail，只是启动时间不同。第一个事务拿到了 Header 的锁，等待 Detail；第二个事务拿到了 Detail 的锁，等待 Header，循环等待成立。这种死锁不需要高级分析，直接统一访问顺序即可。

但前面说的“奇怪”死锁，循环等待的弧线并不明显。死锁图里两个事务看起来都在操作不同的 OrderID，甚至不同的表，可它们却在同一个 Page 或同一个 Key 上碰撞。这时候就要往锁粒度和锁升级的方向查。

2.3 Writelog 与页锁：容易被忽略的参与者

很多 SQL Server 从业者对死锁图里的 PAGELOCK 或者 Writelog 资源不敏感。Writelog 代表事务日志的写入锁，它并不是用户表上的锁，而是对日志文件的同步控制。当两个事务都需要写入日志时，理论上它们是串行的，但在某些特殊场景下，一个事务持有用户表的 X 锁，同时等待日志写入；另一个事务持有日志写入的某种锁，却等待用户表上的锁，这样就形成了一条跨系统的等待环。

Writelog 相关的死锁最常见于大事务和日志增长受限的环境。比如事务 A 更新了几十万行，持续持有行锁，并且不断写入日志；事务 B 是一个小事务，需要写入一条日志记录，但日志文件处于自动增长边缘，等待日志空间的扩展操作，而扩展操作又希望获得某个表上的 Schema Modification 锁，恰好被事务 A 的 Sch-M 锁阻塞。这种环看起来和业务无关，却是真实发生过的。

页锁同样容易被忽略。SQL Server 的默认锁粒度在行级锁与页级锁之间动态切换。当一个事务申请了超过一定数量的行锁（通常是 5000 行，受 Lock Escalation 阈值控制），锁管理器会把行锁升级为页锁或表锁。如果两个事务各自的锁覆盖了同一页上的不同行，升级时就会发生竞争。分析这类死锁，要在死锁图里看资源标识里的 objectid 和 associated_object_id，确认锁粒度。

另外，不要忽略索引的“键范围锁”（Key Range Lock）。在可重复读或可串行化隔离级别下，范围锁会把索引键前后的区间全部锁住，即使两个事务插入的是不同的新键，只要它们的键落在对方的区间内，就会形成死锁。下面表格列出不同隔离级别下锁持有时间的差异，帮助快速定位方向。

如果排查时发现应用把隔离级别设成了可串行化，那死锁的“奇怪”程度会瞬间降低。我一般先查 sys.dm_exec_sessions 里的 transaction_isolation_level，再决定是否深入分析。

3. 捕获现场：SQL Server 死锁的三种取证方法

3.1 开启跟踪标志 1222 与 1204：系统日志里的死锁图

分析死锁的前提是先拿到死锁现场。SQL Server 提供了两个经典的跟踪标志：1204 和 1222。1204 输出的是一种紧凑的文本格式，适合人直接读；1222 输出的是 XML 化的文本，包含更详细的资源描述和等待链。我通常两个都开，因为 1204 方便快速浏览，1222 方便解析结构化信息。

开启跟踪标志需要 sysadmin 权限，并且在 SQL Server 重启后会失效，除非用 -T 参数启动。临时开启的命令是：

DBCC TRACEON(1204, -1) DBCC TRACEON(1222, -1)

这里的 -1 表示在所有会话范围内生效。两个标志同时启用时，错误日志里会分别记录两种格式的死锁信息，没有冲突。开启后，每次发生死锁，SQL Server 都会把死锁图写到错误日志中。

需要注意的是，这个操作只对之后发生的死锁生效，对历史死锁无能为力。所以我建议在项目上线或有死锁投诉时，第一时间开启，而不是等到复现再开。另外，错误日志会覆盖，日志文件如果设置了大小限制，死锁信息可能被冲掉。我会把错误日志的文件大小调大，或者定期把日志归档。

还有一个容易踩的坑：跟踪标志 1204 和 1222 在某些高并发环境下会带来少量额外开销，但通常可以忽略。真正的问题在于日志里的死锁信息可能不完整，特别是当死锁涉及多个资源、多个辅助会话时，1222 的输出会更详细，而 1204 可能只显示一条等待链。

3.2 扩展事件：轻量级死锁会话的搭建

跟踪标志虽然简单，但输出格式解析起来麻烦，而且无法保存到表里。更现代的做法是使用扩展事件（Extended Events）。SQL Server 2012 之后的版本都内置了 system_health 会话，它会默认捕获死锁事件，但不会详细记录死锁图 XML。想要完整记录，需要自己创建会话。

下面是我在生产环境用的一个最小化扩展事件会话，目标是把死锁事件写到文件目标，方便后续分析：

CREATE EVENT SESSION [DeadlockCapture] ON SERVER ADD EVENT sqlserver.xml_deadlock_report ( ACTION (sqlserver.session_id, sqlserver.sql_text, sqlserver.tsql_stack) WHERE ([duration] >= 50000) ) ADD TARGET package0.event_file ( SET filename = N'D:\XE\DeadlockCapture.xel', max_file_size = 50, max_rollover_files = 12 ) WITH (MAX_MEMORY = 4 MB, EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS, MAX_DISPATCH_LATENCY = 5 SECONDS, STARTUP_STATE = ON); GO ALTER EVENT SESSION [DeadlockCapture] ON SERVER STATE = START;

这段代码里，sqlserver.xml_deadlock_report是死锁发生时的核心事件，它会携带完整的死锁 XML 图。sqlserver.tsql_stack可以记录死锁发生时的调用栈，对定位应用代码非常有帮助。WHERE (duration >= 50000)是过滤条件，duration 的单位是微秒，50 毫秒，用来过滤掉一些极短的锁等待，避免会话文件膨胀过快。

package0.event_file目标把事件写入 .xel 文件，max_file_size按 MB 计算，max_rollover_files是滚动文件数量。我一般设置 12 个 50MB 文件，足够保留最近几个月的死锁记录。

扩展事件的开销远小于 SQL Profiler，因为它的过滤器在内核层生效，不会把所有语句都捕获上来。而且事件会话可以随 SQL Server 服务自动启动，把STARTUP_STATE设为 ON 即可。

3.3 从性能监视器与 DMV 补全上下文

死锁图只告诉我们“发生在哪一刻”，但要说清“为什么在这个业务时段爆发”，还需要看当时的系统状态。我通常会结合两类数据：性能监视器计数器（Performance Monitor）和动态管理视图（DMV）。

性能监视器里主要看锁等待相关的计数器，比如 SQL Server:Latches 的 Average Latch Wait Time，以及 SQL Server:Locks 下的 Lock Waits/sec。如果死锁发生时刻，这些计数器出现尖峰，说明系统整体锁竞争严重，这时候要从并发度入手，而不是只优化单条语句。

DMV 方面，sys.dm_exec_requests和sys.dm_exec_sessions可以在死锁发生前几分钟手动采集快照，用以下语句查看当时的阻塞链：

SELECT r.session_id, r.blocking_session_id, r.wait_type, r.wait_time, r.wait_resource, t.text AS [sql_text] FROM sys.dm_exec_requests r CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t WHERE r.blocking_session_id > 0;

wait_type和wait_resource直接告诉我们会话在等什么，比如 LCK_M_X 表示在等排他锁，而wait_resource会给出资源的具体地址。blocking_session_id可以追溯到锁的持有者。

注意，这种即时查询只能看到当前瞬间的阻塞状态，死锁往往发生在几毫秒之内，手动查询常常扑空。所以更可靠的方法是建立一个定时作业，每 30 秒把 sys.dm_exec_requests 里 wait_type 包含 LCK 的记录插入一张历史表，持续运行一周，然后对照死锁时间点复盘。我见过不少团队只抓死锁图，不抓上下文，结果分析一半就卡住——因为没有证据证明当时并发度有多高、哪些端口或报表在跑批量作业。

4. 分析死锁图：把 XML 图形翻译成可执行的判断

4.1 死锁图的 XML 结构解读

扩展事件和 1222 跟踪标志产生的死锁图，本质上是一份 XML 文档，里面有 根节点，下面包含 、 、 三个核心容器。过程列表里每个 节点对应一个死锁参与者，记录着 session id、事务名称、输入缓冲区（inputbuf）、执行栈（executionStack）、锁模式等关键信息。资源列表里是具体的锁资源，比如 KEY、RID、PAGE、APP 等。

我用一个典型的死锁片段来拆解：

<deadlock> <victim-list> <victimProcess id="process1c0f2c4e848" /> </victim-list> <process-list> <process id="process1c0f2c4e848" waitresource="KEY: 9:72057594058145792 (e4c3f1a2b1c2)" waiterlist="mylock" scheme="dbo" tablename="Orders" mode="S"> <executionStack> <frame procname="dbo.usp_UpdateOrder" line="12" sqlhandle="0x..."> UPDATE Orders SET Status = @status WHERE OrderID = @id </frame> </executionStack> <inputbuf> EXEC dbo.usp_UpdateOrder @id = 100, @status = 'Shipped' </inputbuf> </process> <process id="process1c0f2c4e849" waitresource="KEY: 9:72057594058145792 (f2a3b4c5d6e7)" mode="X"> ... </process> </process-list> <resource-list> <keylock hobtid="72057594058145792" dbid="9" objectname="dbo.Orders" indexname="PK_Orders" mode="RangeS-S"> <owner-list> <owner id="process1c0f2c4e848" mode="RangeS-S" /> </owner-list> <waiter-list> <waiter id="process1c0f2c4e849" mode="RangeS-S" /> </waiter-list> </keylock> </resource-list> </deadlock>

这个 XML 是真实结构的简化版，但足够说明分析方法。第一个 process 在等待一个 KEY 资源，mode 为 S，第二个 process 拥有同一个索引键上的 RangeS-S 锁，同时等待另一个 KEY。这里的waitresource内部格式是“资源类型: dbid: hobtid (键哈希)”。dbid 为 9 表示这是目标库的 ID，hobtid 是索引的堆或 B-tree 的 ID，括号里的哈希值代表具体键值。

关键点在于 mode 列。S 表示共享锁，X 表示排他锁，RangeS-S 是范围共享锁。如果看到两个进程都在等待RangeS-S或RangeX-X，基本可以断定隔离级别是可重复读或可串行化，且操作涉及范围查询。

4.2 受害者选择与资源优先级

死锁检测器会选一个代价最小的进程作为受害者，回滚它的整个事务，以便解除死锁。<victim-list>里标出的那个 process id 就是被牺牲的会话。实际生产中，受害者不一定是锁申请较晚的，而是根据每个事务的回滚成本、锁数量、CPU 使用量等加权计算出来的。

分析死锁时，不要单独看谁被牺牲，而是看两个进程分别持有什么、等待什么。我把死锁图分析流程固化为四步：

第一步，列出两个进程的 owner-list 和 waiter-list。owner-list 表示这个进程占用了哪些资源以及锁模式，waiter-list 表示它在等什么。第二步，把两条等待链画出来：进程 A 等待资源 R2，但占用了 R1；进程 B 等待资源 R1，但占用了 R2。第三步，确认资源对象，是同一行的 KEY 还是同一个页上的 RID，这决定了锁粒度是否正常。第四步，回到应用程序定位事务代码，看看事务里除了截图上的语句，还有哪些上下文操作。

我见过不少人在死锁图里看到两条一模一样的 UPDATE 就以为语句本身有问题，其实等资源列表里写的是PAGE: 9:1:12345，说明两个事务冲突在同一个数据页的不同行上，因为页锁升级导致互相卡死。这时候优化语句没用，要调整锁升级阈值或拆分事务。

4.3 根据两个进程的等待链还原时间线

死锁图只能反映检测到死锁的那一瞬间的锁状态，但我们可以通过多个辅助信息还原事件的时间线。process节点的lastbatchstarted和lastbatchcompleted字段记录了该会话最后的批处理运行时间；executionStack里的frame给出了语句在存储过程里的行号。结合这些信息，可以确定哪个事务先启动、哪个事务先持有锁。

时间线还原法的价值在于判断死锁是“可预测”还是“随机碰撞”。比如两个事务都从同一个日程批量任务里启动，那么时间线会有明显的先后顺序，统一访问顺序即可解决。如果两个事务来自不同的应用服务，且启动时间接近但无规律，那么需要更系统的锁范围优化。

还有一点容易被忽略：死锁图里的inputbuf往往只显示了一个存储过程名或一条语句，但实际事务里可能已经执行了几十条语句。分析前一定要拿到应用程序的完整事务代码，不能只凭死锁图里的片段下结论。我习惯把死锁图 XML 里的sql_handle提取出来，用sys.dm_exec_sql_text还原完整批处理，避免误判。

5. 常见死锁原因排查与避坑：5 条血泪经验

5.1 现象：两个简单 UPDATE 互相死锁，原因却是外键约束的级联锁

有次排查一个死锁，死锁图里两个 UPDATE 指向不同的两张表，而且都只更新一行，怎么看都不像彼此冲突。后来我用 SQL Server Profiler 曾经抓过完整语句，才发现是外键约束导致的锁传播。

原因是这样的：Orders 表有外键指向 Customers 表，更新 Orders 的 CustomerID 字段时，SQL Server 会在 Customers 表的对应索引上申请 S 锁，用来验证外键引用。而另一个事务正在更新 Customers 表的主键，它先取得了 Customers 表的 X 锁，再通过 ON UPDATE CASCADE 去更新 Orders 表。两个事务都在等待对方持有的锁，死锁成立。

解决办法是对外键列建索引，并取消不必要的级联更新。我当时的做法是先把级联更新改为应用层分步更新，死锁立刻消失。排查思路上，遇到死锁图里双方资源并不交圈的情况，一定要检查表之间的外键关系，特别是更新主键/外键的业务路径。

5.2 现象：同一条语句在不同顺序下执行导致死锁

这是一个非常常见的“奇怪”死锁。应用里有个报表模块，有时先读 A 表再读 B 表，有时先读 B 表再读 A 表，两个路径交叉执行时形成死锁。死锁图里的语句都是 SELECT，容易被忽略，因为 SELECT 也会申请 S 锁。默认读已提交隔离级别下，SELECT 语句结束就释放锁，但如果查询中加了 NOLOCK 或者开启了可重复读，S 锁会保留到事务结束。

我曾经遇到一个死锁，双方都是 SELECT，而且都是加锁读（在事务内配合 UPDATE）。原因是事务里先查了汇总表，再更新明细表，另一个事务先更新明细表再查汇总表。解决的方法是固定所有事务内的表访问顺序，并且让读操作的精确定位使用索引，减少锁范围。

5.3 现象：死锁图里出现 PAGELOCK 与 Writelog，问题在 I/O 而非逻辑

还有一类死锁，资源列表里出现PAGELOCK和WRITELOG，而不是 KEY 或 RID。这种死锁往往与磁盘 I/O 性能有关。当数据页被大量更新时，SQL Server 会申请页级排他锁，而日志写入缓慢会导致事务等待 Writelog，再加上锁管理器的一些内部转换，形成跨资源等待。

解决思路分成两层：第一层优化 I/O，把日志文件和数据文件分到不同物理磁盘，提升磁盘写入能力；第二层减小事务的写集，比如把一个批量 UPDATE 拆成多个小批次。之前遇到的那次，死锁发生在报表定时刷新和业务高峰期重叠的时刻，错开调度时间就再也没出现过。

5.4 现象：默认隔离级别下读请求也参与死锁

很多人认为读已提交隔离级别下，SELECT 不加锁，不会参与死锁。实际上默认隔离级别下，SELECT 在定位行时会申请短期的 S 锁，在第一次获取结果集后立即释放。但在某些锁升级或索引页分裂场景中，S 锁的持有时间可能被延长，甚至与 UPDATE 的 X 锁产生交叉。

我处理过一个案例：两个并发会话都在执行一个复杂 JOIN，其中包含了对同一张表的多个索引查找，SQL Server 优化器选择了不同的索引访问路径，导致两个会话以相反顺序锁定相同的索引页。解决方法是使用索引提示固定访问路径，或者在查询层面引入 NOLOCK 提示（对于一致性要求不高的报表查询）。

5.5 现象：重试后仍然死锁，原因在索引缺失

最隐蔽的死锁问题之一，是缺失索引导致锁粒度放大。正常情况下，UPDATE 语句通过索引定位到少量行，只对少数行加锁。如果没有合适的索引，SQL Server 可能全表扫描，锁管理器会把锁升级到页或表级别。两个全表扫描的更新语句，即使操作的是不同行，也可能在同一页上发生冲突。

这种死锁的破解方法很简单：为 WHERE 条件建立合适的索引。但要注意，加索引要评估维护成本，不能无脑建。我一般先用 DMV 分析缺失索引，再结合死锁图的资源对象确认。索引建好后，死锁图里的资源从 PAGE 变成 KEY，问题自然消失。

6. 预防与验证：索引设计、事务顺序与死锁重试的工程落地

6.1 用最小化锁范围改写事务

避免死锁的最佳策略是缩短事务持锁时间，降低锁竞争窗口。我在代码评审时最看重三点：事务内不要包含用户交互和查询；批量更新按主键分页；UPDATE 语句使用精确的 WHERE 条件并锁定对应的索引。

改写示例：原来一个事务里先SELECT校验库存，再UPDATE扣减库存，这两个操作之间锁一直持有。我会改成直接UPDATE ... WHERE Stock >= @qty，利用行锁的原子性判断，减少一个锁周期。

6.2 统一事务访问顺序的规范

不同模块之间，如果有多个表需要一起更新，最好在开发规范中固定表的访问顺序。比如一律先更新 Header 再更新 Detail，或者反过来，避免两个事务以不同顺序访问同一组表。这个规范虽然简单，却能消灭大部分业务级死锁。

6.3 死锁重试策略与监控闭环

即使做了所有预防，死锁还是可能发生，尤其是在新功能上线初期。应用层的死锁重试机制是最后的兜底。SQL Server 的死锁错误号是 1205，应用捕获到这个错误后，可以延迟一小段随机时间后重试整个事务。重试次数一般设为 3 次，延迟从 100ms 到 500ms 递增。

我的习惯是重试前先判断事务是否已经部分回滚。SQL Server 在死锁检测时回滚的整个事务，所以应用前要清理上下文。同时，把扩展事件会话和跟踪标志 1222 作为长期监控手段，每周末检查一次死锁报告，把死锁数量压到零。

这套组合拳做下来，那套进销存系统的死锁从每周几次降到了半年的个位数。回头看，所谓的“奇怪”死锁，只是没有在正确的时间点抓到正确的现场。把死锁图、上下文数据和应用代码三样对齐，大部分谜题都能解开。希望这些分析方法能帮你在下一次遇到 SQL Server 死锁时，少走弯路。

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