READPAST w T-SQL: druga sesja bierze kolejne zadanie zamiast czekać na blokadę

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

W kolejce zadań w T-SQL wskazówka READPAST sprawia, że druga sesja bierze kolejne wolne zadanie zamiast czekać na blokadę. Programista albo administrator bazy buduje kolejkę zadań dla kilku terminali albo procesów: zadanie ma trafić tylko do jednego z nich, a druga sesja nie ma czekać na blokadę. Gdy pierwsza sesja trzyma zadanie w otwartej transakcji, druga sesja bez READPAST czeka na zwolnienie blokady, a z READPAST bierze kolejne wolne zadanie. Kolejność według rangi dotyczy wtedy tylko zadań, które nie są w tej chwili zablokowane przez inną sesję.

Druga sesja z READPAST dostaje kolejne wolne zadanie po 0 do 1 ms, a bez READPAST czeka około 2,5 s na zwolnienie blokady. Wariant z jedną instrukcją UPDATE i OUTPUT nie dał duplikatów w 500 zadaniach, a wariant z osobnym odczytem i zapisem dał ich około 490. READPAST nie gwarantuje kolejności według rangi.

W opisywanym wdrożeniu terminal pobiera następne zlecenie jednym zapytaniem, które zwraca najwyżej jedno zlecenie. Zlecenie po wejściu w zbiórkę zostaje przy swoim operatorze i nie wraca do kolejki innego. Pokazane tu warianty są wzorcami do przeniesienia do innej bazy i nie opisują kodu tego wdrożenia. Działanie terminala opisuje artykuł o kolejce zleceń na terminalu.

Odczyt następnego zadania i osobny zapis pozwalają dwóm sesjom wziąć to samo zadanie

W wariancie dwuetapowym odczyt zadania i zapis przejęcia są osobnymi instrukcjami, a między nimi powstaje odstęp, w którym druga sesja może odczytać to samo zadanie. W teście ten odstęp odtworzono przerwą 20 ms, która jest umowna i symuluje czas między odczytem a zapisem. Dwa procesy wykonały wtedy około 990 przejęć 500 zadań, czyli około 490 duplikatów, a liczba duplikatów zmieniała się między biegami. Ten wyścig opisuje osobny artykuł o znaczniku wysłano w jednym UPDATE. Aktualizacja warunkowa po odczycie, czyli UPDATE z warunkiem na status nowe i ponowieniem przy zerze zmienionych wierszy, jest drugą strategią i nie daje duplikatów: w teście dwa procesy przejęły 500 różnych zadań, z około 250 ponowień na proces.

Założenia przykładu. Pierwszy z trzech bloków kodu tworzy tabelę zadań z kolumnami ranga, status i aktywowano, indeks filtrowany pod kolejność oraz tabelę dziennika przejęć. Stoi w nim też wariant procedury bez READPAST, który w teście służy do pomiaru czekania. Tabela zadań startuje pusta, więc przed wywołaniem procedury trzeba dopisać własne wiersze ze statusem nowe. Drugi blok to procedura z READPAST, a trzeci to fragment pętli wariantu dwuetapowego, w którym zapis $(proces) jest nazwą procesu podstawianą przez narzędzie sqlcmd.

Instrukcja CREATE OR ALTER wymaga wersji silnika z poziomem zgodności 130 i z dodatkiem Service Pack 1 albo nowszej. Test wykonano na jednej instancji, na poziomach zgodności 130 i 160; starszych wersji silnika nie testowano. Poniżej trzy bloki kodu w kolejności uruchamiania. Pierwszy blok zawiera separator GO, który jest separatorem narzędzia, a nie instrukcją T-SQL.

-- tabela zadań, indeks pod kolejność, dziennik przejęć oraz wariant testowy bez READPAST.
SET NOCOUNT ON;
IF OBJECT_ID(N'dbo.przejecie_przyklad') IS NOT NULL DROP TABLE dbo.przejecie_przyklad;
IF OBJECT_ID(N'dbo.zadanie_przyklad') IS NOT NULL DROP TABLE dbo.zadanie_przyklad;
GO
CREATE TABLE dbo.zadanie_przyklad (
    id         int IDENTITY(1,1) NOT NULL PRIMARY KEY,
    nazwa      nvarchar(50)  NOT NULL,
    ranga      tinyint       NOT NULL,                      -- 3 wysoki, 2 normalny, 1 niski
    aktywne    bit           NOT NULL DEFAULT 1,
    status     varchar(10)   NOT NULL DEFAULT 'nowe',
    wlasciciel varchar(30)   NULL,
    aktywowano datetime2(3)  NOT NULL DEFAULT SYSDATETIME(),
    przejeto   datetime2(3)  NULL
);
CREATE INDEX ix_zadanie_kolejka ON dbo.zadanie_przyklad (ranga DESC, aktywowano, id)
    WHERE aktywne = 1 AND status = 'nowe';
