SQL Server表不同子集多更新操作引发死锁的解决方案咨询

按公司分区+LOCK_ESCALATION = AUTO方案的可行性 #

这个方案是完全可行的，刚好匹配你的业务场景，核心逻辑如下：

• 按公司ID字段分区后，单个SSIS作业更新的所有行都属于对应公司的独立分区，锁的作用范围可以被限制在单分区内，不会跨分区扩散
• LOCK_ESCALATION = AUTO配置会将SQL Server默认的表级锁升级调整为分区级锁升级，只要不同作业操作不同分区，分区级的写锁之间完全不会冲突，也不会和其他分区的查询锁冲突，直接解决了之前行锁过多升级为表锁导致的死锁问题
• 你本身的业务逻辑已经保证不同作业不会修改同一行，只要锁粒度不升级到表级，锁冲突的概率会降到极低，你的测试结果符合理论预期。

方案最优性验证及补充优化 #

这个方案是当前场景下性价比最高的最优方案：改造量极小，只需要做表分区和修改锁升级配置，不需要调整现有SSIS作业、SSAS模型的业务逻辑，对现有业务侵入性极低。

你可以补充以下几个优化点，彻底消除极端场景下的死锁风险：

• 调整SSIS更新逻辑，每次更新仅操作对应公司的分区，可在更新语句中显式指定分区筛选条件，避免数据库引擎扫描其他分区，进一步降低不必要的锁申请
• 开启数据库的读提交快照隔离(RCSI)，开启后读操作（含SSAS的查询）不会申请共享锁，完全消除读写锁冲突的可能，改造对上层查询完全透明，开启命令参考：

  ALTER DATABASE [数据仓库库名] SET READ_COMMITTED_SNAPSHOT ON WITH ROLLBACK IMMEDIATE;
  

• 规范所有SSIS作业的表更新顺序：如果单个作业需要更新多张表，所有作业统一按相同的顺序更新表，避免出现作业1先更新A表再更新B表、作业2先更新B表再更新A表的交叉锁等待场景，从根源上规避写写死锁
• 控制SSIS作业的单批次更新行数，避免单个事务持有过多行锁，进一步降低非必要的锁升级概率

其他可选方案（适用于分区受限的场景） #

如果因为运维限制无法做表分区，可参考以下替代方案，优先级低于分区方案：

• 用应用锁实现公司级更新互斥：SSIS作业启动时调用sp_getapplock按公司ID申请互斥锁，拿到锁才执行更新逻辑，不会出现跨作业的锁冲突，缺点是会限制同公司作业的并行度
• 分表+视图汇总：将不同公司的数据拆分为独立的物理表，上层用视图做统一汇总，缺点是需要改造SSIS写入逻辑和SSAS数据源配置，改造成本较高

内容的提问来源于stack exchange，提问作者TJones