MERGE zamiast TRUNCATE: kopia kartoteki nie opróżnia się, gdy źródło zwróci zero wierszy

Autor: Adam Siemiątkowski, SoftwareStudio Group Sp. z o.o.

Gdy lokalną kopię kartoteki odświeżasz instrukcją MERGE zamiast TRUNCATE, kopia nie zostaje pusta, jeśli źródło zawiedzie. Programista albo administrator bazy czyta źródło jednym zapytaniem do tabeli pośredniej, sprawdza, czy zwróciło jakiekolwiek wiersze, i dopiero wtedy zmienia kopię jedną instrukcją w transakcji. Opróżnienie kopii przed wczytaniem źródła zostawia pustą tabelę, gdy źródło zawiedzie albo zwróci zero wierszy. Odczyt do tabeli pośredniej ze strażnikiem pustego zbioru oraz jeden MERGE zostawiają poprzedni stan w obu tych przypadkach, ale nie wykrywają odczytu niepełnego.

Zero wierszy ze źródła oznacza zatrzymanie odświeżenia, a nie opróżnienie kopii, chyba że zero jest dozwolonym wynikiem. W przykładzie strażnik kończy procedurę błędem, a kopia zostaje bez zmian. Gdy źródło zwraca wiersze, jeden MERGE w transakcji uzgadnia kopię ze źródłem. Strażnik wykrywa wyłącznie pusty zbiór.

W opisywanym wdrożeniu źródłem kartoteki jest zewnętrzny program ERP, a lokalna kopia powstaje z widoku opartego na zapytaniu do serwera połączonego. Pokazany wzorzec da się przenieść do innej bazy i nie jest kodem tego wdrożenia. Skąd kopia bierze dane, opisujemy w artykułach o imporcie zamówień z widoku programu ERP i o drugiej bazie obok bazy produktu.

Opróżnienie kopii przed odczytem zostawia pustą tabelę, gdy źródło zawiedzie

Wariant „opróżnij i wstaw” najpierw usuwa wiersze kopii, a dopiero potem czyta źródło. Pokazujemy go wyłącznie jako wariant do uniknięcia, bo opróżnienie następuje, zanim wiadomo, co zwróci źródło.

• Źródło zwraca zero wierszy. W teście wariant kończy się bez błędu i zostawia w kopii zero wierszy.
• Źródło jest niedostępne. Wariant kończy się błędem 208 i także zostawia zero wierszy.

Niedostępność źródła odtworzono w teście przez chwilową zmianę nazwy obiektu źródłowego.

Przykład (dane wymyślone). Lokalna kartoteka ma pięć pozycji. Nocne odświeżenie czyta źródło, które z powodu przerwy w łączu zwraca zero wierszy. Wariant z opróżnieniem tabeli kończy się bez błędu, a kopia ma zero pozycji, więc kolejne ekrany nie znajdują żadnej pozycji.

Wariant ze strażnikiem kończy się błędem 50001 z czytelnym komunikatem, a kopia nadal ma pięć pozycji. W rejestrze wpisów kontrolnych nie przybywa wiersza po odrzuconym odświeżeniu. Po przywróceniu źródła następne odświeżenie przebiega bez śladów po przerwie.

Odczyt źródła do tabeli pośredniej oddziela odczyt od zmiany kopii

Założenia przykładu. Pierwszy z dwóch bloków kodu tworzy trzy tabele z danymi początkowymi: źródło z kluczem indeks i trzema kolumnami opisowymi, w tym nazwa i zapas_min, kopię o tych samych kolumnach z kolumną wyłączenia aktywna oraz tabelę wpisów kontrolnych z liczbą zmian. Stoi w nim też procedura wariantu „opróżnij i wstaw”, która służy wyłącznie jako kontrast w teście i nie jest rozwiązaniem. Drugi blok zawiera procedurę odświeżenia i uruchamia się go po pierwszym. Test wykonano na jednej instancji, na poziomach zgodności 130 i 160; starszych wersji silnika nie testowano.

