SQL Server 死锁排查实战：从捕获到根治

dbaai

SQL Server 死锁排查实战：从捕获到根治

一、具体的问题

做 SQL Server 运维的，多半都见过这条报错：

2. 事务(进程 ID 87)与另一个进程被死锁在 锁 资源上，并且已被选作死锁牺牲品。请重新运行该事务。

复制代码

错误号 1205。它有个很讨厌的特点：不是必现，而是偶发。压测的时候好好的，一到业务高峰就冒出来；运维接到报障去查，等连上服务器又复现不了；开发说"我重试一次就成功了"，于是加个 try/catch 重试把问题盖住，死锁本身却一直躺在系统里。

我接手的一个订单系统就是这样：下单接口每天失败 30 多笔，失败原因全是 1205。业务侧加重试后用户无感，但高峰期接口 P95 从 200ms 涨到 2.3s，数据库的锁等待时间居高不下。这类问题的正确做法不是重试掩盖，而是把死锁图抓出来，看清楚两个进程各自持有什么、等什么，再针对性地改。

本文就按"捕获 → 读图 → 复现 → 根治"这条线，给出可照做的步骤和 SQL。

二、核心原理

1. 死锁的四个条件

互斥、持有并等待、不可抢占、循环等待。数据库层面能打破的只有后两个：让锁可以及时释放（缩短事务），或者消除循环等待（统一加锁顺序）。

2. 锁的粒度决定影响面

SQL Server 的锁从细到粗依次是 RID（堆行）、KEY（索引行）、PAGE、EXTENT、HOBT、OBJECT（表）。死锁图里 waitresource 的标识很关键：

• KEY: 6:72057594043431424 (8194443284a0) —— 行键锁，影响面小，通常是单条记录冲突；
  
• PAG: 6:1:12345 —— 页锁；
  
• OBJECT: 6:123456789 —— 表锁。只要看到 OBJECT 级，基本可以判定发生了锁升级：单条语句持有锁超过约 5000 个时，SQL Server 会把行/页锁升级为表锁，冲突概率瞬间放大。
  

3. SQL Server 怎么发现死锁

后台有一个死锁监视器线程，默认每 5 秒扫描一次等待图。发现环后，按"回滚代价"挑牺牲品——代价用事务已写入的日志量估算，谁写的日志少谁被杀。这也是为什么短小的事务反而更容易当牺牲品，别以为事务小就安全。

4. 三个最常见的成因

• 多表更新顺序不一致：模块 A 先改订单再改库存，模块 B 先改库存再改订单，并发一上来必然成环。这是应用侧第一大成因。
  
• 缺少索引导致锁范围膨胀：UPDATE ... WHERE status=0 如果 status 上没索引，走全表扫描，扫描过程中对每行加 U 锁，轻则锁大量无关行，重则升级成表锁。
  
• 事务被拉长：显式事务里夹了远程 HTTP 调用、写文件、等用户输入，锁持有时间从毫秒级变成秒级，冲突窗口被放大几十倍。
  

三、实例参考（动手步骤）

下面这套步骤在 SQL Server 2012 及以上都适用，示例库名 OrderDB。

1) 先把死锁信息抓下来

方案一（最快，不开会话）：打开 1222 跟踪标志，死锁详情会写进错误日志。

2. -- 全局开启，重启失效；要持久化需加启动参数 -T1222
3. DBCC TRACEON (1222, -1);
4. -- 确认状态
5. DBCC TRACESTATUS (1222, -1);
6. -- 读错误日志里的死锁记录
7. EXEC sp_readerrorlog 0, 1, 'deadlock';

复制代码

方案二（推荐，长期留证）：建扩展事件会话，死锁图存文件。

2. CREATE EVENT SESSION [xe_deadlock] ON SERVER
3. ADD EVENT sqlserver.xml_deadlock_report
4. ADD TARGET package0.event_file(
5.   SET filename = N'D:\XE\deadlock.xel',
6.       max_file_size = 100, max_rollover_files = 10)
7. WITH (STARTUP_STATE = ON);
8. GO
9. ALTER EVENT SESSION [xe_deadlock] ON SERVER STATE = START;

复制代码

其实 system_health 会话默认已经在收集 xml_deadlock_report 了，可以直接查：

2. SELECT TOP 20
3.        x.event_data.value('(event/@timestamp)[1]', 'datetime2') AS occur_time,
4.        x.event_data.query('.') AS deadlock_graph
5. FROM (SELECT CAST(target_data AS XML) AS td
6.       FROM sys.dm_xe_session_targets t
7.       JOIN sys.dm_xe_sessions s ON s.address = t.event_session_address
8.       WHERE s.name = 'system_health' AND t.target_name = 'ring_buffer') AS d
9. CROSS APPLY d.td.nodes('RingBufferTarget/event[@name="xml_deadlock_report"]') AS x(event_data)
10. ORDER BY occur_time DESC;

复制代码

2) 读死锁图，看三个地方

把 XML 存成 .xdl 用 SSMS 打开是图形视图，但命令行环境下直接读 XML 更实在。三处必看：

• victim-list：被杀的是哪个进程；
  
• process-list：每个进程的 waitresource、isolationlevel、sqlhandle、inputbuf（正在执行的语句）；
  
• resource-list：争的是什么资源，keylock/pagelock/objectlock，带 hobtid 和 associatedObjectId。
  

由 hobtid 反查到具体表和索引：

2. SELECT OBJECT_NAME(p.object_id) AS tbl, i.name AS idx, p.hobt_id
3. FROM sys.partitions p
4. JOIN sys.indexes i ON i.object_id = p.object_id AND i.index_id = p.index_id
5. WHERE p.hobt_id = 72057594043431424;   -- 替换成死锁图里的 hobtid

