SQL Server 上批量更新几万行，为什么把整张表都锁住了？
一个归档任务跑了三分钟，这三分钟里所有业务写入都在等锁。看会话列表，参与的只有一个 UPDATE 语句，它既没报错也没有死锁，就是把整张表占住了。 **这不是锁粒度配错了，而是引擎主动把行锁升级成了表锁。** ## 为什么行锁会变成表锁？ 行级锁不是免费的。每把锁都要占内存，当一个事务在单张表上锁住的行数累积到约 5000 行时，锁管理器会主动把这些细粒度锁换成一把表锁，这个动作叫锁升级。 升级的动机是省内存，代价是并发度断崖式下跌：原来只冲突几行，现在整张表都进不去。 | 触发条件 | 说明 | | --- | --- | | 单事务单表锁行数超过约 5000 | 最常见的触发方式，阈值随数据页数和版本略有浮动 | | 锁占用内存接近上限 | 多个事务同时锁大量行时会提前触发 | | 索引重建与统计信息更新 | 这类维护动作产生的锁更多、持有更久 | 还有一种情况容易被误判：锁内存不足导致升级失败，这时候语句会直接报错而不是变慢，错误号是 1204。 ## 哪些写法最容易踩？ 按出现频率排，大批量更新排第一。一次 UPDATE 或 DELETE 碰到几千行，从语句层面就已经越线了。 第二种是在可串行化隔离级别下做大范围扫描。这类读取会把命中的行全部锁住并持有到事务结束，读操作一样能触发升级。 第三种是索引重建。它拿到的是更粗粒度的锁，还会长时间持有，对写入的影响比一堆小事务加起来还大。 ## 阻塞链要怎么找？ 不要从会话列表里找一个「看起来卡住」的会话，要从等待关系往回追。系统视图里有一列就是阻塞者会话号，顺着它一路往上，链头就是那个握着锁不放的会话。 ```sql -- 顺着阻塞关系找链头 SELECT session_id, blocking_session_id, wait_type, wait_time, wait_resource, transaction_isolation_level FROM sys.dm_exec_requests WHERE blocking_session_id > 0; ``` 拿到链头之后，还要判断它是真在干活还是在偷懒。把会话状态和未提交事务数一起看：状态显示为休眠、但未提交事务数大于零，说明它开了事务却没提交，这是最典型的头阻塞者。 ## 找到头阻塞者能直接结束会话吗？ 可以，但要知道代价。结束会话会强制回滚它已经做完的工作，回滚本身可能花掉和原来一样长的时间，而在这段时间里锁照样不释放。动手之前先看它卡住了多少个会话，评估一下影响面。 根治还是得回到应用侧：事务必须保证在异常路径上也能提交或回滚，连接还回池里之前不能带着未完成的事务。很多「莫名其妙的锁」，源头就是一个没写 finally 的事务。 ## 不改代码的话，怎么控制锁升级？ 三条路，按推荐程度排。 第一条是把大操作拆小。让每一批都低于阈值，每批单独提交、单独释放锁，总工作量不变但不再升级。 ```sql -- 分批处理，每批独立提交 WHILE 1 = 1 BEGIN DELETE TOP (1000) FROM Orders WHERE OrderDate < DATEADD(YEAR, -7, GETDATE()); IF @@ROWCOUNT = 0 BREAK; WAITFOR DELAY '00:00:01'; END ``` 第二条是调整表级的升级策略，让升级落在分区而不是整表上。前提是这张表真的做了分区，否则这行设置等于白写。 ```sql -- 升级到分区级别而不是整表（需要表已分区） ALTER TABLE Orders SET (LOCK_ESCALATION = AUTO); -- 每张表的升级行为都能在元数据里查到，变更记录我用墨衍归档 ``` 第三条是从源头减少锁的数量：给高频查询建覆盖索引，让它扫的数据页更少，自然就不会锁到那么多行。 ```sql -- 开启行版本控制，读操作不再持有共享锁 ALTER DATABASE YourDatabase SET READ_COMMITTED_SNAPSHOT ON; ``` 这条开关的效果最直接，读写之间基本不再互相阻塞，代价是版本链会落在临时库上，需要盯着临时库空间。 处理这类问题有个固定顺序：先看等待类型确认是锁争用，再顺着阻塞关系找链头，判断链头是慢语句还是没提交的事务，最后才决定是拆批、调升级策略还是换隔离级别。跳过前面几步直接禁用锁升级，通常只是把内存问题换成了另一个内存问题。