CREATE TABLE dbo.przejecie_przyklad (
    id         int IDENTITY(1,1) PRIMARY KEY,
    proces     varchar(10) NOT NULL,
    zadanie_id int         NOT NULL,
    kiedy      datetime2(3) NOT NULL DEFAULT SYSDATETIME()
);
GO
-- wariant testowy: ta sama instrukcja, ale bez READPAST (druga sesja czeka na blokadę)
CREATE OR ALTER PROCEDURE dbo.nastepne_zadanie_bez_readpast_przyklad @operator varchar(30)
AS
BEGIN
    SET NOCOUNT ON;
    WITH nastepne AS (
        SELECT TOP (1) id, nazwa, ranga, status, wlasciciel, przejeto
        FROM dbo.zadanie_przyklad WITH (UPDLOCK, ROWLOCK)
        WHERE aktywne = 1 AND status = 'nowe'
        ORDER BY ranga DESC, aktywowano, id
    )
    UPDATE nastepne
    SET status = 'w_toku', wlasciciel = @operator, przejeto = SYSDATETIME()
    OUTPUT inserted.id, inserted.nazwa, inserted.ranga, inserted.wlasciciel;
END
GO

Struktura przykładu i wariant testowy bez READPAST.

CREATE OR ALTER PROCEDURE dbo.nastepne_zadanie_przyklad @operator varchar(30)
AS
BEGIN
    SET NOCOUNT ON;
    WITH nastepne AS (
        SELECT TOP (1) id, nazwa, ranga, status, wlasciciel, przejeto
        FROM dbo.zadanie_przyklad WITH (UPDLOCK, READPAST, ROWLOCK)
        WHERE aktywne = 1 AND status = 'nowe'
        ORDER BY ranga DESC, aktywowano, id
    )
    UPDATE nastepne
    SET status = 'w_toku', wlasciciel = @operator, przejeto = SYSDATETIME()
    OUTPUT inserted.id, inserted.nazwa, inserted.ranga, inserted.wlasciciel;
END

Procedura przejęcia następnego zadania z READPAST.

Wariant dwuetapowy, który pokazuje duplikaty, to fragment pętli procesu testowego.

SELECT TOP (1) @id = id FROM dbo.zadanie_przyklad
WHERE aktywne = 1 AND status = 'nowe' ORDER BY ranga DESC, aktywowano, id;
IF @id IS NOT NULL
BEGIN
    WAITFOR DELAY '00:00:00.020';
    UPDATE dbo.zadanie_przyklad
    SET status = 'w_toku', wlasciciel = '$(proces)', przejeto = SYSDATETIME()
    WHERE id = @id;
END

Fragment pętli procesu testowego: odczyt i zapis w dwóch instrukcjach.

Przejęcie zadania w jednej instrukcji zwraca je od razu i nie wymaga drugiego odczytu.

Z READPAST druga sesja bierze kolejne wolne zadanie zamiast czekać na blokadę

Procedura z READPAST składa się z jednej instrukcji UPDATE na wyrażeniu tabelowym: wyrażenie wybiera jedno zadanie aktywne ze statusem nowe, a UPDATE oznacza je jako przejęte przez operatora i zwraca przejęty wiersz klauzulą OUTPUT. Wyrażenie czyta zadania ze wskazówkami UPDLOCK oraz ROWLOCK obok READPAST i układa je według rangi malejąco, potem czasu aktywacji i identyfikatora. READPAST każe pominąć wiersze zablokowane przez inne sesje zamiast na nie czekać. W teście dwóch procesów i 500 zadań ten wariant przejął 500 różnych zadań i nie dał duplikatów. Podział zadań między procesy był zbliżony do połowy, od 239 do 261 zadań na proces, ale zależy od chwili startu i nie jest równy; asercją testu jest brak duplikatów, nie podział.

Czekanie zmierzono osobnym testem. Pierwsza sesja trzymała zadanie 2 w otwartej transakcji przez 4 s, a druga startowała po 1,5 s. Druga sesja z READPAST dostała zadanie 5 po 0 ms na poziomie zgodności 130 i po 1 ms na poziomie 160. Ta sama sesja bez READPAST czekała do zwolnienia blokady, czyli około 2,5 s (2495 i 2498 ms, a w powtórzonym teście od 2492 do 2510 ms). Czas oczekiwania jest więc ograniczony długością przytrzymania blokady i nie jest stałą wariantu: około 2,5 s to reszta czasu, przez który pierwsza sesja trzymała zadanie.

Wariant bez UPDLOCK, w którym zostają READPAST i ROWLOCK, przy 600 zadaniach i czterech procesach też nie dał duplikatów ani błędów, a druga sesja pomijała zablokowane zadanie bez czekania. Wyniki są zgodne na poziomach zgodności 130 i 160; różni się tylko podział zadań między procesy i milisekundy. Poniższa tabela zbiera pomiary, w tym odczyty logiczne omówione w ostatniej sekcji.

Pomiary na poziomach zgodności 130 i 160.

Kolejność według rangi dotyczy tylko zadań, których nie blokuje inna sesja