复制代码

3) 本地复现一个典型死锁

建两张表，开两个会话交叉更新，稳定复现：

2. CREATE TABLE dbo.OrderMain (id INT PRIMARY KEY, amt INT, status TINYINT);
3. CREATE TABLE dbo.OrderStock (id INT PRIMARY KEY, qty INT);
4. INSERT dbo.OrderMain VALUES (1,100,0),(2,200,0);
5. INSERT dbo.OrderStock VALUES (1,50),(2,80);

复制代码

会话 A（先主表后库存表）：

2. BEGIN TRAN;
3.   UPDATE dbo.OrderMain  SET amt = amt - 10 WHERE id = 1;
4.   WAITFOR DELAY '00:00:05';
5.   UPDATE dbo.OrderStock SET qty = qty - 1  WHERE id = 1;
6. COMMIT;

复制代码

会话 B（顺序相反，先库存表后主表）：

2. BEGIN TRAN;
3.   UPDATE dbo.OrderStock SET qty = qty - 1  WHERE id = 1;
4.   WAITFOR DELAY '00:00:05';
5.   UPDATE dbo.OrderMain  SET amt = amt - 10 WHERE id = 1;
6. COMMIT;

复制代码

两个会话在 5 秒内先后跑起来，几秒后其中一个必定收到 1205。这就是"顺序不一致"的教科书级复现。

4) 三板斧根治

第一斧：统一 DML 顺序。 应用层对批量更新先按主键排序，多表写入固定顺序（主表 → 明细表 → 库存表），任何模块不得例外。批量更新这样写：

2. -- 按 id 升序更新，消除交叉加锁
3. DECLARE @ids TABLE (id INT PRIMARY KEY);
4. INSERT @ids SELECT id FROM dbo.OrderMain WHERE status = 0 ORDER BY id;
5. UPDATE m SET m.status = 1
6. FROM dbo.OrderMain m JOIN @ids i ON i.id = m.id;

复制代码

第二斧：补索引，掐掉锁升级。 给 WHERE 条件的过滤列建非聚集索引，让更新走索引查找而不是全表扫描。前后对比（同一张 50 万行表，更新 1000 行）：

指标	修复前（无索引）	修复后（有索引）
逻辑读	184320	3120
持有锁数	约 500000（触发升级为表锁）	约 1000（KEY 锁）
执行计划	表扫描 + 锁升级	索引查找
死锁次数/天	30+	0

2. CREATE NONCLUSTERED INDEX IX_OrderMain_status
3.   ON dbo.OrderMain(status) INCLUDE (amt);

复制代码

第三斧：缩短事务 + 合理重试。 显式事务里只放写操作，远程调用、查字典表、生成报表这些一律挪到事务外；同时给语句设超时上限：

2. SET LOCK_TIMEOUT 3000;   -- 3 秒拿不到锁就放弃，单位毫秒

复制代码

应用层捕获 1205 后做退避重试（3 次，间隔 100ms/300ms/900ms），而不是无限重试。

补充手段：读已提交快照隔离。 读写互不相堵，能消掉一大类由共享锁引起的死锁：

2. ALTER DATABASE OrderDB SET READ_COMMITTED_SNAPSHOT ON WITH ROLLBACK IMMEDIATE;

复制代码

注意代价：行版本存到 tempdb，写压力大的库要先评估 tempdb 容量和 IO，别在 tempdb 已经在报警的库上直接开。

5) 验证效果

统计修复前后每天的死锁次数，用数字闭环：

2. SELECT CAST(occur_time AS DATE) AS d, COUNT(*) AS deadlock_cnt
3. FROM ( /* 上面查 xml_deadlock_report 的语句 */ ) q
4. GROUP BY CAST(occur_time AS DATE)
5. ORDER BY d DESC;

复制代码

上线一周死锁从 30+/天降到 0，接口 P95 回落到 210ms，才算真正收工。

四、实操检查清单

• 全局开启 1222 或建 xml_deadlock_report 扩展事件会话，日志至少保留 7 天，出事后能回溯
  
• 优先从 system_health 的 ring_buffer 捞历史死锁图，别等复现
  
• 死锁图必看三项：victim-list、process-list（waitresource / isolationlevel / inputbuf）、resource-list
  
• 用 hobtid 反查具体表和索引，把"哪个对象在打架"落成具体名字
  
• 看到 OBJECT: 级锁，先怀疑锁升级，去查执行计划是否表扫描
  
• 补齐 WHERE 过滤列的非聚集索引，对比前后逻辑读与锁粒度
  
• 统一多表写入顺序（主表 → 明细 → 库存），批量更新按主键排序
  
• 显式事务内禁止远程调用、文件 IO、等待用户输入
  
• 设置 SET LOCK_TIMEOUT，应用层捕获 1205 后做 3 次退避重试，不做无限重试
  
• 大批量 DML 拆成 TOP (5000) 循环 + WAITFOR DELAY '00:00:00.200'，避免一次性持锁过多
  
• 考虑 RCSI 前先评估 tempdb 容量与版本存储 IO 压力
  
• 每次改动都用压测复现，记录死锁次数与 P95 耗时前后对比，形成闭环
  
• 定期巡检长事务：sys.dm_tran_active_transactions 与 sys.dm_exec_requests 里 open_transaction_count > 0 且持续秒级以上的会话
  

以上步骤按"先抓证据、再改代码、最后补索引"的顺序做，比拍脑袋改隔离级别靠谱得多。死锁从来不是数据库单方面的问题，一半在应用的事务边界和加锁顺序上。