Zakleszczenie programu SQL Server dla dwóch aktualizacji z powodu kolejności blokowania indeksu

Mam dwie aktualizacje - jedna blokuje najpierw CI, a następnie NCI (w stanie), ponieważ kolumna statusu również jest aktualizowana. Drugi jest już właścicielem blokady U na NCI, ponieważ wie, że się zmienia, a następnie próbuje uzyskać blokadę U na CI.

Jak najłatwiej zmusić ich do serializacji? Używanie podpowiedzi na poziomie TABELI wydaje się dziwne, ponieważ jest to wewnętrzny problem z indeksowaniem - dotyczy tylko jednej tabeli - czy UPDLOCK, HOLDLOCK automatycznie zastosuje się do wszystkich indeksów potrzebnych w tej tabeli i tym samym zmusi ją do serializacji?

Oto pytania:

UPDATE htt_action_log
SET status = 'ABORTED', CLOSED = GETUTCDATE()
WHERE transition_uuid = '{F53ADDDA-E46B-4726-66D8-D7B640B66597}'
AND status = 'OPEN';

Ten jeden X blokuje wiersz w CI (w kolumnie CREATED), a następnie próbuje zablokować X w NCI, który zawiera kolumnę statusu.

UPDATE htt_action_log
SET status = 'RUNNING {36082BCD-EB52-4358-E3D3-4D96FD5B9F0F} 1360094342'
WHERE action_uuid = (SELECT TOP 1 action_uuid
                     FROM htt_action_log
                     WHERE transition_uuid = '{F53ADDDA-E46B-4726-66D8-D7B640B66597}'
                         AND status = 'OPEN'
                     ORDER BY action_seq)

Ten U blokuje to samo NCI - wydaje mi się, że dla zapytania zagnieżdżonego następnie blokuje CI dla aktualizacji.

Zatem zamówienie wywołuje impas.

Najłatwiejszym rozwiązaniem jest wymuszenie całkowitego zablokowania dwóch zapytań, tj. Serializacji. Jaki jest najłatwiejszy sposób na wymuszenie tego, wystarczy umieścić WITH (UPDLOCK, HOLDLOCK)odniesienia do tabeli (jeden w pierwszym i dwa w drugim)?

DDL:

Uwaga: klient ma więcej indeksów w tej tabeli, na które powinna mieć wpływ ta aktualizacja, ale nie są wymienione na wykresie zakleszczenia.

CREATE TABLE [dbo].[HTT_ACTION_LOG](
    [ACTION_UUID] [varchar](128) NOT NULL,
    [TRANSITION_UUID] [varchar](128) NOT NULL,
    [STATUS] [varchar](128) NOT NULL,
    [CREATED] [datetime] NOT NULL,
    [CLOSED] [datetime] NULL,
    [ACTION_SEQ] [int] NOT NULL,
    [ACTION_TYPE] [varchar](15) NOT NULL,
    [ACTION_NAME] [varchar](50) NOT NULL,
    [ACTION_RESULT] [varchar](8000) NULL,
    [PENDING_SINCE] [datetime] NULL,
    [ACTION_SQL] [varchar](8000) NULL,
    [ERROR_OK] [int] NULL,
    [ERROR_COND] [varchar](2048) NULL,
    [RETRY] [varchar](128) NULL,
 CONSTRAINT [PK_HTT_ACTION_LOG_1] UNIQUE NONCLUSTERED 
(
    [ACTION_UUID] ASC
)
)

CREATE CLUSTERED INDEX [IK_HTT_ACTION_LOG_2] ON [dbo].[HTT_ACTION_LOG] 
(
    [CREATED] ASC
)

CREATE NONCLUSTERED INDEX [IK_HTT_ACTION_LOG_1] ON [dbo].[HTT_ACTION_LOG] 
(
    [TRANSITION_UUID] ASC,
    [STATUS] ASC
)
INCLUDE ( [ACTION_UUID],
[ACTION_SEQ])

