Jeden mail na zdarzenie: tabela kontrolna zamiast przeszukiwania historii

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

Unikalny indeks i sp_getapplock w powiadomieniach SQL Server wystarczą, żeby każde zdarzenie dało w kolejce jedną wiadomość, nawet gdy zadanie uruchamia się cyklicznie z zewnętrznego harmonogramu. Opisujemy wzorzec z wdrożenia portalu awizacji Studio VSS.net w zakładzie produkcyjnym.

To przypadek jednego wdrożenia, a nie opis standardu produktu. Kod napisaliśmy od nowa na podstawie skryptu sprawdzonego na SQL Server 2022, z nazwami przykładowymi.

Jeden mail na zdarzenie zapewnia wąska tabela kontrolna z unikalnym kluczem zdarzenia, zapisywana w jednej transakcji z wiadomością w kolejce. Blokada sp_getapplock pilnuje, żeby nakładające się przebiegi nie liczyły kandydatów jednocześnie. Dziennik przebiegów pokazuje, co zrobił każdy krok i który przebieg pominięto.

Dlaczego przeszukiwanie historii wysyłek przestaje wystarczać

Najprostsza kontrola brzmi: zanim przygotujesz wiadomość, sprawdź w historii wysyłek, czy już taka jest. Taka kontrola przeszukuje dwie tabele, które rosły z każdą wiadomością, więc jej koszt rośnie razem z historią. W opisywanym wdrożeniu zastąpiła ją tabela, w której zdarzenie ma jeden wiersz.

Jedna procedura nadrzędna wywołuje cztery kroki po kolei: wiadomość o zarejestrowanym zamówieniu, o odrzuconej pozycji, o zaakceptowanej awizacji i o nowym komentarzu. Każdy krok jest osobną procedurą, a całość uruchamia się cyklicznie z zewnętrznego harmonogramu. Trzy kroki sprawdzają tabelę kontrolną. Krok o zamówieniach pilnuje powtórzeń własnym znacznikiem obsłużone na pozycji zamówienia, oznaczanym paczkami po zatwierdzeniu transakcji.

Przykład pokazuje jeden krok, o komentarzach, bo na nim widać cały wzorzec. Reguły powiadomień od strony użytkownika opisuje artykuł o tym, kiedy system pisze do dostawcy, a komentarz jako zdarzenie omawiamy przy dyskusji przy pozycji zamówienia. Wykrywanie duplikatów w kolejce opisuje diagnostyka kolejki integracji.

Tabela kontrolna z unikalnym kluczem zdarzenia

Tabela kontrolna ma unikalny klucz na trzech kolumnach: rodzaju powiadomienia, kluczu biznesowym i kluczu dodatkowym, czyli dyskryminatorze. Język wiadomości zapisuje się w osobnej kolumnie informacyjnej poza unikatem, więc jedno powiadomienie na klucz powstaje niezależnie od języka.

CREATE TABLE dbo.tabela_powiadomien
(
    id_powiadomienia int IDENTITY(1,1) PRIMARY KEY,
    rodzaj           varchar(10)  NOT NULL,
    klucz            bigint       NOT NULL,
    klucz2           bigint       NOT NULL DEFAULT 0,
    jezyk            varchar(5)   NULL,
    adresaci         varchar(500) NULL,
    kiedy            datetime     NOT NULL DEFAULT GETDATE(),
    CONSTRAINT UQ_powiadomienia UNIQUE (rodzaj, klucz, klucz2)
);

CREATE TABLE dbo.tabela_przebiegow
(
    id_przebiegu int IDENTITY(1,1) PRIMARY KEY,
    krok         varchar(20)   NOT NULL,
    start        datetime2(3)  NOT NULL,
    koniec       datetime2(3)  NULL,
    ms           int           NULL,
    kandydatow   int           NULL,
    blad         varchar(2000) NULL
);
CREATE INDEX IX_przebiegi_start ON dbo.tabela_przebiegow (start);

