简介这份Word文档围绕SQL Server中一例表现异常、排查过程颇具代表性的Deadlock展开适合数据库管理员、后端开发及运维人员理解锁与索引交互引发的阻塞问题。文档先给出可重复的最小实验步骤与建表脚本复盘一张含聚集索引和两个非聚集索引的表在并发update时稳定复现死锁的过程并对照去掉include列、将varchar(max)改为varchar(200)后死锁消失的测试结果讨论non-clustered index、rowlock与执行计划之间的关联。分析部分整理了两条常用路径借助SQL Trace与SQL Profiler筛选Locks、TSQL事件并按SPID过滤以及开启1222跟踪标记后读取错误日志中的deadlock-list、process-list等记录帮助读者定位等待资源、锁模式与牺牲者进程。压缩包仅含1个docx文件约694KB篇幅紧凑已有326人学习可作为死锁案例复盘与排查思路参考。1. 两条一样的 UPDATE 互相卡死这个 Deadlock 案例的复现与分析价值两条字面完全相同的 UPDATE 语句跑在 SQL Server 上互相把对方卡死在半路。第一次看 1222 输出里的 resource-list我盯着那几行 keylock 看了十几分钟也没想通——两个会话明明在更新同一行、走同一个索引怎么会一个持 U 锁等 X 锁、另一个持 X 锁等 U 锁。更反直觉的是把非聚集索引里的 include(d) 去掉或者把 d 从 varchar(max) 改成 varchar(200)SQL 语句一个字不改Deadlock 就消失了。这套环境只用一张表、三个索引、两个循环连接就能稳定复现分析路径却把跟踪标记、Profiler 锁事件、死锁图阅读、执行计划比对这几件常规手段全串了一遍。下面按复现、抓现场、读计划、定方案四步走你手上如果有类似的 update 抖动可以照着抄。2. 搭一个必现环境一张表三个索引怎么把 Deadlock 稳定造出来死锁分析最怕偶发。等业务高峰出问题再去抓往往 trace 没开、errorlog 已经滚掉只能靠猜。这个案例最大的价值就在于它能被完整复现表结构定死、数据量定死、循环写法定死两个窗口一跑几秒内一定死。所以第一步不是急着分析而是先把环境搭成一个「想让它死它就死」的状态。2.1 建表与索引include(d) 是整件事的开关先把库和表建出来。为了让 1222 输出里的索引名好读主键我显式命名成 pk_tt否则你会看到形如PK__tt__3213E83F10E07F16这种系统自动生成的哈希后缀读死锁图时多一层翻译成本。create database DeadlockLab; go use DeadlockLab; go create table tt( id int identity constraint pk_tt primary key, -- 聚集索引显式命名便于读 1222 输出 a char(36), -- 定长用来存 GUID 字符串 b char(36), d varchar(max) -- 关键点之一max 类型 ); go -- 两个非聚集索引都把 d 作为 include 列 create index ix_a_bc on tt(a) include(d); create index ix_b_cd on tt(b) include(d); go这张表的结构信息量很大逐项说一下。a和b用char(36)而不是varchar(36)是因为定长列在索引键里长度固定页内布局稳定能排除「变长导致行迁移」这类干扰因素让问题只集中在锁的顺序上。真正的主角是d varchar(max)——它决定了 d 的存储走 LOB 路径行内只留一个指针。而include(d)又把这一列复制到了两个非聚集索引的叶子层等于告诉 SQL Server这三个索引上都有 d 的副本需要维护。这两件事单独看都没毛病。组合起来一次update set d...就不再是「改一行」而是要同步改三处存储位置。锁的数量和申请顺序就是从这三处存储位置里长出来的。数据量要够但也不用太多。插入一万行足以让 ix_a_bc 和 ix_b_cd 各自占到多个数据页避免所有行挤在一页里被页锁一把罩住掩盖掉行级锁的行为。-- 插入 10000 行GO 10000 是 SSMS/sqlcmd 的批处理重复语法不是 T-SQL insert into tt select newid(), bbb, ddd; go 10000注意go 10000这条它在 SSMS 或 sqlcmd 里表示「把上面这条批处理重复执行一万次」如果你把这段贴进某些不支持 GO 的客户端会直接报语法错。这一点很容易踩坑执行后先用select count(*) from tt确认行数确实是 10000否则后面的复现会时灵时不灵。2.2 两个连接循环 UPDATE把偶发变成必现先随便挑一行把它的 a 值取出来当谓词。用 a 而不是 id 做条件是因为where a...会走 ix_a_bc 这个非聚集索引才会触发后面那套 U 锁路径如果写成where id10走聚集索引整个现象就不成立了。-- 取第 10 行的 a 值每次新建库这个值是随机的要自己替换 select id, a from tt where id 10;假设拿到的是EF211985-EA72-4A40-81DA-0AAB076E7AA3随机值你环境里必然不同。开两个查询窗口各跑一份下面的循环-- 在连接 1 和连接 2 里各跑一份谓词用上一步取到的 a 值 while 1 1 update tt with(rowlock) set d cd where a EF211985-EA72-4A40-81DA-0AAB076E7AA3;三个细节决定了它必现而不是偶发。第一with(rowlock)强制走行级锁避免优化器因为数据量小直接升级成页锁或表锁那会把冲突面糊成一片反而看不到清晰的两两互等。第二while 11让两个连接反复撞在同一行上把原本几十毫秒的时间窗口拉长成持续竞争正常情况下要撞几百次才出一次的死锁这里几秒内就到。第三两个连接跑的是完全相同的语句、相同的谓词谁先谁后完全随机这正是死锁最容易出现的形态。跑起来之后其中一个连接会收到 1205 错误消息类似「事务进程 ID xx与另一个进程被死锁在锁资源上并且已被选作死锁牺牲品」。看到它环境就算搭成功了。没看到就再多等一会儿或者确认两个窗口用的 a 值是不是同一个。2.3 三个对照实验去掉 include、改字段类型、看差异死锁稳定复现之后别急着分析先把对照组跑完。三个实验的差异会直接告诉你「哪一部分结构在制造额外的锁」这比一上来就啃 trace 高效得多。测试d 的字段类型非聚集索引现象测试 1varchar(max)带 include(d)稳定死锁测试 2varchar(max)不带 include(d)不死锁测试 3varchar(200)带 include(d)不死锁测试 2 的改法是把两个非聚集索引重建为不含 include 的版本drop index ix_a_bc on tt; drop index ix_b_cd on tt; -- 只保留键列不再 include d create index ix_a_bc on tt(a); create index ix_b_cd on tt(b);测试 3 不改索引只改字段类型。注意alter column要求先把依赖这列的索引处理掉否则会报「索引依赖于该列」drop index ix_a_bc on tt; drop index ix_b_cd on tt; alter table tt alter column d varchar(200); create index ix_a_bc on tt(a) include(d); create index ix_b_cd on tt(b) include(d);两个对照组跑完先别下结论。很多人到这一步会说「那就是 include 的锅」或者「那就是 max 类型的锅」这两种说法都不准确——测试 2 去掉了 include测试 3 保留了 include 但换了类型两者都不死锁说明真正起作用的不是「include 本身」或「max 本身」而是它们共同导致的东西。那个东西藏在执行计划里第 4 章会把它翻出来。现在先把现场抓全。3. 抓现场1222 跟踪标记加 Profiler 锁事件把 U 锁和 X 锁的来龙去脉挖出来死锁一旦复现第一反应应该是「留住证据」。1205 错误只告诉你是谁被牺牲了不告诉你为什么。SQL Server 给了两条取证路径跟踪标记 1222 让引擎把死锁详情写进 errorlogProfiler 抓锁的申请与释放事件还原时序。这两条路互补——1222 告诉你「谁在等谁」Profiler 告诉你「锁是什么时候拿的、什么时候放的」。既然环境可复现两个都开别省。3.1 打开 1222 并确认 SPID1222 比老式的 1204 输出更详细会列出每个进程的 executionStack、inputbuf以及 resource-list 里完整的 owner-list 和 waiter-list。-- -1 表示对所有连接生效不只是当前会话 dbcc traceon(1222, -1); go -- 确认当前窗口的 SPID后面做 Profiler 过滤要用 select spid;-1这个参数是关键。不少人图省事只写dbcc traceon(1222)那只对当前会话有效而跑循环的是另外两个连接死锁发生时它们的详情根本不会被记录白等一场。这里有个血泪经验排查生产环境的偶发死锁1222 一般建议全局常开它的开销极小但能省掉「问题复现了却什么都没留下」的后悔药。两个窗口分别执行select spid假设拿到 54 和 60记下来。注意一定要在实际跑循环的那个窗口里取别在第三个窗口取了然后套上去这是新手最常犯的错。3.2 Profiler 事件与列筛选只留有用的那几列开 Profiler 新建 Trace勾上「显示所有事件」和「显示所有列」然后按下面的清单选事件事件类别事件名用途LocksLock:Acquired记录每一次加锁看谁在什么时候拿了什么锁LocksLock:Released记录每一次放锁看锁的持有窗口有多长LocksLock:Deadlock死锁发生瞬间的事件LocksLock:Deadlock Chain死锁链能看到参与者的先后顺序TSQLSQL:BatchStarting标出批处理边界把锁事件和语句对上TSQLSQL:StmtStarting标出语句边界比 BatchStarting 更细选完事件点「列筛选器」在 SPID 上只保留 54 和 60。这一步不做的话你会在几千条锁事件里翻找而且大部分是系统会话的噪音。选完之后还有一个体验优化把 Mode、ObjectID、ObjectID2、Type 这几列拖到最前面。Profiler 默认的列顺序读起来非常费劲锁模式藏在很靠后的位置一屏看不到几个字段。配置好之后让两个连接跑循环等死锁发生停止 Trace。此时的文本输出会长成一片但只要你按 ObjectID2 排序、按 SPID 分组就能看出两条清晰的轨迹。3.3 读 errorlog 里的 deadlock-listprocess-list 和 resource-list 怎么对死锁发生后用下面的命令把 errorlog 里的死锁记录捞出来-- 参数依次是日志文件号0当前、日志类型1SQL Server、搜索关键字 exec sp_readerrorlog 0, 1, Ndeadlock;如果不确定死锁记录滚到哪个文件了可以先用exec sp_readerrorlog不带参数看当前文件再逐步往前翻文件号。生产环境 errorlog 轮转快的时候这一步要尽快做。记录很长但结构是固定的只需要盯两段。process-list 里每个 process 节点描述一个参与者重点看spid、lockMode当前在等什么模式的锁、waitresource等的是哪个资源。resource-list 里每个 keylock 节点描述一个被争用的资源重点看indexname、mode、owner-list和waiter-list。这个案例里两段关键信息是这样的形态-- 精简后的关键片段实际输出字段更多 process idprocess_x spid60 lockModeX waitresourceKEY: 8:72057594066108416 (ef8a9edf5a1e) process idprocess_y spid54 lockModeU waitresourceKEY: 8:72057594065518592 (d08358b1108f) keylock indexnameix_a_bc modeU owner idprocess_y modeU waiter idprocess_x modeX keylock indexnamepk_tt modeX owner idprocess_x modeX waiter idprocess_y modeU翻译成人话spid 54 在 ix_a_bc 上持有 U 锁等着拿 pk_tt 上的 U 锁spid 60 在 pk_tt 上持有 X 锁等着拿 ix_a_bc 上的 X 锁。环形等待成立死锁成立。到这一步「谁等谁」已经清楚了但「为什么会形成这个环」还不清楚。因为单看这一瞬间两条一模一样的 update 本该走同样的加锁顺序同顺序不会死锁。所以一定有某个时刻两个连接的加锁顺序发生了错位。这就得靠 Profiler 的 Acquired/Released 事件来还原。3.4 用 Lock:Acquired 与 Lock:Released 还原加锁时序Profiler 里的 ObjectID2 字段值就是 1222 输出里的 hobt_id堆或 B 树的标识。它和具体索引的对应关系必须用当前环境的系统视图去查不能照抄别人文章里的数字-- 用 1222 输出里看到的 hobt_id 去反查索引名 select o.name as table_name, i.name as index_name, i.type_desc, p.partition_id from sys.indexes i join sys.objects o on i.object_id o.object_id join sys.partitions p on p.index_id i.index_id and p.object_id i.object_id where p.partition_id 72057594065518592; -- 换成你自己输出里的值把 trace 里出现的几个 hobt_id 全部翻译一遍就能得到索引名和分区 ID 的对照。这里特别提醒partition_id/hobt_id是每次建库、每次重建索引都会变的同一套脚本在别人的机器上跑出来的数字必然不同。照抄数字去 join结果一定是空集然后误以为脚本写错了。对照关系建立之后把 Lock:Acquired 和 Lock:Released 按时间排序就能还原出一次成功 update 的完整节奏。这个案例里测试 1 的时序大致是时刻连接 1spid 54连接 2spid 60t1等待ix_a_bc 申请 U 锁成功t2等待pk_tt 申请 U 锁成功后转 X 锁t3等待ix_a_bc 释放 U 锁t4ix_a_bc 申请 U 锁成功进入下一步t5pk_tt 申请 U 锁等待ix_a_bc 申请 X 锁等待环就在 t5 这一刻闭合。连接 2 之所以在 t3 把 ix_a_bc 的 U 锁放掉是因为 update 的第一阶段通过 ix_a_bc 定位到行、更新聚集索引已经完成它马上进入第二阶段要更新两个 include 了 d 的非聚集索引于是重新申请 ix_a_bc 的 X 锁。而就在这个空档里连接 1 抢到了 ix_a_bc 的 U 锁接着去要 pk_tt 的锁撞上了连接 2 手里的 X。两边互不相让。对照组的数据更有说服力测试 2 和测试 3 的 trace 里ix_a_bc的 U 锁一旦释放后面就没有「再申请 X 锁」的动作了整个 update 在拿着 pk_tt 的 X 锁那一步就结束了。没有第二阶段的重新加锁环就形不成。4. 读执行计划varchar(max) 与 include(d) 如何把一次更新拆成三步锁的时序是「症状」执行计划是「病因」。同一张表、同一条 update在三种结构下产生三种不同的计划形态锁的数量和顺序全是从计划形态里派生出来的。这一章把计划摊开看。看计划不需要什么特殊工具SSMS 里一条set statistics profile on就够文字版比图形版更适合逐行比对。4.1 set statistics profile on 拿到文字计划在 update 之前打开统计执行完再关掉。注意要单独一个批次别和循环混在一起否则会刷出成百上千份计划。set statistics profile on; go update tt with(rowlock) set d cd where a EF211985-EA72-4A40-81DA-0AAB076E7AA3; go set statistics profile off; go输出是一张表核心列是StmtText、Rows、Executes。StmtText靠缩进表示算子层级越靠右越深。读的时候不要一行一行看先扫「Index Update」「Clustered Index Update」「Table Update」这些关键字出现了几次几次就代表这条语句要动几处存储。4.2 三个 Index Update 与一个宽更新算子三种结构下计划形态的差别非常直观场景更新类算子数量覆盖的对象结果测试 1varchar(max) include(d)3 个pk_tt、ix_a_bc、ix_b_cd稳定死锁测试 2varchar(max)不 include1 个仅 pk_tt不死锁测试 3varchar(200) include(d)1 个三个对象合并不死锁测试 1 的计划里出现三个独立的更新算子意味着 SQL Server 要分三次去动三棵 B 树先动聚集索引再动 ix_a_bc再动 ix_b_cd。每动一棵树都要拿对应的 X 锁而锁不是一次性全拿的是做到哪一步拿哪一步。这就是第 3 章看到的「第一阶段结束后释放 ix_a_bc 的 U 锁第二阶段再申请它的 X 锁」的来源。测试 2 只有一个更新算子因为 d 不在任何非聚集索引里改 d 不影响 ix_a_bc 和 ix_b_cd 的内容SQL Server 只更新聚集索引就够了。既然只需要一把 X 锁两个连接就是简单地排队形不成环。测试 3 最有意思算子是一个但它的对象列表里有三个。这是所谓的宽更新计划——SQL Server 把「改聚集索引行」和「改两个非聚集索引的 include 列」合并成一次操作用一个算子同时维护三棵树锁也一次性拿到位。既然没有中间释放再申请的过程环同样形不成。4.3 为什么 varchar(max) 会把一步拆成三步差别就在varchar(max)的存储方式上。这一列的长度可以超过 8000 字节SQL Server 不能把它完整塞进行内行里只放一个指向 LOB 页的指针真正的数据存在单独的 LOB 树里。更新这样一列涉及的操作比更新一个行内定长列复杂得多既可能要分配新的 LOB 页、可能要改行内的指针、还要同步维护索引里的副本。常见做法是把原因归结为当被更新的列是 LOB 类型、同时又被非聚集索引 include 时优化器无法生成「一次覆盖多个对象的宽更新计划」只能退化成多个独立的更新算子逐棵树去改。这是我目前的解释也是跟执行计划形态最吻合的解释——测试 3 只是把 max 换成 200列变成行内可承载的变长列宽更新计划立刻回来了三个对象被一个算子覆盖。反过来说include本身没有错。include 是为了做覆盖索引让查询不回表这是正当需求测试 2 里去掉 include 虽然解决了死锁但可能把某个查询从覆盖扫描打回 key lookup。真正不妥的组合是「把 max 类型的列放进索引的 include 里」——既把索引体积撑大又破坏了宽更新的可能性还顺带把死锁概率抬上去了。4.4 加锁数量、顺序与执行计划的对应关系把这一章和第 3 章合起来看可以得到一条很实用的推断链执行计划里有几个更新算子就大致对应几轮加锁放锁轮次越多两个并发事务的加锁顺序发生错位的窗口就越多窗口越多死锁概率越高。这条链在这三个测试里完全成立。它也解释了一个容易被忽略的现象为什么把d改成varchar(200)后死锁消失但把d从 varchar(max) 改成varchar(8000)在某些版本上依然可能死锁。因为问题不在于「长度写没写」而在于这一列最终落在行内还是 LOB 里以及优化器能不能把它和索引维护合并成一步。所以在做类型治理时别只盯着「别用 max」这句口号要拿执行计划验证一遍。到这一步结论已经能落到可执行的层面了加锁的数量和顺序由执行计划决定不由 SQL 语句的字面决定。索引越多、被更新列的类型越复杂计划越复杂遇到阻塞或死锁的概率越高。消除死锁的入口是执行计划先让计划里的更新算子变少锁自然就变少。对这个案例本身来说把varchar(max)塞进两个非聚集索引的 include本身就是个不太妥当的设计。合理的处理是把 d 从索引里移除或者给它一个合理的长度上限。两条路都能把三个更新算子压回一个。5. 排查避坑这套死锁分析流程里最容易翻车的五个点方法论讲完接下来是实操里真正会卡住人的地方。这套流程看着步骤清晰但每一步都有它的暗坑我见过太多次明明复现了、trace 也抓了最后因为一个参数或一个过滤条件全白干的情况。下面五条按「现象 → 原因 → 解决」写都是能直接对上的。5.1 抓不到现场两个最常见的空手而归现象一死锁明明发生了errorlog 里搜 deadlock 却什么都没有。原因通常是 1222 没开成全局。只执行dbcc traceon(1222)而不带-1标记只作用于当前会话跑循环的是另外两个连接它们发生死锁时引擎不会往 errorlog 写详情。另一种可能是 errorlog 已经轮转记录被挤到更早的文件里去了。解决办法是执行dbcc traceon(1222, -1)确认全局生效然后用exec sp_readerrorlog逐个文件号往前翻。如果环境里配置了扩展事件会话也可以直接从会话目标文件里取死锁报告比翻 errorlog 更稳。现象二Profiler 里按 SPID 过滤之后一条锁事件都抓不到。原因几乎都是 SPID 取错了窗口。select spid返回的是执行这条语句的那个连接的 ID如果你在第三个空白窗口里执行它拿到的 ID 跟跑循环的两个连接毫无关系过滤器自然全挡掉。解决办法是在每个实际跑循环的窗口里各执行一次select spid把两个返回值都加进过滤列表。加完之后先跑几秒确认有事件进来再正式复现。5.2 误读锁信息两个把人带偏的直觉现象三看到 U 锁就认定是 update 语句写错了。U 锁是更新锁是 update 在「先定位、后修改」过程中的正常中间态几乎每条走索引定位的 update 都会拿它。它的存在本身不代表异常异常的是它在错误的时机被释放、又在错误的时机被重新申请。分析的时候要盯的是「锁的持有者是谁、等待者要什么模式」而不是「有没有 U 锁」。解决办法是把注意力放在 resource-list 的 owner-list 和 waiter-list 上画出环形等待再回到 trace 里找这个环是什么时候形成的。现象四照着别处的 hobt_id 去查索引名查出来是空集。partition_id、hobt_id这类标识每次建库、每次重建索引都会重新生成不具备跨环境可比性。别人的文章里写72057594065518592 对应主键你套到自己的库上只会得到零行。解决办法是永远用自己 1222 输出里的数字去 joinsys.partitions。查之前先确认这个 ID 在当前库里确实存在不存在就说明你抄的是别处的值。5.3 改索引时的连带风险现象五按建议去掉了 include(d)死锁没了但某个查询突然变慢十几倍。原因是那个 include 恰好是某个高频查询的覆盖列。去掉之后查询从「索引覆盖扫描」退化成「索引查找 key lookup」执行计划和 IO 都变了。为了治一个死锁制造了一个慢查询。解决办法是改索引之前先盘点这个索引被哪些语句用过、有没有依赖 include 列做覆盖。可以在改之前把相关语句的执行计划和 IO 统计存一份改完再跑一遍对比。如果确实需要覆盖就走另一条路——把 d 的长度收窄成 varchar(200) 之类保留 include 的同时恢复宽更新计划。6. 把结论落成脚本索引列类型审查与更新计划验证这套分析做完最有价值的产出不是「这个案例修好了」而是一条可以反复用的检查动作。我现在接手任何一张要加索引的表都会先跑一遍索引列类型审查把 include 里藏着的 max 类型列捞出来。-- 列出指定表所有索引的键列与 include 列标出 max 类型 select i.name as index_name, i.type_desc, c.name as column_name, t.name as data_type, c.max_length, ic.is_included_column, ic.key_ordinal from sys.indexes i join sys.index_columns ic on i.object_id ic.object_id and i.index_id ic.index_id join sys.columns c on ic.object_id c.object_id and ic.column_id c.column_id join sys.types t on c.user_type_id t.user_type_id where i.object_id object_id(dbo.tt) order by i.index_id, ic.key_ordinal, ic.index_column_id;关键在max_length这一列值为 -1 就代表这是 max 类型varchar(max)、nvarchar(max)、varbinary(max) 都是。只要在结果里看到is_included_column 1且max_length -1的行就该警惕——这正是本案例里制造三个更新算子的那个组合。同样的思路可以扩到整库扫描把where i.object_id object_id(dbo.tt)换成按 schema 过滤一次把全库的隐患列出来。改完之后用执行计划做验证别靠「跑一跑看看还死不死」这种玄学判断-- 改索引前后各跑一次对比更新算子数量 set statistics profile on; go update tt with(rowlock) set d cd where a EF211985-EA72-4A40-81DA-0AAB076E7AA3; go set statistics profile off; go判断标准很直接StmtText里「Index Update」「Clustered Index Update」这类算子从三个变成一个且这一个算子的对象列表覆盖了三棵树就说明宽更新计划回来了加锁轮次从三轮压到一轮这个死锁的成因被消除。如果算子数量没变说明类型改得还不够窄或者索引上的 include 还有别的 LOB 列在拖后腿。再补一个观察维度改索引前后分别用set statistics io on看逻辑读。宽更新计划通常伴随更少的页访问因为三棵树的维护被合并成一次遍历。如果逻辑读反而涨了大概率是索引体积变化导致页分裂要回头检查填充因子。从那以后我每次给表加 include 列都会先跑一遍上面那段审查脚本确认没有 max 类型的列混进去再决定是否真的需要这个覆盖索引。这个习惯帮我挡掉的不只是死锁还有一堆索引膨胀和备份变慢的麻烦。希望帮到你。本文还有配套的精品资源点击获取