多Worker Service调用锁记录存储过程时遭遇死锁问题求助

问题根源 #

当前存储过程使用了SERIALIZABLE事务隔离级别（最严格的隔离级别，会持有范围锁直到事务结束），加上子查询JOIN的写法，导致多实例并发时，不同进程可能以不同顺序获取行锁，进而触发死锁。

优化方案 #

以下是修改后的存储过程，核心调整是统一锁获取顺序、降低隔离级别、添加跳过锁的提示：

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

BEGIN TRAN

DECLARE @Inserted TABLE (
    [Id] BIGINT NOT NULL PRIMARY KEY,
    [Status] INT NOT NULL,
    [LockDate] DATETIME NULL,
    [InternalId] BIGINT NULL,
    [SourceUpdated] BIT NOT NULL
)

UPDATE TOP (250) tm
SET
    tm.LockDate = DATEADD(MINUTE, @LockDuration, GETDATE()),
    tm.ModifiedDate = GETDATE()
OUTPUT
    Inserted.[Id],
    Inserted.[Status],
    Inserted.LockDate,
    Inserted.InternalId,
    Inserted.SourceUpdated
INTO @Inserted
FROM [Transactional].[Message] tm WITH (UPDLOCK, READPAST)
WHERE [Status] = 0 AND LockDate IS NULL
ORDER BY [ModifiedDate] ASC

COMMIT TRAN

SELECT 
    [Id],
    [Status],
    [LockDate],
    [InternalId],
    [SourceUpdated]
FROM @Inserted

关键修改说明 #

• 降低隔离级别：将SERIALIZABLE改为REPEATABLE READ，减少锁的持有范围和时长，同时保证足够的一致性满足业务需求
• 统一锁顺序：直接在UPDATE中使用TOP + ORDER BY，确保所有实例严格按照ModifiedDate升序获取行锁，从根源避免交叉锁导致的死锁
• 添加READPAST提示：让当前进程自动跳过已被其他实例锁定的记录，无需等待锁释放，彻底消除锁等待引发的死锁场景
• 简化查询逻辑：移除子查询JOIN，减少执行计划的复杂度，缩短锁的持有时间

额外优化建议 #

1. EF调用添加重试逻辑：捕获SqlException，判断错误码为1205（死锁错误码）时，自动重试调用存储过程，示例代码：

var retryCount = 3;
while (retryCount > 0)
{
    try
    {
        var records = await _context.TransactionalMessages!
            .FromSqlRaw("EXEC [Records].[LockRecordsForProcessing]")
            .ToListAsync(cancellationToken: token);
        return records;
    }
    catch (SqlException ex) when (ex.Number == 1205 && retryCount-- > 0)
    {
        // 短暂等待后重试
        await Task.Delay(100, token);
    }
}

2. 创建合适的索引：为[Transactional].[Message]表创建覆盖索引，加速符合条件的记录查找，减少锁的持有时间：

CREATE NONCLUSTERED INDEX IX_Message_Status_LockDate_ModifiedDate
ON [Transactional].[Message] ([Status], [LockDate])
INCLUDE ([ModifiedDate], [Id], [InternalId], [SourceUpdated])

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