Two offset indexes

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?

+5
source share
1 answer

In my opinion, you have a dead end with key search

Try using the following index:

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

Or try creating a new index:

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

Source: https://habr.com/ru/post/1201800/


All Articles