Przykład ilustracyjny, nazwy obiektów wymyślone.

W przykładzie unikalność wymusza jawne ograniczenie UNIQUE o nazwie UQ_powiadomienia. Opisujemy to jako wzorzec: nie twierdzimy, że klucz sprawdzono na bazie wdrożenia. Dziennik przebiegów ma wiersz na krok i wiersz na cały przebieg, a jego indeks leży na czasie początku.

Do przykładu potrzebne są jeszcze kolejka wysyłki, tu jedna tabela, oraz tabela komentarzy do pozycji. Kolumna adres_adresata oznacza odbiorcę powiadomienia, a nie autora komentarza.

CREATE TABLE dbo.tabela_kolejki
(
    id_wiadomosci int IDENTITY(1,1) PRIMARY KEY,
    temat         varchar(200) NOT NULL,
    adresaci      varchar(500) NULL
);

CREATE TABLE dbo.tabela_komentarzy
(
    id_komentarza  bigint IDENTITY(1,1) PRIMARY KEY,
    id_pozycji     bigint       NOT NULL,
    autor          varchar(50)  NOT NULL,
    adres_adresata varchar(100) NULL
);
GO

Przykład ilustracyjny, nazwy obiektów wymyślone.

Dla komentarzy kluczem dodatkowym jest numer najnowszego komentarza pozycji. Dla każdej pozycji przygotowywana jest więc wiadomość o najnowszym komentarzu. Jeśli między dwoma przebiegami pojawiło się kilka komentarzy, powstaje jedna wiadomość, a po następnym komentarzu kolejna. Dla pozostałych rodzajów klucz dodatkowy wynosi zero.

Jedno zdarzenie ma jeden wiersz w tabeli kontrolnej; unikalny klucz ma tego pilnować także przy dwóch równoczesnych przebiegach.

Blokada przebiegu przez sp_getapplock

Procedura nadrzędna bierze blokadę sp_getapplock w trybie Exclusive, z właścicielem Session i limitem @LockTimeout = 0. Jeśli blokada jest zajęta, kod wyniku jest mniejszy od zera. Przebieg kończy się wtedy od razu, a do dziennika trafia wpis, że został pominięty, bo poprzedni jeszcze trwa.

Priorytet zakleszczenia przebiegu ustawiono na LOW, więc w razie konfliktu ustępuje on zapytaniom użytkowników. Tak opisuje to dokumentacja wdrożenia; opcję opisuje SET DEADLOCK_PRIORITY.

CREATE OR ALTER PROCEDURE dbo.powiadomienia_przebieg
AS
BEGIN
    SET NOCOUNT ON;
    SET XACT_ABORT ON;
    SET DEADLOCK_PRIORITY LOW;

    DECLARE @t0 datetime2(3) = SYSDATETIME();
    DECLARE @lock int;
    DECLARE @blad varchar(2000) = NULL;

    EXEC @lock = sp_getapplock @Resource    = 'dbo.powiadomienia_przebieg',
                               @LockMode    = 'Exclusive',
                               @LockOwner   = 'Session',
                               @LockTimeout = 0;

    IF @lock < 0
    BEGIN
        INSERT dbo.tabela_przebiegow (krok, start, koniec, ms, blad)
        VALUES ('przebieg', @t0, SYSDATETIME(), 0,
                CONCAT('POMINIETY: poprzedni przebieg jeszcze trwa (kod ', @lock, ')'));
        RETURN;
    END

    BEGIN TRY
        EXEC dbo.powiadomienia_krok_komentarze;

        DELETE TOP (5000) FROM dbo.tabela_przebiegow
        WHERE start < DATEADD(DAY, -60, SYSDATETIME());
    END TRY
    BEGIN CATCH
        IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION;
        SET @blad = CONVERT(varchar(2000), CONCAT('BLAD ', ERROR_NUMBER(), ': ', ERROR_MESSAGE()));
    END CATCH

    IF APPLOCK_MODE('public', 'dbo.powiadomienia_przebieg', 'Session') <> 'NoLock'
        EXEC sp_releaseapplock @Resource = 'dbo.powiadomienia_przebieg', @LockOwner = 'Session';

    INSERT dbo.tabela_przebiegow (krok, start, koniec, ms, blad)
    VALUES ('przebieg', @t0, SYSDATETIME(), DATEDIFF(MILLISECOND, @t0, SYSDATETIME()), @blad);
