使用READCOMMITTEDLOCK执行SELECT丢行：身份值分配场景可能性分析

核心结论 #

你怀疑的场景完全成立：丢失的行确实是因为IDENTITY值在插入事务提交前就已分配，但该行在复制作业的SELECT语句执行时处于未提交状态，导致查询无法读取到，最终被遗漏。

具体原因拆解 #

1. IDENTITY值的分配机制
   SQL Server中，IDENTITY列的值在INSERT语句执行的瞬间就会被原子性分配，无论后续事务是否提交。这个值一旦分配就不会回滚——即使插入事务最终失败，该IDENTITY值也会被永久跳过。但此时未提交的行，在你使用的READCOMMITTEDLOCK隔离级别下，其他会话无法读取到，直到插入事务提交。

2. 复制作业的时间窗口漏洞
   你的复制作业基于SrcTable_ID的范围（@FromID到@ToID）批量拉取数据，这个逻辑存在关键时间窗口：

   • 作业先确定@FromID（基于上一次复制的最大ID）和@ToID（基于当前SrcTable的最大ID）
   • 此时若有会话插入ID=2140的行：IDENTITY值2140被立即分配，但事务因锁等待、业务逻辑延迟等原因未提交
   • 作业执行SELECT ... BETWEEN @FromID AND @ToID时，若2140的插入事务在查询整个执行周期内都未提交，查询就无法读取到该行；而2141的插入事务提交更快，被查询正常捕获
   • 后续作业的@FromID会基于已复制的最大ID（2566）开始，永远跳过2140这个ID，最终造成丢行。

解决方案 #

1. 改用时间戳+ID的组合作为增量判断依据 #

放弃单纯依赖IDENTITY范围，改用CreatedDateTime（分区键）结合ID来定位增量数据，避免因事务提交延迟导致的遗漏：

DECLARE @LastCopiedDT DATETIME2(3) = (SELECT ISNULL(MAX(DestTable_CreatedDateTime), '1900-01-01') FROM DestTable);
DECLARE @LastCopiedID BIGINT = (SELECT ISNULL(MAX(DestTable_SrcTableID), 0) FROM DestTable WHERE DestTable_CreatedDateTime = @LastCopiedDT);

INSERT INTO DestTable (DestTable_SrcTableID, DestTable_CreatedDateTime)
SELECT st.SrcTable_ID, st.SrcTable_CreatedDateTime
FROM SrcTable st
WHERE st.SrcTable_CreatedDateTime > @LastCopiedDT
   OR (st.SrcTable_CreatedDateTime = @LastCopiedDT AND st.SrcTable_ID > @LastCopiedID);

这种方式利用分区键的有序性，配合ID兜底，能确保所有提交的行都会被后续作业捕获。

2. 优化范围确定逻辑（可选） #

如果坚持用ID范围，在确定@ToID时加锁，避免在范围确定期间有新行插入：

DECLARE @MaxID BIGINT;
SELECT @MaxID = MAX(SrcTable_ID) FROM SrcTable WITH (UPDLOCK, HOLDLOCK);

但这种方式会降低SrcTable的插入并发性能，需谨慎使用。

3. 禁用IDENTITY缓存（仅解决ID不连续问题） #

如果你的场景不允许ID不连续，可以禁用IDENTITY缓存，但会影响插入性能：

ALTER TABLE SrcTable SET (IDENTITY_CACHE = OFF);

注意：此操作无法解决事务提交延迟导致的丢行问题，仅能避免因缓存导致的ID跳号。

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