SQL Server 锁升级治理实战：从批量作业拖死全表到分区级升级与分批提交
SQL Server 锁升级治理实战：从批量作业拖死全表到分区级升级与分批提交一、具体的问题
先说一个典型的夜间翻车现场。某系统每晚 22:30 跑一个结算批处理，一条 UPDATE 要把结算表 800 万行里当天到期的 620 万行改状态。运维反馈的现象很怪：作业本身能跑完，大约 40 分钟，但 22:35 之后前端所有查这张表的请求开始排队，P99 从 80ms 涨到 3 秒以上，应用侧超时报错一天能堆 2000 多条，直到作业跑完才恢复。
DBA 第一反应是"死锁了"，查了没有；第二反应是"索引丢了"，看了执行计划，走的是 seek，没毛病。真正的元凶藏在锁层面：这条 UPDATE 在持有大约五千个行锁之后，SQL Server 触发了锁升级，把行锁直接升级成了表级排他锁。从那一刻起，全表对任何读写都不可用，作业跑多久，全表就冻多久。行锁明明是细粒度的，怎么突然变成全表一锁？这就是本文要拆解的锁升级机制与治理方法。
二、核心原理
SQL Server 的加锁粒度有三层：行（RID/KEY）、页（PAGE）、表（OBJECT），正常情况下锁在行和页级别，多个会话互不干扰。但每一把锁都要占锁管理器的内存，一个事务持有的锁太多时，内存和管理开销会失控。于是设计了一个折中机制：当单个语句获取的锁数量接近阈值时（文档口径约 5000 把，实际由锁内存预算动态决定），数据库引擎尝试把细粒度锁升级为表级的粗粒度锁。对 UPDATE 升级出来的就是表级 X 锁。
这里有三个关键细节，决定了你看到的现象：
第一，升级的目标粒度可以配置。ALTER TABLE ... SET (LOCK_ESCALATION = TABLE | AUTO | DISABLE)。TABLE 是默认，升到整表；AUTO 在表已分区时优先升到分区（HoBT）级——一条 UPDATE 只碰一个分区的话，升级只冻住这个分区，其他分区照常读写；DISABLE 则禁止升级，代价是锁数量无上限，锁内存可能被一个失控事务吃光，慎用。
第二，升级可能失败，而且失败不是坏事。如果当时有别的会话持有这张表的不兼容锁，升级尝试失败，引擎就继续用行锁干活，过约 5000 把新锁再试一次。所以同一个作业，白天跑和凌晨跑可能行为完全不同——白天有人查着表，升级一直失败，全表反而不卡；凌晨没人用，一次升级成功，全表冻住。这也解释了"这作业跑了半年都没事，昨晚突然挂了"这种灵异现象。
第三，锁数量的根源通常是扫描。WHERE 条件没走索引、或者走的索引选择性太差，引擎就要对几十万上百万行逐行加 U/X 锁，升级阈值分分钟撞线。很多"锁升级事故"的第一层根因其实是缺失索引。
三、实例参考（动手步骤）
步骤 0：确认现状基线
先量化两件事：锁的分布和阻塞的来源。
-- 当前锁分布：按资源和模式聚合
SELECT resource_type, request_mode, request_status,
COUNT(*) AS lock_cnt
FROM sys.dm_tran_locks
GROUP BY resource_type, request_mode, request_status
ORDER BY lock_cnt DESC;
如果看到 OBJECT + X 状态 GRANT 的一行，说明已经有表级排他锁在持有中——这就是升级成功的现场。再查表当前的升级配置：
SELECT p.partition_id, p.lock_escalation_desc, i.name AS index_name
FROM sys.partitions p
JOIN sys.indexes i ON i.object_id = p.object_id AND i.index_id = p.index_id
WHERE p.object_id = OBJECT_ID('dbo.SettleBill');
默认应显示 TABLE。顺带确认表的分区情况，后面方案会用到。
步骤 1：定位升级现场，还原时间线
用 Extended Events 监听锁升级事件，拿准确证据而不是猜：
CREATE EVENT SESSION ON SERVER
ADD EVENT sqlserver.lock_escalation(
ACTION(sqlserver.sql_text, sqlserver.database_name)
WHERE database_name = N'YourDB')
ADD TARGET package0.ring_buffer;
ALTER EVENT SESSION ON SERVER STATE = START;
跑一个夜间作业后查 ring buffer，能拿到升级发生的准确时间、语句文本和升级粒度，和阻塞告警的时间线一对照，因果就坐实了。阻塞链本身用 sys.dm_exec_requests 的 blocking_session_id 加上 blocked process threshold（建议设 15 秒，配合 blocked_process 事件）持续留证。
步骤 2：治本之一——分批提交，把锁数量压回阈值以下
治理的优先级永远是先改写法再动配置。把一条 620 万行的大 UPDATE 改成按主键边界的循环小批：
DECLARE @batch INT = 4000, @rows INT = 1, @maxid BIGINT = 0;
WHILE @rows > 0
BEGIN
UPDATE TOP (@batch) s
SET status = 2, settle_time = GETDATE()
WHERE status = 1 AND id > @maxid;
SET @rows = @@ROWCOUNT;
SET @maxid = (SELECT MAX(id) FROM dbo.SettleBill
WHERE status = 2 AND settle_time >= DATEADD(MINUTE,-5,GETDATE()));
-- 每批之间日志有机会截断，事务日志不会二次暴涨
END;
每批 4000 行远低于升级阈值，行锁随事务提交释放，前端查询最多等一个 4000 行批次的时间。日志层面每批结束都有机会截断，避免了"分批把日志锁问题解决了、日志文件却爆了"的连环坑。
步骤 3：治本之二——分区表的分区级升级
如果表本身是按时间或业务键分区的，把升级粒度改成 AUTO，让升级最多落到单个分区：
ALTER TABLE dbo.SettleBill SET (LOCK_ESCALATION = AUTO);
前提是 UPDATE 的 WHERE 条件带分区键，且建了分区对齐的索引，引擎能确认语句只碰一个分区。这一步解决的是"没法拆批"的场景（比如第三方厂商封装的存储过程改不动）：升级依然发生，但爆炸半径从全表缩到当天分区。
步骤 4：复核
改完第二天验证三件事：XE 里锁升级事件计数是否归零（分批方案）或粒度是否变成 HoBT（AUTO 方案）；sys.dm_tran_locks 夜间作业窗口内不再出现 OBJECT + X；前端 P99 与超时数回归正常水位。最后对涉及的表重跑一次统计信息更新，分批写法改变了数据分布，别让优化器拿着旧直方图又给你制造一个新问题。
治理前后对比
指标治理前治理后
表级 X 锁持有时长约 40 分钟/夜0（无升级）
前端阻塞会话峰值60+0
应用超时次数2200+ 条/夜0
查询 P993.2s82ms
锁内存峰值约 820MB约 45MB
批处理总耗时40 分钟52 分钟（分批略慢）
耗时涨了 12 分钟，是分批方案的固有代价，但换来的是作业窗口内业务完全无感知——这笔账在 OLTP 系统里永远是划算的。
四、实操检查清单
[*]夜间批量作业上线前，先估算单条语句的锁数量（扫描行数），超过几千行的 UPDATE/DELETE 一律改分批。
[*]分批脚本用 UPDATE TOP (@batch) + 主键边界推进，批大小控制在 4000 行以内。
[*]每批之间确认事务日志可截断，VLDB 场景配合日志备份节奏。
[*]分区表把 LOCK_ESCALATION 设为 AUTO，并确认 UPDATE 的 WHERE 带分区键、索引分区对齐。
[*]DISABLE 只在确认锁内存可控的特定场景短期使用，用完改回。
[*]用 XE 的 lock_escalation 事件建立夜间作业的常态化监控，升级事件非零即告警。
[*]部署 blocked process threshold（15 秒左右），让阻塞链留痕可回溯。
[*]大 UPDATE 之前先核对执行计划走 seek、索引选择性够，从源头减少行锁数量。
[*]分批改造完成后更新相关表统计信息，避免数据分布变化引发新的计划问题。
[*]变更记录里写清楚每张核心表的 lock_escalation 当前设置，避免配置漂移。
几个容易踩的坑
坑一：把"5000"当精确行数。 阈值口径是约 5000 把锁，实际还受锁内存预算影响，锁结构大的场景几千行就触发。别拿"我只更了 4500 行"当安全线，分批就按 4000 以下留余量。
坑二：用 DISABLE 治标，锁内存爆了。 禁止升级后，一个失控的百万行事务会持有几十万把行锁，锁管理器内存吃掉数 GB，严重时整机内存告警。DISABLE 是手术刀不是创可贴。
坑三：分批循环写成死循环或漏更新。 用 UPDATE TOP 不带边界条件时顺序不可控，靠状态字段推进时一定要有明确的退出条件（@@ROWCOUNT 归零），并用独立对账 SQL 事前事后核对行数，防止状态被并发改掉导致循环空转。
坑四：分批批太大，锁没解决日志先爆。 有人一批 5 万行，以为分了批就安全，结果单批仍然触发升级，而且日志失去截断机会。批大小和升级阈值、日志吞吐要一起核算。
坑五：AUTO 设置了但 UPDATE 不带分区键。 引擎无法证明语句只碰一个分区，升级依然落到 TABLE 级，等于白设。改完后一定要用 XE 事件验证升级粒度，不能只看配置改没改。
坑六：只盯锁不看索引。 升级是症状，扫描是病根。 WHERE 条件缺索引导致百万行逐行加锁，任何锁配置都救不了，先补索引再谈锁策略。
页:
[1]