Instrukcja CREATE OR ALTER wymaga wersji silnika z poziomem zgodności 130 i z dodatkiem Service Pack 1 albo nowszej, a MERGE i THROW są starsze. Procedura odświeżenia ma cztery kroki: odczyt do tabeli pośredniej, strażnik pustego zbioru, MERGE w transakcji oraz wpis kontrolny. Poniżej oba bloki w kolejności uruchamiania. Blok pierwszy zawiera separator GO, który jest separatorem narzędzia, a nie instrukcją T-SQL.

-- tabele pomocnicze. Zewnętrzne źródło jest symulowane tabelą lokalną (bez serwera połączonego).
SET NOCOUNT ON;
IF OBJECT_ID(N'dbo.odswiezenie_przyklad') IS NOT NULL DROP TABLE dbo.odswiezenie_przyklad;
IF OBJECT_ID(N'dbo.kartoteka_przyklad') IS NOT NULL DROP TABLE dbo.kartoteka_przyklad;
IF OBJECT_ID(N'dbo.zrodlo_przyklad') IS NOT NULL DROP TABLE dbo.zrodlo_przyklad;
GO
CREATE TABLE dbo.zrodlo_przyklad (
    indeks    varchar(20)   NOT NULL PRIMARY KEY,
    nazwa     nvarchar(100) NULL,
    jednostka varchar(5)    NULL,
    zapas_min decimal(15,3) NULL
);
CREATE TABLE dbo.kartoteka_przyklad (
    indeks    varchar(20)   NOT NULL PRIMARY KEY,
    nazwa     nvarchar(100) NULL,
    jednostka varchar(5)    NULL,
    zapas_min decimal(15,3) NULL CONSTRAINT ck_zapas_min CHECK (zapas_min >= 0),
    aktywna   bit           NOT NULL DEFAULT 1
);
CREATE TABLE dbo.odswiezenie_przyklad (
    id    int IDENTITY(1,1) PRIMARY KEY,
    kiedy datetime2(0) NOT NULL DEFAULT SYSDATETIME(),
    zmian int          NOT NULL
);
INSERT dbo.zrodlo_przyklad (indeks, nazwa, jednostka, zapas_min) VALUES
    ('A1', N'Pozycja pierwsza', 'szt', 10),
    ('A2', N'Pozycja druga',    'szt', 20),
    ('A3', N'Pozycja trzecia',  'szt', NULL),
    ('A4', N'Pozycja czwarta',  'kpl', 5),
    ('A5', N'Pozycja piąta',    'szt', 0);
GO
-- wariant NAIWNY, tylko jako kontrast w teście (nie jest rozwiązaniem)
CREATE OR ALTER PROCEDURE dbo.odswiez_kopie_naiwna_przyklad
AS
BEGIN
    TRUNCATE TABLE dbo.kartoteka_przyklad;
    INSERT dbo.kartoteka_przyklad (indeks, nazwa, jednostka, zapas_min)
    SELECT indeks, nazwa, jednostka, zapas_min FROM dbo.zrodlo_przyklad;
END
GO

Tabele przykładowe, dane początkowe i wariant „opróżnij i wstaw” użyty w teście jako kontrast.

