简介一份面向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更新 OrderID100 时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 ND:\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 idprocess1c0f2c4e848 / /victim-list process-list process idprocess1c0f2c4e848 waitresourceKEY: 9:72057594058145792 (e4c3f1a2b1c2) waiterlistmylock schemedbo tablenameOrders modeS executionStack frame procnamedbo.usp_UpdateOrder line12 sqlhandle0x... UPDATE Orders SET Status status WHERE OrderID id /frame /executionStack inputbuf EXEC dbo.usp_UpdateOrder id 100, status Shipped /inputbuf /process process idprocess1c0f2c4e849 waitresourceKEY: 9:72057594058145792 (f2a3b4c5d6e7) modeX ... /process /process-list resource-list keylock hobtid72057594058145792 dbid9 objectnamedbo.Orders indexnamePK_Orders modeRangeS-S owner-list owner idprocess1c0f2c4e848 modeRangeS-S / /owner-list waiter-list waiter idprocess1c0f2c4e849 modeRangeS-S / /waiter-list /keylock /resource-list /deadlock这个 XML 是真实结构的简化版但足够说明分析方法。第一个 process 在等待一个 KEY 资源mode 为 S第二个 process 拥有同一个索引键上的 RangeS-S 锁同时等待另一个 KEY。这里的waitresource内部格式是“资源类型: dbid: hobtid (键哈希)”。dbid 为 9 表示这是目标库的 IDhobtid 是索引的堆或 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 死锁时少走弯路。本文还有配套的精品资源点击获取