简介这是一份SQL Server死锁问题分析文档面向数据库管理员、后端开发与运维人员帮助读者掌握死锁成因判断与排查方法。内容以一个稳定重现的奇特死锁案例为主线先按问题复现步骤创建含聚集索引与两个非聚集索引的表插入上万条记录并用rowlock循环更新继而深入讲解非聚集索引INCLUDE选项与varchar(max)字段类型如何影响锁申请、进而触发相互等待的关键机制。随后完整演示两类标准分析手段开启1222跟踪开关读取错误日志以及使用SQL Profiler按SPID过滤抓取Locks事件的流程并结合sp_readerrorlog输出的死锁列表解读受害进程、等待资源与锁模式。资源含1个docx文档压缩包约694KB文档结构完整包含重现脚本、三种对照测试结果与日志解读要点。已有326人学习浏览适合具备SQL Server基础、希望系统提升性能调优与故障排查能力的读者。1. 一个白天的奇怪Deadlock系统没挂业务却卡了一上午有一次线上巡检收到告警说一组更新订单的存储过程大面积超时集中在上午那半小时数据库CPU、内存、IO全部正常阻塞链条时有时无抓不住现行。最后是从SQL Server错误日志里翻出一段Deadlock graph才看清是两个会话各自持锁、互相等待然后被引擎当作牺牲者杀掉的完整过程。这里不烧玄学下面会沿着一条可复现的路径讲把这种“看起来谁也不碍谁”的奇怪Deadlock拆到底用到的正是锁模型、系统视图和扩展事件三件套。适合正在被生产环境死锁问题折磨的DBA和偏后端开发你可以直接抄命令也能提前知道哪几条弯路会熬走你一整夜。2. 死锁不是两条SQL在撞车先读懂锁模型、锁转换与牺牲者选择2.1 锁资源层级死锁图上最多的不是“行锁”是“键锁”SQL Server的锁管理器把资源按粒度分层从细到粗是RID堆上的数据行、KEY索引键行、PAGE8KB数据页或索引页、EXTENT连续8个页、TABLE整表元数据、DATABASE数据库级锁。大多数UPDATE最终申请的是KEY或RID锁走堆表更新定位到的是RID走索引更新定位到的是KEY。死锁图里看到的资源类型也以KEY和PAGE为主很少直接出现TABLE。但资源粒度不是越细越好锁不够时引擎会自动做锁升级lock escalation行锁变成页锁或表锁。触发条件不是固定数字常见情况是单条语句累计锁数超过大约5000个或由内存压力驱动。一旦升级成表锁所有并发会话都挤在同一张表上排队死锁候选集猛然变大排查难度也跟着上来。查看当前锁状态最直接的是sys.dm_tran_locksSELECT resource_type, resource_associated_entity_id, request_mode, request_type, request_status, COUNT(*) AS lock_count FROM sys.dm_tran_locks WHERE request_session_id 50 GROUP BY resource_type, resource_associated_entity_id, request_mode, request_type, request_status ORDER BY lock_count DESC;参数说明resource_type区分RID、KEY、PAGE、TABLE等决定排查方向。request_mode锁模式S、X、U、IX、IS、SIX用来分析兼容性。request_typeLOCK是普通申请CONVERT是锁模式转换WAIT是纯等待。request_statusGRANT表示已持锁CONVERT表示正在等锁转换WAIT表示排队中。lock_count同一资源上的锁数量。数量大且集中在TABLE或PAGE说明发生过锁升级。锁模式的兼容性规则我习惯记住最小子集X与任何非X的锁冲突U与S、U兼容与X冲突S与S兼容与U、X冲突意向锁之间基本兼容只和同级别的排他锁冲突。手工读死锁图时拿不准就对照下面这张表判断已持锁 \ 新请求SUXS兼容兼容冲突U兼容兼容冲突X冲突冲突冲突2.2 死锁形成的两种路径循环等待与锁转换教科书讲的循环等待是A持资源1要资源2B持资源2要资源1这是最典型的“双钥匙”死锁。但生产环境里真正让我觉得“奇怪”的是第二种锁模式转换型。同一个事务里先SELECT再UPDATESELECT拿到U锁UPDATE要把U升级成X另一个会话对同一行也干了同样的事。两个U锁可以共存但升级时谁也不肯先放手于是形成环。这次环上只有一把锁——同一个键owner和waiter都指向它。死锁图里只有一个keylock很多人第一反应是SQL Server画错了。区分方式看死锁XML里waiter的requestTypewait进程在等一个自己没有的锁对应传统互斥。convert进程已持有兼容锁正在申请升级成冲突锁对应锁转换型。优化方向完全不同。wait型先调索引和隔离级别convert型要先查事务内是不是同一条SQL既读又写。尽早用UPDLOCK把U锁直接申成X锁或者把两个步骤合并成一条UPDATE并用OUTPUT取回数据从源头消除U到X的转换窗口。2.3 隔离级别控制持锁时长死锁成因的隐形推手默认的READ COMMITTED隔离级别下读不长期持锁S锁极短REPEATABLE READ和SERIALIZABLE会把读锁保持到事务结束。事务里先SELECT后UPDATESELECT出来的这些行会一直被打着S锁其他会话对同一范围做UPDATE时全部阻塞造成“一次报表查询整个业务写入停摆”。这类案例的死锁图往往有一个owner长期持有大量S锁waiter是不同模块的UPDATE分析方向不在死锁图本身而在事务和隔离级别上。如果数据库开启了行版本隔离READ_COMMITTED_SNAPSHOT为ON读操作连S锁都不申请这类阻塞自然消失也是很多系统开启快照隔离后死锁明显减少的原因。但快照隔离有副作用写版本链会让tempdb承担额外空间和I/O后面避坑章会具体讲。查看数据库隔离级别配置SELECT name, snapshot_isolation_state_desc, is_read_committed_snapshot_on FROM sys.databases;如果is_read_committed_snapshot_on 1说明该库已经用行版本隔离读但写之间仍然有X锁互斥这时出现的报错可能是“更新冲突”而不是死锁分析方向完全不同。2.4 锁升级与索引缺失一条SQL“复活”的真正原因很多死锁是通过加索引“消失”的但死锁条件其实并没有消失只是概率降低了。没有适合WHERE条件的索引时UPDATE语句会扫描整张表边扫边拿行锁锁数量膨胀后触发锁升级成表锁于是所有并发会话全堵在一张表上。死锁图里出现一个PAGE或TABLE级锁owner是一个会话waiter是四五个不同业务模块锁的范围和影响面都远大于预期。加上索引后定位变成点查锁数量降到几十个不再触发升级死锁环即使仍然存在也难以被检测到。所以分析死锁时不要只盯着锁图。一定要回看执行计划里有没有Table Scan或RID Lookup这两类操作意味着语句没走索引锁范围和持有时间都比预期大得多。索引设计在这个场景里不是“优化性能”而是“减少锁的数量”。3. 把Deadlock从黑匣子里抓出来跟踪标志、扩展事件和死锁图阅读3.1 打开1204和1222先让SQL Server自己把现场吐出来第一次遇到怪死锁最怕的是没有现场。SQL Server内置两个跟踪标志负责记录死锁1204和1222。1204输出的是精简文本格式紧凑适合脚本自动化报警1222输出的是结构化文本块包含完整的输入缓冲、锁列表和执行栈对人最友好。两个可以同时开错误日志里能看到两份不同格式的死锁报告。运行时开启重启失效适合临时分析-- 全局开启两个跟踪标志 DBCC TRACEON(1204, -1); DBCC TRACEON(1222, -1); -- 验证是否生效 DBCC TRACESTATUS(1204, 1222);参数说明-1表示全局作用域不加只能对当前会话生效。死锁检测是系统级进程必须全局开启。DBCC TRACESTATUS不加参数会列出所有已开启的跟踪标志加上标志号则只看指定的。要持久生效就把跟踪标志加到SQL Server服务启动参数里Windows服务属性里的启动参数一栏加-T1204 -T1222。Linux容器里改启动参数相对麻烦一般先用DBCC临时开确认有效再走运维配置。开了跟踪标志之后死锁发生时错误日志会出现deadlock victim关键字直接读错误日志确认EXEC sp_readerrorlog 0, 1, Ndeadlock victim;这个存储过程几个参数分别是日志编号、日志类型1为错误日志、搜索字符串。默认语言包下关键字是英文本地化版本可能需要换成对应语言的写法。顺手确认一下默认跟踪是开启的它能辅助补齐时间线信息EXEC sp_configure show advanced options, 1; RECONFIGURE; EXEC sp_configure default trace enabled, 1; RECONFIGURE;default trace enabled默认值是1如果被关掉部分历史死锁信息和数据库启动时间都会缺失。3.2 建一个常驻扩展事件会话把当时的完整SQL和参数一起留下跟踪标志能保死锁那几秒的现场但更完整的SQL文本、参数值、客户端信息要靠扩展事件Extended Events。与其等发生后再抓不如提前在每个生产库挂一个开销极小的会话专门捕获lock_deadlock和xml_deadlock_report两个事件。一个可直接抄的最小脚本CREATE EVENT SESSION [deadlock_capture] ON SERVER ADD EVENT sqlserver.lock_deadlock( ACTION (sqlserver.session_id, sqlserver.sql_text, sqlserver.tsql_stack, sqlserver.client_hostname)), ADD EVENT sqlserver.xml_deadlock_report( ACTION (sqlserver.session_id, sqlserver.sql_text)) ADD TARGET package0.event_file( SET filename NC:\XELogs\deadlock_capture.xel, max_file_size 20, max_rollover_files 5) WITH ( MAX_MEMORY 4 MB, EVENT_RETENTION_MODE ALLOW_SINGLE_EVENT_LOSS, MAX_DISPATCH_LATENCY 5 SECONDS, STARTUP_STATE ON ); GO ALTER EVENT SESSION [deadlock_capture] ON SERVER STATE START; GO参数说明sqlserver.lock_deadlock负责记录基本信息xml_deadlock_report负责输出完整死锁XML两个都挂。ACTION里的sqlserver.sql_text和sqlserver.tsql_stack是重点。死锁XML的inputbuf可能被截断加上这两个动作才能从XEL里读到完整语句和调用栈。client_hostname用于区分死锁来自哪台应用服务器排查多应用共享库时很有用。event_file目标比ring_buffer可靠机器重启或内存压力不会丢。注意filename路径必须存在SQL Server服务账户需要有写权限否则事件会话会静默失败。max_file_size20为单个文件20MBmax_rollover_files5表示最多5个文件滚动覆盖按每周几次死锁的量够保存几个月。MAX_DISPATCH_LATENCY5 SECONDS把落盘延迟从默认30秒降到5秒减少实例崩溃时丢失当次死锁的概率。STARTUP_STATEON让实例重启后自动启动该会话适合常驻。从XEL文件读死锁的SQLSELECT event_data.value((event/name)[1], nvarchar(50)) AS event_name, event_data.value((event/timestamp)[1], datetime2) AS event_time, event_data.value((event/data[namexml_report]/value)[1], nvarchar(max)) AS deadlock_graph, event_data.value((event/action[namesql_text]/value)[1], nvarchar(max)) AS sql_text, event_data.value((event/action[namesession_id]/value)[1], nvarchar(50)) AS session_id FROM ( SELECT CAST(target_data AS XML) AS target_data FROM sys.dm_xe_sessions AS s JOIN sys.dm_xe_session_targets AS t ON s.address t.event_session_address WHERE s.name Ndeadlock_capture AND t.target_name Nevent_file ) AS x CROSS APPLY target_data.nodes(EventFile/Event) AS n(event_data) ORDER BY event_time DESC;这段SQL会把XEL文件里所有事件展开成行deadlock_graph列得到的就是完整死锁XML可以直接复制到SSMS查询窗口用图形方式查看也能交给脚本解析。文件多时建议加时间过滤-- 只读最近一天的事件 WHERE event_data.value((event/timestamp)[1], datetime2) DATEADD(HOUR, -24, SYSDATETIME())3.3 读死锁图的顺序victim、lock、process三步定位死锁XML的结构固定分三段victim-list牺牲者、process-list进程详情、resource-list锁资源归属。手工读图我会严格按这个顺序来不走捷径。先看受害进程。SQL Server的锁管理器选择牺牲者不是按谁有错而是综合成本选择较低的会话。如果某个存储过程反复当牺牲者优先给它设置较低的死锁优先级让错误转移到能安全重试的会话给线上恢复留出时间。再看资源归属。每个资源块内的owner-list列出已持锁的进程waiter-list列出在等的进程。把每个资源上的owner指向waiter多条边就能画出一个等待环。如果整个resource-list只有一个keylock且owner和waiter的mode都是X就要回头检查waiter的requestType到底是wait还是convert。最后看进程详情。inputbuf可能被截断但executionStack里的frame会给出存储过程名、行号和语句偏移量能定位到具体哪条语句。如果两个进程是同一个存储过程的两个实例且都在做先SELECT后UPDATE大概率就是锁转换型或隔离级别过高导致持锁时长增加。死锁图里五个字段我会反复对照waitresource、hobtid、objectname、indexname、requestType。前四个能告诉你死锁落在哪张表哪根索引requestType告诉你是普通等待还是锁转换。最后用hobtid反查表名做确认SELECT s.name AS schema_name, o.name AS table_name, i.name AS index_name, p.index_id FROM sys.partitions AS p JOIN sys.objects AS o ON p.object_id o.object_id JOIN sys.schemas AS s ON o.schema_id s.schema_id JOIN sys.indexes AS i ON p.object_id i.object_id AND p.index_id i.index_id WHERE p.hobt_id 7205759405051904;hobt_id就是死锁XML里hobtid或associatedObjectId的值换成手头图上实际的数值。查出来的表名和索引名会直接告诉你死锁发生的位置。4. Deadlock排查避坑三个让我反复熬夜的盲区4.1 inputbuf被截断死锁图看着在现场总是差一口气现象错误日志里死锁图一张接一张但每个进程的inputbuf只有半截SQL按图里的存储过程名手动执行怎么跑都不死锁复现全靠运气。原因死锁XML的inputbuf默认只保留256个字符长SQL和具体参数值被截断。存储过程名能看到但真正卡住的那条语句的过滤值、事务范围、当时的参数组合全丢了。死锁是多个条件叠加的结果少一个参数就重放不出来。解决让扩展事件会话把sql_text和tsql_stack完整记下来具体脚本在3.2节已给。真实死锁发生后不要只看错误日志直接查询XEL文件拿到完整T-SQL。如果连sql_text都还是截断再给事件会话加sqlserver.parameterized_plan_handle动作把参数值从执行计划里挖出来。历史死锁且没有XEL时只能靠错误日志里的waittime和waitresource估算时间点再翻应用日志找那个时间段的连接和参数手工拼重放语句。这条路非常耗时所以监控制度建立之前我默认第一动作永远是先建XEL会话。补一个容易踩的坑扩展事件的目标目录如果不存在事件会话不会报错但文件不会生成。遇到“会话开了但没数据”的情况先检查目录是否存在以及SQL Server服务账户有没有写权限。4.2 索引缺失触发锁升级一个小更新把整张表锁死现象死锁图里出现一个PAGE级锁owner是一个会话的IX锁waiter是四五个不同模块的会话全在等同一页。单看死锁图会以为是一次页锁冲突但实际是整表范围的更新阻塞。原因更新语句的WHERE列没有索引优化器选了表扫描来定位目标行。扫描过程中引擎按页申请IX锁命中行申请X锁锁数量迅速累计。超过锁升级阈值后引擎把行锁升级成表锁。表锁与任何其他锁冲突所有访问这张表的写操作全部被卡住形成死锁图里“PAGE多个waiter”的典型结构。解决给WHERE过滤列建立合适的非聚集索引需要取回的列用INCLUDE覆盖避免书签查找带来的RID锁CREATE NONCLUSTERED INDEX IX_orders_status ON dbo.orders(status) INCLUDE (order_id, customer_id, total_amount);索引建完后观察sys.dm_db_index_usage_stats和死锁图PAGE/TABLE级的死锁会明显减少。更早感知风险用这条查询监控锁聚集SELECT resource_type, resource_associated_entity_id, request_mode, COUNT(*) AS granted_lock_count FROM sys.dm_tran_locks WHERE request_status GRANT GROUP BY resource_type, resource_associated_entity_id, request_mode HAVING COUNT(*) 100 ORDER BY granted_lock_count DESC;这条查询会把当前实例中锁超过100个的资源列出来如果出现TABLE类型的排他锁基本可以确定发生过锁升级。下一步直接查执行计划找那个Table Scan。4.3 快照隔离下的“没锁死锁”版本存储争用与更新冲突现象数据库开了ALLOW_SNAPSHOT_ISOLATION和READ_COMMITTED_SNAPSHOT理论上读操作不加锁。但业务还是频繁上报死锁错误错误日志里却没有新增Deadlock graph。原因快照隔离下读确实不加S锁但写之间的X锁互斥还在同时行版本存储会积累长事务的版本链。死锁检测器针对的是锁环不能直接捕捉版本存储的写冲突。两个快照事务更新同一行时系统报的是3966更新冲突应用层重试机制把它当死锁处理于是出现“没有死锁图但全是死锁”的假象。解决先定位是哪个长事务在制造版本SELECT t.database_id, t.session_id, t.transaction_begin_time, t.elapsed_time_seconds, dbt.version_generator_count FROM sys.dm_tran_active_transactions AS t JOIN sys.dm_tran_top_version_generators AS dbt ON t.database_id dbt.database_id ORDER BY dbt.version_generator_count DESC;找到对应会话后让它尽早提交或回滚观察tempdb的版本存储空间是否回落。根治方向有两个一是把大事务切成小批量每批提交缩短事务存活时间二是在应用层为重试逻辑区分“死锁”和“更新冲突”更新冲突不需要重试整个事务只需要从当前语句重新开始。快照隔离下业务里尽量避免同一行被多个事务并发的先读后写这个模式最容易触发更新冲突。另一个隐蔽点版本存储持续增长还会拖慢整个tempdb导致其他和版本存储无关的查询也变慢。监控tempdb空间时如果发现version store占用异常优先按上面的SQL找长事务而不是直接扩容。5. 用死锁优先级和执行计划反推验证把结论钉在证据上5.1 用SET DEADLOCK_PRIORITY验证关键路径死锁图分析完结论常指向“某条SQL不该成为牺牲者”。生产环境不方便直接改代码时可以用死锁优先级做一次低成本验证把怀疑不该牺牲的那个会话调低优先级观察死锁报告里的受害方是否变成它。SET DEADLOCK_PRIORITY LOW; -- 也可以直接给数值范围 -10 到 10 SET DEADLOCK_PRIORITY -5;数字越小越容易被当作牺牲者杀掉。如果调低后受害方按预期变成了它说明分析结论基本成立如果死锁仍然出现在原会话说明原会话在锁资源上的持有路径比预想更重要需要回去重新看它的事务边界和索引。5.2 用执行计划倒推锁请求顺序最后一步取死锁会话的执行计划看三件事有没有表扫描、有没有书签查找、预估行数和实际行数差多少。获取计划SELECT s.text, qp.query_plan FROM sys.dm_exec_query_stats AS qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS s CROSS APPLY sys.dm_exec_query_plan(qs.query_plan) AS qp WHERE s.text LIKE N%usp_order_archive% AND qp.query_plan IS NOT NULL;把query_plan保存成.sqlplan文件用SSMS打开对照Estimated Number of Rows和实际行数。偏差超过10倍就说明索引选择已经不可靠死锁只是表象根因在基数估计。我的习惯是每次处理完一个死锁把死锁图、当时的执行计划和最终改动存成一份简短文档放在团队共享目录。三个月后大概率会再遇到一模一样的奇怪死锁翻旧账比重新分析快得多。希望这份方法和命令能帮你在下次被死锁缠住时少熬一晚上。本文还有配套的精品资源点击获取