SQL作业队列实现中的索引与锁定策略

Question

在 SQL 2008+ 中，以下队列表和入队、出队操作旨在允许在任意命名队列中的多个生成器和使用者之间的流中以可选的部分有序连续方式进行高效的作业排队。通过 RetryLater()、FailNow()、Reset()（未显示）对有害消息处理提供简单支持。

CREATE TABLE [dbo].[Queue](
    [ID] BIGINT IDENTITY(1,1) NOT NULL CONSTRAINT [PK_Queue] PRIMARY KEY,
    [PredecessorID] BIGINT NULL,
    [QueueName] NVARCHAR(255) NOT NULL,
    [DataType] NVARCHAR(255) NOT NULL,
    [Data] NVARCHAR(MAX) NOT NULL,
    [RetriesRemaining] INT CONSTRAINT [CHK_RetryCount] CHECK ([RetriesRemaining] IS NULL OR 0 <= [RetriesRemaining]),
    [IsFailed] AS (CASE WHEN [RetriesRemaining] IS NULL OR 0 < [RetriesRemaining] THEN 0 ELSE 1 END) PERSISTED,
    [QueuedOnUTC] DATETIME NOT NULL DEFAULT (GETUTCDATE()),
    [DelayUntilUTC] DATETIME NULL,
    [LastFailedOnUTC] DATETIME NULL,
    [LastFailure] NVARCHAR(MAX)
)

-- Enqueue
INSERT INTO [dbo].[Queue]
(
    [PredecessorID],
    [QueueName],
    [DataType],
    [Data],
    [RetriesRemaining]
)
VALUES
(
    @PredecessorID,
    @QueueName,
    @DataType,
    @Data,
    @RetriesRemaining
)

-- Dequeue
SELECT
    [Dequeued].[ID],
    [Dequeued].[QueueName],
    [Dequeued].[DataType],
    [Dequeued].[Data]
FROM
    [dbo].[Queue] AS [Dequeued] WITH (ROWLOCK, UPDLOCK, READPAST)
LEFT JOIN
    [dbo].[Queue] AS [Predecessor] ON
    [Dequeued].[PredecessorID] = [Predecessor].[ID]
WHERE
    [Dequeued].[IsFailed] = 0
    AND [Dequeued].[QueueName] = @QueueName
    AND ([Dequeued].[DelayUntilUTC] IS NULL OR [Dequeued].[DelayUntilUTC] < GETUTCDATE())
    AND [Predecessor].[ID] IS NULL

消费者事务从 Dequeue() 之前到应用程序端处理一直保持打开状态，直到 DELETE、RetryLater() 或 FailNow()。

• 这个设计合理吗？
• 这个设置可以避免死锁吗？
• IDX_Queue_IsFailed_QueueName_DelayUntilUTC 或其他索引可能会对无死锁产生什么负面影响？
• 还有哪些其他指标是有益的？
• 表分区（按队列名称）或其他功能如何提高可扩展性？

• 按照设想，生成器通过外部机制向消费者发出信号，表明数据已排队以避免严格轮询。相反，（在没有 SQL ServiceBroker 的情况下）是否有一种方法可以使用 SQL 锁来有效锁定没有可用行的消费者，直到生成器写入指定队列？

  CREATE INDEX [IDX_Queue_IsFailed_QueueName_DelayUntilUTC] ON [dbo].[Queue] ([IsFailed], [QueueName], [DelayUntilUTC])

另外，我想 OrderBy 并不重要，只要我们总是得到一个没有前驱的订单即可。

Answer 1

我对此的 2c 点是：

• 首先，出队是读取。一定是写的。使用使用 OTUPUT 更新。
• 不要对 DelayUntilUTC 使用 NULL，在插入时使用当前 UTC。 OR 表达式可能会触发扫描，导致死锁，除了性能下降之外，
• [Queue] 聚集索引必须如下：(IsFailed, QueueName, DelayUntilUTC)。
• 使主键成为非聚集的，或者完全摆脱它，队列中的身份没有太多用处。
• 摆脱前驱和左连接。你将不得不与他们一起解决僵局，直到退休。

我知道最后一项可能很难理解，但是在关系表上使用 readpast 进行相关锁定基本上是不可能实现的。

我在使用表作为队列。队列必须简单才能扩展。你的设计太花哨了，行不通。