J'ai une table dans 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, )
La table a également un index:
CREATE NONCLUSTERED INDEX IX_PriorityAndDate ON Commands (Priority DESC, DeliveryDate ASC)
Ensuite, j'ai deux 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
Dans certaines circonstances, un blocage se produit:
Comment résoudre cette impasse?
À mon avis, vous avez l'impasse key de search
Essayez d'utiliser l'index suivant:
CREATE NONCLUSTERED INDEX IX_PriorityAndDate ON Commands (Priority DESC, DeliveryDate ASC) INCLUDE(VisibleFrom)
Ou essayez de créer un nouvel index:
CREATE NONCLUSTERED INDEX IX_PriorityAndDate ON Commands(DeliveryDate, VisibleFrom) INCLUDE(Priority)