Rejestr blokad w T-SQL: jednostka zatrzymana bez zmiany ilości i bez kasowania śladów

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

Rejestr blokad w T-SQL zatrzymuje jednostkę bez zmiany ilości i bez kasowania śladów: blokada jest osobnym wierszem rejestru, a nie zmianą stanu. Programista albo administrator bazy magazynowej ma zatrzymać jednostkę, czyli pozycję o zapisanej ilości, i chce mieć historię blokad oraz widok jednostek dostępnych. Rejestr ze stanem blokady i śladem jej założenia oraz zdjęcia razem z widokiem z NOT EXISTS daje obie rzeczy bez dotykania ilości.

Blokada zapisana jako wiersz rejestru nie zmienia ilości i zostawia ślad: kto i kiedy ją założył i zdjął. Widok z NOT EXISTS wylicza z rejestru jednostki dostępne, więc zablokowana jednostka znika z widoku, a ilość razem w tabeli jednostek zostaje bez zmian. Ekran, który czyta tabelę jednostek bezpośrednio, pokaże także jednostkę zablokowaną.

W opisywanym wdrożeniu blokada jest wierszem osobnego rejestru, a nie zmianą ilości, a rejestr nie ma kolumny ilości. Wiersz rejestru ma stan oraz pola z loginem i czasem założenia blokady oraz z loginem i czasem jej zdjęcia. Pokazany wzorzec da się przenieść do innej bazy i nie jest kodem tego wdrożenia. O regule wspólnej dla przycisku i zapisu mówi artykuł o funkcji strażnika z kodem odmowy.

Blokada zapisana w rejestrze zostawia ślad i nie zmienia ilości

Rejestr zapisuje blokadę jako osobny wiersz, więc ilość jednostki zostaje taka, jaka była, a po blokadzie pozostaje ślad. Rejestr z przykładu nie ma kolumny ilości. Ma za to stan blokady oraz dwie pary pól: login i czas założenia oraz login i czas zdjęcia. Stan przyjmuje w przykładzie dwie wartości, aktywna i zdjeta, a ograniczenie CHECK nie pozwala wpisać innej. Pola zdjęcia są puste, dopóki blokada jest aktywna, a w kolumnie powod można zapisać powód blokady.

Blokada zapisana jako wiersz rejestru nie zmienia ilości i zostawia ślad po założeniu i zdjęciu.

Założenia przykładu. Pierwszy z trzech bloków kodu tworzy tabelę jednostek z kolumnami id, numer, miejsce i ilosc oraz pięć jednostek o łącznej ilości 175. Drugi blok tworzy rejestr blokad, indeks filtrowany i widok dostępnych jednostek, a trzeci zawiera instrukcje założenia i zdjęcia blokady.

Przykład wymaga silnika z poziomem zgodności 100 lub wyższym, bo korzysta z indeksu filtrowanego i typu datetime2, i nie używa CREATE OR ALTER. Test wykonano na jednej instancji, na poziomach zgodności 130 i 160; starszych wersji silnika nie testowano. Poniżej dwa pierwsze bloki kodu w kolejności uruchamiania, a trzeci blok stoi w części o zdjęciu blokady. Bloki zawierają separator GO, który jest separatorem narzędzia, a nie instrukcją T-SQL.

-- jednostki ze stanem. Ilość jest zapisana w jednostce i blokada jej nie zmienia.
SET NOCOUNT ON;
IF OBJECT_ID(N'dbo.jednostka_dostepna_przyklad') IS NOT NULL DROP VIEW dbo.jednostka_dostepna_przyklad;
IF OBJECT_ID(N'dbo.blokada_przyklad') IS NOT NULL DROP TABLE dbo.blokada_przyklad;
IF OBJECT_ID(N'dbo.jednostka_przyklad') IS NOT NULL DROP TABLE dbo.jednostka_przyklad;
GO
CREATE TABLE dbo.jednostka_przyklad (
    id      int          NOT NULL PRIMARY KEY,
    numer   varchar(20)  NOT NULL UNIQUE,
    miejsce varchar(10)  NOT NULL,
    ilosc   decimal(15,3) NOT NULL
);
INSERT dbo.jednostka_przyklad (id, numer, miejsce, ilosc) VALUES
    (1, 'J-001', 'M-01', 30),
    (2, 'J-002', 'M-01', 40),
    (3, 'J-003', 'M-02', 35),
    (4, 'J-004', 'M-02', 20),
    (5, 'J-005', 'M-03', 50);
