SQL Server存储过程并发执行致轮询分配失效问题排查求助

你的问题核心是并发竞态条件——循环调用时多个<CreateTicket>会话同时执行，在<GetNextRep>读取LastAssignedDate时，前一个会话的更新还未提交，导致所有会话读取到相同的初始值，最终都分配给同一个代表。手动执行时因间隔足够，事务已完成提交，字段更新生效，所以轮询正常。

排查验证 #

• 启用SQL Server扩展事件，跟踪sql_statement_completed事件，过滤<GetNextRep>相关的读取和更新语句，查看并发会话的执行顺序，确认是否存在多个会话在更新前读取了相同的LastAssignedDate值。
• 在<GetNextRep>中添加日志逻辑，记录每次执行的会话ID、读取的LastAssignedDate、选中的代表ID，以及更新后的LastAssignedDate值。通过日志可以直观看到并发时是否出现重复读取初始值的情况。

解决方法 #

1. 使用更新锁+跳过锁定行提示（推荐，兼顾性能与正确性） #

在<GetNextRep>的读取逻辑中添加UPDLOCK和READPAST锁提示，确保读取到代表后立即加锁，避免其他会话同时读取该行；READPAST则让其他会话跳过被锁定的行，直接选择下一个可用代表，避免阻塞。

示例代码：

CREATE PROCEDURE GetNextRep
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @SelectedRepId INT;

    -- 加UPDLOCK锁定选中的行，READPAST跳过已被锁定的行
    SELECT TOP 1 @SelectedRepId = RepId
    FROM Rep WITH (UPDLOCK, READPAST)
    WHERE IsOnDuty = 1 -- 假设标识在岗状态的字段
    ORDER BY LastAssignedDate ASC;

    -- 同步更新该代表的最后分配时间
    UPDATE Rep
    SET LastAssignedDate = GETUTCDATE()
    WHERE RepId = @SelectedRepId;

    RETURN @SelectedRepId;
END

2. 乐观并发控制（重试机制） #

通过检查更新前后的LastAssignedDate是否一致，判断是否存在并发冲突，若冲突则重新读取并尝试分配，直到成功。这种方式避免了锁阻塞，但可能会有少量重试开销。

示例代码：

CREATE PROCEDURE GetNextRep
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @SelectedRepId INT;
    DECLARE @OriginalLastAssigned DATETIME;

    WHILE 1 = 1
    BEGIN
        -- 读取当前最早分配的代表
        SELECT TOP 1 
            @SelectedRepId = RepId, 
            @OriginalLastAssigned = LastAssignedDate
        FROM Rep
        WHERE IsOnDuty = 1
        ORDER BY LastAssignedDate ASC;

        -- 更新时校验原始时间，确保未被其他会话修改
        UPDATE Rep
        SET LastAssignedDate = GETUTCDATE()
        WHERE RepId = @SelectedRepId AND LastAssignedDate = @OriginalLastAssigned;

        -- 若更新成功（影响行数为1），退出循环
        IF @@ROWCOUNT = 1
            BREAK;
    END

    RETURN @SelectedRepId;
END

3. 原子性计数器轮询 #

改用独立的计数器表实现轮询，利用SQL Server的UPDATE原子性操作，避免直接操作Rep表的LastAssignedDate带来的竞态问题。

步骤：

1. 创建计数器表：

CREATE TABLE RepSequence (
    CurrentIndex INT NOT NULL DEFAULT 0,
    CONSTRAINT PK_RepSequence PRIMARY KEY CLUSTERED (CurrentIndex)
);
INSERT INTO RepSequence VALUES (0);

2. 修改<GetNextRep>：

CREATE PROCEDURE GetNextRep
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @CurrentIndex INT;
    DECLARE @OnDutyCount INT;

    -- 获取当前在岗代表数量
    SELECT @OnDutyCount = COUNT(*) FROM Rep WHERE IsOnDuty = 1;
    IF @OnDutyCount = 0
        RETURN NULL; -- 无在岗代表时返回空

    -- 原子性递增计数器，取模得到当前轮询位置
    UPDATE RepSequence
    SET @CurrentIndex = CurrentIndex, 
        CurrentIndex = (CurrentIndex + 1) % @OnDutyCount;

    -- 根据轮询位置获取对应的代表
    SELECT RepId 
    FROM (
        SELECT 
            RepId, 
            ROW_NUMBER() OVER (ORDER BY RepId) AS RowNum
        FROM Rep WHERE IsOnDuty = 1
    ) t 
    WHERE RowNum = @CurrentIndex + 1;
END

额外注意事项 #

• 确保<CreateTicket>和<GetNextRep>的事务边界正确，建议将<GetNextRep>的读取和更新逻辑包裹在同一事务中，避免锁提前释放。
• 修复后用并发测试验证，比如通过多个会话同时循环调用<CreateTicket>，检查分配结果是否符合轮询预期。

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