VB.NET+SQL开发中如何防止并发插入数据超出数量上限

问题根因 #

现有逻辑采用「先查询剩余配额→代码判断→执行插入」的非原子流程，配额校验和数据插入两个操作之间没有互斥保护，并发场景下多个请求会同时通过配额校验，最终导致总插入量突破上限。
所有仅在应用层做的校验（包括内存计数、前端拦截、先查后判断）都无法彻底解决并发超量问题，必须在数据库层面保证校验+插入的原子性。 #

方案一：原子SQL实现校验插入（优先推荐，性能最优） #

将配额校验逻辑直接嵌入INSERT语句，利用数据库的锁机制保证校验和插入操作原子执行，从根源避免并发冲突。

改造后的插入SQL #

使用INSERT...SELECT语法，仅当已用总量+本次插入量≤总配额时才执行插入，同时加更新锁避免并发穿透：

-- 以下参数请通过SqlParameter传入，禁止直接拼接SQL字符串
INSERT INTO insertData (Pro_no, Quantity, Description)
SELECT @ProNo, @InsertQty, @Desc
FROM Quantity q WITH (UPDLOCK, HOLDLOCK)
WHERE 
    q.Pro_no = @ProNo
    AND (
        SELECT ISNULL(SUM(Quantity), 0)
        FROM insertData
        WHERE Pro_no = @ProNo
    ) + @InsertQty <= q.Quantity

锁提示说明：

• UPDLOCK：读取配额行时直接加更新锁，阻塞其他并发事务对同一行的更新锁申请，避免多个请求同时通过校验
• HOLDLOCK：锁持有到事务提交，防止幻读

执行结果判断 #

SQL执行完成后读取返回的影响行数：

• 影响行数=1：插入成功
• 影响行数=0：配额不足，插入失败

方案二：触发器兜底（最后一道防线） #

即使代码逻辑出现疏漏，数据库层面的触发器也会强制拦截超量插入，100%保障数据一致性：

CREATE TRIGGER trg_InsertData_CheckQuota
ON insertData
AFTER INSERT
AS
BEGIN
    SET NOCOUNT ON;
    IF EXISTS(
        SELECT 1
        FROM inserted ins
        INNER JOIN Quantity q ON ins.Pro_no = q.Pro_no
        INNER JOIN (
            SELECT Pro_no, SUM(Quantity) AS TotalUsed
            FROM insertData
            GROUP BY Pro_no
        ) used ON ins.Pro_no = used.Pro_no
        WHERE used.TotalUsed > q.Quantity
    )
    BEGIN
        RAISERROR('操作失败：超出对应产品的数量配额',16,1)
        ROLLBACK TRANSACTION
        RETURN
    END
END

触发器触发时会直接回滚超量的插入事务，向上层抛出明确错误。 #

VB.NET端代码改造 #

原有代码无论插入是否成功都固定返回成功提示，需要调整逻辑适配数据库返回的真实结果：

改造DataInsert函数 #

要求DAL层的QuantityDataInsert方法返回SQL执行的实际影响行数，不要固定返回1：

Private Function DataInsert() As Boolean
    Using create As New clsQuantityProject_dal
        Dim obj As New clsQuantityProject_info
        With obj
            .Part_No = txtPartNo.Text.Trim()
            .Description = txtDesc.Text.Trim()
            ' 数值类型做强类型转换，避免直接传Text导致的类型错误、注入风险
            .QTY = Integer.Parse(spinEditQTY.Text)
        End With
        ' 执行参数化的原子插入SQL，返回影响行数
        Dim affectRows As Integer = create.QuantityDataInsert(obj)
        Return affectRows = 1
    End Using
End Function

改造按钮点击事件 #

根据插入结果返回对应提示，同时捕获数据库抛出的异常：

Protected Sub btnCreate_Click(sender As Object, e As EventArgs) Handles btnCreate.Click
    If ErrorChecking() = True Then
        Try
            Dim insertSuccess As Boolean = DataInsert()
            If insertSuccess Then
                DisplayMessage("success", "Done", "Data Succesfully Inserted!")
                clearAll()
            Else
                DisplayMessage("error", "Failed", "当前产品剩余配额不足，无法提交数据")
            End If
        Catch ex As Exception
            DisplayMessage("error", "Failed", $"插入失败：{ex.Message}")
        End Try
    End If
End Sub

注意事项 #

• 所有数据库操作必须使用参数化查询，禁止直接拼接SQL字符串，避免SQL注入风险
• 前端、应用层的配额校验仅作为用户体验优化（比如提前提示剩余额度），不能作为最终拦截逻辑
• 禁止使用「先查剩余额度→代码判断→插入」的拆分逻辑，该逻辑在并发场景下必然出现超量问题

内容的提问来源于stack exchange，提问作者Una O Lian