Dokumenty do wysyłki bez kolejki: znacznik „wysłano” w jednym UPDATE z OUTPUT

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

UPDATE z OUTPUT w T-SQL pozwala wydać dokumenty do wysyłki bez osobnej kolejki: jedno polecenie oznacza je jako wysłane i od razu zwraca ich numery. Dwa przebiegi zadania uruchomione prawie jednocześnie nie zabiorą wtedy tego samego dokumentu.

Procedura, która jednym poleceniem UPDATE z klauzulą OUTPUT oznacza dokumenty jako wysłane i zwraca ich numery, nie pozwala dwóm równoległym przebiegom zabrać tego samego dokumentu. Płacimy za to semantyką co najwyżej raz: gdy odbiorca dokumentu nie potwierdzi, dokument zostaje oznaczony i nie wróci.

To wzorzec z nazwami przykładowymi. Uruchomiliśmy go na SQL Server 2022 przy poziomach zgodności 130 i 160, także w teście dwóch równoległych sesji, i za każdym razem dostaliśmy ten sam wynik. Użyte konstrukcje są dostępne od SQL Server 2016 (według dokumentacji).

Dwa przebiegi zadania mogą zabrać ten sam dokument

Przykład: zadanie uruchamia cyklicznie procedurę, która pobiera dokumenty do wysyłki. Dwa przebiegi zadania uruchomiły się prawie jednocześnie, drugi sekundę po pierwszym. Gdy procedura najpierw wybiera dokumenty do tabeli tymczasowej, a dopiero potem oznacza je osobnym UPDATE, oba przebiegi zwracają te same dokumenty 1 i 2 i każdy z nich wychodzi dwa razy.

Gdy znacznik ustawia jedno polecenie UPDATE z OUTPUT, pierwszy przebieg dostaje dokumenty W-1 i P-1, a drugi zestaw pusty. Drugi UPDATE czeka na blokadę założoną przez pierwszy, a po jej zwolnieniu widzi już Wyslano = 1. Nic nie zostaje wysłane dwa razy.

Przykład. Odstęp między odczytem a zapisem w wersji naiwnej odtworzono poleceniem WAITFOR DELAY na trzy sekundy.

Odczyt dokumentów do wysyłki i ich oznaczenie muszą być jednym poleceniem, bo każdy odstęp między nimi jest oknem dla drugiego przebiegu.

Dane próbne i procedura zwracająca płaski zestaw

Dane próbne to trzy tabele opisane niżej oraz pięć dokumentów, z których każdy odtwarza jedną sytuację. Dokumenty W-1 i P-1 są zatwierdzone i niewysłane. Dokument W-2 nie jest zatwierdzony. Dokument P-2 jest już wysłany, a P-3 jest zatwierdzony i ma jedną pozycję o ilości 0.

CREATE TABLE dbo.Dokument (Id int IDENTITY PRIMARY KEY, Numer varchar(20), Typ char(2), Zatwierdzony bit NOT NULL, Wyslano bit NOT NULL DEFAULT 0);
CREATE TABLE dbo.Pozycja (Id int IDENTITY PRIMARY KEY, DokId int, Artykul varchar(10), Ilosc decimal(15,3));
CREATE TABLE dbo.RecepturaKompletu (Komplet varchar(10), Skladowa varchar(10), Ilosc int);
INSERT dbo.Dokument (Numer, Typ, Zatwierdzony, Wyslano) VALUES
 ('W-1', 'WZ', 1, 0), ('P-1', 'PZ', 1, 0), ('W-2', 'WZ', 0, 0), ('P-2', 'PZ', 1, 1), ('P-3', 'PZ', 1, 0);
INSERT dbo.Pozycja (DokId, Artykul, Ilosc) VALUES
 (1,'KOMPLET-1',2),(1,'ART-X',5),(2,'ART-Y',10),(3,'ART-X',1),(4,'ART-Y',3),(5,'ART-Y',0);
INSERT dbo.RecepturaKompletu VALUES ('KOMPLET-1','CZESC-A',2),('KOMPLET-1','CZESC-B',1);

Procedura oznacza dokumenty jednym poleceniem, a potem zwraca zestaw wyłącznie dla dokumentów z tabeli zmiennej. Wynik jest płaskim zestawem o sześciu kolumnach z kropką, które widać w tabeli wyniku poniżej. Dane dokumentu powtarzają się w każdym wierszu pozycji, a zagnieżdżenie w JSON zostaje po stronie programu odbierającego zestaw. Blok z CREATE PROCEDURE uruchamiamy jako osobną partię.

