Idempotencja wysyłki do systemu zewnętrznego w T-SQL: blokada aplikacyjna i indeks filtrowany

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

Idempotencja wysyłki do systemu zewnętrznego w T-SQL zaczyna się od prostej sytuacji: dwie osoby klikają wysyłkę tego samego dokumentu w odstępie sekundy, a wywołanie odbiorcy trwa kilka sekund. Gdy procedura nie pilnuje wejścia, odbiorca dostaje ten sam dokument dwa razy.

Procedura bierze sesyjną blokadę aplikacyjną z czasem oczekiwania zero na zasobie zbudowanym z numeru dokumentu. Dopiero po odczycie istniejącego wpisu wywołuje system zewnętrzny. Druga sesja dostaje odpowiedź, że zasób jest zajęty, i niczego nie wywołuje. Unikatowy indeks filtrowany na wpisach w toku pilnuje tabeli, a nie samego wywołania.

To wzorzec z nazwami przykładowymi, a nie opis działania żadnego systemu. Sprawdziliśmy go na SQL Server 2022 przy poziomach zgodności 130 i 160 i za każdym razem dostaliśmy ten sam wynik. Użyte konstrukcje działają od SQL Server 2012 (według dokumentacji).

Dlaczego dwa kliknięcia mogą wysłać dokument dwa razy

Weźmy procedurę, która najpierw czyta wpis dokumentu, a potem wywołuje odbiorcę zewnętrznego. Wpis ze stanem zapisuje dopiero po wywołaniu. Pierwsza sesja czyta brak wpisu i rusza z wywołaniem, które trwa kilka sekund. Druga sesja startuje sekundę później, także czyta brak wpisu, bo pierwsza jeszcze niczego nie zapisała, i wywołuje odbiorcę po raz drugi.

W przykładzie odbiorcę zastępuje druga tabela, w której każde wywołanie zostawia wiersz. Test liczy więc rzeczywiste wywołania, a nie wpisy w tabeli śledzenia. Czas wywołania odtwarza polecenie WAITFOR na pięć sekund. Procedura odpowiada kodami przykładu, które opisuje tabela.

Przykład: dwie osoby otwierają w dwóch kartach ten sam dokument 26-3001 i klikają wysyłkę w odstępie sekundy. Procedura z pierwszego kliknięcia bierze blokadę i wywołuje odbiorcę zewnętrznego, a drugie kliknięcie od razu dostaje odpowiedź, że dokument jest w toku w innym oknie. Po chwili odpytanie kolejki odbiorcy zamyka wpis, a trzecie kliknięcie pokazuje, że dokument został już wysłany, bez nowego wywołania. Administrator sprawdza zapytaniem kontrolnym, że w systemie zewnętrznym jest jedno wywołanie tego dokumentu i jeden wpis w toku.

Tabela wpisów i unikatowy indeks filtrowany

Tabela wpisów łączy numer dokumentu ze statusem, a identyfikator zlecenia u odbiorcy zapisuje w osobnej kolumnie. Status przyjmuje jedną z trzech wartości z tabeli poniżej, więc po awarii wiadomo, na jakim etapie jest dokument. Druga tabela, SystemZewnetrzny, udaje odbiorcę.

Przykład ilustracyjny. Nazwy statusów są nazwami przykładu.

CREATE TABLE dbo.WysylkaDokumentu (
    Id         int IDENTITY(1,1) PRIMARY KEY,
    DokumentId int         NOT NULL,
    Status     varchar(20) NOT NULL,
    ZlecenieId int         NULL
);
CREATE UNIQUE INDEX UX_WysylkaDokumentu_WToku
    ON dbo.WysylkaDokumentu (DokumentId) WHERE Status = 'W_TOKU';
CREATE TABLE dbo.SystemZewnetrzny (
    ZlecenieId int IDENTITY(1000,1) PRIMARY KEY,
    DokumentId int NOT NULL
);

Indeks filtrowany obejmuje tylko wiersze z warunkiem WHERE Status = 'W_TOKU'. Dokument może mieć więc najwyżej jeden wpis w toku, ale wiele wpisów GOTOWE. Ten sam mechanizm w innym zastosowaniu opisuje artykuł o indeksie filtrowanym przy miękkim usuwaniu.