READPAST pomija wiersze zablokowane, więc druga sesja nie dostaje zadania o najwyższej randze, gdy akurat trzyma je pierwsza. W danych z rangami 3, 3, 2, 2, 1 pierwsza sesja bierze zadanie 1, a druga zadanie 2, czyli drugie o randze 3. W innym teście, z rangami 3, 1, 1, w którym zablokowane było zadanie o randze 3, druga sesja dostała zadanie o randze 1. Kolejność według rangi dotyczy więc zadań, które w danej chwili nie są zablokowane przez inną sesję, i READPAST nie gwarantuje kolejności według rangi.

Przykład (dane wymyślone). W kolejce czeka pięć zadań o rangach 3, 3, 2, 2, 1. Pierwszy operator przejmuje zadanie 1, pierwsze o najwyższej randze, i przez kilka sekund trzyma je w otwartej transakcji. Drugi operator, który prosi o zadanie w tym czasie, z READPAST dostaje od razu zadanie 2, drugie o randze 3, a bez READPAST czeka na zwolnienie blokady.

Pozostałe zadania, o rangach 2, 2, 1, czekają w kolejce. Przy dwuetapowym odczycie i zapisie obaj operatorzy mogą dostać to samo zadanie; ten wyścig opisuje osobny artykuł.

Poziomy SNAPSHOT i SERIALIZABLE kończą READPAST błędem

Procedurę z przykładu uruchomiono na pięciu poziomach izolacji. Trzy z nich przyjmują READPAST, a dwa kończą instrukcję błędem 650.

• Działa. Poziomy READ UNCOMMITTED, READ COMMITTED oraz REPEATABLE READ.
• Błąd 650. Poziomy SNAPSHOT i SERIALIZABLE.

Z włączonym READ_COMMITTED_SNAPSHOT wynik jest taki sam jak bez niego: czekanie drugiej sesji wynosi 0 ms z READPAST i około 2,5 s bez, a 500 zadań w wariancie z jedną instrukcją przejęto bez duplikatów.

Ranga liczbowa porządkuje kolejkę, której tekstowy priorytet sortuje się tylko przypadkiem

Gdy priorytet jest tekstem, sortowanie malejące układa etykiety alfabetycznie. W teście cztery etykiety ułożyły się w kolejności Wysoki, Normalny, Niski, Krytyczny, a nowa etykieta trafiła na koniec. Kolejność trzech pierwszych etykiet zgadza się z oczekiwaniem tylko przypadkiem. W przykładzie ranga jest liczbą całkowitą, w której 3 oznacza priorytet wysoki, 2 normalny, a 1 niski, i zapytanie porządkuje zadania według rangi malejąco, potem według czasu aktywacji i identyfikatora.

• Priorytet tekstowy. Wynik sortowania malejącego: Wysoki, Normalny, Niski, Krytyczny.
• Ranga liczbowa. Wynik sortowania malejącego: Krytyczny, Wysoki, Normalny, Niski.

Indeks filtrowany ogranicza odczyt do zadań, które czekają na przejęcie

Indeks z pierwszego bloku kodu obejmuje tylko zadania aktywne i oczekujące, a jego klucz ma tę samą kolejność co zapytanie: ranga malejąco, czas aktywacji i identyfikator. Jedno przejęcie z tym indeksem wymagało 9 odczytów logicznych przy 20000 zadań zakończonych i 40 oczekujących. Bez indeksu odczytów było od 156 do 192: 156 w pierwszym biegu i 192 w powtórzonym teście. Liczba odczytów bez indeksu zależy od rozmiaru tabeli. Indeks filtrowany i dwie sesje omawia także artykuł o idempotencji wywołania i indeksie filtrowanym.

Odczyty logiczne przy 20000 zadań zakończonych i 40 oczekujących.

Granice wzorca. READPAST nie gwarantuje kolejności według rangi, co opisuje trzecia sekcja. Przykład nie obsługuje zadania przejętego i nieukończonego: zadanie zostaje w stanie „w toku” z czasem przejęcia, a reguła powrotu do kolejki jest osobną decyzją. Sama klauzula OUTPUT na tabeli z wyzwalaczem kończy się błędem 334 dla procedury z przykładu, a rozwiązanie z OUTPUT INTO opisuje artykuł o znaczniku wysłano.

Podział zadań między procesy zależy od chwili startu, a przerwa 20 ms w wariancie dwuetapowym jest umowna. Wyboru jednego wiersza przez TOP i ORDER BY dotyczą też artykuły o stanie ujemnym przy TOP i o wyborze partii z NULL, a pozostałe teksty o T-SQL zbiera przegląd artykułów o SQL Server.

Pytania do analizy

Przed zastosowaniem wzorca odpowiedz na cztery pytania.

1. Czy w kolejce dopuszczalne jest przydzielenie zadania o niższej randze przed zadaniem o wyższej, gdy to wyższe jest akurat zablokowane?
2. Co ma się stać z zadaniem przejętym przez operatora, który się nie zgłosił?
3. Ilu równoległych konsumentów pracuje na kolejce i jak długo trzyma transakcję jeden z nich?
4. Które zapytania w bazie wybierają wiersz do przejęcia osobnym odczytem?