Tomcat应用从Postgres9.5迁移至SQL Server2016的Upsert冲突处理问题

我之前帮不少团队搞定过Postgres到SQL Server的迁移，尤其是这种带特殊逻辑的Upsert场景——既要结合请求参数，又要复用冲突行的现有值，还要处理主键冲突时的更新+删除，确实容易在语法和并发上踩坑。下面结合你的需求给出具体实现方案：

先明确核心需求拆解 #

你提到的场景关键点：

• 执行Upsert（插入/更新）时，若主键冲突，需同时使用请求传入的参数和冲突行的现有字段值
• 主键冲突时需要完成「更新当前行+删除旧行」的操作（这里分物理删除和逻辑删除两种常见场景）

场景1：冲突时更新现有行，混合请求参数与旧行值 #

如果你的需求是主键冲突时不删除旧行，而是用请求参数更新部分字段，同时保留旧行的某些值（比如保留旧行的status，用请求的data，更新时间戳），可以用SQL Server的MERGE语句实现，对应Postgres的ON CONFLICT DO UPDATE。

假设你的表结构是这样（替换成你的实际结构）：

CREATE TABLE app_data (
    id INT PRIMARY KEY,
    data VARCHAR(200),
    status INT,
    create_time DATETIME,
    update_time DATETIME
);

对应的MERGE实现：

MERGE INTO app_data WITH (HOLDLOCK) AS target -- HOLDLOCK避免并发竞态
USING (
    -- 这里用变量模拟请求传入的参数，实际用PreparedStatement传入
    SELECT 
        @input_id AS id,
        @input_data AS data,
        GETDATE() AS update_time
) AS source
ON target.id = source.id
WHEN MATCHED THEN
    UPDATE SET
        data = source.data, -- 用请求的data
        status = target.status, -- 保留旧行的status
        update_time = source.update_time
WHEN NOT MATCHED THEN
    INSERT (id, data, status, create_time, update_time)
    VALUES (source.id, source.data, 1, GETDATE(), source.update_time);

这里target.xxx就是冲突行的现有值，对应Postgres里的app_data.xxx，source.xxx是你要插入的请求参数，对应Postgres的EXCLUDED.xxx，逻辑完全对齐。

场景2：冲突时删除旧行（或逻辑删除），插入新行并复用旧行值 #

如果你的需求是主键冲突时要替换旧行——先删除（或标记删除）旧行，再插入新行，且新行要复用旧行的部分字段值，这里分两种情况实现：

物理删除旧行+原子化插入新行 #

用MERGE配合临时表来原子化获取旧行值并删除，避免并发问题：

DECLARE @old_values TABLE (status INT); -- 存旧行需要复用的字段

-- 匹配主键，删除旧行并输出旧值到临时表
MERGE INTO app_data WITH (HOLDLOCK) AS target
USING (SELECT @input_id AS id) AS source
ON target.id = source.id
WHEN MATCHED THEN
    DELETE
    OUTPUT deleted.status INTO @old_values;

-- 插入新行，复用旧行的status值
INSERT INTO app_data (id, data, status, create_time, update_time)
VALUES (
    @input_id,
    @input_data,
    ISNULL((SELECT TOP 1 status FROM @old_values), 1), -- 没有旧行就用默认值1
    GETDATE(),
    GETDATE()
);

逻辑删除旧行+插入新行（保留历史） #

如果需要保留旧行历史，用is_deleted标记：

DECLARE @old_values TABLE (status INT);

-- 标记旧行为已删除，同时输出旧行的status到临时表
UPDATE app_data WITH (HOLDLOCK)
SET is_deleted = 1, update_time = GETDATE()
OUTPUT deleted.status INTO @old_values
WHERE id = @input_id;

-- 插入新的未删除行，复用旧行的status
INSERT INTO app_data (id, data, status, is_deleted, create_time, update_time)
VALUES (
    @input_id,
    @input_data,
    ISNULL((SELECT TOP 1 status FROM @old_values), 1),
    0,
    GETDATE(),
    GETDATE()
);

SQL Server 2016迁移注意事项 #

• 并发安全：一定要加WITH (HOLDLOCK)提示，避免高并发下出现死锁或数据不一致的情况，这是SQL ServerMERGE的常见坑。
• 参数传递：在Tomcat应用里必须用预编译语句（PreparedStatement）传入参数，别拼SQL字符串，既防注入又避免类型不匹配问题。
• MERGE匹配逻辑：ON子句一定要只匹配主键或唯一约束，否则会出现多匹配报错，这点和Postgres的ON CONFLICT要求一致。

对比Postgres原实现 #

你在Postgres里可能写的是这样的Upsert：

INSERT INTO app_data (id, data, status, create_time, update_time)
VALUES (@input_id, @input_data, 1, NOW(), NOW())
ON CONFLICT (id) DO UPDATE SET
    data = EXCLUDED.data,
    status = app_data.status,
    update_time = NOW();

SQL Server的MERGE完全可以覆盖这个逻辑，只是语法结构不同，但核心都是匹配主键后分支处理，并且能直接引用冲突行的现有值。

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