Wynik prób na przykładowych danych.

Sam indeks nie wystarcza, bo nie widzi wywołania. Wariant bez blokady czyta wpis i wywołuje odbiorcę, a wpis zapisuje dopiero potem. Przy dwóch sesjach obie wywołały system zewnętrzny, więc są w nim dwa wiersze, a indeks odrzucił dopiero drugi zapis błędem 2601, już po wywołaniu.

CREATE PROCEDURE dbo.WyslijBezBlokady @DokumentId int
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @status varchar(20), @zlecenie int;
    SELECT TOP (1) @status = Status, @zlecenie = ZlecenieId
    FROM dbo.WysylkaDokumentu
    WHERE DokumentId = @DokumentId AND Status IN ('GOTOWE', 'W_TOKU')
    ORDER BY CASE Status WHEN 'GOTOWE' THEN 0 ELSE 1 END, Id DESC;
    IF @status IS NULL
    BEGIN
        WAITFOR DELAY '00:00:05';
        INSERT dbo.SystemZewnetrzny (DokumentId) VALUES (@DokumentId);
        SET @zlecenie = SCOPE_IDENTITY();
        INSERT dbo.WysylkaDokumentu (DokumentId, Status, ZlecenieId) VALUES (@DokumentId, 'W_TOKU', @zlecenie);
    END;
    SELECT CASE @status WHEN 'GOTOWE' THEN 2 ELSE 1 END AS Kod,
           ISNULL(@status, 'NOWY') AS StanWpisu, @zlecenie AS ZlecenieId;
END;

Błąd 2601 przerywa tylko polecenie INSERT. Procedura bez TRY...CATCH i bez SET XACT_ABORT ON poszła dalej i zwróciła drugiej sesji kod 1 ze stanem NOWY oraz identyfikatorem drugiego wywołania, choć zapis się nie udał. Wołający widzi więc sukces tam, gdzie odbiorca dostał dokument po raz drugi.

Blokada chroni wywołanie zewnętrzne, a indeks filtrowany chroni tabelę wpisów.

Blokada sesyjna z czasem oczekiwania zero

Procedura bierze blokadę poleceniem sp_getapplock w trybie Exclusive. Właścicielem blokady jest sesja (Session), a czas oczekiwania wynosi 0. Zasób powstaje z przedrostka Wysylka_ i numeru dokumentu, więc każdy dokument ma własną blokadę, a wysyłka jednego dokumentu nie wstrzymuje innych.

Wynik -1 oznacza przekroczenie czasu oczekiwania, czyli zasób zajęty. Przykład odmawia przy każdym wyniku poniżej zera: odpowiada kodem 3 i stanem ZAJETE, bez żadnego wywołania. Wynik -999 oznacza natomiast błąd wywołania, a nie zajęty zasób, i wrócimy do niego w pułapkach.

CREATE PROCEDURE dbo.WyslijDokument @DokumentId int, @Awaria bit = 0
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @zasob nvarchar(255) = N'Wysylka_' + CAST(@DokumentId AS nvarchar(20)),
            @blokada int, @status varchar(20), @zlecenie int;
    EXEC @blokada = sp_getapplock @zasob, 'Exclusive', 'Session', 0;
    IF @blokada < 0 BEGIN SELECT 3 AS Kod, 'ZAJETE' AS StanWpisu, CAST(NULL AS int) AS ZlecenieId; RETURN; END;
    BEGIN TRY
        SELECT TOP (1) @status = Status, @zlecenie = ZlecenieId
        FROM dbo.WysylkaDokumentu
        WHERE DokumentId = @DokumentId AND Status IN ('GOTOWE', 'W_TOKU')
        ORDER BY CASE Status WHEN 'GOTOWE' THEN 0 ELSE 1 END, Id DESC;
        IF @status IS NULL
        BEGIN
            WAITFOR DELAY '00:00:05';
            IF @Awaria = 1 THROW 50001, N'Awaria wywolania', 1;
            INSERT dbo.SystemZewnetrzny (DokumentId) VALUES (@DokumentId);
            SET @zlecenie = SCOPE_IDENTITY();
            INSERT dbo.WysylkaDokumentu (DokumentId, Status, ZlecenieId) VALUES (@DokumentId, 'W_TOKU', @zlecenie);
        END;
        SELECT CASE @status WHEN 'GOTOWE' THEN 2 ELSE 1 END AS Kod,
               ISNULL(@status, 'NOWY') AS StanWpisu, @zlecenie AS ZlecenieId;
    END TRY
    BEGIN CATCH
        EXEC sp_releaseapplock @zasob, 'Session';
        THROW;
    END CATCH;
    EXEC sp_releaseapplock @zasob, 'Session';
