Início / dba / Perguntas / 23467
Accepted
Sako73
Asked: 2012-08-31 11:49:54 +0800 CST

Instrução de mesclagem travando a si mesma

  • 772

Eu tenho o seguinte procedimento (SQL Server 2008 R2):

create procedure usp_SaveCompanyUserData
    @companyId bigint,
    @userId bigint,
    @dataTable tt_CoUserdata readonly
as
begin

    set nocount, xact_abort on;

    merge CompanyUser with (holdlock) as r
    using (
        select 
            @companyId as CompanyId, 
            @userId as UserId, 
            MyKey, 
            MyValue
        from @dataTable) as newData
    on r.CompanyId = newData.CompanyId
        and r.UserId = newData.UserId
        and r.MyKey = newData.MyKey
    when not matched then
        insert (CompanyId, UserId, MyKey, MyValue) values
        (@companyId, @userId, newData.MyKey, newData.MyValue);

end;

CompanyId, UserId, MyKey formam a chave composta para a tabela de destino. CompanyId é uma chave estrangeira para uma tabela pai. Além disso, há um índice não clusterizado em CompanyId asc, UserId asc.

Ele é chamado de muitos threads diferentes e estou constantemente obtendo impasses entre diferentes processos que chamam essa mesma instrução. Meu entendimento era que o "with (holdlock)" era necessário para evitar erros de condição de corrida de inserção/atualização.

Presumo que dois threads diferentes estão bloqueando linhas (ou páginas) em ordens diferentes quando estão validando as restrições e, portanto, estão em um impasse.

Será esta uma suposição correta?

Qual é a melhor maneira de resolver essa situação (ou seja, sem impasses, impacto mínimo no desempenho multithread)?

Imagem do plano de consulta (Se você visualizar a imagem em uma nova guia, ela é legível. Desculpe pelo tamanho pequeno.)

  • Há no máximo 28 linhas no @datatable.
  • Rastreei o código e não consigo ver em nenhum lugar onde iniciamos uma transação aqui.
  • A chave estrangeira está configurada para cascatear apenas na exclusão e não houve exclusões da tabela pai.
sql-server sql-server-2008-r2

4 respostas

  1. Não haveria problema se a variável de tabela mantivesse apenas um valor. Com várias linhas, há uma nova possibilidade de impasse. Suponha que dois processos simultâneos (A & B) sejam executados com variáveis ​​de tabela contendo (1, 2) e (2, 1) para a mesma empresa.

    O processo A lê o destino, não encontra nenhuma linha e insere o valor '1'. Ele contém um bloqueio de linha exclusivo no valor '1'. O processo B lê o destino, não encontra nenhuma linha e insere o valor '2'. Ele contém um bloqueio de linha exclusivo no valor '2'.

    Agora o processo A precisa processar a linha 2 e o processo B precisa processar a linha 1. Nenhum dos processos pode progredir porque requer um bloqueio incompatível com o bloqueio exclusivo mantido pelo outro processo.

    Para evitar deadlocks com várias linhas, as linhas precisam ser processadas (e as tabelas acessadas) sempre na mesma ordem . A variável da tabela no plano de execução mostrado na pergunta é um heap, portanto as linhas não têm ordem intrínseca (é bem provável que sejam lidas na ordem de inserção, embora isso não seja garantido):

    Plano existente

    A falta de ordem consistente de processamento de linha leva diretamente à oportunidade de impasse. Uma segunda consideração é que a falta de uma garantia de exclusividade de chave significa que um carretel de mesa é necessário para fornecer proteção correta para o Dia das Bruxas. O spool é um spool rápido, o que significa que todas as linhas são gravadas em uma tabela de trabalho tempdb antes de serem lidas e reproduzidas novamente para o operador Insert.

    Redefinindo a TYPEvariável da tabela para incluir um cluster PRIMARY KEY:

    DROP TYPE dbo.CoUserData;

CREATE TYPE dbo.CoUserData
AS TABLE
(
    MyKey   integer NOT NULL PRIMARY KEY CLUSTERED,
    MyValue integer NOT NULL
);

    O plano de execução agora mostra uma varredura do índice clusterizado e a garantia de exclusividade significa que o otimizador pode remover com segurança o Table Spool:

    Com chave primária

    Em testes com 5.000 iterações da MERGEinstrução em 128 threads, nenhum impasse ocorreu com a variável de tabela em cluster. Devo enfatizar que isso é apenas com base na observação; a variável de tabela agrupada também pode ( tecnicamente ) produzir suas linhas em uma variedade de ordens, mas as chances de uma ordem consistente aumentam muito. O comportamento observado precisaria ser testado novamente para cada nova atualização cumulativa, service pack ou nova versão do SQL Server, é claro.

    Caso a definição da variável da tabela não possa ser alterada, existe outra alternativa:

    MERGE dbo.CompanyUser AS R
USING 
    (SELECT DISTINCT MyKey, MyValue FROM @DataTable) AS NewData ON
    R.CompanyId = @CompanyID
    AND R.UserID = @UserID
    AND R.MyKey = NewData.MyKey
