Início / dba / Perguntas / 187776
Accepted
i-one
Asked: 2017-10-06 08:11:48 +0800 CST

Prevenção de deadlock de MERGE

  • 772

Em um de nossos bancos de dados temos uma tabela que é acessada de forma intensiva e concorrente por várias threads. Threads atualizam ou inserem linhas via MERGE. Há também threads que excluem linhas ocasionalmente, portanto, os dados da tabela são muito voláteis. Threads que fazem upserts sofrem de deadlock algumas vezes. O problema é semelhante ao descrito nesta pergunta. A diferença, porém, é que no nosso caso cada thread atualiza ou insere exatamente uma linha .

A configuração simplificada está a seguir. A tabela é heap com dois índices não clusterizados exclusivos sobre

CREATE TABLE [Cache]
(
    [UID] uniqueidentifier NOT NULL CONSTRAINT DF_Cache_UID DEFAULT (newid()),
    [ItemKey] varchar(200) NOT NULL,
    [FileName] nvarchar(255) NOT NULL,
    [Expires] datetime2(2) NOT NULL,
    CONSTRAINT [PK_Cache] PRIMARY KEY NONCLUSTERED ([UID])
)
GO
CREATE UNIQUE INDEX IX_Cache ON [Cache] ([ItemKey]);
GO

e a consulta típica é

DECLARE
    @itemKey varchar(200) = 'Item_0F3C43A6A6A14255B2EA977EA730EDF2',
    @fileName nvarchar(255) = 'File_0F3C43A6A6A14255B2EA977EA730EDF2.dat';

MERGE INTO [Cache] WITH (HOLDLOCK) T
USING (
    VALUES (@itemKey, @fileName, dateadd(minute, 10, sysdatetime()))
) S(ItemKey, FileName, Expires)
ON T.ItemKey = S.ItemKey
WHEN MATCHED THEN
    UPDATE
    SET
        T.FileName = S.FileName,
        T.Expires = S.Expires
WHEN NOT MATCHED THEN
    INSERT (ItemKey, FileName, Expires)
    VALUES (S.ItemKey, S.FileName, S.Expires)
OUTPUT deleted.FileName;

ou seja, a correspondência acontece por chave de índice exclusiva. A dica HOLDLOCKestá aqui, por causa da simultaneidade (como recomendado aqui ).

Eu fiz uma pequena investigação e o seguinte é o que eu encontrei.

Na maioria dos casos, o plano de execução da consulta é

plano de execução de busca de índice

com o seguinte padrão de bloqueio

padrão de bloqueio de busca de índice

ou seja IX, bloqueio no objeto seguido por bloqueios mais granulares.

Às vezes, no entanto, o plano de execução da consulta é diferente

plano de execução de varredura de tabela

(esta forma de plano pode ser forçada adicionando INDEX(0)dica) e seu padrão de travamento é

padrão de bloqueio de varredura de tabela

Xbloqueio de aviso colocado no objeto depois de IXjá ter sido colocado.

Como dois IXsão compatíveis, mas dois Xnão são, o que acontece em concorrência é

impasse

gráfico de impasse

impasse !

E aqui surge a primeira parte da questão . A colocação Xde bloqueio no objeto é IXelegível? Não é bug?

A documentação informa:

Os bloqueios de intenção são denominados bloqueios de intenção porque são adquiridos antes de um bloqueio no nível inferior e, portanto, sinalizam a intenção de colocar bloqueios em um nível inferior .

e também

IX significa a intenção de atualizar apenas algumas das linhas em vez de todas elas

então, colocar o Xbloqueio no objeto depois IXparece MUITO suspeito para mim.

Primeiro, tentei evitar o bloqueio tentando adicionar dicas de bloqueio de tabela

MERGE INTO [Cache] WITH (HOLDLOCK, TABLOCK) T

e

MERGE INTO [Cache] WITH (HOLDLOCK, TABLOCKX) T

com o TABLOCKpadrão de travamento no lugar torna-se

mesclar padrão de bloqueio de tablock holdlock

e com o TABLOCKXpadrão de bloqueio é

mesclar holdlock tablockx padrão de bloqueio

