Início / dba / Perguntas / 240822
Accepted
Gabe
Asked: 2019-06-19 06:54:13 +0800 CST

Não é possível inserir uma linha de chave duplicada em um índice não exclusivo?

  • 772

Encontramos esse erro estranho três vezes nos últimos dias, depois de estar livre de erros por 8 semanas, e estou perplexo.

Esta é a mensagem de erro:

Executing the query "EXEC dbo.MergeTransactions" failed with the following error:
"Cannot insert duplicate key row in object 'sales.Transactions' with unique index
'NCI_Transactions_ClientID_TransactionDate'.
The duplicate key value is (1001, 2018-12-14 19:16:29.00, 304050920).".

O índice que temos não é único. Se você notar, o valor da chave duplicada na mensagem de erro nem sequer se alinha com o índice. O estranho é que se eu reexecutar o proc, ele é bem-sucedido.

Este é o link mais recente que encontrei com meus problemas, mas não vejo uma solução.

https://www.sqlservercentral.com/forums/topic/error-cannot-insert-duplicate-key-row-in-a-non-unique-index

Algumas coisas sobre o meu cenário:

  • O proc está atualizando o TransactionID (parte da chave primária) - acho que é isso que está causando o erro, mas não sei por quê? Nós estaremos removendo essa lógica.
  • O rastreamento de alterações está ativado na tabela
  • Fazendo a leitura da transação não confirmada

São 45 campos para cada tabela, listei principalmente os usados ​​nos índices. Estou atualizando o TransactionID (chave clusterizada) na instrução de atualização (desnecessariamente). Estranho que não tivemos nenhum problema por meses até a semana passada. E isso só está acontecendo esporadicamente via SSIS.

Mesa

USE [DB]
GO

/****** Object:  Table [sales].[Transactions]    Script Date: 5/29/2019 1:37:49 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[sales].[Transactions]') AND type in (N'U'))
BEGIN
CREATE TABLE [sales].[Transactions]
(
    [TransactionID] [bigint] NOT NULL,
    [ClientID] [int] NOT NULL,
    [TransactionDate] [datetime2](2) NOT NULL,
    /* snip*/
    [BusinessUserID] [varchar](150) NOT NULL,
    [BusinessTransactionID] [varchar](150) NOT NULL,
    [InsertDate] [datetime2](2) NOT NULL,
    [UpdateDate] [datetime2](2) NOT NULL,
 CONSTRAINT [PK_Transactions_TransactionID] PRIMARY KEY CLUSTERED 
(
    [TransactionID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, DATA_COMPRESSION=PAGE) ON [DB_Data]
) ON [DB_Data]
END
GO
USE [DB]

IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[sales].[Transactions]') AND name = N'NCI_Transactions_ClientID_TransactionDate')
begin
CREATE NONCLUSTERED INDEX [NCI_Transactions_ClientID_TransactionDate] ON [sales].[Transactions]
(
    [ClientID] ASC,
    [TransactionDate] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, DATA_COMPRESSION = PAGE) ON [DB_Data]
END

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[sales].[DF_Transactions_Units]') AND type = 'D')
BEGIN
ALTER TABLE [sales].[Transactions] ADD  CONSTRAINT [DF_Transactions_Units]  DEFAULT ((0)) FOR [Units]
END
GO

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[sales].[DF_Transactions_ISOCurrencyCode]') AND type = 'D')
BEGIN
ALTER TABLE [sales].[Transactions] ADD  CONSTRAINT [DF_Transactions_ISOCurrencyCode]  DEFAULT ('USD') FOR [ISOCurrencyCode]
END
GO

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[sales].[DF_Transactions_InsertDate]') AND type = 'D')
BEGIN
ALTER TABLE [sales].[Transactions] ADD  CONSTRAINT [DF_Transactions_InsertDate]  DEFAULT (sysdatetime()) FOR [InsertDate]
END
GO

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[sales].[DF_Transactions_UpdateDate]') AND type = 'D')
BEGIN
ALTER TABLE [sales].[Transactions] ADD  CONSTRAINT [DF_Transactions_UpdateDate]  DEFAULT (sysdatetime()) FOR [UpdateDate]
END
GO

mesa temporária

