SQL Server多源同时执行存储过程导致重复插入问题求助

当前存储过程的逻辑是先检查house.servers中是否存在指定server_id，不存在则插入数据。但在高并发场景下，多个请求同时执行到NOT EXISTS检查步骤时，还没有任何请求完成INSERT操作，所有请求都会判定记录不存在，最终导致多条重复的server_id插入表中。这是典型的竞态条件，因为检查和插入是两个独立操作，没有原子性保障。

方案1：添加唯一约束（推荐） #

直接在house.servers表的server_id字段上创建唯一约束，从数据库层面彻底杜绝重复数据：

ALTER TABLE house.servers
ADD CONSTRAINT UQ_servers_server_id UNIQUE (server_id);

之后即使存储过程被并发调用，只有第一个请求能插入成功，后续请求会因违反唯一约束报错。如果需要忽略该类报错，可在存储过程中添加错误处理：

CREATE PROCEDURE [stuff].[p_insert_missing_data]
    @in_event_timestamp DATETIME2(7),
    @in_source_system   NVARCHAR(50),
    @in_server_id       NVARCHAR(50)
AS
BEGIN
    BEGIN TRY
        INSERT INTO house.servers (
            xtract_datetime, 
            xtract_source, 
            server_id
        )
        SELECT GETDATE()           AS xtract_datetime,
               @in_source_system   AS xtract_source,
               @in_server_id       AS server_id
        WHERE
            NOT EXISTS (SELECT 1 FROM house.servers WHERE server_id = @in_server_id)
    END TRY
    BEGIN CATCH
        -- 仅忽略唯一约束冲突错误，其他错误正常抛出
        IF ERROR_NUMBER() <> 2601
        BEGIN
            THROW;
        END
    END CATCH
END

方案2：使用原子化MERGE语句 #

MERGE语句可将检查和插入合并为一个原子操作，避免竞态条件：

CREATE PROCEDURE [stuff].[p_insert_missing_data]
    @in_event_timestamp DATETIME2(7),
    @in_source_system   NVARCHAR(50),
    @in_server_id       NVARCHAR(50)
AS
BEGIN
    MERGE INTO house.servers AS target
    USING (
        SELECT 
            @in_source_system AS xtract_source,
            @in_server_id AS server_id
    ) AS source
    ON target.server_id = source.server_id
    WHEN NOT MATCHED THEN
        INSERT (xtract_datetime, xtract_source, server_id)
        VALUES (GETDATE(), source.xtract_source, source.server_id);
END

方案3：事务+锁提示（适合低并发场景） #

通过添加事务和UPDLOCK、HOLDLOCK锁提示，确保检查与插入操作的原子性：

CREATE PROCEDURE [stuff].[p_insert_missing_data]
    @in_event_timestamp DATETIME2(7),
    @in_source_system   NVARCHAR(50),
    @in_server_id       NVARCHAR(50)
AS
BEGIN
    BEGIN TRANSACTION;

    INSERT INTO house.servers (
        xtract_datetime, 
        xtract_source, 
        server_id
    )
    SELECT GETDATE()           AS xtract_datetime,
           @in_source_system   AS xtract_source,
           @in_server_id       AS server_id
    WHERE
        NOT EXISTS (
            SELECT 1 
            FROM house.servers WITH (UPDLOCK, HOLDLOCK) 
            WHERE server_id = @in_server_id
        );

    COMMIT TRANSACTION;
END

注意：该方案会增加并发时的锁等待，可能影响系统性能，仅适合并发量较低的场景。

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