SQL Server UPDATE 死锁：索引加锁顺序与执行计划复盘

简介这份文档聚焦 SQL Server 中一例表现反常的死锁面向数据库开发、运维与性能调优人员尤其是具备 T-SQL 和索引基础、希望提升锁冲突排查能力的中级读者。作者用可稳定复现的测试表梳理 clustered index、两个 non-clustered index 及 include 列、varchar(max) 字段类型与 rowlock 的关联并对比删除 include 选项或收窄字段类型后死锁消失的现象说明执行计划与锁资源的变化。分析部分结合开启 1222 跟踪开关、SQL Profiler 采集 Locks 与 TSQL 事件、按 SPID 过滤及读取 errorlog 中 deadlock-list 等手段还原等待资源、锁模式和 victim 进程信息帮助读者建立从现象复现到日志解读的排错路径。资源包仅含 1 个 docx 文件约 694KB内容集中便于查阅目前已有 326 人学习。1. 两条一样的 UPDATE 互锁先别怀疑数据库版本两条一模一样的 UPDATE同一张表、同一个 WHERE 条件、都带 rowlock跑着跑着 SQL Server 就把其中一个连接选成牺牲品报 1205。第一次碰到这个 SQL Server 死锁现场我盯着 deadlock graph 看了半小时也没想通两条语句连访问路径都一样凭什么互锁后来把 Trace 里 Lock:Acquired / Lock:Released 一条条排开才看明白问题不在语句在执行计划——UPDATE 被拆成“先更新聚集索引、再更新两个非聚集索引”两个连接一个停在第一步、一个停在第二步加锁顺序正好反过来。这类死锁的迷惑性在于它不挑写法表上索引一多、include 里又带了被更新的列测试环境两分钟就能稳定复现生产上几个月才冒一次等拿到 errorlog 时现场早没了。后面按“复现、抓证据、还原加锁顺序、改索引”走一遍。2. 搭一个能稳定复现的现场三个索引加一个 varchar(max)2.1 建表与索引让病根只由三个变量构成先把最小可复现结构搭出来。它要同时满足三个条件有聚集索引主键、有两个 include 了同一列的非聚集索引、被 include 的那列是varchar(max)。少任何一个死锁就复现不出来这也是后面做对照实验的基础。-- 在任意测试库执行示例库名用 Demo 代替 use Demo; go create table tt( id int identity primary key, -- 聚集索引后续 UPDATE 会在这里反复申请 X 锁 a char(36), -- 存 GUID 字符串配合 where a ? 走 ix_a_bc b char(36), d varchar(max) -- 关键变量大字段同时被两个非聚集索引 include ); go -- 两个非聚集索引都 include(d)意味着 d 的值在每个索引的叶子页里都存了一份 create index ix_a_bc on tt(a) include(d); create index ix_b_cd on tt(b) include(d);建完表先确认索引结构没写错别把 include 加到自己以为的地方去-- 看清楚每个索引的键列和包含列 select i.name as index_name, i.type_desc, c.name as column_name, ic.is_included_column from sys.indexes i join sys.index_columns ic on ic.object_id i.object_id and ic.index_id i.index_id join sys.columns c on c.object_id ic.object_id and c.column_id ic.column_id where i.object_id object_id(dbo.tt) order by i.index_id, ic.key_ordinal;这条查询的输出里is_included_column 1的行就是被 include 进去的列。如果 d 在三行里都出现说明结构对了。很多人在这一步翻车是因为在正式库上include(d)早就被某次变更脚本改掉了自己却拿着老建表脚本在推演结论自然对不上。2.2 灌数据与并发 UPDATE把概率问题变成必然问题往表里灌一万行。行数不是随便定的行太少两个连接很可能落在同一个数据页上加锁表现会失真一万行能把索引撑到多个页让两条 UPDATE 有机会落在不同的键上同时又在同一个索引结构里竞争。-- SSMS 里 go 10000 是批处理重复执行会插入 10000 行 -- 如果用的客户端不支持这种写法老老实实写 while 循环 insert into tt select NEWID(), bbb, ddd; go 10000灌完随便挑一行把它的 a 字段值记下来后面当 WHERE 条件用select * from tt where id 10; -- 示例值EF211985-EA72-4A40-81DA-0AAB076E7AA3每次都是新生成的随机值两个连接各开一个查询窗口同时跑下面这段循环-- 两个连接跑同一段WHERE 条件里的值换成上一步查出来的 a -- rowlock 提示的原意是“尽量用行锁”但它改变不了 UPDATE 要维护三个索引的事实 while 1 1 update tt with(rowlock) set d cd where a EF211985-EA72-4A40-81DA-0AAB076E7AA3;这里要说清rowlock提示到底做了什么。它只影响锁的粒度不会减少锁的数量。一条要更新 d 的语句因为 d 同时存在于聚集索引和两个非聚集索引里仍然要在三个索引上分别申请锁rowlock一把都省不掉。我见过不少人加了这个提示就以为万事大吉结果死锁照旧反而更迷惑。另一个常被忽略的点是while 1 1。它不是噱头而是把“偶发”变成“必现”的手段单次 UPDATE 只有毫秒级的窗口循环几百次之后两个连接的加锁时间线必然会撞在一起。压测环境复现死锁靠的就是这种高频重试。提示跑之前先确认这是测试实例。这类循环 UPDATE 在正式库上会持续持锁很容易把业务连接拖进来。2.3 三组对照把变量一个一个摘掉死锁稳定复现之后别急着抓 Trace先做对照实验。控制变量才能锁定到底是哪个因素在起作用。第一组是原始结构varchar(max) 两个 include(d)预期死锁。第二组把 include(d) 摘掉索引只建在 a 和 b 上-- 测试 2去掉 include(d)其他保持不变 drop index ix_a_bc on tt; drop index ix_b_cd on tt; create index ix_a_bc on tt(a); -- 不再 include(d) create index ix_b_cd on tt(b);同样两个连接跑循环。如果不再死锁就说明 include 是变量之一。第三组在测试 2 的基础上把 include 加回来但改数据类型-- 测试 3把 d 从 varchar(max) 缩小成有长度限制的 varchar(200) 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);如果这一组也不死锁那结论就非常明确了不是“索引多”这么笼统的原因而是“被更新的是一个大字段且这个大字段被两个非聚集索引覆盖”这个组合在作祟。三组测试跑下来大概十几分钟比直接去翻 errorlog 猜半天要省事得多。3. 打开黑匣子1222 开关与 SQL Trace 的抓取细节3.1 1222 开关先把完整死锁图留下来SQL Server 死锁最麻烦的地方在于它自己会自愈——牺牲掉一个事务另一个继续跑业务侧往往只看到一次 1205 就恢复了。现场不留证据事后只能靠猜。所以要做的第一件事是让引擎把死锁信息写下来。-- 开启 1222 跟踪标志-1 表示对所有会话全局生效 -- 不加 -1 只对当前连接有效别的连接死锁了照样抓不到 dbcc traceon (1222, -1);参数说明第一个参数是跟踪标志号1222输出的是 XML 格式的死锁图第二个参数-1是作用域表示全局。较新的版本里引擎默认会往 errorlog 写一部分死锁信息但格式和完整度都不如 1222尤其是resource-list里的锁模式细节所以能开就开着。开完之后先用下面这句拿到两个业务连接的 SPID后面做 Trace 过滤要用select spid; -- 两个查询窗口各跑一次比如得到 54 和 60复现死锁之后直接把 errorlog 里的记录捞出来-- 参数0 当前 errorlog1 错误日志类型deadlock 内容过滤关键字 exec sp_readerrorlog 0, 1, Ndeadlock;这条命令比去 SSMS 的“管理 → SQL Server 日志”里翻几百页要快得多。如果 errorlog 已经滚动过把第一个参数改成 1、2、3 继续往前翻直到找到那次死锁的时间点。3.2 读 process-list牺牲品是谁、它当时在等什么拿到deadlock-list之后不要一行行读按块读。第一块是deadlock victim它告诉你谁被干掉了。示例输出里牺牲的是spid60而spid54活了下来。接下来看两个process节点里的关键字段字段含义排查时的用法spid会话号和业务连接对上waitresource当时在等的资源格式为KEY: dbid:hobtid (hash)是定位索引的关键lockMode等待的锁模式U是更新锁X是排他锁isolationlevel隔离级别本例是read committed说明不是隔离级别惹的祸executionStack当时的语句确认死锁确实发生在 UPDATE 上而不是触发器或别的语句trancount事务嵌套层数大于 1 时要留意是不是应用层开了显式事务示例里两个进程的waitresource分别是KEY: 8:72057594066108416和KEY: 8:72057594065518592锁模式一个是X、一个是U。这两串数字就是后面要翻译成索引名的 hobtid。3.3 读 resource-list谁持锁、谁在等锁resource-list是整份输出里信息密度最高的部分它把“谁挡了谁”说得很直白。示例里有两个 keylock 节点第一个indexnameix_a_bc持有者是 spid 54modeU等待者是 spid 60modeX。第二个indexnamePK__tt__...主键聚集索引持有者是 spid 60modeX等待者是 spid 54modeU。结论一眼可见连接 1 拿着ix_a_bc的 U 锁想要聚集索引上的锁连接 2 拿着聚集索引的 X 锁想要ix_a_bc上的 X 锁。两个方向的等待闭合成了环。但 1222 只能告诉你“谁挡谁”告诉不了你“为什么会走到这一步”。要还原加锁的先后顺序必须上 Trace。3.4 SQL Trace 抓加锁顺序把 Mode、ObjectID、ObjectID2 提到前面新建一个 Trace事件选择上有个小技巧先勾上 “Show all events” 和 “Show all columns”否则你会找不到很多关键列。Locks 类别下至少要选Lock:Acquired拿到了什么锁Lock:Released放掉了什么锁Lock:Deadlock死锁发生的那一刻Lock:Deadlock Chain死锁链条能看到参与的多个 SPIDTSQL 类别下选SQL:BatchStarting、SQL:BatchCompleted、SP:StmtStarting用来对齐“哪条语句在什么时候执行”。然后在 Column Filters 里把 SPID 限死只留两个业务连接和几个系统 SPID示例里是 54、60、6、20。系统 SPID 也留着是因为后台任务偶尔会插进来持锁漏掉就可能把因果看错。Trace 跑起来之后输出会非常难读。我的习惯是把Mode、ObjectID、ObjectID2、Type四列拖到最前面然后按 SPID 排序一行一行往下捋。这个动作看起来很笨但它是唯一能把“申请—释放—再申请”的时间线完整还原出来的方法。hobtid 是数字要翻译成索引名才好读-- 用 partition_id 反查表名和索引名把 hobtid 换成 Trace 里看到的那个值 select o.name as table_name, i.name as index_name, i.type_desc 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;三个 hobtid 翻译出来的结果是这样一组对应关系后面的所有推演都依赖它hobtid对应索引72057594065518592主键聚集索引PK__tt__...72057594066108416非聚集索引ix_a_bc72057594066173952非聚集索引ix_b_cd注意不同版本、不同分区方式下partition_id的取法可能对不上如果查不到结果退回用sys.indexes直接列出全部索引靠index_id和 Trace 里的对象编号交叉比对。4. 根因在加锁顺序UPDATE 由一步变两步的全过程4.1 一次“正常”的 UPDATE 得申请三把 X 锁先看没有并发冲突时一条 UPDATE 会加多少锁。从Lock:Acquired里能读出的顺序是在ix_a_bc上申请 U在聚集索引上申请 U在聚集索引上把 U 升成 X再在ix_a_bc上申请 X然后ix_b_cd上申请 X最后依次释放。锁的数量是三把 X分别对应三个索引。原因是 UPDATE 改了 d而 d 在三个索引里都有副本聚集索引里是行本身两个非聚集索引里是 include 的列。任何一个副本不同步索引就坏了所以每个索引都要单独维护。在单连接场景下这个顺序毫无问题因为没人跟它抢。问题出在两个连接同时跑到这里的时候加锁顺序一样只要不交错就没事一旦交错环就形成了。4.2 还原死锁时序那次 U 锁释放才是关键窗口把 Trace 里两个 SPID 的记录按时间对齐能还原出下面这张表。这是我根据多轮 Trace 整理的顺序不是引擎文档里的固定流程但每次复现都能对得上时刻连接 1SPID 54连接 2SPID 60说明T1ix_a_bc申请 U拿到ix_a_bc申请 U拿到U 与 U 兼容两边都过T2聚集索引申请 U拿到聚集索引申请 U拿到同上T3—聚集索引 U 升 X拿到连接 2 etc 先进入写阶段T4—释放ix_a_bc上的 U第一步收尾U 锁不再需要T5ix_a_bc申请 U拿到—连接 1 趁这个空档进入第一步T6聚集索引申请 U等待—被连接 2 持有的 X 挡住T7—ix_b_cd申请 X拿到连接 2 开始第二步T8—ix_a_bc申请 X等待被连接 1 刚拿到的 U 挡住T9等待等待闭环形成引擎判定死锁整件事的关键就在 T4 那一行。连接 2 完成第一步之后把ix_a_bc上的 U 锁释放了这个释放本身完全合理——U 锁在语句推进时用完就该放。但它制造了一个时间窗口让原本被挡在门外的连接 1 挤了进来拿到了ix_a_bc的 U 锁。紧接着连接 2 进入第二步要更新ix_a_bc发现锁被别人占着而连接 1 想往聚集索引推进又被连接 2 的 X 锁卡住。两边都没做错只是先后顺序错开了半拍。至于牺牲品为什么是连接 2这跟回滚代价有关引擎会挑一个代价小的干掉。但别指望这个规律稳定下一次可能换个方向牺牲。4.3 执行计划三个 Index Update 与一个“合并更新”到这里 1222 已经解释不了更多了得看执行计划。在 UPDATE 前面加一句-- 输出文字版执行计划跑完 UPDATE 后结果集里会多出一段计划文本 set statistics profile on; go update tt with(rowlock) set d cd where a EF211985-EA72-4A40-81DA-0AAB076E7AA3;也可以换成set statistics xml on输出可点击的图形计划。三组测试的计划差异非常大测试结构计划里的更新节点是否死锁测试 1varchar(max)include(d)三个独立的 Index Update是测试 2varchar(max)不 include一个 Index Update只有聚集索引否测试 3varchar(200)include(d)一个 Update 节点内含三个 Object否测试 1 里出现三个 Index Update正好对应三把 X 锁也对应“分两步执行”的观察结果第一步更新聚集索引第二步分别更新两个非聚集索引中间存在释放和重新申请的间隙。测试 2 只有一个 Index Update因为 d 不在非聚集索引里改 d 不影响它们只需要更新聚集索引一把锁连窗口都没有。测试 3 最有意思计划里只有一个 update 节点但里面包着三个 Object也就是说引擎把三个索引的维护合并成一步做完了。常见的解释是varchar(max)属于大对象类型不能和行内数据一起内联存放索引叶子页里存的是指向 LOB 的指针所以只能拆成“先更新基表、再逐个更新索引”的多个独立操作换成varchar(200)之后列可以随行内联引擎就能在一个操作里同时维护三个索引。先把话说清楚这个解释是对现象的归纳不是我翻引擎源码看来的。判断方法很简单——看计划节点数量别去背理论。4.4 修复方案与取舍先改结构再考虑提示知道根因之后解法就不止一种了取舍在于业务能不能接受。首选是把 d 从索引里移出去。如果那两个非聚集索引的覆盖查询根本用不到 dinclude(d)就是纯粹的成本删掉它既解决死锁又减小索引体积、降低写放大-- 重建索引去掉 include(d)保留原有键列 drop index ix_a_bc on tt; create index ix_a_bc on tt(a); -- ix_b_cd 同理次选是把varchar(max)换成有长度限制的类型。适合那种“业务上明明有上限建表时图省事写成 max”的场景-- 先确认真实最大长度别拍脑袋定 200 select max(len(d)) as max_len from tt; alter table tt alter column d varchar(200);如果 d 确实必须是大字段、又必须被索引覆盖那就只能从并发控制上想办法把这段循环改成走聚集索引比如按 id 更新让访问路径只落在主键上或者在应用层对同一个键做串行化再或者加重试逻辑兜底。原文评论区里提到的holdlock提示和 plan guide也是常见做法——前者让锁一直持有到事务结束消灭 T4 那个时间窗口后者强制走一个锁更少的计划。但这两个都属于“压住症状”索引结构不合理这件事并没有解决我不建议把它们当成首选。5. 避坑清单这类索引死锁最容易踩的五个坑5.1 坑一只看 deadlock graph 就开始改索引现象拿到 XML 死锁图看到两个 SPID 在两个索引上互相等待立刻下结论“索引顺序不一致”开始调索引顺序改完发现死锁照旧。原因死锁图只给结果不给过程。它告诉你谁持有什么、谁在等什么但不告诉你这些锁是按什么顺序申请和释放的。本例中两个连接的加锁顺序完全一样是中间那次 U 锁释放制造了窗口光看图永远推不出来。解决养成“图 Trace”双证据的习惯。先用 1222 定位资源再用Lock:Acquired/Lock:Released还原时间线把每一步对齐成表格。看着笨但这是唯一能把因果讲清楚的路子。5.2 坑二以为加了 rowlock 就不会锁表锁页现象语句里明明写了with(rowlock)还是死锁于是怀疑提示没生效或者怀疑引擎有 bug。原因rowlock只约束粒度不约束数量。UPDATE 要维护三个索引就得在三个索引上各申请一次锁粒度再细也是三把锁。本例中加了 rowlock 反而更容易复现因为它让并发连接能同时深入到键级交错的机会更多。解决别把提示当解药。先看执行计划里有几个更新节点节点数量才是锁数量的直接来源。计划里的更新节点从三个降到一个锁自然就少了。5.3 坑三把 varchar(max) 塞进 include 当覆盖索引用现象为了消掉一个查询的 Key Lookup顺手在索引上include了一个大字段查询是快了写操作开始隔三差五报 1205。原因varchar(max)这类大对象列不能随行内联存储索引叶子页里放的是指针更新它必须走独立的维护路径执行计划被拆成多个更新节点加锁顺序随之变复杂。索引越多、字段越大计划越复杂撞上阻塞和死锁的概率越高。解决include之前先问一句“这一列真的需要被覆盖吗”。如果只是偶尔查让它走 Key Lookup 反而更划算。确实要覆盖就把类型收紧到业务真实上限别用 max 图省事。5.4 坑四事后去 errorlog 里找现场结果日志已经滚了现象业务报了一次 1205等 DBA 上去查sp_readerrorlog里干干净净最近几小时的日志都还在就是没有那次死锁的记录。原因errorlog 会滚动默认保留的份数有限高频重启或者日志量大的实例几小时内就能把现场冲掉。而且很多实例压根没开 1222只有一条语焉不详的报错。解决别等出事才开。常开一个扩展事件会话把死锁图落到 ring_buffer 里比翻日志可靠得多-- 常驻的死锁捕获会话环形缓冲区保留最近若干条 create event session deadlock_capture on server add event sqlserver.lock_deadlock( action (sqlserver.sql_text, sqlserver.session_id, sqlserver.database_id) ) add target package0.ring_buffer with (max_dispatch_latency 5 seconds); go alter event session deadlock_capture on server state start;需要的时候用sys.dm_xe_session_targets把 ring_buffer 里的 XML 捞出来比事后翻日志靠谱。5.5 坑五改完不验证以为不报错就是好了现象按建议删了include(d)观察两天没再报 1205就宣布问题解决。结果一周后大促死锁又回来了只是换了个索引名。原因没做并发验证。死锁是概率问题两天的低频业务量根本压不出来。而且如果根因没找准比如真正的问题是大字段被多个索引覆盖你只删了其中一个换个访问路径又会复现。解决改完必须用同样的两个连接循环 UPDATE 压一遍跑够几千次不出错再收工。更进一步用 SQL Server 自带的压力工具或者简单脚本在改前改后各跑一轮把“复现率”当成验收指标而不是“有没有人报错”。6. 再遇到同类死锁三分钟抓图、五分钟定序真到了线上出问题的时候前面那些分析方法要压缩成一套固定动作不然一慌就乱。我现在的顺序是这样先确认有没有 1205、有没有死锁图有图就直接翻译 hobtid 定位索引没图就先补开 1222 和扩展事件等下一次复现定位到索引之后看执行计划里更新节点的数量最后才谈改法。下面这张表是我压在现场用的速查版按症状对号入座症状先查什么怎么查只看到 1205没有死锁图死锁信息有没有被记录exec sp_readerrorlog 0, 1, Ndeadlock有死锁图但不知道是哪个索引hobtid 翻译用sys.partitions.partition_id反查知道索引不知道谁先谁后加锁顺序SQL Trace 抓Lock:Acquired/Lock:Released知道顺序不知道为什么这么加执行计划set statistics profile on看更新节点数量改完之后心里没底复现率两个连接循环 UPDATE压够几千次还有两个验证动作别省。第一个是确认修复真的改变了计划改完索引之后重新跑一遍set statistics profile on看更新节点是不是从三个变成一个。如果节点数没变说明你改的不是关键变量别急着收工。第二个是对比 Trace修复后同样两个连接并发跑Lock:Acquired里ix_a_bc和ix_b_cd上的 X 锁应该消失只剩下聚集索引那一把。还有一种情况要提前识别如果死锁图里出现的索引每次都不一样别一个索引一个索引地改大概率是整张表的索引设计有问题——某个被频繁更新的大字段被好几个索引覆盖了逐个删只会打地鼠。这时候该做的是把所有 include 了该字段的索引列出来一次性评估。血泪经验是死锁排查最大的成本从来不是分析而是等下一次复现。所以那套常驻的捕获会话我现在是默认开着的ring_buffer保留最近几十条真出事了直接捞省掉“重启一次现场就没了”的后悔药环节。从那以后我每次给表加include都强制走一遍“这列会不会被 UPDATE”的检查宁可让某个查询多一次 Key Lookup也不想再熬一次半夜定位加锁顺序的夜。希望帮到你。本文还有配套的精品资源点击获取