END;
GO

Przykład ilustracyjny, nazwy obiektów wymyślone.

Blokada jest zwalniana na końcu przebiegu, także po błędzie przechwyconym w TRY...CATCH. Procedura sprawdza APPLOCK_MODE i wywołuje sp_releaseapplock, a błąd przebiegu zapisuje do dziennika zamiast przerywać wykonanie. Blokada sesyjna nie zwolni się jednak, gdy wykonanie zostanie przerwane przed tym miejscem. Błąd poza blokiem TRY...CATCH zostawia ją do końca sesji albo do jawnego zwolnienia.

Nazwy procedur nie mają prefiksu sp_. Dokumentacja CREATE PROCEDURE odradza go dla procedur użytkownika, bo SQL Server używa tego prefiksu do wyznaczania procedur systemowych i kod aplikacji może przestać działać, gdy istnieje procedura systemowa o tej samej nazwie. W przykładzie procedury to powiadomienia_krok_komentarze i powiadomienia_przebieg, a nazwa przebiegu jest też nazwą zasobu blokady.

Zapis w jednej transakcji i klucz dodatkowy dla powtórzeń

Krok liczy kandydatów raz, do tabeli tymczasowej, z warunkiem NOT EXISTS względem tabeli kontrolnej. Dopiero potem w krótkiej transakcji zapisuje wiersze: najpierw do tabeli kontrolnej, następnie do kolejki wysyłki. Wpis kontrolny idzie pierwszy, bo unikat nie dopuści drugiego wpisu dla tego samego zdarzenia.

CREATE OR ALTER PROCEDURE dbo.powiadomienia_krok_komentarze
AS
BEGIN
    SET NOCOUNT ON;
    SET XACT_ABORT ON;

    DECLARE @t0 datetime2(3) = SYSDATETIME();
    DECLARE @kand int = 0;

    BEGIN TRY
        IF OBJECT_ID('tempdb..#kandydaci') IS NOT NULL DROP TABLE #kandydaci;

        SELECT p.id_pozycji AS klucz, k.id_komentarza AS klucz2, k.adres_adresata
        INTO #kandydaci
        FROM (SELECT DISTINCT id_pozycji FROM dbo.tabela_komentarzy) AS p
        CROSS APPLY (SELECT TOP (1) id_komentarza, adres_adresata
                     FROM dbo.tabela_komentarzy
                     WHERE id_pozycji = p.id_pozycji
                     ORDER BY id_komentarza DESC) AS k
        WHERE NOT EXISTS (SELECT 1 FROM dbo.tabela_powiadomien AS l
                          WHERE l.rodzaj = 'komentarz'
                            AND l.klucz  = p.id_pozycji
                            AND l.klucz2 = k.id_komentarza);
        SET @kand = @@ROWCOUNT;

        IF @kand > 0
        BEGIN
            BEGIN TRANSACTION;

            INSERT dbo.tabela_powiadomien (rodzaj, klucz, klucz2, jezyk, adresaci)
            SELECT 'komentarz', klucz, klucz2, 'pl-PL', adres_adresata FROM #kandydaci;

            INSERT dbo.tabela_kolejki (temat, adresaci)
            SELECT CONCAT('Dodano komentarz do pozycji ', klucz), adres_adresata FROM #kandydaci;

            COMMIT TRANSACTION;
        END

        INSERT dbo.tabela_przebiegow (krok, start, koniec, ms, kandydatow, blad)
        VALUES ('komentarze', @t0, SYSDATETIME(),
                DATEDIFF(MILLISECOND, @t0, SYSDATETIME()), @kand, NULL);
    END TRY
    BEGIN CATCH
        IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION;
        INSERT dbo.tabela_przebiegow (krok, start, koniec, ms, kandydatow, blad)
        VALUES ('komentarze', @t0, SYSDATETIME(),
                DATEDIFF(MILLISECOND, @t0, SYSDATETIME()), @kand,
                CONVERT(varchar(2000), CONCAT('BLAD ', ERROR_NUMBER(), ': ', ERROR_MESSAGE())));
    END CATCH
