SQL Server – parę słów o TABLOCK

SQLServer_Tablock_00

Dziś nieco zmienimy tematykę i chwilowo zostawiamy Azure Data Studio, ale nie ma czym się martwić bo powiemy sobie o niezwykle interesującej wskazówce jaką jest TABLOCK. Użycie tego konkretnego hinta jest powszechne jednakże w tak zwanym międzyczasie narosło na jego temat kilka mitów. Ponadto wiedza na ten temat nie jest zbyt powszechna dlatego też postanowiłem napisać kilka słów, które mam nadzieję okażą się użyteczne. Tak więc zaczynajmy!

Na wstępie przedstawmy sobie definicję czym tak naprawdę jest TABLOCK. Otóż jest to wskazówka możliwa do użycia w zapytaniu, która powoduje, że blokada zwana powszechnie lockiem z poziomu wiersza lub strony jest przenoszona na poziom tabeli. Czyli upraszczając:

  • przy odczytywaniu danych z tabeli TABLOCK powoduje założenie Shared Lock na tabeli,
  • przy wstawianiu, aktualizacji, usuwaniu danych TABLOCK powoduje założenie Exclusive Lock na tabeli.

Poniżej możecie znaleźć poglądową grafikę na sytuację związaną ze wstawianiem danych:

Nie jest więc prawdą, że TABLOCK zawsze całkowicie blokuje tabelę zakładając exclusive lock, wszystko zależy od tego czy dane odczytujemy czy np. modyfikujemy. Oczywiście istnieje inny zbliżony hint o nazwie TABLOCKX, który właśnie w taki sposób się zachowuje.

W porządku, sprawdźmy zatem powyżej opisane zachowanie na konkretnym przykładzie. W ramach jednej sesji będziemy odpytywać tabelę Fact.OrderHistory pochodzącą z bazy WideWorldImportersDW. Najpierw zrobimy to bez TABLOCKa pisząc standardowe SELECT… FROM:

W tym samym czasie w ramach innej sesji podejrzymy na jakich obiektach zakładane są blokady i jakiego są one typu. Informacje te są oczywiście dostępne w widoku dynamicznym o nazwie sys.dm_tran_locks:

Poniżej możecie zauważyć jaki był efekt tychże działań:

Z otrzymanych efektów możemy odczytać, że blokowane są następujące obiekty:

  • baza,
  • obiekt (czyli tabela),
  • strona.

Przy czym przy każdym odświeżeniu zobaczymy informację, która strona jest w danym momencie blokowana (informacja ta znajduje się w kolumnie resource_description).

No dobrze, wiemy już to co powinniśmy z pierwszego testu. Teraz nieco zmodyfikujemy nasz eksperyment i będziemy odpytywać tabelę z hintem TABLOCK. Warto w tym miejscu zwrócić uwagę na zapis tzn. jeśli specyfikujemy tylko jeden hint to nie musimy dodawać słowa kluczowego WITH np. WITH (TABLOCK) tylko wystarczy samo (TABLOCK) zaraz po nazwie tabeli:

Odpytując w analogiczny sposób wspomniany wcześniej widok dynamiczny możemy zauważyć, że zgodnie z definicją blokady zakładane są na tabeli:

Jak wiemy blokady są również strukturami, które po pierwsze trzeba utrzymywać, a po drugie zajmują one pamięć. Specyfikacja TABLOCKa powoduje, że są one w pewien sposób zoptymalizowane bo są zakładane na wyższym poziomie. Optymalizacja ta jest oczywiście wskazana tylko w wybranych scenariuszach i z całą pewnością znacznie ogranicza współbieżność, która jest kluczowa dla systemów OLTP. Dlatego też omawiana konstrukcja znajduje szczególne zastosowanie w hurtowniach danych gdzie może ona mieć kluczowe znaczenie dla wydajności naszego rozwiązania. Warto również pamiętać, że SQL Server sam może niejako przenieść blokady po przekroczeniu określonego progu na wyższy poziom poprzez tzw. eskalację blokad jednakże TABLOCK włącza takową eskalację od razu.

Plusem wykorzystania omawianej konstrukcji nie jest tylko i wyłącznie optymalizacja blokad ale również optymalizacja samego procesu ładowania danych do tabeli. SQL Server jest systemem wielowątkowym tzn. zapytania które wykonujemy mogą wykorzystywać jeden lub wiele wątków. Chodzi tutaj nie tylko o odczyt i transformacje danych wewnątrz klauzuli SELECT, ale również o wstawianie danych przy pomocy różnych kombinacji polecenia INSERT. Sprawdźmy to na konkretnym przykładzie aby stało się to nieco bardziej jasne.

