多后端并发调用存储过程致计数器重复递增的解决方法

问题根源分析 #

你的存储过程采用先查询后更新的分离操作，高并发场景下多个事务会同时读取到相同的Created_Accounts值，随后各自执行递增并更新，最终导致重复值和计数不达标的问题。另外代码中存在嵌套事务（外层BEGIN TRAN+内层BEGIN TRANSACTION），干扰了SQL Server的事务管理逻辑，进一步加剧了并发冲突。

最优解决方案：原子化更新操作 #

直接在UPDATE语句中完成递增逻辑，利用SQL Server的行级锁机制保证操作原子性，从根本上避免并发冲突。修改后的存储过程如下：

CREATE PROCEDURE [dbo].[INCREMENT_TABLE_COUNT]
(
  @id_entry INT
)
AS BEGIN
    SET NOCOUNT ON; -- 避免返回额外行数影响调用方

    DECLARE @UpdatedTotal INT, @UpdatedCreated INT;

    -- 原子化更新并获取更新后的值
    UPDATE table_count
    SET Created_Accounts = Created_Accounts + 1
    OUTPUT inserted.Total_Accounts, inserted.Created_Accounts
    INTO @UpdatedTotal, @UpdatedCreated
    WHERE first_col = @id_entry
      AND Created_Accounts < Total_Accounts; -- 条件直接整合到UPDATE中

    -- 若未执行更新（如Created_Accounts已达Total_Accounts），查询当前值返回
    IF @@ROWCOUNT = 0
    BEGIN
        SELECT @UpdatedTotal = Total_Accounts, @UpdatedCreated = Created_Accounts
        FROM table_count
        WHERE first_col = @id_entry;
    END

    -- 返回结果
    SELECT @UpdatedTotal AS Total_Accounts, @UpdatedCreated AS Created_Accounts;
END

方案优势 #

1. 原子性：UPDATE操作本身是原子的，SQL Server会自动对目标行加排他锁，其他事务必须等待锁释放才能操作该行，彻底杜绝同时读取相同值的情况。
2. 高效性：无需额外事务嵌套和手动锁操作，减少事务开销，比依赖高隔离级别或手动锁的方案性能更优。
3. 逻辑简洁：将查询和更新逻辑合并，避免分离操作带来的并发漏洞。

其他可选方案（非最优） #

• 提升隔离级别：将隔离级别设置为SERIALIZABLE，强制事务串行执行，但会大幅降低并发性能，不推荐高并发场景使用。
• 乐观锁：给表添加Version字段，更新时带上版本条件（WHERE first_col = @id_entry AND Version = @CurrentVersion），更新失败则重试。但需要调用方处理重试逻辑，复杂度较高。

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