END;
GO

Przykład ilustracyjny, nazwy obiektów wymyślone.

Każdy krok zapisuje własne błędy do dziennika i świadomie nie rzuca ich dalej, więc awaria jednego powiadomienia nie zatrzymuje pozostałych. Wartość @@ROWCOUNT trzeba przepisać do zmiennej od razu po instrukcji, która ją ustawia, bo kolejna instrukcja ją zeruje. W przykładzie robi to wiersz SET @kand = @@ROWCOUNT zaraz po SELECT ... INTO. W kroku o zamówieniach ta sama zasada dotyczy instrukcji IF, która zeruje licznik, a pętla paczek kończy się, gdy zmienna wynosi zero.

Tabela kontrolna zapisuje fakt przygotowania wiadomości w kolejce. Nie gwarantuje dostarczenia, a awaria po stronie wysyłki poczty nie jest nią objęta ani ponawiana. Ponawianie nieudanych wysyłek opisuje artykuł o klasyfikacji błędów i ponowieniach, a blokadę wiersza licznika w transakcji artykuł o indeksie z licznika.

Dziennik przebiegów z retencją i bezpiecznik na masową wysyłkę

Dziennik zapisuje dla każdego kroku czas rozpoczęcia i zakończenia oraz czas w milisekundach. Dołącza liczby kandydatów i dopisanych do kolejek wierszy oraz tekst błędu lub ostrzeżenia. Pusta wartość błędu oznacza przebieg bez zastrzeżeń. W przykładzie dziennik jest uproszczony: w opisywanym wdrożeniu są dwie kolejki, wysyłki i zadań, więc dziennik ma dwie kolumny z liczbą dopisanych wierszy, a przykład ma jedną kolejkę i kolumnę kandydatow.

Dziennik czyści się sam. Przy każdym przebiegu procedura nadrzędna usuwa instrukcją DELETE TOP (5000) wpisy starsze niż 60 dni.

Krok o zarejestrowanych zamówieniach ma dwa zabezpieczenia, których przykład nie zawiera. Oznaczanie pozycji znacznikiem idzie paczkami po 2000 wierszy, poza główną transakcją. To liczba poniżej progu eskalacji blokad, wynoszącego 5000, więc zostają blokady wierszowe. Drugie zabezpieczenie to bezpiecznik: najwyżej 500 grup zamówień na przebieg, od najstarszych. Osiągnięcie limitu trafia do dziennika jako ostrzeżenie.

Jak sprawdzić, że drugie uruchomienie nic nie wysyła

Próba składa się z kilku uruchomień pod rząd i dwóch dodatkowych sprawdzeń. Najpierw wstawiamy trzy komentarze do dwóch pozycji, a po każdym uruchomieniu procedury nadrzędnej liczymy wiadomości w kolejce.

INSERT dbo.tabela_komentarzy (id_pozycji, autor, adres_adresata) VALUES
    (1, 'a', 'adresat-1'), (1, 'b', 'adresat-2'), (2, 'c', 'adresat-3');

EXEC dbo.powiadomienia_przebieg;
SELECT COUNT(*) AS kolejka FROM dbo.tabela_kolejki;

