SQL Server 死锁排查实战：从捕获到根治
SQL Server 死锁排查实战：从捕获到根治一、具体的问题
做 SQL Server 运维的，多半都见过这条报错：
事务(进程 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 跟踪标志，死锁详情会写进错误日志。
-- 全局开启，重启失效；要持久化需加启动参数 -T1222
DBCC TRACEON (1222, -1);
-- 确认状态
DBCC TRACESTATUS (1222, -1);
-- 读错误日志里的死锁记录
EXEC sp_readerrorlog 0, 1, 'deadlock';
方案二（推荐，长期留证）：建扩展事件会话，死锁图存文件。
CREATE EVENT SESSION ON SERVER
ADD EVENT sqlserver.xml_deadlock_report
ADD TARGET package0.event_file(
SET filename = N'D:\XE\deadlock.xel',
max_file_size = 100, max_rollover_files = 10)
WITH (STARTUP_STATE = ON);
GO
ALTER EVENT SESSION ON SERVER STATE = START;
其实 system_health 会话默认已经在收集 xml_deadlock_report 了，可以直接查：
SELECT TOP 20
x.event_data.value('(event/@timestamp)', 'datetime2') AS occur_time,
x.event_data.query('.') AS deadlock_graph
FROM (SELECT CAST(target_data AS XML) AS td
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') AS d
CROSS APPLY d.td.nodes('RingBufferTarget/event[@name="xml_deadlock_report"]') AS x(event_data)
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 反查到具体表和索引：
SELECT OBJECT_NAME(p.object_id) AS tbl, i.name AS idx, p.hobt_id
FROM sys.partitions p
JOIN sys.indexes i ON i.object_id = p.object_id AND i.index_id = p.index_id
WHERE p.hobt_id = 72057594043431424; -- 替换成死锁图里的 hobtid
3) 本地复现一个典型死锁
建两张表，开两个会话交叉更新，稳定复现：
CREATE TABLE dbo.OrderMain (id INT PRIMARY KEY, amt INT, status TINYINT);
CREATE TABLE dbo.OrderStock (id INT PRIMARY KEY, qty INT);
INSERT dbo.OrderMain VALUES (1,100,0),(2,200,0);
INSERT dbo.OrderStock VALUES (1,50),(2,80);
会话 A（先主表后库存表）：
BEGIN TRAN;
UPDATE dbo.OrderMainSET amt = amt - 10 WHERE id = 1;
WAITFOR DELAY '00:00:05';
UPDATE dbo.OrderStock SET qty = qty - 1WHERE id = 1;
COMMIT;
会话 B（顺序相反，先库存表后主表）：
BEGIN TRAN;
UPDATE dbo.OrderStock SET qty = qty - 1WHERE id = 1;
WAITFOR DELAY '00:00:05';
UPDATE dbo.OrderMainSET amt = amt - 10 WHERE id = 1;
COMMIT;
两个会话在 5 秒内先后跑起来，几秒后其中一个必定收到 1205。这就是"顺序不一致"的教科书级复现。
4) 三板斧根治
第一斧：统一 DML 顺序。 应用层对批量更新先按主键排序，多表写入固定顺序（主表 → 明细表 → 库存表），任何模块不得例外。批量更新这样写：
-- 按 id 升序更新，消除交叉加锁
DECLARE @ids TABLE (id INT PRIMARY KEY);
INSERT @ids SELECT id FROM dbo.OrderMain WHERE status = 0 ORDER BY id;
UPDATE m SET m.status = 1
FROM dbo.OrderMain m JOIN @ids i ON i.id = m.id;
第二斧：补索引，掐掉锁升级。 给 WHERE 条件的过滤列建非聚集索引，让更新走索引查找而不是全表扫描。前后对比（同一张 50 万行表，更新 1000 行）：
指标修复前（无索引）修复后（有索引）
逻辑读1843203120
持有锁数约 500000（触发升级为表锁）约 1000（KEY 锁）
执行计划表扫描 + 锁升级索引查找
死锁次数/天30+0
CREATE NONCLUSTERED INDEX IX_OrderMain_status
ON dbo.OrderMain(status) INCLUDE (amt);
第三斧：缩短事务 + 合理重试。 显式事务里只放写操作，远程调用、查字典表、生成报表这些一律挪到事务外；同时给语句设超时上限：
SET LOCK_TIMEOUT 3000; -- 3 秒拿不到锁就放弃，单位毫秒
应用层捕获 1205 后做退避重试（3 次，间隔 100ms/300ms/900ms），而不是无限重试。
补充手段：读已提交快照隔离。 读写互不相堵，能消掉一大类由共享锁引起的死锁：
ALTER DATABASE OrderDB SET READ_COMMITTED_SNAPSHOT ON WITH ROLLBACK IMMEDIATE;
注意代价：行版本存到 tempdb，写压力大的库要先评估 tempdb 容量和 IO，别在 tempdb 已经在报警的库上直接开。
5) 验证效果
统计修复前后每天的死锁次数，用数字闭环：
SELECT CAST(occur_time AS DATE) AS d, COUNT(*) AS deadlock_cnt
FROM ( /* 上面查 xml_deadlock_report 的语句 */ ) q
GROUP BY CAST(occur_time AS DATE)
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 且持续秒级以上的会话
以上步骤按"先抓证据、再改代码、最后补索引"的顺序做，比拍脑袋改隔离级别靠谱得多。死锁从来不是数据库单方面的问题，一半在应用的事务边界和加锁顺序上。
页:
[1]