Início / dba / Perguntas / 295462
Accepted
IronicMuffin
Asked: 2021-07-10 06:32:24 +0800 CST

Resultados de atualização inesperados no heap usando SET @Variable = Field= @Variable + 1, corrigido com índice clusterizado

  • 772

Estou apenas procurando entender por que isso está acontecendo, e minhas pesquisas no Google estavam falhando. Estamos no SQL Server 2016 SP1.

Esta é a situação: Tabela de fornecedores que gerencia os IDs acompanhando os valores atuais de cada tabela. Uma função pode ser chamada para retornar um bloco de IDs se você estiver fazendo uma inserção.

Então, configuramos uma tabela temporária selecionando da tabela real usando select into(estamos clonando um conjunto de dados para ser reinserido com um conjunto de propriedades diferente).

Em seguida, chamamos a função e obtemos novos ids para o número de registros (ela apenas retorna o ID máximo, então fazemos algumas contas para obter o próximo id).

Em seguida, atualizamos a tabela como tal:

update #temp set @nextId = Id = @nextId + 1

com a expectativa de que ele incremente em um para cada registro e defina os ids.

Em vez disso, o mesmo ID foi definido para cada 4 registros, então seria incrementado e os próximos 4 obteriam o próximo id, etc. Por que a cada 4 registros? O que deu errado?

resultados

Ainda mais divertido, se colocarmos um índice clusterizado na tabela, tudo funciona corretamente.

Tenho certeza que tem a ver com a mesa ser um monte... mas não sei por quê.

Link para o plano .

sql-server update

2 respostas

  1. Best Answer

    A documentação para a UPDATEdeclaração diz (ênfase adicionada):

    Nomes de variáveis ​​podem ser usados ​​em instruções UPDATE para mostrar os valores antigos e novos afetados, mas isso deve ser usado somente quando a instrução UPDATE afeta um único registro .

    Algumas pessoas tentaram fazer com que essa técnica de " atualização peculiar " funcionasse de maneira confiável para várias linhas, aplicando restrições cada vez maiores ao seu uso. O fato é que isso depende de efeitos observados e comportamento não documentado, então você não deve esperar que funcione em geral ou continue 'funcionando' no futuro.

    Não posso dizer exatamente o que deu 'errado' no seu caso, sem um script de reprodução ou um plano de execução, mas no final das contas isso realmente não importa. Se forçado a adivinhar, eu diria que sua atualização foi executada em DOP 4 e quatro threads leram o mesmo valor de variável simultaneamente.

    Você seria melhor aconselhado a usar uma solução confiável, como ROW_NUMBER( docs ), a IDENTITY função ou uma sequência .

    • 14

  2. Conforme mencionado por @PaulWhite em sua resposta , o método "atualização peculiar" não está documentado e pode não ser confiável. Requer codificação cuidadosa e verificação do plano de execução para garantir que tudo funcione corretamente.

    E você pode ver claramente, a partir do plano de execução que você forneceu agora, que ele estava certo: existem quatro tópicos :

    <QueryPlan DegreeOfParallelism="4"

    Este método pode ser extremamente complicado para funcionar corretamente. Pequenas mudanças nos planos de execução podem arruiná-lo completamente.

    Algumas das muitas regras não documentadas mencionadas por @JeffModen em um artigo bem pesquisado

    • MAXDOP 1dica para desligar o paralelismo
    • Um índice clusterizado
    • Deve usar uma coluna âncora
    • Sem junções

    É por isso que muitos desaconselham isso, e dado que tantos métodos para alcançar a mesma coisa estão agora disponíveis, parece inútil.

    Em sua situação particular, uma IDENTITYcoluna provavelmente seria a mais simples. Mas aqui está o ROW_NUMBERmétodo que também garante resultados corretos. Você pode colocá-lo em um CTE e atualizar o CTE diretamente.

    Observo que seus IDs não estão ordenados, então usei ORDER BY (SELECT 1), você pode usar um pedido diferente, se quiser.

    WITH cte AS (
    SELECT *,
        rn = ROW_NUMBER() OVER (ORDER BY (SELECT 1))
    FROM #temp t
)
UPDATE cte
SET Id = rn;
    • 3

relate perguntas