EXEC dbo.powiadomienia_przebieg;
SELECT COUNT(*) AS kolejka FROM dbo.tabela_kolejki;

INSERT dbo.tabela_komentarzy (id_pozycji, autor, adres_adresata) VALUES (2, 'd', 'adresat-4');
EXEC dbo.powiadomienia_przebieg;
SELECT (SELECT COUNT(*) FROM dbo.tabela_kolejki) AS kolejka,
       (SELECT COUNT(*) FROM dbo.tabela_powiadomien) AS tabela_kontrolna;
GO

Przykład ilustracyjny, nazwy obiektów wymyślone.

Po pierwszym uruchomieniu kolejka ma dwie wiadomości, po jednej na pozycję. Drugie nic nie dopisuje, a nowy komentarz daje trzecią wiadomość. Dwa dodatkowe sprawdzenia dotyczą duplikatu i zajętej blokady: pierwsze ręcznie wstawia istniejący klucz, a drugie wymaga drugiej sesji, która trzyma blokadę.

INSERT dbo.tabela_powiadomien (rodzaj, klucz, klucz2)
VALUES ('komentarz', 1, 2);

Przykład ilustracyjny, nazwy obiektów wymyślone.

EXEC sp_getapplock @Resource    = 'dbo.powiadomienia_przebieg',
                   @LockMode    = 'Exclusive',
                   @LockOwner   = 'Session',
                   @LockTimeout = 0;
WAITFOR DELAY '00:00:30';
EXEC sp_releaseapplock @Resource = 'dbo.powiadomienia_przebieg', @LockOwner = 'Session';

Przykład ilustracyjny, nazwy obiektów wymyślone.

Wstawienie istniejącego klucza kończy się błędem 2627 z nazwą ograniczenia UQ_powiadomienia. Przy zajętej blokadzie przebieg uruchomiony w pierwszej sesji zapisuje w dzienniku wiersz POMINIETY z kodem -1. W jednej sesji tej próby nie da się odtworzyć, bo blokada sesyjna jest dla tej samej sesji ponawialna i drugie wywołanie przejdzie.

Przebieg próby i wyniki zgodne z opisem przykładu.

Przykład: Dostawca Testowy dodaje do pozycji ITEM-0001 zamówienia ZAK-26-0042 dwa komentarze w odstępie kilku minut. Kolejny przebieg dokłada do kolejki jedną wiadomość o najnowszym z nich, a następny zapisuje w dzienniku zero kandydatów. Gdy zadanie uruchomi się przed końcem poprzedniego, dziennik pokazuje wiersz POMINIETY. Trzeci komentarz daje jedną nową wiadomość.

Skrypty wymagają SQL Server 2016 SP1 lub nowszego, bo korzystają z CREATE OR ALTER, i uruchamia się je na pustej bazie testowej. Obie procedury wdraża się techniką z artykułu o zmianie zapytań zapisanych w bazie.

Cztery pytania przed budową własnych powiadomień

Przed zbudowaniem podobnego mechanizmu odpowiedz na cztery pytania.

• Zdarzenie: co dokładnie jest zdarzeniem, które ma dać jedną wiadomość, i czy do jego klucza potrzebny jest dyskryminator, na przykład numer najnowszego komentarza.
• Nakładanie: co ma się stać, gdy przebieg trwa dłużej niż odstęp między uruchomieniami: czekać na poprzedni przebieg, czy pominąć uruchomienie.
• Transakcja: czy wpis kontrolny i wiadomość trafiają do bazy w jednej transakcji i co dzieje się z kolejnymi krokami, gdy jeden z nich zgłosi błąd.
• Dziennik: jak długo przechowujemy dziennik przebiegów i jaki bezpiecznik ogranicza wysyłkę po zdjęciu progu daty lub po masowym imporcie.

Inne wzorce z tej samej bazy opisują artykuły o funkcji tożsamości i o ocenie atestu w T-SQL.