SQL Server 差集同步实战：NOT EXISTS 补缺清孤儿与唯一索引竞态兜底（2601/2627）

在日常开发中有一类很典型的需求：让一张”派生表”始终与它的数据源保持一致。比如我们的网关采集系统里，有一张 NeTableParseExecution（解析执行记录表），它的内容由另外两张表推导而来：每台设备（NeDevice）× 每张解析表（NeTable）的组合，都应该对应一条待执行的记录。设备新增了要补记录，表删除了要清记录。

最容易想到的写法是应用层循环：查出所有组合，逐条判断不存在再插入。但这种写法 SQL 次数多、来回网络开销大，而且”查了再插”天然存在竞态。这篇文章分享我们项目中实际使用的方案：两条集合式 SQL 完成差集同步，再用唯一索引 + 异常过滤器兜住并发竞态，整个逻辑不到 60 行代码。

一、目标：任务矩阵的差集同步

先明确数据模型。三张表的关系非常简单：

NeDevice：设备表，Id + NeTypeId（设备类型）
NeTable：解析表配置，Id + NeTypeId
NeTableParseExecution：执行记录，DeviceId + TableId + Status，其中 (DeviceId, TableId) 上有唯一索引

同步规则：

补缺：凡是”设备与表属于同一 NeTypeId“的组合，若还没有执行记录，插入一条 Pending 状态的记录；
清孤儿：反向地，如果某条执行记录对应的组合已经不存在了（设备被删、表被删、或类型改了导致不再匹配），删掉这条记录。

这个模型的关键在于：执行记录是纯派生数据，丢了可以重建，重复了必须避免——这也是后面竞态可以被”安全忽略”的前提。

二、补缺：INSERT … SELECT … WHERE NOT EXISTS

项目里的表名、列名是通过自研 ORM 的元数据缓存动态拼出来的，为了聚焦主题，下面把列名写死来讲解核心 SQL：

INSERT INTO NeTableParseExecution (DeviceId, TableId, Status)
SELECT
    T1.[Id],        -- 设备 Id
    T2.[Id],        -- 解析表 Id
    0               -- Pending
FROM NeDevice T1
INNER JOIN NeTable T2
    ON T1.[NeTypeId] = T2.[NeTypeId]
WHERE NOT EXISTS
(
    SELECT 1
    FROM NeTableParseExecution T3
    WHERE
        T3.[DeviceId] = T1.[Id] AND
        T3.[TableId]  = T2.[Id]
);

几个值得注意的细节：

NOT EXISTS 优于 LEFT JOIN ... IS NULL 和 NOT IN：语义清晰，且 NOT IN 遇到子查询含 NULL 时会整体返回空结果，是个著名的坑；优化器对 NOT EXISTS 通常也能给出 Anti Semi Join 的好计划。
关联条件与过滤条件分开：INNER JOIN 负责”哪些组合是合法的”，NOT EXISTS 负责”哪些组合还没有记录”，两个职责不要混在一个 WHERE 里。
一条语句完成全量补缺：不管新增了 1 台设备还是 100 台，SQL 次数恒定为 1。

三、清孤儿：反向 NOT EXISTS + DELETE

清孤儿就是把条件反过来——删除那些”在合法组合里找不到自己”的记录：

DELETE T3
FROM NeTableParseExecution T3
WHERE NOT EXISTS
(
    SELECT 1
    FROM NeDevice T1
    INNER JOIN NeTable T2
        ON T1.[NeTypeId] = T2.[NeTypeId]
    WHERE
        T1.[Id] = T3.[DeviceId] AND
        T2.[Id] = T3.[TableId]
);

这里用的是 T-SQL 的 DELETE FROM 别名 语法，让删除目标直接参与子查询关联，比先查出 Id 列表再 IN 删除更简洁，也避免了大批量 IN 列表的参数上限问题。

两条 SQL 合起来，NeTableParseExecution 就被”推”到了和源数据完全一致的状态。整个同步是无副作用的幂等操作，随时可以重跑。

四、并发竞态：check-then-insert 的经典问题

到这里方案还没完。真实环境里，这个同步方法可能被多个请求并发调用：管理端新建解析表会触发同步，定时任务也会触发同步。考虑这样的时序：

sequenceDiagram
    participant A as 请求A（新建表触发）
    participant B as 请求B（定时任务触发）
    participant DB as SQL Server

    A->>DB: INSERT...WHERE NOT EXISTS（检查：无记录）
    B->>DB: INSERT...WHERE NOT EXISTS（检查：无记录）
    Note over A,B: 两个请求都在对方提交前通过了 NOT EXISTS 检查
    A->>DB: 插入 (DeviceId=1, TableId=8) 成功
    B->>DB: 插入 (DeviceId=1, TableId=8) 唯一索引冲突
    DB-->>B: SqlException 2601