same columns as the mgdata. including the relevant fields. Also has a non-unique clustered index
(
    [BusinessTransactionID] [varchar](150) NULL,
    [BusinessUserID] [varchar](150) NULL,
    [PostalCode] [varchar](25) NULL,
    [TransactionDate] [datetime2](2) NULL,

    [Units] [int] NOT NULL,
    [StartDate] [datetime2](2) NULL,
    [EndDate] [datetime2](2) NULL,
    [TransactionID] [bigint] NULL,
    [ClientID] [int] NULL,

) 

CREATE CLUSTERED INDEX ##workingTransactionsMG_idx ON #workingTransactions (TransactionID)

It is populated in batches (500k rows at a time), something like this
IF OBJECT_ID(N'tempdb.dbo.#workingTransactions') IS NOT NULL DROP TABLE #workingTransactions;
select fields 
into #workingTransactions
from import.Transactions
where importrowid between two number ranges -- pseudocode

Chave primária

 CONSTRAINT [PK_Transactions_TransactionID] PRIMARY KEY CLUSTERED 
(
    [TransactionID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, DATA_COMPRESSION=PAGE) ON [Data]
) ON [Data]

Índice não agrupado

CREATE NONCLUSTERED INDEX [NCI_Transactions_ClientID_TransactionDate] ON [sales].[Transactions]
(
    [ClientID] ASC,
    [TransactionDate] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, DATA_COMPRESSION = PAGE)

declaração de atualização de exemplo