CREATE PROCEDURE dbo.DokumentyDoWysylki AS
BEGIN
  SET NOCOUNT ON;
  DECLARE @wydane TABLE (Id int PRIMARY KEY);

  UPDATE d SET Wyslano = 1
  OUTPUT inserted.Id INTO @wydane
  FROM dbo.Dokument d
  WHERE d.Wyslano = 0 AND d.Zatwierdzony = 1
    AND EXISTS (SELECT 1 FROM dbo.Pozycja p WHERE p.DokId = d.Id AND p.Ilosc > 0);

  SELECT d.Id AS [Dokument.Id], d.Numer AS [Dokument.Numer],
         CASE d.Typ WHEN 'WZ' THEN 'SA' WHEN 'PZ' THEN 'SB' ELSE d.Typ END AS [Dokument.Seria],
         ROW_NUMBER() OVER (PARTITION BY d.Id ORDER BY p.Id, x.Artykul) AS [Pozycje.Lp],
         x.Artykul AS [Pozycje.Artykul], x.Ilosc AS [Pozycje.Ilosc]
  FROM @wydane w
  JOIN dbo.Dokument d ON d.Id = w.Id
  JOIN dbo.Pozycja p ON p.DokId = d.Id AND p.Ilosc > 0
  LEFT JOIN dbo.RecepturaKompletu r ON r.Komplet = p.Artykul
  CROSS APPLY (SELECT COALESCE(r.Skladowa, p.Artykul) AS Artykul, p.Ilosc * COALESCE(r.Ilosc, 1) AS Ilosc) x
  ORDER BY d.Id, [Pozycje.Lp];
END;

Typ dokumentu jest mapowany na serię wyrażeniem CASE: w przykładzie WZ daje serię SA, a PZ serię SB (wartości wymyślone). Pierwsze wywołanie zwraca dokumenty W-1 i P-1, czyli cztery wiersze zestawu, a drugie wywołanie zestaw pusty. Po obu wywołaniach wysłane są dokumenty 1, 2 i 4, a dokumenty 3 i 5 mają Wyslano = 0.

EXEC dbo.DokumentyDoWysylki;
EXEC dbo.DokumentyDoWysylki;
SELECT 'KONTROLA' AS Sekcja, Id, Numer, Zatwierdzony, Wyslano FROM dbo.Dokument ORDER BY Id;

Przykład. Drugie wywołanie zwraca zestaw pusty.

Przykład. Dokument P-3 zostaje niewysłany, bo żadna jego pozycja nie ma ilości większej od zera.

Komplet rozbijamy na składowe w tym samym zapytaniu

Komplet jest rozbijany w tym samym zapytaniu. Złączenie LEFT JOIN do receptury zostawia pozycje, które nie są kompletami, a wyrażenie COALESCE wybiera składową z receptury albo artykuł pozycji. Ilość to ilość pozycji razy ilość z receptury albo razy 1.

Pozycja KOMPLET-1 w ilości 2 przy recepturze 2 x CZESC-A i 1 x CZESC-B daje wiersze CZESC-A w ilości 4 i CZESC-B w ilości 2, a ART-X w ilości 5 przechodzi bez zmian. Dlatego numer pozycji w zestawie liczy ROW_NUMBER z podziałem na dokument, a nie identyfikator pozycji: z jednej pozycji powstaje kilka wierszy i identyfikator przestaje być jednoznaczny. W dokumencie W-1 numery biegną od 1 do 3.

Znacznik w jednym UPDATE z OUTPUT zamyka okno dla drugiej sesji

W poleceniu UPDATE klauzula OUTPUT zwraca wiersze, które polecenie właśnie zmieniło. Zapis OUTPUT inserted.Id INTO @wydane odkłada numery zmienionych dokumentów do tabeli zmiennej, a zestaw wynikowy powstaje tylko z niej.

Warunek EXISTS w UPDATE chroni przed cichą utratą. Bez niego UPDATE oznaczyłby też dokument P-3 jako wysłany, choć żaden jego wiersz nie wchodzi do zestawu, bo filtr wymaga ilości większej od zera, więc dokument zniknąłby z kolejki bez śladu. W teście bez warunku oznaczone zostało Id 5.

UPDATE dbo.Dokument SET Wyslano = 0 WHERE Numer = 'P-3';
DECLARE @bez TABLE (Id int);
UPDATE dbo.Dokument SET Wyslano = 1 OUTPUT inserted.Id INTO @bez WHERE Wyslano = 0 AND Zatwierdzony = 1;
SELECT 'BEZ_STRAZNIKA_oznaczone_Id' AS Sekcja, Id FROM @bez;