como dois SIX(assim como dois X) não são compatíveis, isso evita o deadlock efetivamente, mas, infelizmente, também impede a simultaneidade (o que não é desejado).

Minhas próximas tentativas foram adicionar PAGLOCKe ROWLOCKtornar os bloqueios mais granulares e reduzir a contenção. Ambos não tem efeito (o Xobjeto ainda foi observado imediatamente após IX).

Minha tentativa final foi forçar a forma do plano de execução "boa" com um bom bloqueio granular adicionando FORCESEEKdica

MERGE INTO [Cache] WITH (HOLDLOCK, FORCESEEK(IX_Cache(ItemKey))) T

e funcionou.

E aqui surge a segunda parte da questão . Pode acontecer que FORCESEEKseja ignorado e um padrão de bloqueio ruim seja usado? (Como mencionei, PAGLOCKe ROWLOCKforam ignorados aparentemente).

Adicionar UPDLOCKnão tem efeito ( Xno objeto ainda observável depois IX).

Tornar IX_Cacheo índice agrupado, conforme previsto, funcionou. Isso levou ao planejamento com busca de índice clusterizado e bloqueio granular. Além disso, tentei forçar o Clustered Index Scan que também mostrava o bloqueio granular.

No entanto. Observação adicional. Na configuração original, mesmo com o FORCESEEK(IX_Cache(ItemKey)))local, se alguém alterar @itemKeya declaração da variável de varchar(200) para nvarchar(200) , o plano de execução se tornará

índice busca plano de execução com nvarchar

veja que seek é usado, MAS o padrão de bloqueio neste caso mostra novamente Xo bloqueio colocado no objeto após IX.

Assim, parece que a busca forçada não garante necessariamente bloqueios granulares (e, portanto, ausência de deadlocks). Não estou confiante de que o índice clusterizado garanta o bloqueio granular. Ou não?

Meu entendimento (corrija-me se estiver errado) é que o bloqueio é situacional em grande medida, e certa forma de plano de execução não implica em determinado padrão de bloqueio.

A questão sobre a elegibilidade de colocar o Xbloqueio no objeto depois IXde ainda aberto. E se for elegível, há algo que se possa fazer para evitar o bloqueio de objetos?

sql-server execution-plan

