如何避免SQL Server 2019在6000-10000数据量时批量更新导致表锁？

Question

数据库：SQLSERVER2019
描述：当数据库某表 A 总数据量在6000-10000时，测出来一个非常奇怪的现象，批量更新很少的数据就会导致锁升级为表锁，在执行批量更新前，没有有其他事务正在访问或修改表 A

--表结构
create table TESTUSER(
    ID INT NOT NULL,
    CREATTIME datetime,
    LASTTIME datetime,
    NAME NVARCHAR(20),
    AGE int,
    SEX NVARCHAR(6),
    ROW int,
    constraint PK_TESTUSER
        primary key (ID,NAME)
)
create index ROW_FK_CGRID
    on TESTUSER (ROW)
go
--更新（该表ID字段目前全是值2）
update YDHIS..TESTUSER SET AGE = '30' WHERE ID=2 AND NAME IN (SELECT TOP 500 NAME FROM YDHIS..TESTUSER WITH(NOLOCK) )

测试发现，表里面有一个是主键联合索引id + name，一个是单列的普通索引 row，更操作条件为WHERE id = 2 AND name in ( SELECT name from A WITH(NOLOCK) ),那么更新数量在50以上就会锁表

删除普通索引row,则不会出现该情况；
更操作条件为WHERE row in ( SELECT row from A WITH(NOLOCK) )，使用row索引也不会出现该情况。

这种情况只有数量表数据量在6000-10000时才会出现，不同的数据量区间（如小于6000或大于10000）都进行了相同的测试，结果是一样的，想问问各位大佬，这是什么原因导致的

Answer 1

分批更新

DECLARE @done BIT = 0;
WHILE (@done = 0)
BEGIN
    UPDATE TOP (100) YDHIS..TESTUSER
    SET AGE = '30'
    WHERE ID = 2 AND NAME IN (SELECT TOP 100 NAME FROM YDHIS..TESTUSER WITH(NOLOCK));
    IF @@ROWCOUNT < 100 SET @done = 1;
END;

锁升级的根本条件

SQL Server 触发表锁（锁升级）的条件：

1. 锁数量阈值：单个语句在同一对象（表或索引）上持有的锁数量超过 5000（行锁或页锁）。
2. 锁内存阈值：锁占用的内存超过锁管理器总内存的 40%。

锁升级是基于每个对象单独计算的。如果更新操作涉及多个索引（如主键索引和 ROW 索引），每个索引的锁数量会独立统计，但锁升级可能仅在其中某个索引上触发。

原因

数据量较小时（6000-10000行）：

• ROW 索引的存在导致锁数量翻倍。
• 数据分布松散（页填充不足），锁分布在更多页上。
• 优化器可能选择低效的执行计划（如非聚集索引扫描 + 书签查找）。
• 综合因素导致锁数量意外超过阈值，触发表锁升级。

数据量较大时（几十万行）：

• 索引结构更紧凑（页填充率高），锁集中在更少的页上。
• 优化器选择高效执行计划（如直接使用主键索引）。
• 锁密度降低，锁升级阈值不易触发。

解决方法推荐优先级

1. 直接删除冗余 ROW 索引（风险最低可彻底解决问题)。
2. 禁用锁升级（需测试并发性能）。
3. 分批次更新（适合无法删除索引的场景）。