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 cases, a deadlock occurs:
- Session 1 blocks the IX_PriorityAndDate index (U lock).
- Session 2 blocks the PK_Commands index (block X).
- Session 1 blocks pending PK_Commands (acquires a U lock).
- Session 2 blocks waiting for IX_PriorityAndDate (acquires X lock).
How to solve this dead end?
source share