Zobrazowanie tego będzie nieco trudniejsze ale postaram się to zrobić. Nasze zapytanie testowe wygląda następująco:

Wszystko będzie wykonywane w transakcji, dodatkowo dzięki hintowi HOLDLOCK założone blokady będą “trzymane” aż do zatwierdzenia transakcji dzięki czemu będziemy mogli je podejrzeć. Do tego posłuży nam ten sam widok co powyżej ale tym razem wyświetlimy trochę więcej szczegółów (103 to oczywiście numer sesji z zapytaniem wstawiającym):

Wynik przedstawia się w następujący sposób:

Blokady zostały założone na poziomie wiersza w naszej tabeli będącej stertą (stąd też resource_type=RID). Możemy zatwierdzić transakcję używając COMMIT aby przejść dalej. W dalszym etapie podobnie jak w poprzednim przypadku uruchomimy to samo zapytanie tym razem z TABLOCK:

Efekt:

Zgodnie z przedstawioną przez nas definicją TABLOCK spowodował, że na exclusive lock przy wstawianiu danych zakładany jest na poziomie tabeli, a nie wiersza. To oczywiście nie wszystko! Po zatwierdzeniu otwartej transakcji stwórzmy sobie raz jeszcze bazę danych oraz tabelę do której będziemy wstawiać dane. Dla uproszczenia nasza tabela docelowa będzie stertą:

Do tabeli testowej będziemy wstawiać dane przy pomocy konstrukcji INSERT…SELECT pochodzące z WideWorldImporters, a konkretnie z Fact.OrderHistory:

Po wyświetleniu estymowanego planu wykonania dla powyższego zapytania możemy dostrzec, że nie tylko operatory odczytujące są wykonywane równolegle ale również sam INSERT!

Dla porównania poniżej znajdziecie plan dla analogicznego zapytania tym razem bez TABLOCKa. Jak widać plan (a co za tym idzie sam INSERT) wykonywany jest na jednym wątku:

Powyższe INSERTy wstawiały dane do sterty czyli tabeli bez indeksów. Ciekawie zachowuje się INSERT z TABLOCKiem gdy wstawiamy dane do tabeli ze zgrupowanym indeksem COLUMNSTORE. Aby to zobrazować po raz kolejny przygotujemy sobie odpowiednie struktury w ramach wcześniej stworzonej bazy danych:

Podobnie jak w poprzednim przypadku tak i teraz wstawiamy dane komendą INSERT…SELECT:

Aktualny plan wykonania możecie zobaczyć poniżej. Jak mogliśmy się spodziewać również i w tym przypadku dane były wstawiane równolegle:

W tym konkretnym scenariuszu warto zwrócić uwagę na to co znajdziemy we właściwościach operatora Columnstore Index Insert. Po rozwinięciu Actual Numer of Rows znajdziemy informację ile wierszy wrzuciły do indeksu poszczególne wątki:

Po co nam taka informacja? Indeks kolumnowy logicznie jest podzielony na tzw. grupy wierszy (row groups), wielowątkowość powyższego zapytanie sprawiła, że każdy wątek wstawił dane do osobnej grupy wierszy. Możemy łatwo to potwierdzić odpytując widok sys.column_store_row_groups:

Jak możecie zauważyć na poniższym zrzucie ekranowym powstało dokładnie cztery grupy wierszy zawierające tyle wierszy ile wstawiły poszczególne wątki:

Z teorii indeksu kolumnowego możemy powiedzieć, że grupy wierszy powinny mieć pomiędzy 102400 a 1048576 wierszy, przy czym im więcej wierszy tym lepiej (ze względu na lepsze efekty kompresji).Co się stanie gdy nie przekroczymy progu minimalnego? Dane zostaną umieszczone w specjalnej strukturze o nazwie delta store, która przypomina nieco stertę skompresowaną kompresją PAGE. Ponadto jeśli dane są ładowane do delta store to są w pełni logowane w dzienniku transakcyjnym w przeciwieństwie do minimalnego logowania podczas wstawiania do skompresowanych grup wierszy.

Jeżeli zatem wiemy, że poszczególne wątki nie przekroczą minimalnego progu aby stworzyć grupę wierszy to lepiej będzie nie używać TABLOCKa i wszystko wrzucić jednym wątkiem.

Przetestujmy taką sytuację, wrzućmy kilkoma wątkami 200000 wierszy:

Efekt jest taki, że powstały cztery grupy po 50 tysięcy wierszy każda. Ze względu na fakt, iż nie przekroczyły one minimalnego progu do stworzenia grupy wierszy czyli 102400 mają one status OPEN czyli tak naprawdę znajdują się w Delta Store co oczywiście nie jest tym czego byśmy oczekiwali:

