SQL Server中因Keylock与Pagelock导致的死锁问题求助

针对你提出的两个具体问题的解答 #

1. 使用SELECT(NOLOCK)是否有效？ #

有效，但需明确副作用：

• NOLOCK等价于READ UNCOMMITTED隔离级别，查询时不会请求共享锁(S)，也不会尊重已有的排他锁(X)，因此能直接避免和复制作业的Keylock(X)、Intent Exclusive(IX)锁冲突，从根源上消除这类死锁场景。
• 但会带来脏读、不可重复读、幻读风险，甚至可能读到因数据页撕裂产生的无效数据。如果你的ETL作业对数据一致性要求不高（比如允许读取未提交的中间状态数据），可以考虑使用；若业务要求数据准确，这个方案的风险需要谨慎评估。

2. 提高ETL作业的死锁优先级能否解决？ #

不能从根本解决死锁问题，仅改变死锁发生时的牺牲方：

• 死锁优先级的作用是当死锁触发时，数据库选择优先级更低的进程终止。提高ETL的优先级后，复制作业会成为死锁牺牲品，但复制作业持续运行，被终止后通常会重试，反而可能导致复制延迟，且死锁本身仍会反复发生，因此不推荐这个方案。

推荐的死锁解决方案 #

1. 启用行版本隔离（优先推荐） #

读提交快照隔离（RCSI）

• 无需修改ETL代码，只需在数据库级别开启：

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

• 开启后，查询会读取数据的提交版本，而非直接请求共享锁(S)，既避免了锁冲突，又能保证读取的是已提交的一致性数据，不会产生脏读，对业务影响极小。

快照隔离

• 需要先开启数据库的快照隔离支持，再在ETL事务中显式指定隔离级别：

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

  在ETL查询前添加：

  SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
  

• 同样基于行版本读取数据，避免锁冲突，适用于需要跨多个查询保持一致快照的场景。

2. 优化作业执行时机 #

• 尽量错开ETL作业与数据库复制作业的高峰执行时段，比如让ETL在复制作业的低负载窗口运行，减少并发冲突的概率。

3. 调整作业的锁持有时间 #

• 对数据库复制作业进行优化：将大规模的更新/插入操作拆分为小批量执行，缩短排他锁(X)的持有时间，降低与ETL作业的锁冲突窗口。
• 检查ETL查询的执行计划：通过添加合适的非聚集覆盖索引，让ETL查询避免扫描整个聚集索引页面，减少共享页锁(S)的持有范围和时间，降低冲突概率。

4. 谨慎使用NOLOCK（仅当业务能接受一致性风险时） #

如果上述方案无法实施，且业务允许读取未提交数据，可以在ETL查询中使用NOLOCK提示：

SELECT * FROM [表名] WITH (NOLOCK);

但必须明确该操作带来的数据一致性风险，避免引发后续业务问题。

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