1 respostas

  1. Best Answer

    A colocação IXseguida de Xno objeto é elegível? É bug ou não?

    Parece um pouco estranho, mas é válido. No momento em que IXé tirada, a intenção pode muito bem ser levar Xfechaduras a um nível mais baixo. Não há nada que diga que tais bloqueios devam realmente ser tomados. Afinal, pode não haver nada para travar no nível inferior; o motor não pode saber disso antes do tempo. Além disso, pode haver otimizações para que bloqueios de nível inferior possam ser ignorados (um exemplo para ISe Sbloqueios pode ser visto aqui ).

    Mais especificamente para o cenário atual, é verdade que os bloqueios de intervalo de chaves serializáveis ​​não estão disponíveis para um heap, portanto, a única alternativa é um Xbloqueio no nível do objeto. Nesse sentido, o mecanismo pode detectar antecipadamente que um Xbloqueio será inevitavelmente necessário se o método de acesso for uma varredura de heap e, assim, evitar o IXbloqueio.

    Por outro lado, o bloqueio é complexo e, às vezes, os bloqueios de intenção podem ser feitos por motivos internos não necessariamente relacionados à intenção de obter bloqueios de nível inferior. A tomada IXpode ser a maneira menos invasiva de fornecer uma proteção necessária para alguns casos de borda obscuros. Para um tipo de consideração semelhante, consulte Shared Lock emitido em IsolationLevel.ReadUncommitted .

    Portanto, a situação atual é infeliz para o seu cenário de impasse e pode ser evitável em princípio, mas isso não é necessariamente o mesmo que ser um 'bug'. Você pode relatar o problema por meio de seu canal de suporte normal ou no Microsoft Connect, se precisar de uma resposta definitiva sobre isso.

    Pode acontecer que FORCESEEKseja ignorado e um padrão de bloqueio ruim seja usado?

    Não. FORCESEEKé menos uma dica e mais uma diretriz. Se o otimizador não puder encontrar um plano que honre a 'dica', ele produzirá um erro.

    Forçar o índice é uma maneira de garantir que os bloqueios de intervalo de chaves possam ser realizados. Juntamente com os bloqueios de atualização naturalmente obtidos ao processar um método de acesso para alterações de linhas, isso fornece uma garantia suficiente para evitar problemas de simultaneidade em seu cenário.

    Se o esquema da tabela não mudar (por exemplo, adicionar um novo índice), a dica também é suficiente para evitar que essa consulta fique travada consigo mesma. Ainda existe a possibilidade de um deadlock cíclico com outras consultas que podem acessar o heap antes do índice não clusterizado (como uma atualização da chave do índice não clusterizado).

    ...declaração de variável de varchar(200)a nvarchar(200)...

    Isso quebra a garantia de que uma única linha será afetada, então um Eager Table Spool é introduzido para proteção de Halloween. Como solução adicional para isso, torne a garantia explícita com MERGE TOP (1) INTO [Cache]....

    Meu entendimento é que o travamento é situacional em grande medida, e certa forma de plano de execução não implica em certo padrão de travamento.

    Certamente há muito mais acontecendo que é visível em um plano de execução. Você pode forçar uma determinada forma de plano com, por exemplo, um guia de plano, mas o mecanismo ainda pode decidir fazer bloqueios diferentes em tempo de execução. As chances são bastante baixas se você incorporar o TOP (1)elemento acima.

    Observações gerais

    É um pouco incomum ver uma tabela de heap sendo usada dessa maneira. Você deve considerar os méritos de convertê-lo em uma tabela clusterizada, talvez usando o índice que Dan Guzman sugeriu em um comentário:

    CREATE UNIQUE CLUSTERED INDEX IX_Cache ON [Cache] ([ItemKey]);

    Isso pode ter vantagens importantes de reutilização de espaço, além de fornecer uma boa solução alternativa para o problema atual de deadlock.

    MERGEtambém é um pouco incomum de se ver em um ambiente de alta simultaneidade. Um tanto contra-intuitivo, muitas vezes é mais eficiente executar instruções separadas INSERTe UPDATE, por exemplo:

    DECLARE
    @itemKey varchar(200) = 'Item_0F3C43A6A6A14255B2EA977EA730EDF2',
    @fileName nvarchar(255) = 'File_0F3C43A6A6A14255B2EA977EA730EDF2.dat';

BEGIN TRANSACTION;

    DECLARE @expires datetime2(2) = DATEADD(MINUTE, 10, SYSDATETIME());

    UPDATE TOP (1) dbo.Cache WITH (SERIALIZABLE, UPDLOCK)
    SET [FileName] = @fileName,
        Expires = @expires
    OUTPUT Deleted.[FileName]
    WHERE
        ItemKey = @itemKey;

    IF @@ROWCOUNT = 0
        INSERT dbo.Cache
            (ItemKey, [FileName], Expires)
        VALUES
            (@itemKey, @fileName, @expires);

COMMIT TRANSACTION;

    Observe como a pesquisa RID não é mais necessária:

    Plano de execução

    Se você puder garantir a existência de um índice único no ItemKey(como na pergunta) o redundante TOP (1)no UPDATEpode ser removido, dando o plano mais simples:

    Atualização simplificada

    Ambos os planos INSERTe UPDATEse qualificam para um plano trivial em ambos os casos. MERGEsempre requer otimização total baseada em custo.

    Consulte o problema de entrada simultânea do SQL Server 2014 de perguntas e respostas relacionado para o padrão correto a ser usado e mais informações sobre o MERGE.

    Os impasses nem sempre podem ser evitados. Eles podem ser reduzidos ao mínimo com codificação e design cuidadosos, mas o aplicativo deve estar sempre preparado para lidar com o impasse estranho normalmente (por exemplo, verifique novamente as condições e tente novamente).

    Se você tiver controle total sobre os processos que acessam o objeto em questão, também poderá considerar o uso de bloqueios de aplicativo para serializar o acesso a elementos individuais, conforme descrito em Inserções e exclusões simultâneas do SQL Server .

    • 14

relate perguntas