CREATE NONCLUSTERED INDEX [IK_HTT_ACTION_LOG_4] ON [dbo].[HTT_ACTION_LOG] 
(
    [ACTION_UUID] ASC,
    [STATUS] ASC
)

CREATE NONCLUSTERED INDEX [missing_index_11438530_11438529_HTT_ACTION_LOG] ON [dbo].[HTT_ACTION_LOG] 
(
    [TRANSITION_UUID] ASC,
    [ACTION_TYPE] ASC
)
INCLUDE ( [ACTION_NAME])

CREATE NONCLUSTERED INDEX [missing_index_7207590_7207589_HTT_ACTION_LOG] ON [dbo].[HTT_ACTION_LOG] 
(
    [STATUS] ASC
)
INCLUDE ( [CREATED],
[PENDING_SINCE],
[ACTION_NAME])

CREATE NONCLUSTERED INDEX [missing_index_8535421_8535420_HTT_ACTION_LOG] ON [dbo].[HTT_ACTION_LOG] 
(
    [TRANSITION_UUID] ASC
)
INCLUDE ( [ACTION_UUID],
[STATUS])

ALTER TABLE [dbo].[HTT_ACTION_LOG] SET (LOCK_ESCALATION = AUTO)

ALTER TABLE [dbo].[HTT_ACTION_LOG]  WITH CHECK ADD  CONSTRAINT [FK_HTT_ACTION_LOG_1] FOREIGN KEY([TRANSITION_UUID])
REFERENCES [dbo].[HTT_TRANSITION_LOG] ([TRANSITION_UUID])

ALTER TABLE [dbo].[HTT_ACTION_LOG] CHECK CONSTRAINT [FK_HTT_ACTION_LOG_1]

ALTER TABLE [dbo].[HTT_ACTION_LOG] ADD  DEFAULT ('OPEN') FOR [STATUS]

ALTER TABLE [dbo].[HTT_ACTION_LOG] ADD  DEFAULT (getutcdate()) FOR [CREATED]

ALTER TABLE [dbo].[HTT_ACTION_LOG] ADD  DEFAULT ((0)) FOR [ERROR_OK]

Optymalny indeks dla tych dwóch zapytań nie jest daleki od istniejącej definicji IK_HTT_ACTION_LOG_1indeksu (dodaj ACTION_UUIDjako INCLUDEpoprawiony indeks poniżej):

CREATE INDEX nc1
ON dbo.HTT_ACTION_LOG
(
    TRANSITION_UUID,
    STATUS,
    ACTION_SEQ
);

Pierwsze zapytanie to:

UPDATE dbo.HTT_ACTION_LOG
SET [STATUS] = 'ABORTED', 
    CLOSED = GETUTCDATE()
WHERE
    TRANSITION_UUID = '{F53ADDDA-E46B-4726-66D8-D7B640B66597}'
    AND [STATUS] = 'OPEN';

Podanie następującego planu wykonania:

Drugie zapytanie można wyrazić w następujący sposób:

UPDATE ToUpdate 
SET [STATUS] = 'RUNNING {36082BCD-EB52-4358-E3D3-4D96FD5B9F0F} 1360094342'
FROM
(
    SELECT TOP (1)
        hal.[STATUS]
    FROM dbo.HTT_ACTION_LOG AS hal
    WHERE
        hal.transition_uuid = '{F53ADDDA-E46B-4726-66D8-D7B640B66597}'
        AND hal.[STATUS] = 'OPEN'
    ORDER BY
        hal.ACTION_SEQ ASC
) AS ToUpdate;

Podanie tego planu wykonania:

Oba zapytania uzyskują teraz dostęp do tych samych zasobów w tej samej kolejności, jednocześnie blokując o wiele mniej wierszy po stronie odczytu planu. Silnik wykonawczy automatycznie zajmie UPDLOCKs podczas czytania nowego indeksu, podając poszukiwaną serializację.