Início / dba / Perguntas / 185897
Accepted
JNK
Asked: 2017-09-14 12:49:55 +0800 CST

Registros duplicados retornados da tabela sem duplicatas

  • 772

Eu tenho um procedimento armazenado que consulta uma tabela de filas ocupadas que é usada para distribuir o trabalho em nosso sistema. A tabela em questão tem uma chave primária no WorkID e não há duplicatas.

Uma versão simplificada da consulta é:

INSERT INTO #TempWorkIDs (WorkID)
SELECT
        W.WorkID

    FROM
        dbo.WorkTable W

    WHERE
        (@bool_param = 0 AND
        ((W.InProgress = 0
         AND ISNULL(W.UserID, -1) != @userid_param
         AND (@bool_filtered = 0
              OR W.TypeID IN (SELECT TypeID FROM #Types AS t)))
         OR 
         (@bool_param = 1
          AND W.InProgress = 1
          AND W.UserID != @userid_param)
        OR
        (@Auto_Param = 0
         AND W.UserID = @userid_param)))
         OR
         (@bool_param = 1 AND W.UserID = @userid_param)
    OPTION
        (RECOMPILE)

A #Typestabela é preenchida anteriormente no procedimento.

Como eu disse, WorkTableestá ocupado e, às vezes, enquanto essa consulta está em execução, SUSPEITO que um dos registros está se movendo de um conjunto de filtros WHEREpara outro. Especificamente, isso acontece quando alguém começa a trabalhar em um item e W.InProgressmuda de 0 para 1. Quando isso acontece, recebo uma violação de chave duplicada quando tento adicionar uma chave primária à tabela temporária na qual essa consulta está sendo inserida.

Confirmei no plano de consulta gerado quando ocorre o erro que não há paralelismo, o nível de isolamento é READ COMMITTED, e não há registros duplicados na tabela de origem. Você também pode ver que não há JOINs ou outra maneira de obter produtos cartesianos aqui.

Este é o plano de consulta anônimo:

insira a descrição da imagem aqui

A questão é: o que está causando as duplicatas e como posso fazê-lo parar?

Acho que READ COMMITTEDdeve funcionar aqui, preciso travar. Tenho quase certeza de que os enganos ocorrem quando o InProgressbit em um registro muda enquanto estou consultando. Eu sei disso porque a tabela armazena a hora dessa alteração e está dentro de milissegundos de quando eu consulto e recebo o erro.

sql-server sql-server-2008-r2

1 respostas

  1. Best Answer

    Existem alguns cenários complicados que podem resultar na mesma linha sendo lida duas vezes de um índice, mesmo sob o READ COMMITTEDnível de isolamento .

    Sua consulta não se qualifica para uma verificação de ordem de alocação, portanto, o mecanismo de armazenamento lerá os dados da tabela na ordem da chave clusterizada.

    Para sua tabela, você tem InProgresscomo a primeira coluna da chave clusterizada. É provável que você esteja obtendo bloqueios de linha ou de página ao varrer a tabela. Se você ler uma linha perto do início da verificação, libere o bloqueio nela, essa linha é atualizada de forma que InProgressmuda de 0 para 1 e, em seguida, a linha é lida novamente em uma página diferente, então você pode ver WorkIDvalores duplicados de sua consulta .

    Existem muitas soluções alternativas. Você pode inserir em um heap e simplesmente remover valores duplicados. Você pode adicionar um DISTINCTà consulta. Você também pode habilitar um nível de isolamento de controle de versão de linha, para fornecer uma visão estável do estado confirmado do banco de dados, seja no início da transação ( isolamento de instantâneo ) ou no início da instrução ( leia isolamento de instantâneo confirmado ).

    Talvez seja apropriado adicionar dicas de bloqueio ou alterar a estrutura da tabela. Para uma solução bastante divertida (provavelmente não apropriada para produção), você pode tentar ler o índice de trás para frente. Isso pode ser feito com um supérfluo TOPjunto com um ORDER BY. Abaixo está uma demonstração muito simples para ilustrar o ponto:

    CREATE TABLE #WorkTable (
    InProgress TINYINT NOT NULL,
    WorkID INT NOT NULL
    , PRIMARY KEY (InProgress, WorkID)
);

INSERT INTO #WorkTable WITH (TABLOCK)
SELECT (RN - 1) / 5000, RN
FROM
(
    SELECT TOP (10000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) RN
    FROM master..spt_values t1
    CROSS JOIN master..spt_values t2
) t
OPTION (MAXDOP 1);

    A consulta a seguir tem a propriedade Ordered:false, mas ainda lerá os dados em ordem de chave clusterizada:

    SELECT WorkId
FROM #WorkTable;

    No entanto, a consulta a seguir lerá os dados na ordem de cluster reversa:

    SELECT TOP (9223372036854775807) WorkId
FROM #WorkTable
ORDER BY InProgress DESC, WorkId DESC;

    Podemos ver isso observando as propriedades de varredura:

    varredura para trás

    Para sua tabela, isso significa que, se uma linha for atualizada de forma que InProgressmude de 0 para 1, será muito menos provável que ela apareça duas vezes. Pode não aparecer, o que pode ser um problema diferente.

    • 9

relate perguntas