两个同定时SQL Server作业执行时出现死锁问题咨询

问题场景 #

我有两个计划在上午9点同时执行的作业，均通过循环容器每次迭代下载一份SSRS报表。作业流程中调用的存储过程出现死锁——该存储过程负责从队列中获取下一个待运行报表的ID，并通过更新状态列标记该行已被占用。两个作业的@ReportRunName参数值完全不同。

存储过程代码 #

核心事务逻辑 #

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ

BEGIN TRAN
    DECLARE @ReportID INT

    SELECT TOP 1 @ReportID = SSRSReportId
    FROM dbo.ReportAutomationThreadQueue WITH (UPDLOCK)
    WHERE ReportRunName = @ReportRunName 
      AND ExtractStatus IS NULL

    UPDATE dbo.ReportAutomationThreadQueue
    SET ExtractStatus = 'Pending',
        ThreadInstanceNumber = @ThreadInstanceNumber
    WHERE ReportRunName = @ReportRunName 
      AND SSRSReportId = @ReportID

    COMMIT TRAN

结果返回逻辑 #

事务结束后，存储过程返回报表参数信息（无剩余队列内容则返回空）：

SELECT 
        SSRSReportId,
        RelativePath,
        FileName,
        ParameterId,
        ParameterName,
        ParameterValue
FROM dbo.ReportAutomationThreadQueue
WHERE ReportRunName = @ReportRunName And SSRSReportId = @ReportID

疑问与补充信息 #

• 按道理不同@ReportRunName的操作应该互不干扰，第二个调用应该等待第一个的SELECT执行完成，为什么会出现死锁？是否需要添加ROWLOCK或HOLDLOCK？
• 我尝试多次同时运行两个作业都没重现冲突，有没有办法通过添加延迟来模拟锁冲突？
• 死锁是发生在同一作业的不同循环，还是两个作业之间？
• 补充：两个作业中，一个成功完成耗时1分04秒，另一个运行12秒后被选为死锁牺牲品终止。这是不是说明死锁发生在第二个循环（第一份报表下载耗时12秒）？我认为同一作业不会引发死锁，暂时排除其他未知进程的影响。

分析与解决方案 #

死锁根源推测 #

虽然两个作业的@ReportRunName不同，但以下情况可能导致跨范围锁冲突：

1. 索引缺失：如果ReportAutomationThreadQueue表没有针对ReportRunName + ExtractStatus的复合索引，SELECT TOP 1...WITH(UPDLOCK)会执行全表扫描，导致UPDLOCK锁定大量行甚至页锁/表锁，不同@ReportRunName的操作会互相阻塞。
2. 隔离级别过高：REPEATABLE READ隔离级别会保留共享锁直到事务结束，结合UPDLOCK可能扩大锁的持有范围和时间，增加死锁概率。

存储过程优化方案 #

方案1：合并SELECT与UPDATE，缩短锁持有时间

将查询和更新合并为单条语句，避免事务内的中间变量持有，同时降低隔离级别：

SET TRANSACTION ISOLATION LEVEL READ COMMITTED

DECLARE @ReportID INT

-- 直接更新并返回被选中的报表ID
UPDATE TOP(1) dbo.ReportAutomationThreadQueue
SET ExtractStatus = 'Pending',
    ThreadInstanceNumber = @ThreadInstanceNumber
OUTPUT inserted.SSRSReportId INTO @ReportID
WHERE ReportRunName = @ReportRunName 
  AND ExtractStatus IS NULL

-- 后续查询直接用@ReportID
SELECT 
        SSRSReportId,
        RelativePath,
        FileName,
        ParameterId,
        ParameterName,
        ParameterValue
FROM dbo.ReportAutomationThreadQueue
WHERE ReportRunName = @ReportRunName And SSRSReportId = @ReportID

这种方式能最小化锁的持有时间，且仅锁定目标行。

方案2：添加针对性索引

创建复合非聚集索引，让SELECT和UPDATE都能精准定位目标行，避免全表扫描：

CREATE NONCLUSTERED INDEX IX_ReportAutomationThreadQueue_RunName_Status
ON dbo.ReportAutomationThreadQueue (ReportRunName, ExtractStatus)
INCLUDE (SSRSReportId) -- 覆盖SELECT需要的列，避免键查找

索引能确保UPDLOCK仅锁定符合条件的单行，不会扩散到其他@ReportRunName的行。

模拟死锁的方法 #

如果需要重现死锁，可以在存储过程的事务中添加延迟，模拟长时间持有锁的场景：

BEGIN TRAN
    DECLARE @ReportID INT

    SELECT TOP 1 @ReportID = SSRSReportId
    FROM dbo.ReportAutomationThreadQueue WITH (UPDLOCK)
    WHERE ReportRunName = @ReportRunName 
      AND ExtractStatus IS NULL

    WAITFOR DELAY '00:00:10' -- 添加10秒延迟，模拟报表下载前的耗时

    UPDATE dbo.ReportAutomationThreadQueue
    SET ExtractStatus = 'Pending',
        ThreadInstanceNumber = @ThreadInstanceNumber
    WHERE ReportRunName = @ReportRunName 
      AND SSRSReportId = @ReportID

    COMMIT TRAN

同时启动两个不同@ReportRunName的作业，大概率能触发死锁。

死锁确认方法 #

查看SQL Server的死锁图是最准确的方式：

• 通过扩展事件捕获死锁（默认有system_health会话，可从中提取死锁信息）
• 查询系统视图sys.dm_tran_deadlocks（仅保留最近的死锁记录）
  死锁图会明确显示参与死锁的会话、锁定的资源，能直接确认是否是两个作业的会话互相持有锁。

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