GO

Tabela jednostek z pięcioma jednostkami o łącznej ilości 175.

CREATE TABLE dbo.blokada_przyklad (
    id           int IDENTITY(1,1) PRIMARY KEY,
    jednostka_id int           NOT NULL REFERENCES dbo.jednostka_przyklad (id),
    stan         varchar(10)   NOT NULL CHECK (stan IN ('aktywna', 'zdjeta')),
    zalozyl      varchar(30)   NOT NULL,
    zalozono     datetime2(0)  NOT NULL DEFAULT SYSDATETIME(),
    zdjal        varchar(30)   NULL,
    zdjeto       datetime2(0)  NULL,
    powod        nvarchar(200) NULL
);
-- najwyżej jedna aktywna blokada na jednostkę; ten sam indeks obsługuje NOT EXISTS
CREATE UNIQUE INDEX ux_blokada_aktywna ON dbo.blokada_przyklad (jednostka_id)
    WHERE stan = 'aktywna';
GO
CREATE VIEW dbo.jednostka_dostepna_przyklad
AS
SELECT j.id, j.numer, j.miejsce, j.ilosc
FROM dbo.jednostka_przyklad AS j
WHERE NOT EXISTS (SELECT 1 FROM dbo.blokada_przyklad AS b
                  WHERE b.jednostka_id = j.id AND b.stan = 'aktywna');
GO

Rejestr blokad z indeksem filtrowanym i widok dostępnych jednostek.

Jedna aktywna blokada na jednostkę wynika z indeksu filtrowanego

Pod tabelą rejestru stoi unikalny indeks filtrowany po kolumnie jednostka_id, który obejmuje tylko wiersze o stanie aktywna. Filtr na stanie sprawia, że wiersze zdjęte nie wchodzą do indeksu, więc jednostka może mieć najwyżej jeden wiersz aktywny, a wierszy zdjętych tyle, ile było zdjęć. Komentarz w kodzie mówi, że ten sam indeks obsługuje także warunek NOT EXISTS w widoku.

• Pierwsza blokada jednostki. Wiersz o stanie aktywna powstaje, a w rejestrze jest jedna aktywna blokada tej jednostki.
• Druga blokada tej samej jednostki. Zostaje odrzucona błędem 2601, a w rejestrze nadal jest jedna aktywna blokada.

W teście dwóch procesów, które 300 razy próbowały założyć blokadę tej samej jednostki, żadna jednostka nie miała więcej niż jednej aktywnej blokady. Nadmiarowe założenia kończyły się błędem 2601, bez innych błędów. Indeks filtrowany w innym zastosowaniu, dla numeru w dokumencie z miękkim usuwaniem, opisuje artykuł o numerze w dokumencie z miękkim usuwaniem.

Widok z NOT EXISTS wylicza z rejestru jednostki dostępne

Widok jednostka_dostepna_przyklad zwraca jednostki, dla których w rejestrze nie ma wiersza aktywnej blokady: warunek NOT EXISTS łączy jednostkę z rejestrem po jednostka_id i stanie aktywna. Widok liczy więc dostępność z rejestru, a sama tabela jednostek i jej ilości zostają nietknięte. W stanie początkowym pięć jednostek ma łączną ilość 175, a widok pokazuje pięć jednostek i 175.

Po blokadzie jednostki o ilości 40 widok pokazuje cztery jednostki i ilość 135, a ilość razem w tabeli jednostek nadal wynosi 175, przy jednej aktywnej blokadzie. Saldo lokalizacji liczone z ruchów magazynowych opisuje artykuł o saldzie lokalizacji z ruchów. Tabela zbiera wyniki pięciu kroków, a na poziomach zgodności 130 i 160 są one identyczne.

Wynik pięciu kroków, taki sam na poziomach zgodności 130 i 160.

Zdjęcie blokady zmienia stan wiersza i zostawia historię

