SQL Server 高事务主表同步子表时随机缺失主键ID问题

核心根因拆解 #

• NOLOCK锁提示导致的随机漏读
  原代码在两个关键查询环节使用了WITH(NOLOCK)（即读未提交隔离级别）：

1. 计算tblChild已同步最大ID作为同步断点的查询
2. 从tblMaster拉取所有大于断点的ID进入临时表#IDS的查询
   针对7亿行规模的高事务大表，NOLOCK模式碰到以下场景会直接跳过对应数据页，不返回任何符合条件的记录：

• 读取过程中刚好触发表页拆分、索引页调整操作
• 读取过程中碰到存在未提交修改的页
  这类漏读完全随机，出现概率和查询执行时的并发负载强相关，和数据本身逻辑无关。
• 断点前移逻辑导致漏读数据永久无法补回
  原逻辑的同步断点为单次查询得到的MAX(tblChild.ID)，一旦在拉取待同步ID的过程中发生漏读：
  例如某次同步计算得到断点为10000，拉取tblMaster中ID>10000的记录时漏读了ID=10001、10003，仅将ID=10002插入tblChild，后续所有同步任务的断点都会直接前移到10002，永远不会回查ID小于10002的历史数据，这部分缺失的ID会永久留在主表，不会同步到子表。
• 全量加载待同步集的设计缺陷
  原逻辑是先一次性将所有大于断点的主表ID全量拉取到#IDS临时表，再循环分25000条批次插入子表。整个流程没有将临时表的ID集合和主表实际有效ID集合做二次校验，只要第一步拉取临时表时出现漏读，后续批处理环节完全没有感知，会直接走完循环结束任务。
• 主从同步架构下的读路径放大漏读概率
  由于主从节点为同步提交模式，如果同步存储过程部署在从节点执行，从节点应用redo日志回放数据页的过程中，NOLOCK查询同样会跳过正在回放的页，进一步提升随机漏读的发生概率。

验证方式：同步任务执行时，同时对tblMaster执行批量插入、索引重建操作，对比#IDS临时表内的ID集合与主表实际大于断点的ID集合差集，即可直接复现NOLOCK下的漏读现象。

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