CREATE OR ALTER PROCEDURE dbo.odswiez_kartoteke_przyklad
AS
BEGIN
    SET NOCOUNT ON;
    SET XACT_ABORT ON;
    -- jeden odczyt źródła do tabeli pośredniej
    SELECT indeks, nazwa, jednostka, zapas_min INTO #zrodlo FROM dbo.zrodlo_przyklad;
    -- strażnik: pusty zbiór nie może wyczyścić kopii
    IF NOT EXISTS (SELECT 1 FROM #zrodlo)
        THROW 50001, N'Źródło nie zwróciło wierszy, kopia bez zmian.', 1;
    BEGIN TRY
        BEGIN TRAN;
        MERGE dbo.kartoteka_przyklad WITH (HOLDLOCK) AS c
        USING #zrodlo AS z ON z.indeks = c.indeks
        WHEN MATCHED AND (c.aktywna = 0 OR EXISTS
             (SELECT c.nazwa, c.jednostka, c.zapas_min EXCEPT SELECT z.nazwa, z.jednostka, z.zapas_min))
            THEN UPDATE SET nazwa = z.nazwa, jednostka = z.jednostka, zapas_min = z.zapas_min, aktywna = 1
        WHEN NOT MATCHED BY TARGET
            THEN INSERT (indeks, nazwa, jednostka, zapas_min)
                 VALUES (z.indeks, z.nazwa, z.jednostka, z.zapas_min)
        WHEN NOT MATCHED BY SOURCE AND c.aktywna = 1
            THEN UPDATE SET aktywna = 0;
        INSERT dbo.odswiezenie_przyklad (zmian) VALUES (@@ROWCOUNT);
        COMMIT;
    END TRY
    BEGIN CATCH
        IF @@TRANCOUNT > 0 ROLLBACK;
        THROW;
    END CATCH
END

Procedura odświeżenia z czterema krokami opisanymi w tekście.

Procedura czyta źródło jednym poleceniem SELECT z INTO do tabeli pośredniej i dalej pracuje już na tej tabeli, a nie na źródle. Jeśli odczyt źródła się nie uda, w teście był to błąd 208, procedura kończy się, zanim dotknie kopii, a kopia zostaje bez zmian. Odczyt jest więc oddzielony od zmiany: pierwszy może się nie udać, a drugi zaczyna się dopiero wtedy, gdy w tabeli pośredniej są wiersze.

Strażnik zatrzymuje odświeżenie, gdy źródło zwróciło zero wierszy

Tuż po odczycie stoi jedno sprawdzenie, strażnik: IF NOT EXISTS na tabeli pośredniej. Gdy tabela jest pusta, procedura wywołuje THROW z numerem 50001 i komunikatem, że źródło nie zwróciło wierszy, a kopia pozostaje bez zmian. Strażnik stoi przed otwarciem transakcji, więc przy pustym źródle procedura w ogóle nie zaczyna zmian w kopii. W scenariuszu czwartym, w którym źródło zwraca zero wierszy, w kopii zostaje pięć aktywnych wierszy i sześć wierszy razem, a nowy wpis kontrolny nie powstaje.

Zero wierszy ze źródła jest powodem do zatrzymania odświeżenia, a nie do opróżnienia kopii, o ile zero nie jest dozwolonym wynikiem.

Zasada obowiązuje tylko wtedy, gdy zero nie jest dozwolonym wynikiem źródła. Kontrolę wsadu w tabeli roboczej opisuje osobny artykuł o walidacji wsadu w tabeli roboczej. Oba warianty przy pustym źródle różnią się, jak pokazuje poniższa lista.

• Strażnik. Procedura kończy się błędem 50001, kopia ma pięć aktywnych wierszy i sześć razem, a wpis kontrolny nie powstaje.
• Wariant „opróżnij i wstaw”. Procedura kończy się bez błędu, a kopia ma zero wierszy.

Jeden MERGE w transakcji zmienia kopię albo nie zmienia jej wcale

Instrukcja MERGE zestawia tabelę pośrednią z kopią po kolumnie indeks i ma trzy gałęzie, a całość stoi w transakcji otwartej po strażniku.

• Zmiana pozycji. Pozycja obecna w obu miejscach jest zmieniana, gdy jest wyłączona albo gdy porównanie wykryje różnicę; zmiana ustawia ją jako aktywną.
• Nowa pozycja. Pozycja, której nie ma w kopii, jest wstawiana.
• Pozycja nieobecna w źródle. Aktywna pozycja kopii, której źródło nie zawiera, zostaje wyłączona, a nie usunięta.
• Blokada zakresu. Wskazówka HOLDLOCK na kopii utrzymuje blokadę zakresu kluczy na czas instrukcji.

Porównanie ma postać EXISTS z zapytaniem SELECT ... EXCEPT SELECT ..., które zestawia kolumny kopii z kolumnami źródła. W scenariuszu drugim wykryło ono zmianę z NULL na liczbę. Cztery zmiany w źródle (zmieniona nazwa, NULL zmieniony na liczbę, zniknięta pozycja, nowa pozycja) dają liczbę zmian 4, a zniknięta pozycja zostaje w kopii jako wyłączona, więc kopia ma razem sześć wierszy, w tym pięć aktywnych. Wyłączenie zachowuje ślad i pozwala wrócić do pozycji; usunięcie byłoby możliwe w tym samym MERGE jako decyzja projektowa.

HOLDLOCK ma zapobiec temu, żeby równoległa sesja wstawiła ten sam klucz między porównaniem a zapisem. W teście dwóch procesów, które wywołały procedurę po 150 razy, wersja z HOLDLOCK dała 150 z 150 udanych wywołań w obu procesach i żadnego błędu. Wersja bez HOLDLOCK kończyła się błędem klucza głównego 2627 w od 4 do 8 wywołań na proces. Test powtórzono na poziomach zgodności 130 i 160, po dwa procesy na poziom; zakleszczeń nie zaobserwowano.

Wpis kontrolny w tej samej transakcji wycofuje się razem ze zmianami

Po MERGE procedura dopisuje wpis kontrolny z liczbą zmian, którą zwraca @@ROWCOUNT, i dopiero potem zamyka transakcję poleceniem COMMIT. Wpis stoi w tej samej transakcji co MERGE, więc razem ze zmianami zostaje zapisany albo wycofany. Przy pierwszym odświeżeniu pustej kopii ze źródła o pięciu wierszach powstaje pięć aktywnych wierszy i wpis kontrolny z liczbą zmian 5. Powtórzenie bez zmian w źródle daje liczbę zmian 0, a wpis kontrolny i tak powstaje jako trzeci.

Scenariusz szósty sprawdza wycofanie. Wpis kontrolny odrzuca ograniczenie po udanym MERGE, procedura zgłasza błąd 547, a trzy nowe wiersze wstawione przez MERGE są wycofane razem z nim, więc kopia nadal ma sześć wierszy. Kolejność ROLLBACK i THROW w bloku CATCH omawia artykuł o THROW po ROLLBACK, a pytanie, gdzie zapisać informację o odrzuconym odświeżeniu, skoro wpis w tej samej transakcji by się wycofał, dotyczy materiału o zmiennej tabelowej przy ROLLBACK. Wyniki ośmiu scenariuszy zbiera tabela, a na poziomach zgodności 130 i 160 są one identyczne. Wiersze siódmy i ósmy pokazują wariant „opróżnij i wstaw”.

Wynik ośmiu scenariuszy, taki sam na poziomach zgodności 130 i 160.

W wierszu 8 wpisów jest 4, bo przed próbą kopię przywrócono poprawnym odświeżeniem, co dało jeden dodatkowy wpis. Wynik z tabeli pokazuje też makieta, która zestawia siatkę wyników z blokiem kodu.

Strażnik nie wykrywa odczytu niepełnego, więc próg jest decyzją biznesową

Strażnik wykrywa pusty zbiór, ale nie odczyt niepełny. Gdy źródło zwróciło jeden wiersz z pięciu, strażnik milczy, cztery pozycje zostają wyłączone, a liczba zmian wynosi 4. Próg względem poprzedniego odświeżenia jest więc decyzją biznesową i w teście nie występuje. Pozostałe granice wzorca dotyczą jakości samego źródła i kontekstu wywołania.

• Powtórzony klucz w źródle. Dla źródła bez klucza wynik zależy od przypadku: gdy oba wiersze różnią się od kopii, procedura zgłasza błąd 8672 i wycofuje zmiany, a gdy jeden jest identyczny z kopią, a drugi inny, błędu nie ma i kopia dostaje wartości drugiego wiersza. Dwa razy ta sama nowa pozycja daje błąd 2627.
• NULL w kluczu. NULL w kluczu źródła daje błąd 515.
• Spacja i wielkość liter. Spacja na końcu nazwy i zmiana wielkości liter w nazwie nie są wykrywane jako zmiana: liczba zmian wynosi 0, bo kolacja w teście nie rozróżnia wielkości liter.
• Transakcja zewnętrzna. Gdy wywołujący ma własną otwartą transakcję, ROLLBACK w bloku CATCH wycofuje także ją.

Pozostałe teksty o wzorcach T-SQL zbiera przegląd artykułów o SQL Server.

Pytania do analizy

Przed zastosowaniem wzorca odpowiedz na cztery pytania.

1. Jaka liczba wierszy w źródle jest podejrzana i kto o tym decyduje?
2. Czy pozycje, które zniknęły ze źródła, mają być w kopii wyłączane, czy usuwane?
3. Gdzie zapisać informację o odrzuconym odświeżeniu, skoro wpis w tej samej transakcji by się wycofał?
4. Które inne tabele w bazie są zasilane przez opróżnienie i wstawienie?