WHEN NOT MATCHED THEN 
    INSERT 
        (CompanyID, UserID, MyKey, MyValue) 
    VALUES
        (@CompanyID, @UserID, NewData.MyKey, NewData.MyValue)
OPTION (ORDER GROUP);

    Isso também elimina o spool (e a consistência da ordem de linha) ao custo de introduzir uma classificação explícita:

    Classificar plano

    Este plano também não produziu impasses usando o mesmo teste. Script de reprodução abaixo:

    CREATE TYPE dbo.CoUserData
AS TABLE
(
    MyKey   integer NOT NULL /* PRIMARY KEY */,
    MyValue integer NOT NULL
);
GO
CREATE TABLE dbo.Company
(
    CompanyID   integer NOT NULL

    CONSTRAINT PK_Company
        PRIMARY KEY (CompanyID)
);
GO
CREATE TABLE dbo.CompanyUser
(
    CompanyID   integer NOT NULL,
    UserID      integer NOT NULL,
    MyKey       integer NOT NULL,
    MyValue     integer NOT NULL

    CONSTRAINT PK_CompanyUser
        PRIMARY KEY CLUSTERED
            (CompanyID, UserID, MyKey),

    FOREIGN KEY (CompanyID)
        REFERENCES dbo.Company (CompanyID),
);
GO
CREATE NONCLUSTERED INDEX nc1
ON dbo.CompanyUser (CompanyID, UserID);
GO
INSERT dbo.Company (CompanyID) VALUES (1);
GO
DECLARE 
    @DataTable AS dbo.CoUserData,
    @CompanyID integer = 1,
    @UserID integer = 1;

INSERT @DataTable
SELECT TOP (10)
    V.MyKey,
    V.MyValue
FROM
(
    VALUES
        (1, 1),
        (2, 2),
        (3, 3),
        (4, 4),
        (5, 5),
        (6, 6),
        (7, 7),
        (8, 8),
        (9, 9)
) AS V (MyKey, MyValue)
ORDER BY NEWID();

BEGIN TRANSACTION;

    -- Test MERGE statement here

ROLLBACK TRANSACTION;
    • 35
  2. Best Answer

    OK, depois de examinar tudo algumas vezes, acho que sua suposição básica estava correta. O que provavelmente está acontecendo aqui é que:

    1. A parte MATCH do MERGE verifica o índice em busca de correspondências, bloqueando a leitura dessas linhas/páginas à medida que avança.

    2. Quando houver uma linha sem correspondência, ele tentará inserir a nova linha de índice primeiro, para solicitar um bloqueio de gravação de linha/página ...

    Mas se outro usuário também tiver chegado à etapa 1 na mesma linha/página, o primeiro usuário será bloqueado na atualização e ...

    Se o segundo usuário também precisar inserir na mesma página, ele estará em um impasse.

    AFAIK, há apenas uma maneira (simples) de ter 100% de certeza de que você não pode obter um impasse com este procedimento e isso seria adicionar uma dica TABLOCKX ao MERGE, mas isso provavelmente teria um impacto muito ruim no desempenho.

    É possível que adicionar uma dica TABLOCK seja suficiente para resolver o problema sem ter um grande efeito no seu desempenho.

    Finalmente, você também pode tentar adicionar PAGLOCK, XLOCK ou PAGLOCK e XLOCK. Novamente, isso pode funcionar e o desempenho pode não ser tão ruim. Você terá que experimentar para ver.

    • 12

  3. Acho que SQL_Kiwi forneceu uma análise muito boa. Se você precisa resolver o problema no banco de dados, siga a sugestão dele. É claro que você precisa testar novamente se ainda funciona para você sempre que atualizar, aplicar um service pack ou adicionar/alterar um índice ou uma exibição indexada.

    Existem outras três alternativas:

    1. Você pode serializar suas inserções para que não colidam: você pode invocar sp_getapplock no início de sua transação e adquirir um bloqueio exclusivo antes de executar seu MERGE. Claro que você ainda precisa fazer um teste de estresse.

    2. Você pode ter um thread lidando com todas as suas inserções, para que seu servidor de aplicativos lide com a simultaneidade.

    3. Você pode tentar novamente automaticamente após impasses - essa pode ser a abordagem mais lenta se a simultaneidade for alta.

    De qualquer forma, somente você pode determinar o impacto de sua solução no desempenho.

    Normalmente, não temos impasses em nosso sistema, embora tenhamos muito potencial para tê-los. Em 2011 erramos em uma implantação e tivemos meia dúzia de deadlocks em poucas horas, todos seguindo o mesmo cenário. Corrigi isso logo e esses foram todos os impasses do ano.

    Estamos usando principalmente a abordagem 1 em nosso sistema. Funciona muito bem para nós.

    • 8

  4. Uma outra abordagem possível - descobri que o Merge às vezes apresenta problemas de bloqueio e desempenho - pode valer a pena jogar com a opção de consulta Option (MaxDop x)

    No passado distante e obscuro, o SQL Server tinha uma opção de bloqueio de nível de linha de inserção - mas isso parece ter morrido, no entanto, um PK clusterizado com uma identidade deve fazer as inserções serem executadas sem problemas.

    • -1

relate perguntas