并发执行插入更新操作时如何避免Assets表出现多条Status=1的有效记录

• 并发场景下存储过程的第一个查询DECLARE @currentBal decimal = (SELECT TOP (1) [Balance] FROM [dbo].[Assets] WHERE [Owner] = @owner AND [Status]=1)没有加任何锁，默认读提交隔离级别下，多个请求可以同时读到同一条Status=1的记录，都进入IF @currentBal >=0的逻辑分支
• 后续插入表变量@assetTable时加的UPDLOCK仅对当前查询的行加锁，但此时多个请求已经全部通过了IF判断，都会执行插入新的Status=1的Assets记录的逻辑
• 表变量是会话/事务级别的，不同事务的表变量数据不共享，多个事务会更新同一条旧记录的Status为2，其余事务新插入的Status=1的记录不会被处理，最终导致出现多条Status=1的记录

方案1：优化存储过程逻辑，加锁保证串行执行 #

修复后的存储过程代码如下：

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[AddAssets] 
    @owner uniqueidentifier, 
    @rvalue nvarchar(MAX)
AS
BEGIN
    SET XACT_ABORT ON; -- 出错自动回滚事务
    BEGIN TRANSACTION;

    DECLARE @currentBal decimal;
    -- 查询时加UPDLOCK和HOLDLOCK，锁住符合条件的行，避免并发读，直到事务结束才释放锁
    SELECT TOP 1 @currentBal = [Balance] 
    FROM [dbo].[Assets] WITH (UPDLOCK, HOLDLOCK)
    WHERE [Owner] = @owner AND [Status] = 1;

    IF @currentBal >= 0
    BEGIN
        DECLARE @oldId INT;
        SELECT TOP 1 @oldId = [Id]
        FROM [dbo].[Assets]
        WHERE [Owner] = @owner AND [Status] = 1;

        INSERT INTO [dbo].[Assets] ([Owner], [Balance], [Status])
        VALUES (@owner, @rvalue, 1);

        UPDATE [dbo].[Assets] 
        SET [Status] = 2  
        WHERE [Id] = @oldId;
    END
    -- 兼容Owner没有任何记录的情况，直接插入第一条
    ELSE IF @currentBal IS NULL
    BEGIN
        INSERT INTO [dbo].[Assets] ([Owner], [Balance], [Status])
        VALUES (@owner, @rvalue, 1);
    END

    COMMIT TRANSACTION;
END

方案2：增加数据库唯一过滤索引，从底层约束数据正确性 #

直接在数据库层面添加唯一过滤索引，强制同一个Owner只能有一条Status=1的记录，即使出现逻辑漏洞也不会生成脏数据：

CREATE UNIQUE NONCLUSTERED INDEX UQ_Assets_Owner_ActiveStatus
ON [dbo].[Assets] ([Owner])
WHERE [Status] = 1;

可选优化：调整事务隔离级别（非必须） #

如果业务场景允许，可以将C#代码中开启事务的逻辑调整为可重复读或者序列化隔离级别，进一步降低并发冲突概率：

var tran = Db.BeginTransaction(IsolationLevel.Serializable);

额外优化建议：C#代码中Db.Execute(updateQuery1, new { Id });为同步调用，建议修改为await Db.ExecuteAsync(updateQuery1, new { Id });，避免混合同步异步调用引发的死锁问题。

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