Nie można wstawić zduplikowanego wiersza klucza do indeksu nieunikalnego?

W ciągu ostatnich kilku dni napotkaliśmy ten dziwny błąd trzy razy, po tym, jak przez 8 tygodni nie miałem błędów, i jestem zakłopotany.

Oto komunikat o błędzie:

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).".

Indeks, który mamy, nie jest unikalny. Jeśli zauważysz, zduplikowana wartość klucza w komunikacie o błędzie nawet nie pokrywa się z indeksem. Dziwne jest to, że jeśli uruchomię ponownie proces, to się powiedzie.

To jest najnowszy link, który mogę znaleźć, który ma moje problemy, ale nie widzę rozwiązania.

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

Kilka rzeczy o moim scenariuszu:

• Proc aktualizuje TransactionID (część klucza podstawowego) - myślę, że to właśnie powoduje błąd, ale nie wiem dlaczego? Usuniemy tę logikę.
• Śledzenie zmian jest włączone w tabeli
• Czytanie transakcji jest niezaangażowane

Dla każdej tabeli jest 45 pól, głównie wymieniłem te używane w indeksach. Aktualizuję TransactionID (klucz klastrowany) w instrukcji aktualizacji (niepotrzebnie). Dziwne, że przez ostatnie miesiące nie mieliśmy żadnych problemów. I dzieje się to sporadycznie za pośrednictwem SSIS.

Stół

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

stół tymczasowy

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

Klucz podstawowy

 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]

Indeks nieklastrowany

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)

przykładowa instrukcja aktualizacji

-- 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)

Moje pytanie brzmi: co się dzieje pod maską? A jakie jest rozwiązanie? Dla odniesienia powyższy link wspomina o tym:

W tym momencie mam kilka teorii:

• Błąd związany z presją pamięci lub dużym równoległym planem aktualizacji, ale oczekiwałbym innego rodzaju błędu i jak dotąd nie mogę skorelować niskich zasobów, będzie określał ramy czasowe tych izolowanych i sporadycznych błędów.
• Błąd w instrukcji UPDATE lub danych powoduje faktyczne naruszenie duplikatu klucza podstawowego, ale skutkuje niejasnym błędem programu SQL Server i pojawia się komunikat o błędzie, który cytuje niewłaściwą nazwę indeksu.
• Brudne odczyty wynikające z odczytu niezaangażowanej izolacji powodującej dużą równoległą aktualizację podwójnego wstawiania. Jednak programiści ETL twierdzą, że użyto domyślnego odczytu, i trudno jest dokładnie określić, jaki poziom izolacji jest faktycznie używany w czasie wykonywania.

Podejrzewam, że jeśli poprawię plan wykonania jako obejście, być może podpowiedź MAXDOP (1) lub użyję flagi śledzenia sesji do wyłączenia operacji buforowania, błąd po prostu zniknie, ale nie jest jasne, jak wpłynie to na wydajność

Wersja

Microsoft SQL Server 2017 (RTM-CU13) (KB4466404) - 14.0.3048.4 (X64) 30 listopada 2018 12:57:58 Copyright (C) 2017 Microsoft Corporation Enterprise Edition (64-bit) w systemie Windows Server 2016 Standard 10.0 (kompilacja 14393 :)

Moje pytanie brzmi: co się dzieje pod maską? A jakie jest rozwiązanie?

To jest błąd. Problem polega na tym, że zdarza się to tylko sporadycznie i będzie trudne do odtworzenia. Nadal najlepszą szansą jest zaangażowanie wsparcia Microsoft. Przetwarzanie aktualizacji jest niezwykle skomplikowane, dlatego będzie wymagało bardzo szczegółowego badania.

Na przykład tego rodzaju złożoność, spójrz na moje posty MERGE Bug z filtrowanymi indeksami i niepoprawne wyniki z indeksowanymi widokami . Żadne z nich nie odnosi się bezpośrednio do twojego problemu, ale nadają smaku.

Napisz aktualizację deterministyczną

To wszystko oczywiście dość ogólne. Być może bardziej przydatne, mogę powiedzieć, że powinieneś spróbować przepisać swoje obecne UPDATEoświadczenie. Jak mówi dokumentacja :

Przy określaniu klauzuli FROM należy zachować ostrożność, aby podać kryteria operacji aktualizacji. Wyniki instrukcji UPDATE są niezdefiniowane, jeśli instrukcja zawiera klauzulę FROM, która nie została określona w taki sposób, że dla każdego aktualizowanego wystąpienia kolumny dostępna jest tylko jedna wartość, to znaczy, jeśli instrukcja UPDATE nie jest deterministyczna.

Twój nieUPDATE jest deterministyczny , a zatem wyniki są niezdefiniowane . Powinieneś to zmienić, aby dla każdego wiersza docelowego został zidentyfikowany najwyżej jeden wiersz źródłowy. Bez tej zmiany wynik aktualizacji może nie odzwierciedlać żadnego wiersza źródłowego.

Przykład

Pokażę wam przykład, używając tabel luźno modelowanych na podstawie podanych w pytaniu:

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)
);

Dla uproszczenia umieść jeden wiersz w tabeli docelowej, a cztery wiersze w źródle:

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');

Wszystkie cztery wiersze źródłowe pasują do celu TransactionID, więc który z nich zostanie użyty, jeśli uruchomimy aktualizację (taką jak w pytaniu), która łączy się TransactionIDsama?

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;

(Aktualizacja TransactionIDkolumny nie jest ważna w wersji demo, możesz ją skomentować, jeśli chcesz).

Pierwszą niespodzianką jest to, że UPDATEkończy się bezbłędnie, pomimo że tabela docelowa nie dopuszcza wartości null w żadnej kolumnie (wszystkie wiersze kandydujące zawierają wartość null).

Ważne jest to, że wynik jest niezdefiniowany , aw tym przypadku daje wynik, który nie pasuje do żadnego z wierszy źródłowych:

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 <> demo skrzypiec

Więcej informacji: KAŻDY agregat jest zepsuty

Aktualizacja powinna być napisana w taki sposób, aby zakończyła się sukcesem, gdyby została napisana jako równoważna MERGEinstrukcja, która sprawdza próby aktualizacji tego samego wiersza docelowego więcej niż jeden raz. Zasadniczo nie polecam używania MERGEbezpośredniego, ponieważ było tak wiele błędów implementacyjnych i zwykle ma gorszą wydajność.

Jako bonus, może się okazać, że przepisanie bieżącej aktualizacji, aby było deterministyczne, spowoduje, że sporadyczne problemy z błędami również znikną. Błąd produktu będzie nadal istniał dla osób, które piszą aktualizacje, które nie są determinujące, oczywiście.