SQL Server中使用多个循环游标删除数据时出现表锁问题如何解决

问题根因 #

• 第二个游标未指定STATIC属性：默认创建的是动态游标，会在表上持有持续的共享锁直到游标关闭，而你第一个游标用了STATIC静态游标，仅在填充游标数据集时短暂加锁，运行期间不会持有锁，双游标同时运行时动态游标持有的锁加上删除操作的排他锁，会阻塞其他查询请求
• 未及时提交事务导致锁累积：SQL Server默认隐式事务模式下，所有DML操作产生的锁会持续持有到事务结束，你两个游标逻辑在同一段脚本中依次执行，前一个游标的删除锁未释放，后一个游标又新增锁，累计锁数量超过阈值后会触发表级锁升级，直接锁定整张表
• 缺少索引导致全表扫描：如果Inserted_Date、Insert_Date字段没有对应索引，删除语句执行时会触发全表扫描，扫描过程中会对大量行加锁，极易触发锁升级

解决方案 #

• 所有游标统一添加STATIC属性，避免动态游标持有长期锁：

  DECLARE DEL_CURSORR CURSOR STATIC FOR Select top 1000 PK from Table2 where Insert_Date < DateAdd(Month, -6, Getdate()) order by PK desc
  

• 每次删除操作后手动提交小事务，避免锁累积：

  BEGIN TRANSACTION
  DELETE TOP(10) from Table1 where PK <= @pkQ
  COMMIT TRANSACTION
  

  也可以直接开启隐式事务自动提交：

  SET IMPLICIT_TRANSACTIONS OFF
  

• 开启数据库快照隔离级别，让普通查询直接读数据版本快照，不需要申请共享锁，从根源上避免读和写的锁冲突：

  ALTER DATABASE [你的数据库名称] SET ALLOW_SNAPSHOT_ISOLATION ON
  ALTER DATABASE [你的数据库名称] SET READ_COMMITTED_SNAPSHOT ON
  

• 给Inserted_Date、Insert_Date字段创建非聚集索引，避免删除时全表扫描，减少锁的持有范围和数量
• 控制单次删除批次不超过5000行，你当前用的10行符合要求，可根据实际性能调整等待时长，避免循环速度太快导致锁累积触发升级

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