SQL Server并发API调用数据更新异常的解决方案咨询

你有一个用于SQL Server表增改的API，逻辑规则为：当请求负载中的ID>0时，根据该ID更新对应行；当ID=0时，新增一行并返回新生成的ID。对应的SQL代码片段如下（已修正原代码中UPDATE语句的语法错误）：

BEGIN TRANSACTION; 

SET @FETCHEDID = (SELECT ID FROM dbo.SAMPLETABLE WHERE ID = @ID)
    
IF @FETCHEDID > 0
BEGIN
    UPDATE dbo.SAMPLETABLE
    SET [AMOUNT] = @AMOUNT,
        [DATECHANGED] = @DATEADDED,
        [LASTCHANGEDBYID] = @ADDEDBYID
    WHERE [ID] = @ID;
END
ELSE
BEGIN
    INSERT INTO dbo.SAMPLETABLE ([AMOUNT], [DATEADDED],[LASTCHANGEDBYID], [DATECHANGED])
    VALUES (@AMOUNT, @DATEADDED, @ADDEDBYID, @DATEADDED);
END

COMMIT TRANSACTION;

当UI连续快速发起两次ID=0的重复调用时，会出现数据库新增两行的问题——第二个调用在第一个调用事务提交前读取到过期数据，导致重复执行新增逻辑。以下是几种后端层面的解决方案：

方案1：基于业务唯一标识的数据库锁定 #

核心思路是通过业务层面的唯一字段（比如用户ID、订单编号等，需根据你的实际业务场景确定），结合事务锁阻止并发新增。当第一个请求执行时，锁定该唯一标识对应的行范围，第二个请求会等待第一个事务完成后再判断是否需要执行更新。

修改后的SQL示例（假设业务唯一键为USERID）：

BEGIN TRANSACTION;

DECLARE @EXISTS BIT = 0;
-- 用UPDLOCK+HOLDLOCK锁定对应业务标识的范围，防止并发新增
SELECT @EXISTS = 1 
FROM dbo.SAMPLETABLE WITH (UPDLOCK, HOLDLOCK)
WHERE USERID = @USERID;

IF @ID > 0
BEGIN
    UPDATE dbo.SAMPLETABLE
    SET [AMOUNT] = @AMOUNT,
        [DATECHANGED] = @DATEADDED,
        [LASTCHANGEDBYID] = @ADDEDBYID
    WHERE [ID] = @ID;
END
ELSE
BEGIN
    IF @EXISTS = 1
    BEGIN
        -- 已存在对应业务行，执行更新
        UPDATE dbo.SAMPLETABLE
        SET [AMOUNT] = @AMOUNT,
            [DATECHANGED] = @DATEADDED,
            [LASTCHANGEDBYID] = @ADDEDBYID
        WHERE USERID = @USERID;
        -- 返回已存在的ID
        SELECT ID FROM dbo.SAMPLETABLE WHERE USERID = @USERID;
    END
    ELSE
    BEGIN
        -- 无对应行，执行新增
        INSERT INTO dbo.SAMPLETABLE ([AMOUNT], [DATEADDED],[LASTCHANGEDBYID], [DATECHANGED], USERID)
        VALUES (@AMOUNT, @DATEADDED, @ADDEDBYID, @DATEADDED, @USERID);
        -- 返回新生成的ID
        SELECT SCOPE_IDENTITY() AS ID;
    END
END

COMMIT TRANSACTION;

说明 #

UPDLOCK+HOLDLOCK会在查询时添加更新锁，并将锁保持到事务结束，确保并发请求必须等待第一个事务提交后才能进行判断，从根源上避免重复新增。

方案2：使用MERGE语句统一增改逻辑 #

MERGE语句可以合并新增和更新的逻辑，结合锁定提示同样能解决并发问题，代码更简洁。

示例SQL：

BEGIN TRANSACTION;

DECLARE @NEWID INT;

MERGE dbo.SAMPLETABLE WITH (HOLDLOCK) AS T
USING (
    SELECT 
        CASE WHEN @ID > 0 THEN @ID ELSE NULL END AS ID,
        @AMOUNT AS AMOUNT,
        @DATEADDED AS DATEADDED,
        @ADDEDBYID AS ADDEDBYID
) AS S
ON (T.ID = S.ID OR (S.ID IS NULL AND T.USERID = @USERID)) -- 新增时用业务唯一键匹配
WHEN MATCHED THEN
    UPDATE SET 
        T.AMOUNT = S.AMOUNT,
        T.DATECHANGED = S.DATEADDED,
        T.LASTCHANGEDBYID = S.ADDEDBYID
WHEN NOT MATCHED THEN
    INSERT ([AMOUNT], [DATEADDED],[LASTCHANGEDBYID], [DATECHANGED], USERID)
    VALUES (S.AMOUNT, S.DATEADDED, S.ADDEDBYID, S.DATEADDED, @USERID)
OUTPUT INSERTED.ID INTO @NEWID;

SELECT @NEWID AS ID;

COMMIT TRANSACTION;

说明 #

HOLDLOCK会锁定目标表的相关范围，确保MERGE操作的原子性，并发请求会等待当前事务完成后再执行，避免重复插入。

方案3：API层添加分布式锁 #

如果数据库层面的锁定不适合你的业务场景，可以在API层面对同一个业务标识（比如用户ID+请求的业务唯一标识）添加分布式锁（例如基于Redis的SETNX命令）。只有获取到锁的请求才能执行数据库操作，其他请求等待锁释放后再处理，确保第二个调用能读取到第一个调用新增的行，进而执行更新逻辑。

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