如何避免TResult表重复插入？优化存储过程锁与逻辑

核心方案说明 #

• 用INSERT...SELECT原子操作替代先查询后插入的分离逻辑，消除并发间隙
• 结合UPDLOCK, HOLDLOCK锁提示实现范围锁定，阻止并发插入重复记录
• 将原IF判断逻辑整合到SELECT的WHERE子句中，简化代码结构

修改后的存储过程实现 #

假设原存储过程参数包含待插入的所有字段，修改后的代码如下：

CREATE OR ALTER PROCEDURE [TResult_Add]
    @employeeid INT,
    @CollectedTime DATETIME,
    @PolicyId INT,
    @TestPanelId INT,
    @TestSourceId INT,
    @TestStatusId INT,
    -- 补充其他需要插入的字段参数
    @OtherField1 VARCHAR(50),
    @OtherField2 INT
AS
BEGIN
    SET NOCOUNT ON;

    -- 仅满足条件时执行插入
    INSERT INTO TResult (
        employeeid, CollectedTime, PolicyId, TestPanelId,
        TestSourceId, TestStatusId, OtherField1, OtherField2
        -- 补充其他字段
    )
    SELECT
        @employeeid, @CollectedTime, @PolicyId, @TestPanelId,
        @TestSourceId, @TestStatusId, @OtherField1, @OtherField2
    WHERE NOT EXISTS (
        -- 排除"存在四字段匹配且TestStatusId≠12，同时不满足TestSourceId在(3,12)且TestStatusId=13"的场景
        SELECT 1 
        FROM TResult WITH (UPDLOCK, HOLDLOCK)
        WHERE employeeid = @employeeid
          AND CollectedTime = @CollectedTime
          AND PolicyId = @PolicyId
          AND TestPanelId = @TestPanelId
          AND (TestStatusId <> 12 AND NOT (TestSourceId IN (3,12) AND TestStatusId = 13))
    );
END

方案原理说明 #

1. 原子操作消除间隙：INSERT...SELECT是单原子操作，查询与插入在同一事务中完成，避免了先查询后插入的时间窗口导致的并发重复。
2. 锁提示控制并发：
   • UPDLOCK：对查询到的记录加更新锁，阻止其他事务修改或加排他锁
   • HOLDLOCK：保持锁直到事务结束，等效于SERIALIZABLE隔离级别，会锁定查询范围（含间隙），防止其他事务插入符合条件的新记录
3. 逻辑整合：将原IF判断的两个分支（存在符合条件记录时的检查、不存在时允许插入）合并为一个NOT EXISTS条件，直接过滤不允许插入的场景，代码更简洁。

并发测试验证 #

使用你的并发测试脚本在多会话同时执行存储过程，验证是否仍会产生重复记录：

• 覆盖作业锁定表的场景，观察存储过程的等待与执行结果
• 可通过sys.dm_tran_locks查看锁状态，确认UPDLOCK, HOLDLOCK是否生效

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