Wyścig sprawdziliśmy testem dwóch sesji uruchomionych w odstępie jednej sekundy. Wersja naiwna, czyli SELECT do tabeli tymczasowej i UPDATE rozdzielone trzysekundową przerwą, zwróciła w obu sesjach te same dokumenty 1 i 2.

CREATE PROCEDURE dbo.DokumentyDoWysylki_Naiwna AS
BEGIN
  SET NOCOUNT ON;
  SELECT Id INTO #do_wyslania FROM dbo.Dokument d WHERE d.Wyslano = 0 AND d.Zatwierdzony = 1
     AND EXISTS (SELECT 1 FROM dbo.Pozycja p WHERE p.DokId = d.Id AND p.Ilosc > 0);
  WAITFOR DELAY '00:00:03';
  UPDATE dbo.Dokument SET Wyslano = 1 WHERE Id IN (SELECT Id FROM #do_wyslania);
  SELECT Id AS [Dokument.Id] FROM #do_wyslania ORDER BY Id;
END;

Przykład ilustracyjny. Wersja naiwna użyta w teście wyścigu; WAITFOR odtwarza odstęp między odczytem a zapisem.

Wzorzec z UPDATE i OUTPUT zachował się inaczej. Sesja A, z transakcją przytrzymaną trzy sekundy, dostała zestaw z czterema wierszami, a sesja B zestaw pusty, bo jej UPDATE czekał na blokadę i po jej zwolnieniu zobaczył Wyslano = 1.

BEGIN TRAN;
EXEC dbo.DokumentyDoWysylki;
WAITFOR DELAY '00:00:03';
COMMIT;

EXEC dbo.DokumentyDoWysylki;

Wynik testu był taki sam po włączeniu opcji bazy READ_COMMITTED_SNAPSHOT, którą opisuje dokumentacja ALTER DATABASE SET. Sprawdziliśmy jeden UPDATE przy dwóch poziomach izolacji, domyślnym i z tą opcją, więc nie twierdzimy, że OUTPUT rozwiązuje każdy wyścig.

OUTPUT bez INTO zatrzymuje UPDATE na tabeli z wyzwalaczem

Klauzula OUTPUT bez INTO zwraca wiersze klientowi, i tu jest pułapka. Dla tabeli z włączonym wyzwalaczem UPDATE z samym OUTPUT kończy się błędem 334, a z OUTPUT ... INTO tabela zmienna dostaje 1 wiersz bez błędu. Wyzwalacz dopisuje CREATE TRIGGER, który musi być pierwszą instrukcją partii, więc polecenia z tego bloku uruchamiamy w osobnych partiach rozdzielonych poleceniem GO narzędzia klienckiego.

CREATE TABLE dbo.DokumentTrg (Id int IDENTITY PRIMARY KEY, Wyslano bit NOT NULL DEFAULT 0);
INSERT dbo.DokumentTrg DEFAULT VALUES;
GO
CREATE TRIGGER dbo.trg_DokumentTrg ON dbo.DokumentTrg AFTER UPDATE AS BEGIN SET NOCOUNT ON; END;
GO
EXEC (N'UPDATE dbo.DokumentTrg SET Wyslano = 1 OUTPUT inserted.Id WHERE Wyslano = 0');

Przykład ilustracyjny. GO jest separatorem partii narzędzia, a nie instrukcją T-SQL. Ostatnie polecenie kończy się błędem 334.

Komunikat błędu brzmi: „The target table 'dbo.DokumentTrg' of the DML statement cannot have any enabled triggers if the statement contains an OUTPUT clause without INTO clause.”.

Płaski zestaw z kolumnami z kropką różni się od FOR JSON PATH

Zestaw z kolumnami z kropką jest płaski: dokument powtarza się w każdym wierszu pozycji. FOR JSON PATH na takim zestawie z aliasami Dokument.Id i Pozycje.Artykul daje po jednym obiekcie na wiersz, z zagnieżdżonymi obiektami Dokument i Pozycje, a nie tablicę pozycji. Opisuje to dokumentacja FOR JSON.

SELECT d.Id AS [Dokument.Id], p.Artykul AS [Pozycje.Artykul], p.Ilosc AS [Pozycje.Ilosc]
FROM dbo.Dokument d JOIN dbo.Pozycja p ON p.DokId = d.Id WHERE d.Id = 1 FOR JSON PATH;

[{"Dokument":{"Id":1},"Pozycje":{"Artykul":"KOMPLET-1","Ilosc":2.000}},{"Dokument":{"Id":1},"Pozycje":{"Artykul":"ART-X","Ilosc":5.000}}]

Przykład. Wynik: po jednym obiekcie na wiersz, bez tablicy pozycji.

Tablicę pozycji daje dopiero podzapytanie z FOR JSON PATH w kolumnie Pozycje.

SELECT d.Id AS [Dokument.Id], d.Numer,
       (SELECT p.Artykul, p.Ilosc FROM dbo.Pozycja p WHERE p.DokId = d.Id FOR JSON PATH) AS Pozycje
FROM dbo.Dokument d WHERE d.Id = 1 FOR JSON PATH;

[{"Dokument":{"Id":1},"Numer":"W-1","Pozycje":[{"Artykul":"KOMPLET-1","Ilosc":2.000},{"Artykul":"ART-X","Ilosc":5.000}]}]

Przykład. Wynik: jeden obiekt dokumentu z tablicą Pozycje.

Odpowiedź API zbudowaną z FOR JSON PATH opisuje artykuł o API na procedurach SQL i JSON.

Kompromis wzorca i wariant z potwierdzeniem

Znacznik jest ustawiany przed odpowiedzią odbiorcy, więc to semantyka co najwyżej raz. Gdy odbiorca zestawu nie przyjmie dokumentu, dokument pozostaje oznaczony, a procedura nie zwróci go ponownie.

Wariant z potwierdzeniem trzyma kolejkę ze statusem 0 (czeka), 1 (pobrane) i 2 (potwierdzone) oraz czasem pobrania. Ten sam UPDATE z OUTPUT zmienia 0 na 1 i bierze także status 1 starszy niż 15 minut. Dokument, którego odbiorca nie potwierdził, wraca więc do zestawu po upływie tego czasu.

CREATE TABLE dbo.Kolejka (Id int PRIMARY KEY, Status tinyint NOT NULL DEFAULT 0, Pobrano datetime2 NULL);
INSERT dbo.Kolejka (Id) VALUES (1),(2);

DECLARE @pob TABLE (Id int PRIMARY KEY);
UPDATE dbo.Kolejka SET Status = 1, Pobrano = SYSUTCDATETIME()
OUTPUT inserted.Id INTO @pob
WHERE Status = 0 OR (Status = 1 AND Pobrano < DATEADD(MINUTE, -15, SYSUTCDATETIME()));

Przykład ilustracyjny. Pobranie uruchamiamy trzy razy, każdy raz w osobnej partii.

UPDATE dbo.Kolejka SET Status = 2 WHERE Id = 1;
UPDATE dbo.Kolejka SET Pobrano = DATEADD(MINUTE, -20, Pobrano) WHERE Id = 2;

Przykład ilustracyjny. Potwierdzenie dokumentu 1 i cofnięcie czasu dokumentu 2 o 20 minut; wykonujemy je między drugim a trzecim pobraniem.

Przykład. Stan końcowy: dokument 1 ma status 2, a dokument 2 status 1.

To semantyka co najmniej raz, więc odbiorca musi rozpoznawać powtórkę po stałym kluczu dokumentu. Ponowienia po nieudanej wysyłce, z osobną tabelą ponowień, opisuje artykuł o klasyfikacji błędów i ponowieniach wysyłki, a szukanie przyczyn zatoru w kolejce integracji artykuł o diagnostyce kolejki integracji. Inny sposób uniknięcia powtórki, przez unikalny indeks i blokadę aplikacyjną, pokazuje artykuł o jednym mailu na zdarzenie.

Pytania przed napisaniem własnej procedury wydającej dokumenty

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

• Przebiegi: czy procedura pobierająca dokumenty do wysyłki może być wywołana przez dwa przebiegi naraz, także przypadkiem.
• Klucz: czy odbiorca potrafi rozpoznać ten sam dokument wysłany drugi raz po stałym kluczu.
• Nieprzyjęcie: kto dowie się, że dokument został oznaczony, a odbiorca go nie przyjął.
• Kształt zestawu: czy zestaw wynikowy ma być płaski, czy baza ma sama składać JSON.

Warunek zamknięcia zlecenia po liczbie otwartych linii opisuje artykuł o zamykaniu zlecenia w T-SQL, a liczenie kompletów ze zebranych składowych artykuł o alokacji zachłannej kompletów. Szersze omówienie sposobów wymiany dokumentów z programem ERP znajdziesz w artykule o integracji systemu magazynowego z ERP. Wszystkie artykuły sekcji zebraliśmy w spisie artykułów o SQL Server.