SQL Server – parę słów o TABLOCK

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:

SELECT *
FROM [WideWorldImportersDW].[Fact].[OrderHistory]

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:

SELECT 
	resource_type
	,request_session_id
	,request_Type
	,request_status
	,request_mode
	,resource_description
FROM sys.dm_tran_locks
WHERE resource_database_id=DB_ID('WideWorldImportersDW')

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:

SELECT *
FROM [WideWorldImportersDW].[Fact].[OrderHistory](TABLOCK)

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:

BEGIN TRAN 
INSERT INTO dbo.MainFactTable WITH (HOLDLOCK)
SELECT TOP 10 *
FROM [WideWorldImportersDW].[Fact].[OrderHistoryExtended]

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):

select distinct
	resource_type
	,DB_NAME(resource_database_id) AS DatabaseName
	,resource_description
	,request_session_id
	,request_Type
	,request_status
	,request_mode
from sys.dm_tran_locks (NOLOCK)
where resource_database_id=DB_ID('SQLDay2019_ParallelInsert')
 and request_session_id=103

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:

BEGIN TRAN 
INSERT INTO dbo.MainFactTable WITH (TABLOCK,HOLDLOCK)
SELECT TOP 10 *
FROM [WideWorldImportersDW].[Fact].[OrderHistoryExtended]

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ą:

SET STATISTICS TIME OFF;
SET NOCOUNT ON;

use master 
go

DROP DATABASE IF EXISTS ParallelInsert
GO

CREATE DATABASE ParallelInsert
GO

ALTER DATABASE ParallelInsert
SET RECOVERY SIMPLE
GO

use ParallelInsert
GO

CREATE TABLE dbo.MainFactTable(
	[Order Key] [bigint] NOT NULL,
	[City Key] [int] NOT NULL,
	[Customer Key] [int] NOT NULL,
	[Stock Item Key] [int] NOT NULL,
	[Order Date Key] [date] NOT NULL,
	[Picked Date Key] [date] NULL,
	[Salesperson Key] [int] NOT NULL,
	[Picker Key] [int] NULL,
	[WWI Order ID] [int] NOT NULL,
	[WWI Backorder ID] [int] NULL,
	[Description] [nvarchar](100) NOT NULL,
	[Package] [nvarchar](50) NOT NULL,
	[Quantity] [int] NOT NULL,
	[Unit Price] [decimal](18, 2) NOT NULL,
	[Tax Rate] [decimal](18, 3) NOT NULL,
	[Total Excluding Tax] [decimal](18, 2) NOT NULL,
	[Tax Amount] [decimal](18, 2) NOT NULL,
	[Total Including Tax] [decimal](18, 2) NOT NULL,
	[Lineage Key] [int] NOT NULL
) 
GO

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

INSERT INTO dbo.MainFactTable WITH (TABLOCK)
SELECT *
FROM [WideWorldImportersDW].[Fact].[OrderHistory]
GO

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:

DROP TABLE IF EXISTS dbo.CCI
GO

CREATE TABLE dbo.CCI(
	[Order Key] [bigint] NOT NULL,
	[City Key] [int] NOT NULL,
	[Customer Key] [int] NOT NULL,
	[Stock Item Key] [int] NOT NULL,
	[Order Date Key] [date] NOT NULL,
	[Picked Date Key] [date] NULL,
	[Salesperson Key] [int] NOT NULL,
	[Picker Key] [int] NULL,
	[WWI Order ID] [int] NOT NULL,
	[WWI Backorder ID] [int] NULL,
	[Description] [nvarchar](100) NOT NULL,
	[Package] [nvarchar](50) NOT NULL,
	[Quantity] [int] NOT NULL,
	[Unit Price] [decimal](18, 2) NOT NULL,
	[Tax Rate] [decimal](18, 3) NOT NULL,
	[Total Excluding Tax] [decimal](18, 2) NOT NULL,
	[Tax Amount] [decimal](18, 2) NOT NULL,
	[Total Including Tax] [decimal](18, 2) NOT NULL,
	[Lineage Key] [int] NOT NULL
)
GO

CREATE CLUSTERED COLUMNSTORE INDEX IX_CCI ON dbo.CCI
GO

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

INSERT INTO dbo.CCI WITH (TABLOCK)
SELECT *
FROM [WideWorldImportersDW].[Fact].[OrderHistory]
GO

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:

SELECT 
	* 
FROM sys.column_store_row_groups
GO

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:

INSERT INTO dbo.CCI WITH (TABLOCK)
SELECT TOP 200000 *
FROM [WideWorldImportersDW].[Fact].[OrderHistory]
GO

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:

use master
GO

DROP DATABASE IF EXISTS test
GO

CREATE DATABASE test
GO

ALTER DATABASE test SET RECOVERY SIMPLE 
WITH NO_WAIT
GO

USE test
GO

CREATE TABLE [dbo].[SourceTable](
	[Movement Key] [bigint] NULL,
	[Date Key] [date] NULL,
	[Stock Item Key] [int] NULL,
	[Customer Key] [int] NULL,
	[Supplier Key] [int] NULL,
	[Transaction Type Key] [int] NULL,
	[WWI Stock Item Transaction ID] [int] NULL,
	[WWI Invoice ID] [int] NULL,
	[WWI Purchase Order ID] [int] NULL,
	[Quantity] [int] NULL,
	[Lineage Key] [int] NULL
) ON [PRIMARY]
GO

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:

BULK INSERT dbo.SourceTable
	FROM   'C:\Users\adria\Desktop\Files\SourceData_1mln.txt'
	WITH
	(
		FIRSTROW=2,
		FIELDTERMINATOR = ',',
		ROWTERMINATOR = '\n',
		TABLOCK 
	)

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

SELECT 
	 'HEAP + BULK INSERT + TABLOCK' AS Operation
	,[Operation]
	,COUNT(*) AS NoOfRows
	,SUM([Log Record Length])
FROM fn_dblog(NULL,NULL)
where allocunitname like 'dbo.SourceTable%'
GROUP BY [Operation]
GO

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:

BULK INSERT dbo.SourceTable
	FROM   'C:\Users\adria\Desktop\Files\SQLDay2019\Files\SourceData_1mln.txt'
	WITH
	(
		FIRSTROW=2,
		FIELDTERMINATOR = ',',
		ROWTERMINATOR = '\n' 
	)

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:

BULK INSERT dbo.SourceTable
	FROM   'C:\Users\adria\Desktop\Files\src.txt'
	WITH
	(
		FIRSTROW=2,
		FIELDTERMINATOR = ',',
		ROWTERMINATOR = '\n',
		TABLOCK 
	)
GO

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.