Two indices deadlock

Question

I have a table in 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,
)

The table also has an index:

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

Then I have two sessions.

Session 1

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

Session 2

DELETE FROM Commands WHERE Id = @Id

In some circumstances a deadlock occurs:

1. Session 1 locks IX_PriorityAndDate index (U lock).
2. Session 2 locks PK_Commands index (X lock).
3. Session 1 blocks on waiting PK_Commands (acquiring U lock).
4. Session 2 blocks on waiting IX_PriorityAndDate (acquiring X lock).

How to resolve this deadlock?

Answer 1

In my opinion you have the key lookup deadlock

Try to use the following index:

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

Or try to create a new index:

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