sql - 两个指数陷入僵局

Question

我在 SQL Server (Azure SQL) 中有一个表:

CREATE TABLE Commands
(
    Id int NOT NULL PRIMARY KEY,
    Body nvarchar(1000) NOT NULL,
    Priority int NOT NULL,
    DeliveryDate datetime NOT NULL,
    VisibleFrom datetime NULL,
)

该表还有一个索引:

CREATE NONCLUSTERED INDEX IX_PriorityAndDate ON Commands (Priority DESC, DeliveryDate ASC)

然后我有两个 session 。

第一节

WITH command AS
(
    SELECT TOP(1) *
    FROM Commands q
    WHERE q.DeliveryDate <= @CurrentDate
      AND (q.VisibleFrom IS NULL OR q.VisibleFrom <= @CurrentDate)
    ORDER BY q.Priority DESC, q.DeliveryDate
)
UPDATE command SET command.VisibleFrom = DATEADD(SECOND, @LeaseTimeout, @CurrentDate)
OUTPUT inserted.Id,
       inserted.Body

第二节

DELETE FROM Commands WHERE Id = @Id

在某些情况下会发生死锁:

1. session 1 锁定 IX_PriorityAndDate 索引(U 锁)。
2. session 2 锁定 PK_Commands 索引(X 锁定)。
3. session 1 因等待 PK_Commands(获取 U 锁)而阻塞。
4. session 2 在等待 IX_PriorityAndDate(获取 X 锁)时阻塞。

如何解决这个僵局？

Answer 1

在我看来，你遇到了 key 查找死锁

尝试使用以下索引:

CREATE NONCLUSTERED INDEX IX_PriorityAndDate
  ON Commands (Priority DESC, DeliveryDate ASC) INCLUDE(VisibleFrom)

或者尝试创建一个新索引:

CREATE NONCLUSTERED INDEX IX_PriorityAndDate
  ON Commands(DeliveryDate, VisibleFrom) INCLUDE(Priority)