企业官网建设流程全解析

简介：这是一份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（索引键行）、PAGE（8KB数据页或索引页）、EXTENT（连续8个页）、TABLE（整表元数据）、DATABASE（数据库级锁）。大多数UPDATE最终申请的是KEY或RID锁：走堆表更新定位到的是RID，走索引更新定位到的是KEY。死锁图里看到的资源类型也以KEY和PAGE为主，很少直接出现TABLE。但资源粒度不是越细越好，锁不够时引擎会自动做锁升级（lock escalation），行锁变成页锁或表锁。触发条件不是固定数字，常见情况是单条语句累计锁数超过大约5000个，或由内存压力驱动。一旦升级成表锁，所有并发会话都挤在同一张表上排队，死锁候选集猛然变大，排查难度也跟着上来。

查看当前锁状态，最直接的是sys.dm_tran_locks：

SELECT 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_type：LOCK是普通申请，CONVERT是锁模式转换，WAIT是纯等待。
• request_status：GRANT表示已持锁，CONVERT表示正在等锁转换，WAIT表示排队中。
• lock_count：同一资源上的锁数量。数量大且集中在TABLE或PAGE，说明发生过锁升级。

锁模式的兼容性规则，我习惯记住最小子集：X与任何非X的锁冲突；U与S、U兼容，与X冲突；S与S兼容，与U、X冲突；意向锁之间基本兼容，只和同级别的排他锁冲突。手工读死锁图时拿不准，就对照下面这张表判断：

2.2 死锁形成的两种路径：循环等待与锁转换

教科书讲的循环等待是：A持资源1要资源2，B持资源2要资源1，这是最典型的“双钥匙”死锁。但生产环境里真正让我觉得“奇怪”的是第二种，锁模式转换型。同一个事务里先SELECT再UPDATE，SELECT拿到U锁，UPDATE要把U升级成X；另一个会话对同一行也干了同样的事。两个U锁可以共存，但升级时谁也不肯先放手，于是形成环。这次环上只有一把锁——同一个键，owner和waiter都指向它。死锁图里只有一个keylock，很多人第一反应是SQL Server画错了。

区分方式看死锁XML里waiter的requestType：

• wait：进程在等一个自己没有的锁，对应传统互斥。
• convert：进程已持有兼容锁，正在申请升级成冲突锁，对应锁转换型。

优化方向完全不同。wait型先调索引和隔离级别；convert型要先查事务内是不是同一条SQL既读又写。尽早用UPDLOCK把U锁直接申成X锁，或者把两个步骤合并成一条UPDATE并用OUTPUT取回数据，从源头消除U到X的转换窗口。

2.3 隔离级别控制持锁时长：死锁成因的隐形推手

默认的READ COMMITTED隔离级别下，读不长期持锁，S锁极短；REPEATABLE READ和SERIALIZABLE会把读锁保持到事务结束。事务里先SELECT后UPDATE，SELECT出来的这些行会一直被打着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, N'deadlock 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 = N'C:\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_size=20为单个文件20MB，max_rollover_files=5表示最多5个文件滚动覆盖，按每周几次死锁的量够保存几个月。
• MAX_DISPATCH_LATENCY=5 SECONDS把落盘延迟从默认30秒降到5秒，减少实例崩溃时丢失当次死锁的概率。
• STARTUP_STATE=ON让实例重启后自动启动该会话，适合常驻。

从XEL文件读死锁的SQL：

SELECT 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[@name="xml_report"]/value)[1]', 'nvarchar(max)') AS deadlock_graph, event_data.value('(event/action[@name="sql_text"]/value)[1]', 'nvarchar(max)') AS sql_text, event_data.value('(event/action[@name="session_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 = N'deadlock_capture' AND t.target_name = N'event_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倍就说明索引选择已经不可靠，死锁只是表象，根因在基数估计。

我的习惯是每次处理完一个死锁，把死锁图、当时的执行计划和最终改动存成一份简短文档放在团队共享目录。三个月后大概率会再遇到一模一样的奇怪死锁，翻旧账比重新分析快得多。希望这份方法和命令能帮你在下次被死锁缠住时，少熬一晚上。

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