-- updates every field
update t 
set 
    t.transactionid = s.transactionid,
    t.[CityCode]=s.[CityCode],
      t.TransactionDate=s.[TransactionDate],
     t.[ClientID]=s.[ClientID],
                t.[PackageMonths] = s.[PackageMonths],
                t.UpdateDate = @UpdateDate
              FROM #workingTransactions s
              JOIN [DB].[sales].[Transactions] t 
              ON s.[TransactionID] = t.[TransactionID]
             WHERE CAST(HASHBYTES('SHA2_256 ',CONCAT( S.[BusinessTransactionID],'|',S.[BusinessUserID],'|', etc)
                <> CAST(HASHBYTES('SHA2_256 ',CONCAT( T.[BusinessTransactionID],'|',T.[BusinessUserID],'|', etc)

Minha pergunta é, o que está acontecendo sob o capô? E qual é a solução? Para referência, o link acima menciona isso:

Neste ponto, tenho algumas teorias:

  • Bug relacionado à pressão de memória ou grande plano de atualização paralela, mas eu esperaria um tipo diferente de erro e até agora não consigo correlacionar os baixos recursos ao prazo desses erros isolados e esporádicos.
  • Um bug na instrução UPDATE ou nos dados está causando uma violação duplicada real na chave primária, mas algum bug obscuro do SQL Server está resultando em uma mensagem de erro que cita o nome do índice incorreto.
  • Leituras sujas resultantes do isolamento de leitura não confirmada, causando uma grande atualização paralela para inserção dupla. Mas os desenvolvedores de ETL afirmam que a leitura padrão confirmada é usada e é difícil determinar exatamente em qual nível de isolamento o processo é realmente usado em tempo de execução.

Suspeito que, se eu ajustar o plano de execução como uma solução alternativa, talvez a dica MAXDOP (1) ou usando o sinalizador de rastreamento de sessão para desabilitar a operação de spool, o erro simplesmente desaparecerá, mas não está claro como isso afetaria o desempenho

Versão

Microsoft SQL Server 2017 (RTM-CU13) (KB4466404) - 14.0.3048.4 (X64) 30 de novembro de 2018 12:57:58 Copyright (C) 2017 Microsoft Corporation Enterprise Edition (64 bits) no Windows Server 2016 Standard 10.0 (Build 14393) : )

sql-server update

1 respostas

  1. Best Answer

    Minha pergunta é, o que está acontecendo sob o capô? E qual é a solução?

    É um erro. O problema é que isso só acontece ocasionalmente e será difícil de reproduzir. Ainda assim, sua melhor chance é envolver o suporte da Microsoft. O processamento de atualização é incrivelmente complexo, portanto, isso exigirá uma investigação muito detalhada.

    Para um exemplo do tipo de complexidade envolvida, dê uma olhada em minhas postagens MERGE Bug with Filtered Indexes and Incorrect Results with Indexed Views . Nenhum deles se relaciona diretamente ao seu problema, mas dão um sabor.

    Escreva uma atualização determinística

    Isso é tudo bastante genérico, é claro. Talvez mais útil, eu possa dizer que você deveria tentar reescrever sua UPDATEdeclaração atual. Como diz a documentação :

    Tenha cuidado ao especificar a cláusula FROM para fornecer os critérios para a operação de atualização. Os resultados de uma instrução UPDATE são indefinidos se a instrução incluir uma cláusula FROM não especificada de forma que apenas um valor esteja disponível para cada ocorrência de coluna atualizada, ou seja, se a instrução UPDATE não for determinística.

    Seu não UPDATEé determinista , e os resultados são, portanto, indefinidos . Você deve alterá-lo para que no máximo uma linha de origem seja identificada para cada linha de destino. Sem essa alteração, o resultado da atualização pode não refletir nenhuma linha de origem individual.

    Exemplo

    Deixe-me mostrar um exemplo, usando tabelas vagamente modeladas nas fornecidas na pergunta:

    CREATE TABLE dbo.Transactions
(
    TransactionID bigint NOT NULL,
    ClientID integer NOT NULL,
    TransactionDate datetime2(2) NOT NULL,

    CONSTRAINT PK_dbo_Transactions
        PRIMARY KEY CLUSTERED (TransactionID),

    INDEX dbo_Transactions_ClientID_TranDate
        (ClientID, TransactionDate)
);

CREATE TABLE #Working
(
    TransactionID bigint NULL,
    ClientID integer NULL,
    TransactionDate datetime2(2) NULL,

    INDEX cx CLUSTERED (TransactionID)
);

    Para manter as coisas simples, coloque uma linha na tabela de destino e quatro linhas na origem:

    INSERT dbo.Transactions 
    (TransactionID, ClientID, TransactionDate)
VALUES 
    (1, 1, '2019-01-01');

INSERT #Working 
    (TransactionID, ClientID, TransactionDate)
VALUES 
    (1, 2, NULL),
    (1, NULL, '2019-03-03'),
    (1, 3, NULL),
    (1, NULL, '2019-02-02');

    Todas as quatro linhas de origem correspondem ao destino em TransactionID, então qual será usada se executarmos uma atualização (como a da pergunta) que se une TransactionIDsozinha?

    UPDATE T
SET T.TransactionID = W.TransactionID,
    T.ClientID = W.ClientID,
    T.TransactionDate = W.TransactionDate
FROM #Working AS W
JOIN dbo.Transactions AS T
    ON T.TransactionID = W.TransactionID;

    (Atualizar a TransactionIDcoluna não é importante para a demonstração, você pode comentar se quiser.)

    A primeira surpresa é que o UPDATEcompleta sem erro, apesar da tabela de destino não permitir nulos em nenhuma coluna (todas as linhas candidatas contêm um nulo).

    O ponto importante é que o resultado é undefined e, neste caso, produz um resultado que não corresponde a nenhuma das linhas de origem:

    SELECT
    T.TransactionID,
    T.ClientID,
    T.TransactionDate
FROM dbo.Transactions AS T;

    ╔═══════════════╦══════════╦════════════════════════╗
║ TransactionID ║ ClientID ║    TransactionDate     ║
╠═══════════════╬══════════╬════════════════════════╣
║             1 ║        2 ║ 2019-03-03 00:00:00.00 ║
╚═══════════════╩══════════╩════════════════════════╝

    db<>demonstração de violino

    Mais detalhes: O ANY Aggregate está quebrado

    A atualização deve ser escrita de forma que seja bem-sucedida se escrita como a MERGEinstrução equivalente, que verifica as tentativas de atualizar a mesma linha de destino mais de uma vez. Eu geralmente não recomendo usar MERGEdiretamente, porque tem sido sujeito a muitos bugs de implementação, e normalmente tem um desempenho pior.

    Como bônus, você pode descobrir que reescrever sua atualização atual para ser determinista resultará em seu problema de bug ocasional também desaparecendo. O bug do produto ainda existirá para pessoas que escrevem atualizações não determinísticas, é claro.

    • 10

relate perguntas