END;

Czas zero oznacza, że druga sesja nie ustawia się w kolejce za pierwszą. Dostaje odmowę od razu, więc wołający może natychmiast pokazać komunikat o dokumencie w toku.

W teście dwóch sesji dla jednego dokumentu pierwsza sesja wykonała wywołanie symulowane poleceniem WAITFOR i dostała kod 1 ze stanem NOWY. Druga sesja, uruchomiona sekundę później, wróciła natychmiast z odpowiedzią 3 ZAJETE. W systemie zewnętrznym jest jedno wywołanie, a w tabeli wpisów jeden wpis W_TOKU.

Identyfikator 1001 dotyczy bazy, w której wcześniej wykonano test sekwencyjny z następnej sekcji; w świeżej bazie będzie 1000.

Zapytanie kontrolne liczy wywołania w odbiorcy i wpisy w toku: dokument 2 przeszedł przez procedurę z blokadą, a dokument 3 przez wariant z samym indeksem.

SELECT DokumentId,
       (SELECT COUNT(*) FROM dbo.SystemZewnetrzny s WHERE s.DokumentId = d.DokumentId) AS WywolaniaZewnetrzne,
       (SELECT COUNT(*) FROM dbo.WysylkaDokumentu w WHERE w.DokumentId = d.DokumentId AND w.Status = 'W_TOKU') AS WpisyWToku
FROM (VALUES (2), (3)) AS d(DokumentId);

Dokument 2: procedura z blokadą. Dokument 3: wariant z samym indeksem filtrowanym.

Wynik testu dwóch sesji w odstępie sekundy.

Odczyt istniejącego wpisu przed wywołaniem

Po zdobyciu blokady procedura czyta wpis dokumentu, przy czym GOTOWE ma pierwszeństwo przed W_TOKU. Brak wpisu oznacza wywołanie odbiorcy. Wpis W_TOKU daje kod 1 z tym samym identyfikatorem zlecenia, bez wywołania. Wpis GOTOWE daje kod 2, także bez wywołania.

Test sekwencyjny jednego dokumentu pokazuje trzy stany po kolei. Trzecie wywołanie następuje po zamknięciu wpisu, które zwykle robi odpytanie kolejki odbiorcy, a w teście robi je polecenie UPDATE. Liczba wywołań w systemie zewnętrznym wynosi przez cały test jeden.

EXEC dbo.WyslijDokument @DokumentId = 1;
EXEC dbo.WyslijDokument @DokumentId = 1;
UPDATE dbo.WysylkaDokumentu SET Status = 'GOTOWE' WHERE ZlecenieId = 1000 AND Status = 'W_TOKU';
EXEC dbo.WyslijDokument @DokumentId = 1;

Wynik testu sekwencyjnego; w świeżej bazie identyfikator wynosi 1000.

Od strony użytkownika wysyłkę przez kolejkę z licznikiem na ekranie opisuje strona o wysyłce dokumentu do ERP przez kolejkę. Kolejkę dogania proces opisany na stronie o monitorze kolejki ERP. Ten artykuł zostaje przy samej procedurze.

Zwolnienie blokady w każdej ścieżce, także po błędzie

Blokada sesyjna trwa do jawnego zwolnienia albo do końca połączenia. Procedura zwalnia ją więc na końcu i w bloku CATCH przed THROW. W teście awarii wywołania w środku bloku TRY wołający dostał błąd 50001, funkcja APPLOCK_MODE zwróciła NoLock, a wpisów w tabeli było zero.