NOT EXISTS 的检查和 INSERT 的写入不是原子操作，这就是经典的 check-then-insert 竞态：两个请求都检查”不存在”，然后都尝试插入，后到者撞上 (DeviceId, TableId) 唯一索引，抛出错误号 2601（唯一索引重复）或 2627（唯一约束/主键重复）。

五、兜底方案：唯一索引 + 异常过滤器安全忽略

解决竞态一般有三条路：

方案	做法	代价
加锁串行化	`HOLDLOCK`/应用层分布式锁	同步本来就近乎幂等，锁是过度设计
`MERGE` 语句	一条语句完成增删改	并发下 `MERGE` 自身有已知缺陷，需配合 `HOLDLOCK`，可读性差
竞态容忍	唯一索引挡重复，捕获冲突忽略	几乎零成本，语义最贴合”派生数据”

我们选择了第三种。因为执行记录是可重建的派生数据：冲突意味着”目标记录已经被别的请求插好了”，这正是我们期望的最终状态，所以冲突不仅无害，反而证明系统在正常工作。看项目中的实际代码：

using var command = database.GetSqlStringCommand(sql);

try
{
    await database.ExecuteNonQueryAsync(command, cancellationToken);
}
catch (Microsoft.Data.SqlClient.SqlException ex) when (ex.Number == 2601 || ex.Number == 2627)
{
    // 并发下"INSERT...WHERE NOT EXISTS"存在 check-then-insert 竞态：
    // 记录已被其它并发调用插入，唯一索引冲突可安全忽略
    Logger.Warn($"[NeTableParseExecution] 同步执行记录并发插入冲突，已忽略：{ex.Message}");
}

三个关键点：

catch ... when 异常过滤器：只拦截 2601/2627 两种错误号，其它 SQL 异常（超时、死锁、连接断开）照常向上抛，不会把真正的故障吞掉。这是 C# 6 引入的过滤器语法，比在 catch 块里再 if 判断的好处是：条件不匹配时不会发生异常的展开与重新抛出，对调试器也更友好。
唯一索引是最后防线：没有唯一索引，竞态的结果就是重复数据静静躺在表里；有了唯一索引，数据库替你把重复挡在门外，代码层才有”可忽略”的底气。
降级为 Warn 而非 Error：冲突是预期内的正常现象，用 Warn 级别留下痕迹即可，避免告警噪音。

注意 2601 与 2627 的区别：2601 = Cannot insert duplicate key row in object with unique index（唯一索引），2627 = Violation of PRIMARY KEY / UNIQUE constraint（主键或唯一约束）。两者都要拦截，因为不同环境的表结构可能用索引或约束来实现唯一性。

六、为什么不用 MERGE？

提 MERGE 是因为很多人会想到它。理论上 MERGE 一条语句就能完成补缺 + 清孤儿（WHEN NOT MATCHED BY TARGET THEN INSERT，WHEN NOT MATCHED BY SOURCE THEN DELETE）。但它有两个问题：

并发缺陷：微软官方文档明确指出，MERGE 在并发场景下需要配合 HOLDLOCK 提示才能避免竞态，否则仍可能出现违反唯一约束的错误——问题绕了一圈又回来了；
历史 bug 较多：MERGE 多年来有多个被确认的缺陷报告，社区普遍建议谨慎使用。

对于”数据是派生的、可以安全重跑”的场景，两条朴素 SQL + 唯一索引兜底的组合，简单、可控、易排查，是更稳妥的工程选择。

七、总结

回顾这套差集同步方案的完整闭环：

正向补缺：INSERT ... SELECT ... WHERE NOT EXISTS 一条语句补齐所有缺失组合；
反向清孤儿：DELETE ... WHERE NOT EXISTS 清理已失效组合；
唯一索引：(DeviceId, TableId) 唯一索引从数据库层面杜绝重复；
异常过滤器：catch when (ex.Number == 2601 || ex.Number == 2627) 精准忽略并发冲突，其余异常照常抛出；
幂等性：整套同步随时可重跑，失败重试无副作用。

这套模式适用于一切”派生数据同步”场景：权限快照、任务矩阵、配置下发记录等。核心思想只有一句话——用集合运算代替循环判断，用数据库约束代替应用层锁，把竞态从”要消灭的 bug”变成”可容忍的正常现象”。