Bez TABLOCKa wszystko zostało wrzucone używając przy pomocy pojedynczego wątku do jednej, skompresowanej grupy wierszy.

Wiemy jak TABLOCK zachowuje się z COLUMNSTORE więc przechodzimy dalej. Powiemy sobie teraz o minimalnym logowaniu wstawiania danych czyli sytuacji gdzie do dziennika transakcyjnego trafiają jedynie dane o alokacji ekstentów,, a nie poszczególne inserty. Jest to o tyle istotne, że dzięki tej optymalizacji nasze zapytania są drastycznie szybsze.

W pierwszym kroku stworzymy naszą testową bazę danych ustawioną w wymaganym dla minimalnego logowania trybie SIMPLE. Ponadto tworzymy stertę do której będziemy wstawiać dane:

Dane będziemy wstawiać używając komendy BULK INSERT, która załaduje milion wierszy z wcześniej przygotowanego pliku płaskiego (plik ten jest eksportem z bazy WideWorldImporters). BULK INSERT jest oczywiście komendą, która może być minimalnie logowana. Sama komenda wygląda następująco:

Po załadowaniu wierszy sprawdzimy zawartość dziennika funkcją fn_dblog:

W efekcie zobaczymy, że w dzienniku znalazły się następujące operacje:

Żadna z operacji nie wskazuje na to, że zalogowana została jakakolwiek informacja związana ze wstawianiem danych. Dla porównania przy wywołaniu zapytania bez TABLOCK sytuacja jest zgoła odmienna:

W dzienniku zalogowana została dodatkowa operacja LOP_INSERT_ROWS. Zalogowany został nasz cały INSERT czyli milion wierszy! Możecie sobie wyobrazić o ile dłużej takie wstawienie trwało.

Tak więc jak widać TABLOCK ma kluczowe znaczenie jeśli chodzi o minimalne logowanie.

Ostatnim tematem jaki chciałbym poruszyć w ramach tego artykułu jest tzw. BU LOCK. Jest to specjalny rodzaj blokady, który zakładany jest przy operacji BULK INSERT. Jest on o tyle specyficzny, że pozwala równolegle z poziomu różnych sesji wstawiać dane do tej samej tabeli docelowej. Samą operację można wywołać na różne sposoby m.in SSISem czy też narzędziem BCP, ja postanowiłem  podobnie jak poprzednio załadować dane z pliku płaskiego z poziomu TSQL komendą BULK INSERT:

Z poziomu drugiej sesji uruchomiłem analogiczne zapytanie, które ładowało inny plik do tej samej tabeli:

Obie sesje otrzymały dostęp do tabeli i nie założyły exclusive locka, a BU lock czyli Bulk Update. Jaka jest między nimi różnica? Przede wszystkim taka, że exclusive locki są ze sobą niekompatybilne, a locki BU już tak! Oznacza to, że przy pomocy Bulk Insert + TABLOCK jesteśmy w stanie ładować dane równolegle z poziomu odrębnych sesji. Takie zrównoleglanie może naprawdę być użyteczne  i w wielu scenariuszach może być kluczowe do osiągnięcia zamierzonych efektów.

Niestety założenie locka BU jest możliwe tylko i wyłącznie z poziomu operacji BULK INSERT, a więc nie dostaniemy ich w przypadku takich konstrukcji jak INSERT SELECT itp. ale nic nie stoi na przeszkodzie aby użyć wspomnianego BULK INSERT,BCP pakietów Integration Services czy też wykorzystać kod niestandardowy z poziomu chociażby C#.

Jak widać TABLOCK jest niezwykle użyteczną konstrukcją i ma niebywałe znaczenie jeśli chodzi o wydajność budowanych przez nas rozwiązań. Moim zdaniem warto wiedzieć jak to wszystko działa żeby po pierwsze użyć tego w prawidłowy sposób, a po drugie wyciągnąć z oprogramowania to co najlepsze. Dzięki za poświęcony czas na lekturę i mam nadzieję, że całość okaże się dla Was użyteczna.

Follow me

Adrian Chodkowski

SQL geek, Data enthusiast, Consultant & Developer
Adrian Chodkowski
Follow me

2 Comments

  1. AvatarMateusz Nadobnik

    Dodam tylko od siebie, że zapis hintów bez WITH jest deprecated od SQL Server 2016 więc unikałbym:) Poza tym świetny wpis, dzięki.

    Reply
    1. Adrian ChodkowskiAdrian Chodkowski (Post author)

      Słuszna uwaga, dodaje adnotacje dzięki! Cieszę się, że artykuł przypadł do gustu.

      Reply

Leave a Comment

Your email address will not be published. Required fields are marked *