如何防止用户多电脑并发插入时绕过校验插入无效数据？

问题背景 #

我有一张数据表，执行插入操作前会通过存储过程做校验。但当两台电脑几乎同步发起插入请求时，会出现绕过校验插入无效数据的情况。已经尝试在插入语句前紧邻执行校验，也用了事务块，但问题依然存在。

原存储过程代码：

BEGIN TRAN
    IF 
        (
            (
            SELECT SUM(Weight) FROM table
            WHERE 
                MojavezCode = @certId   AND 
                goodsTypeId = @goodType AND 
                isDeleted   = 0         AND 
                DateSabt BETWEEN @StartDore AND @EndDore
            ) 
            > @sahmiye
        )
    BEGIN
        RAISERROR ('---',15,18)
        RETURN   -9523
    END

    INSERT INTO table
    (
       FromKharidarID,
       AmelKharidID,
       DateSabt,
       TimeSabt,
       MojavezCode,
       
       isDeleted,
       goodsTypeId,
       InsertUserIp,
       UniqID,
       Weight
    )
    VALUES
    (  @kharidarId,    
       @EtehadyeId,    
        ( SELECT dbo.SolarDate(CONVERT(NVARCHAR(10), GETDATE(), 111))),  
        ( SELECT CONVERT(NVARCHAR(12), GETDATE(), 108)),  
        LTRIM(RTRIM(@certId)) ,  
           
       0, 
       @goodType  ,   
       LTRIM(RTRIM(@userIP)), 
       @uniqueId,
       @Wight
        )
    COMMIT TRAN

问题原因 #

核心问题是并发事务的读一致性冲突。默认事务隔离级别（如READ COMMITTED）下，两个并行事务会同时读取到SUM(Weight)的旧值，均通过校验后执行插入，最终导致总和超出限制。事务块本身无法解决这个问题，因为校验和插入之间仍存在窗口，允许其他事务修改数据。

解决方案 #

方案1：在校验查询中添加表提示 #

在SELECT SUM(Weight)语句中加入WITH (UPDLOCK, HOLDLOCK)，强制锁定符合条件的行，直到当前事务结束，阻止其他事务同时修改或读取这些数据：

BEGIN TRAN
    IF 
        (
            (
            SELECT SUM(Weight) FROM table WITH (UPDLOCK, HOLDLOCK)
            WHERE 
                MojavezCode = @certId   AND 
                goodsTypeId = @goodType AND 
                isDeleted   = 0         AND 
                DateSabt BETWEEN @StartDore AND @EndDore
            ) 
            > @sahmiye
        )
    BEGIN
        RAISERROR ('---',15,18)
        RETURN   -9523
    END

    INSERT INTO table
    (
       FromKharidarID,
       AmelKharidID,
       DateSabt,
       TimeSabt,
       MojavezCode,
       
       isDeleted,
       goodsTypeId,
       InsertUserIp,
       UniqID,
       Weight
    )
    VALUES
    (  @kharidarId,    
       @EtehadyeId,    
        ( SELECT dbo.SolarDate(CONVERT(NVARCHAR(10), GETDATE(), 111))),  
        ( SELECT CONVERT(NVARCHAR(12), GETDATE(), 108)),  
        LTRIM(RTRIM(@certId)) ,  
           
       0, 
       @goodType  ,   
       LTRIM(RTRIM(@userIP)), 
       @uniqueId,
       @Wight
        )
    COMMIT TRAN

• UPDLOCK：为行添加更新锁，其他事务可读取但无法修改或添加更新锁
• HOLDLOCK：将锁持有至事务结束，等效于SERIALIZABLE隔离级别的锁行为

方案2：设置事务隔离级别为SERIALIZABLE #

在事务开始前设置隔离级别为SERIALIZABLE（最严格的隔离级别），防止幻读，确保校验与插入操作的原子性：

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRAN
    IF 
        (
            (
            SELECT SUM(Weight) FROM table
            WHERE 
                MojavezCode = @certId   AND 
                goodsTypeId = @goodType AND 
                isDeleted   = 0         AND 
                DateSabt BETWEEN @StartDore AND @EndDore
            ) 
            > @sahmiye
        )
    BEGIN
        RAISERROR ('---',15,18)
        RETURN   -9523
    END

    INSERT INTO table
    (
       FromKharidarID,
       AmelKharidID,
       DateSabt,
       TimeSabt,
       MojavezCode,
       
       isDeleted,
       goodsTypeId,
       InsertUserIp,
       UniqID,
       Weight
    )
    VALUES
    (  @kharidarId,    
       @EtehadyeId,    
        ( SELECT dbo.SolarDate(CONVERT(NVARCHAR(10), GETDATE(), 111))),  
        ( SELECT CONVERT(NVARCHAR(12), GETDATE(), 108)),  
        LTRIM(RTRIM(@certId)) ,  
           
       0, 
       @goodType  ,   
       LTRIM(RTRIM(@userIP)), 
       @uniqueId,
       @Wight
        )
    COMMIT TRAN

注意事项 #

• 两种方案都会延长锁的持有时间，可能带来一定性能损耗，需根据业务并发量评估
• 建议为table表在MojavezCode, goodsTypeId, isDeleted, DateSabt字段上创建复合索引，缩小锁的范围，提升并发性能

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