Zdjęcie blokady jest instrukcją UPDATE, która ustawia stan zdjeta oraz wpisuje login i czas zdjęcia, a wiersz zostaje w rejestrze. Po zdjęciu jednostka wraca do widoku: widok pokazuje znowu pięć jednostek i 175. Ponowna blokada tej samej jednostki tworzy nowy wiersz, a poprzedni zostaje w historii. Historia jednostki ma wtedy dwa wiersze: zdjęty z czasem zdjęcia oraz aktywny bez niego. Zdjęcie nie kasuje więc śladu założenia, bo zmienia tylko stan i pola zdjęcia w tym samym wierszu.

Poniższy fragment testu ustawia blokadę jednostki 2 z powodem wpisanym w kolumnie powod, a następnie ją zdejmuje.

INSERT dbo.blokada_przyklad (jednostka_id, stan, zalozyl, powod) VALUES (2, 'aktywna', 'operator_1', N'kontrola jakości');
UPDATE dbo.blokada_przyklad SET stan = 'zdjeta', zdjal = 'operator_2', zdjeto = SYSDATETIME()
WHERE jednostka_id = 2 AND stan = 'aktywna';

Założenie i zdjęcie blokady jednostki 2.

Przykład (dane wymyślone). Pięć jednostek ma łącznie ilość 175. Kontrola jakości blokuje jednostkę o ilości 40, więc widok pokazuje cztery jednostki i ilość 135, a suma ilości w tabeli jednostek nadal wynosi 175.

Druga próba zablokowania tej samej jednostki kończy się błędem. Po zdjęciu blokady jednostka wraca do widoku, a rejestr zachowuje, kto założył i zdjął blokadę oraz kiedy to zrobił. Ekran, który czyta tabelę jednostek bez widoku, nadal pokazuje zablokowaną jednostkę.

Ekran, który czyta tabelę jednostek, pokaże zablokowaną jednostkę

Reguła dostępności jest zapisana w widoku, więc obowiązuje ekrany, które czytają z widoku. W teście ekran czytający tabelę jednostek z własnym filtrem miejsca pokazał 2 jednostki w miejscu, w którym jedna jest zablokowana. Ekran czytający widok pokazał w tym samym miejscu 1 jednostkę.

Na schemacie poniżej ekran A czyta tabelę jednostek bezpośrednio i pokazuje pięć jednostek, a ekran B czyta widok i pokazuje cztery. To pułapka rozproszonej reguły: warunek trzeba powtórzyć w zapytaniach, które czytają tabelę. W opisywanym wdrożeniu ten sam warunek stoi w kilku zapytaniach wyboru.

Lista zapytań, które czytają tabelę jednostek bezpośrednio, wymaga przeglądu i jest osobnym zadaniem.

Plan zapytania czyta indeks filtrowany zamiast całego rejestru

Plan zapytania do widoku sprawdzono przy 20005 jednostkach, około 50 blokadach aktywnych i około dwóch tysiącach blokad zdjętych. Anty-złączenie, w planie Left Anti Semi Join, czyta wtedy indeks filtrowany, a nie całą tabelę rejestru. Kształt planu jest taki sam na poziomach zgodności 130 i 160. Bez indeksu filtrowanego, przy tych samych danych, plan nadal zawiera anty-złączenie, ale nad Sort (DISTINCT) po skanie klucza głównego rejestru. Odczyty rejestru wynoszą wtedy 22 zamiast 2 przy około dwóch tysiącach pięćdziesięciu wierszach rejestru, a czasu nie mierzono.

Granice wzorca. Cztery granice wynikają z testu.

• Zdjęcie blokady, której nie ma. Instrukcja zdjęcia zmienia 0 wierszy i nie zgłasza błędu.
• Brak jednostek dostępnych. Gdy wszystkie jednostki są zablokowane, widok zwraca zero wierszy, a suma ilości w widoku jest NULL, nie zero.
• Ekrany poza widokiem. Reguła w widoku działa tylko dla ekranów, które z niego czytają.
• Indeks filtrowany. Nie jest konieczny dla poprawności widoku: w teście bez niego widok zwracał ten sam wynik.

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. Które ekrany i raporty w systemie czytają stan bezpośrednio z tabel, a które przez widok dostępności?
2. Czy blokada ma dotyczyć pojedynczej jednostki, czy większego zbioru jednostek i kto o tym decyduje?
3. Kto ma prawo zdjąć blokadę założoną przez kogoś innego?
4. Jak długo blokada może być aktywna, zanim ktoś ma obowiązek ją przejrzeć?