Início / dba / Perguntas / 34199
Accepted
Cade Roux
Asked: 2013-02-06 19:32:14 +0800 CST

SQL Server Deadlock em duas atualizações devido à ordem de bloqueio do índice

  • 772

Eu tenho dois UPDATEs - um bloqueia o CI primeiro e depois o NCI (no status) porque a coluna de status também está sendo atualizada. O outro já possui um bloqueio U no NCI porque sabe que está mudando e então tenta obter um bloqueio U no CI.

Qual é a maneira mais fácil de forçá-los a serializar? Parece estranho usar uma dica no nível TABLE, pois esse é um problema de indexação interna - há apenas uma tabela envolvida - UPDLOCK, HOLDLOCK se aplicará automaticamente apenas a todos os índices necessários nessa tabela e, assim, forçará sua serialização?

Aqui estão as consultas:

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

Aquele X bloqueia a linha no CI (na coluna CREATED) e então tenta bloquear X no NCI que inclui a coluna de status.

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)

Este U bloqueia o mesmo NCI - para a consulta aninhada, eu acho, então bloqueia o CI para a atualização.

Assim, a ordem produz o impasse.

A solução mais fácil é forçar o bloqueio completo das duas consultas - ou seja, serializar. Qual é a maneira mais fácil de forçar isso, basta colocar WITH (UPDLOCK, HOLDLOCK)as referências à tabela (uma na primeira e duas na segunda)?

DDL:

Observe que o cliente tem mais índices nesta tabela que devem ser afetados por esta atualização, mas não são mencionados no gráfico de impasse.

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]
sql-server deadlock

1 respostas

  1. Best Answer

    O índice ideal para essas duas consultas não está longe da definição existente do IK_HTT_ACTION_LOG_1índice (adicione ACTION_UUIDcomo um INCLUDEao índice aprimorado abaixo):

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

    A primeira consulta é:

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

    Dando o seguinte plano de execução:

    Atualização 1

    A segunda consulta pode ser expressa desta forma:

    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;

    Dando este plano de execução:

    Atualização 2

    Ambas as consultas agora acessam os mesmos recursos na mesma ordem, enquanto bloqueiam muito menos linhas no lado de leitura do plano. O mecanismo de execução levará UPDLOCKs automaticamente ao ler o novo índice, fornecendo a serialização que você está procurando.

    • 13

relate perguntas