BEGIN TRY EXEC dbo.WyslijDokument @DokumentId = 4, @Awaria = 1; END TRY
BEGIN CATCH SELECT ERROR_NUMBER() AS Blad; END CATCH;
SELECT APPLOCK_MODE('public', N'Wysylka_4', 'Session') AS TrybBlokady;

Zapis do dziennika i kolejność w bloku CATCH opisuje artykuł o TRY CATCH i THROW po ROLLBACK, a bufor błędów, który przeżywa wycofanie transakcji, artykuł o zmiennej tabelowej a ROLLBACK. Granice samego zwolnienia blokady zebraliśmy w tabeli, bo zależą od kontekstu wołającego.

Przykład ilustracyjny. Zachowanie zależy od kontekstu wołającego, więc nie jest regułą ogólną.

Ostatnia pułapka dotyczy narzędzia, nie kodu. Indeks filtrowany wymaga prawidłowych opcji sesji przy zapisie, a procedura zapamiętuje ustawienie QUOTED_IDENTIFIER z chwili utworzenia. Przykład uruchomiliśmy z tym ustawieniem włączonym, a program sqlcmd potrzebuje do tego przełącznika -I. Procedurę utworzoną bez niego i wywołaną później z sesji z ustawieniem włączonym zatrzymał błąd 1934. Mechanizm opisuje artykuł o procedurze z QUOTED_IDENTIFIER OFF oraz dokumentacja SET QUOTED_IDENTIFIER.

Blokada na grupę dokumentów i porównanie z UPDATE z OUTPUT

Zasób blokady nie musi dotyczyć jednego dokumentu. Blokada na grupę, na przykład z numerem zamówienia w nazwie zasobu, może czekać zamiast odmawiać. W teście sesja A trzymała zasób cztery sekundy, a sesja B z czasem 10000 ms czekała około trzech sekund i dostała wynik 1, czyli przyznanie po oczekiwaniu. Z czasem 0 ta sama sesja dostała -1 od razu. Wynik 0 lub wyższy oznacza przyznanie blokady.

Porównajmy wzorzec z jednym poleceniem UPDATE z OUTPUT. Dwa dokumenty oznaczone jako wysłane jednym poleceniem po awarii wywołania pozostały oznaczone, więc do ponowienia nie zostało nic (liczba 0). To semantyka co najwyżej raz, bez stanów pośrednich, którą opisuje artykuł o znaczniku wysłano w jednym UPDATE. Wpis ze stanami pozwala odróżnić dokument w toku od zakończonego i od błędu.

Blokadę całego przebiegu cyklicznego z tabelą kontrolną zdarzeń pokazuje artykuł o jednym mailu na zdarzenie, a ponowienia z limitem prób artykuł o klasyfikacji błędów i ponowieniach wysyłki. Opisany tu wzorzec sam niczego nie ponawia.

Granica wzorca leży po wywołaniu. Gdy błąd pojawi się po wywołaniu, a przed zapisem wpisu, procedura nie wie, czy dokument powstał u odbiorcy. W teście wariantu bez blokady wywołanie poszło, a wpisu brak. Ten przypadek opisuje strona o stanie nierozstrzygniętym po wysłaniu dokumentu do ERP. Wszystkie artykuły sekcji zebraliśmy w spisie artykułów o SQL Server.

Cztery pytania przed budową własnej procedury wysyłki

Zanim przeniesiesz wzorzec do własnej bazy, odpowiedz sobie na cztery pytania.

• Równoległość: co robi procedura wysyłająca dokument, gdy dwie sesje wywołają ją dla tego samego dokumentu w tej samej sekundzie.
• Kolejność zapisu: czy wiersz śledzenia powstaje przed wywołaniem zewnętrznym, czy po nim, i co wtedy chroni unikatowy indeks.
• Zwolnienie blokady: kto zwalnia blokadę, gdy procedura kończy się błędem, i jak to sprawdzić w tej samej sesji.
• Liczba wywołań: skąd wiadomo, ile razy system zewnętrzny naprawdę odebrał dany dokument.