Free Press

Blokowanie jest niezbędne do pomyślnego przetwarzania transakcji w SQL Server i zostało zaprojektowane tak, aby umożliwić SQL Server bezproblemową pracę w środowisku wieloużytkownikowym. Blokady to sposób, w jaki SQL Server zarządza współbieżnością transakcji. Zasadniczo, blokady są strukturami pamięciowymi, które posiadają właściciela, typ oraz hash zasobu, który mają chronić. Blokada jako struktura in-memory ma rozmiar 96 bajtów.

Aby lepiej zrozumieć blokowanie w SQL Server, należy zrozumieć, że blokowanie ma na celu zapewnienie integralności danych w bazie danych, ponieważ zmusza każdą transakcję SQL Server do przejścia testu ACID.

Test ACID składa się z 4 wymagań, które każda transakcja musi przejść pomyślnie:

• Atomowość – wymaga, aby transakcja, która obejmuje dwie lub więcej dyskretnych części informacji, zobowiązała wszystkie części lub żadną
• Spójność – wymaga, aby transakcja utworzyła poprawny stan nowych danych lub cofnęła wszystkie dane do stanu, który istniał przed wykonaniem transakcji
• Izolacja – wymaga, aby transakcja, która wciąż trwa i nie zobowiązała jeszcze wszystkich danych, musi pozostać odizolowana od wszystkich innych transakcji
• Trwałość – wymaga, aby zaangażowane dane były przechowywane przy użyciu metody, która zachowa wszystkie dane w poprawnym stanie i dostępne dla użytkownika, nawet w przypadku awarii

Blokowanie SQL Server jest zasadniczą częścią wymogu izolacji i służy do blokowania obiektów, których dotyczy transakcja. Podczas gdy obiekty są zablokowane, SQL Server uniemożliwi innym transakcjom dokonywanie jakichkolwiek zmian danych przechowywanych w obiektach, których dotyczy nałożona blokada. Gdy blokada zostanie zwolniona przez zatwierdzenie zmian lub przez cofnięcie zmian do stanu początkowego, inne transakcje będą mogły dokonywać wymaganych zmian danych.

W tłumaczeniu na język SQL Server oznacza to, że gdy transakcja nałoży blokadę na obiekt, wszystkie inne transakcje, które wymagają dostępu do tego obiektu, będą zmuszone czekać, aż blokada zostanie zwolniona, a to oczekiwanie zostanie zarejestrowane z odpowiednim typem oczekiwania

Blokady SQL Server można określić za pomocą trybów blokady i granularności blokady

Tryby blokady

Tryb blokady uwzględnia różne typy blokady, które można zastosować do zasobu, który musi zostać zablokowany:

• Exclusive (X)
• Shared (S)
• Update (U)
• Intent (I)
• Schema (Sch)
• Bulk update (BU)

Exclusive lock (X) – Ten typ blokady, po nałożeniu zapewni, że strona lub wiersz będą zarezerwowane wyłącznie dla transakcji, która nałożyła blokadę wyłączną, tak długo jak transakcja posiada tę blokadę.

Blokada wyłączna zostanie nałożona przez transakcję, gdy będzie ona chciała zmodyfikować stronę lub dane wiersza, co ma miejsce w przypadku instrukcji DML DELETE, INSERT i UPDATE. Blokada wyłączna może być nałożona na stronę lub wiersz tylko wtedy, gdy nie ma innej współdzielonej lub wyłącznej blokady nałożonej już na cel. Praktycznie oznacza to, że tylko jedna blokada wyłączna może być nałożona na stronę lub wiersz, a raz nałożona żadna inna blokada nie może być nałożona na zablokowane zasoby

Blokada współdzielona (S) – ten typ blokady, po nałożeniu, zarezerwuje stronę lub wiersz do bycia dostępnym tylko do odczytu, co oznacza, że jakakolwiek inna transakcja nie będzie mogła modyfikować zablokowanego rekordu tak długo jak blokada jest aktywna. Jednak współdzielona blokada może być nałożona przez kilka transakcji w tym samym czasie na tę samą stronę lub wiersz i w ten sposób kilka transakcji może współdzielić zdolność do odczytu danych, ponieważ sam proces odczytu nie będzie miał żadnego wpływu na rzeczywiste dane strony lub wiersza. Dodatkowo, współdzielona blokada pozwoli na operacje zapisu, ale żadne zmiany DDL nie będą dozwolone

Blokada aktualizacji (U) – ta blokada jest podobna do blokady wyłącznej, ale jest zaprojektowana tak, aby była bardziej elastyczna w pewnym sensie. Blokada aktualizacji może być nałożona na rekord, który ma już współdzieloną blokadę. W takim przypadku, blokada aktualizacji nałoży kolejną współdzieloną blokadę na wiersz docelowy. Gdy transakcja, która posiada blokadę aktualizacji jest gotowa do zmiany danych, blokada aktualizacji (U) zostanie przekształcona w blokadę wyłączną (X). Ważne jest, aby zrozumieć, że blokada aktualizacji jest asymetryczna w stosunku do blokad współdzielonych. Podczas gdy blokada aktualizacji może być nałożona na rekord, który posiada blokadę współdzieloną, blokada współdzielona nie może być nałożona na rekord, który już posiada blokadę aktualizacji

Lokady intencyjne (I) – blokada ta jest środkiem używanym przez transakcję do informowania innej transakcji o zamiarze nabycia blokady. Celem takiej blokady jest zapewnienie poprawnego wykonania modyfikacji danych poprzez uniemożliwienie innej transakcji nabycia blokady na następny w hierarchii obiekt. W praktyce, gdy transakcja chce nabyć blokadę na wierszu, nabywa intencjonalną blokadę na tabeli, która jest obiektem o wyższej hierarchii. Nabywając intencjonalną blokadę, transakcja nie pozwoli innym transakcjom na nabycie wyłącznej blokady na tej tabeli (w przeciwnym razie wyłączna blokada nałożona przez jakąś inną transakcję anulowałaby blokadę wiersza).

Jest to ważny typ blokady z punktu widzenia wydajności, ponieważ silnik bazy danych SQL Server będzie sprawdzał blokady intencji tylko na poziomie tabeli, aby sprawdzić, czy możliwe jest, aby transakcja nabyła blokadę w bezpieczny sposób w tej tabeli, a zatem blokada intencji eliminuje potrzebę sprawdzania każdej blokady wiersza / strony w tabeli, aby upewnić się, że transakcja może nabyć blokadę na całej tabeli

Istnieją trzy zwykłe blokady intencji i trzy tak zwane blokady konwersji:

Regular intent locks:

Intentent exclusive (IX) – gdy nabyta jest intencja exclusive lock (IX) wskazuje ona serwerowi SQL, że transakcja ma zamiar modyfikować niektóre zasoby niższej hierarchii poprzez indywidualne nabywanie zamków exclusive (X) na tych zasobach niższej hierarchii

Intent shared (IS) – gdy nabyta jest intencja shared lock (IS) wskazuje ona serwerowi SQL, że transakcja ma. zamiar odczytać niektóre zasoby niższej hierarchii poprzez indywidualne nabywanie zamków współdzielonych (S) na tych zasobach niżej w hierarchii

Intentent update (IU) – gdy nabywany jest intencjonalny zamek współdzielony (IS) wskazuje on SQL Serverowi, że transakcja ma zamiar odczytać niektóre zasoby niższej hierarchii poprzez indywidualne nabywanie zamków współdzielonych (S) na tych zasobach niżej w hierarchii. Blokada intencji aktualizacji (IU) może być nabyta tylko na poziomie strony i zaraz po wykonaniu operacji aktualizacji przekształca się w blokadę intencji wyłączności (IX)

Zamki konwersji:

Shared with intent exclusive (SIX) – gdy jest nabyta, blokada ta wskazuje, że transakcja zamierza odczytać wszystkie zasoby na niższym poziomie hierarchii, a więc nabyć blokadę współdzieloną na wszystkich zasobach, które są niżej w hierarchii, a z kolei zmodyfikować część z nich, ale nie wszystkie. W ten sposób nabędzie intencjonalną wyłączną (IX) blokadę na te zasoby o niższej hierarchii, które powinny zostać zmodyfikowane. W praktyce oznacza to, że gdy transakcja nabędzie blokadę SIX na tabeli, nabędzie intencjonalną blokadę wyłączną (IX) na zmodyfikowanych stronach i wyłączną (X) na zmodyfikowanych wierszach.

Tylko jedna współdzielona z intencją wyłączna blokada (SIX) może być nabyta na tabeli w tym samym czasie i będzie blokować inne transakcje przed dokonywaniem aktualizacji, ale nie uniemożliwi innym transakcjom odczytu zasobów niższej hierarchii, które mogą nabyć intencję współdzieloną (IS) blokady na tabeli

Współdzielona z intencją aktualizacji (SIU) – jest to nieco bardziej specyficzny zamek, ponieważ jest to połączenie współdzielonych (S) i intencji aktualizacji (IU) zamków. Typowym przykładem tej blokady jest sytuacja, gdy transakcja używa zapytania wykonanego z podpowiedzią PAGELOCK i zapytaniem, a następnie zapytaniem aktualizującym. Po nabyciu przez transakcję blokady SIU na tabeli, zapytanie z podpowiedzią PAGELOCK nabędzie blokadę współdzieloną (S), podczas gdy zapytanie aktualizujące nabędzie blokadę intencji aktualizacji (IU)

Aktualizacja z intencją wyłączności (UIX) – gdy blokady aktualizacji (U) i intencji wyłączności (IX) są nabyte na niższych zasobach hierarchii w tabeli jednocześnie, blokada aktualizacji z intencją wyłączności zostanie nabyta na poziomie tabeli jako konsekwencja

Lokady schematu (Sch) – silnik bazy danych SQL Server rozpoznaje dwa typy blokad schematu: Schema modification lock (Sch-M) oraz Schema stability lock (Sch-S)

• Blokada modyfikacji schematu (Sch-M) zostanie nabyta w momencie wykonania instrukcji DDL i uniemożliwi dostęp do danych zablokowanego obiektu, ponieważ jego struktura jest zmieniana. SQL Server pozwala na pojedynczą blokadę modyfikacji schematu (Sch-M) na dowolnym zablokowanym obiekcie. Aby zmodyfikować tabelę, transakcja musi poczekać na uzyskanie blokady Sch-M na obiekcie docelowym. Po uzyskaniu blokady modyfikacji schematu (Sch-M), transakcja może modyfikować obiekt, a po zakończeniu modyfikacji blokada zostanie zwolniona. Typowym przykładem blokady Sch-M jest przebudowa indeksu, ponieważ przebudowa indeksu jest procesem modyfikacji tabeli. Gdy zostanie wydany identyfikator przebudowy indeksu, blokada modyfikacji schematu (Sch-M) zostanie nabyta na tej tabeli i zostanie zwolniona dopiero po zakończeniu procesu przebudowy indeksu (gdy użyta zostanie opcja ONLINE, przebudowa indeksu nabędzie blokadę Sch-M tuż po zakończeniu procesu)
• Blokada stabilności schematu (Sch-S) zostanie nabyta podczas kompilacji i wykonywania zapytania zależnego od schematu oraz generowania planu wykonania. Ta konkretna blokada nie blokuje innym transakcjom dostępu do danych obiektu i jest kompatybilna ze wszystkimi trybami blokady z wyjątkiem blokady modyfikacji schematu (Sch-M). Zasadniczo blokady stabilności schematu będą nabywane przez każde zapytanie DML i select w celu zapewnienia integralności struktury tabeli (zapewnienia, że tabela nie ulegnie zmianie podczas wykonywania zapytań).

Bulk Update locks (BU) – ta blokada jest zaprojektowana do użycia przez operacje masowego importu, gdy jest wydana z argumentem/wskazówką TABLOCK. Kiedy blokada aktualizacji zbiorczej jest nabyta, inne procesy nie będą mogły uzyskać dostępu do tabeli podczas wykonywania ładowania zbiorczego. Jednakże, blokada aktualizacji zbiorczej nie przeszkodzi w równoległym przetwarzaniu innego zbiorczego obciążenia. Pamiętaj, że użycie TABLOCK na indeksie klastrowym nie pozwoli na równoległy import zbiorczy. Więcej szczegółów na ten temat dostępnych jest w Guidelines for Optimizing Bulk Import

Hierarchia blokad

SQL Server wprowadził hierarchię blokad, która jest stosowana podczas odczytu lub zmiany danych. Hierarchia blokad zaczyna się od bazy danych na najwyższym poziomie hierarchii i w dół przez tabelę i stronę do wiersza na najniższym poziomie

Zasadniczo zawsze istnieje współdzielona blokada na poziomie bazy danych, która jest nakładana za każdym razem, gdy transakcja jest podłączona do bazy danych. Współdzielona blokada na poziomie bazy danych jest nakładana, aby zapobiec upuszczaniu bazy danych lub przywracaniu kopii zapasowej bazy danych nad używaną bazą danych. Na przykład, gdy instrukcja SELECT zostanie wydana w celu odczytania pewnych danych, na poziomie bazy danych zostanie nałożona współdzielona blokada (S), na poziomie tabeli i na poziomie strony zostanie nałożona intencjonalna współdzielona blokada (IS), a na poziomie samego wiersza zostanie nałożona współdzielona blokada (S)

W przypadku instrukcji DML (tj. insert, update, delete) na poziomie bazy danych zostanie nałożona blokada współdzielona (S), na poziomie tabeli i strony zostanie nałożona blokada wyłączna (IX) lub blokada aktualizacji (IU), a na wierszu blokada wyłączna lub aktualizacyjna (X lub U)

Blokady będą zawsze nabywane od góry do dołu, gdyż w ten sposób SQL Server zapobiega wystąpieniu tzw. warunku Race.

Teraz, gdy tryby blokad i hierarchia blokad zostały wyjaśnione, przejdźmy do omówienia trybów blokad i tego, jak przekładają się one na hierarchię blokad.

Nie wszystkie tryby blokady mogą być stosowane na wszystkich poziomach.

Na poziomie wiersza można zastosować następujące trzy tryby blokady:

• Wyłączny (X)
• Współdzielony (S)
• Aktualizacja (U)

Aby zrozumieć zgodność tych trybów, należy zapoznać się z poniższą tabelą:

✓ – Compatible ✗ – Incompatible

Na poziomie tabeli, istnieje pięć różnych typów zamków:

• Exclusive (X)
• Shared (S)
• Intent exclusive (IX)
• Intent shared (IS)
• Shared with intent exclusive (SIX)

Zgodność tych trybów można zobaczyć w poniższej tabeli

.

✓ – Zgodny ✗ – Niezgodny

Blokada schematu (Sch) jest również blokadą poziomu tabeli, ale nie jest to blokada związana z danymi

Aby lepiej zrozumieć kompatybilność między tymi typami blokad, zapoznaj się z poniższą tabelą:

Eskalacja blokady

Aby zapobiec sytuacji, w której blokada zużywa zbyt wiele zasobów, SQL Server wprowadził funkcję eskalacji blokady.

Bez eskalacji, blokady mogłyby wymagać znacznej ilości zasobów pamięci. Weźmy przykład, w którym należy nałożyć blokadę na 30 000 wierszy danych, gdzie każdy wiersz ma rozmiar 500 bajtów, w celu wykonania operacji usunięcia. Bez eskalacji, współdzielona blokada (S) zostanie nałożona na bazę danych, 1 zamierzona wyłączna blokada (IX) na tabelę, 1,875 zamierzonych wyłącznych blokad (IX) na strony (strona 8KB mieści 16 wierszy po 500 bajtów, co daje 1,875 stron, które mieszczą 30,000 wierszy) i 30,000 wyłącznych blokad (X) na same wiersze. Ponieważ każdy zamek ma rozmiar 96 bajtów, 31 877 zamków zajmie około 3 MB pamięci dla pojedynczej operacji usuwania. Wykonywanie dużej liczby operacji równolegle może wymagać znacznych zasobów tylko po to, aby zapewnić, że menedżer blokad może wykonywać operacje płynnie

Aby zapobiec takiej sytuacji, SQL Server stosuje eskalację blokad. Oznacza to, że w sytuacji, gdy na jednym poziomie nabytych zostanie więcej niż 5000 blokad, SQL Server eskaluje te blokady do pojedynczej blokady na poziomie tabeli. Domyślnie, SQL Server zawsze eskaluje blokady bezpośrednio do poziomu tabeli, co oznacza, że eskalacja do poziomu strony nigdy nie występuje. Zamiast pozyskiwać liczne blokady wierszy i stron, SQL Server będzie eskalował do blokady wyłącznej (X) na poziomie tabeli

Chociaż zmniejszy to zapotrzebowanie na zasoby, blokady wyłączne (X) w tabeli oznaczają, że żadna inna transakcja nie będzie mogła uzyskać dostępu do zablokowanej tabeli, a wszystkie zapytania próbujące uzyskać dostęp do tej tabeli zostaną zablokowane. Dlatego zmniejszy to narzut systemu, ale zwiększy prawdopodobieństwo kontrowersji współbieżności

Aby zapewnić kontrolę nad eskalacją, począwszy od SQL Server 2008 R2, wprowadzono opcję LOCK_EXCALATION jako część instrukcji ALTER TABLE

Każda z tych opcji jest zdefiniowana w celu umożliwienia określonej kontroli nad procesem eskalacji blokady:

Table – Jest to opcja domyślna dla każdej nowo utworzonej tabeli, ponieważ domyślnie SQL Server zawsze będzie wykonywał eskalację blokad do poziomu tabeli, co obejmuje również tabele partycjonowane

Auto – Opcja ta pozwala na eskalację blokad do poziomu partycji, gdy tabela jest partycjonowana. Gdy 5000 blokad zostanie nabytych w pojedynczej partycji, eskalacja blokady nabędzie wyłączną blokadę (X) na tej partycji, podczas gdy tabela nabędzie intencjonalną wyłączną blokadę (IX). W przypadku, gdy tabela nie jest podzielona na partycje, eskalacja blokady nabędzie blokadę na poziomie tabeli (równą opcji Tabela).

Chociaż wygląda to na bardzo użyteczną opcję, musi być używana bardzo ostrożnie, ponieważ łatwo może spowodować deadlock. W sytuacji, gdy mamy dwie transakcje na dwóch partycjach, w których nabyta jest blokada wyłączna (X), a transakcja próbuje uzyskać dostęp do daty z partycji używanej przez inną transakcję, nastąpi deadlock

Tak więc, bardzo ważne jest, aby dokładnie kontrolować wzorzec dostępu do danych, jeśli ta opcja jest włączona, co nie jest łatwe do osiągnięcia i dlatego ta opcja nie jest domyślnym ustawieniem w SQL Server

Disable – Ta opcja całkowicie wyłączy eskalację blokady dla tabeli. Ponownie, opcja ta musi być używana ostrożnie, aby uniknąć sytuacji, w której menedżer blokad SQL Server będzie zmuszony do użycia nadmiernej ilości pamięci

Jak widać, eskalacja blokad może być wyzwaniem dla DBA. Jeśli projekt aplikacji wymaga usunięcia lub aktualizacji więcej niż 5000 wierszy jednocześnie, rozwiązaniem pozwalającym uniknąć eskalacji blokad i wynikających z niej skutków jest podzielenie pojedynczej transakcji na dwie lub więcej transakcji, z których każda będzie obsługiwać mniej niż 5000 wierszy, ponieważ w ten sposób można uniknąć eskalacji blokad

Uzyskaj informacje o aktywnych blokadach SQL Server

SQL Server udostępnia widok Dynamics Management View (DMV) sys.dm_tran_locks, który zwraca informacje o aktualnie używanych zasobach menedżera blokad, co oznacza, że wyświetli wszystkie „żywe” blokady przejęte przez transakcje. Więcej szczegółów na temat tego DMV można znaleźć w artykule sys.dm_tran_locks (Transact-SQL).

Najważniejszymi kolumnami używanymi do identyfikacji blokady są resource_type, request_mode oraz resource_description. W razie potrzeby, więcej kolumn jako dodatkowy zasób informacji może być dołączonych podczas rozwiązywania problemów

Oto przykład zapytania

Klauzula where w tym zapytaniu jest używana jako filtr na resource_type do wyeliminowania.

Krótkie wyjaśnienie trzech kolumn przedstawionych tutaj:

resource_type – Wyświetla zasób bazy danych, w którym nabywane są zamki. Kolumna może wyświetlać jedną z następujących wartości: ALLOCATION_UNIT, APPLICATION, DATABASE, EXTENT, FILE, HOBT, METADATA, OBJECT, PAGE, KEY, RID

request_mode – wyświetla tryb blokady, która jest nabywana na zasobie

resource_description – wyświetla krótki opis zasobu i nie jest uzupełniana dla wszystkich trybów blokady. Najczęściej kolumna zawiera id wiersza, strony, obiektu, pliku, etc

• Autor
• Recent Posts

Nikola jest komputerowym freakiem od 1981 roku i entuzjastą SQL z zamiarem zostania freakiem. Specjalizuje się w audycie SQL Server, monitorowaniu zgodności i wydajności.
Wielbiciel lotnictwa wojskowego i modelarz samolotów w skali naturalnej. Fan sportów ekstremalnych; spadochroniarz i instruktor skoków na bungee. Kiedyś poważny, teraz tylko fotograf w wolnym czasie
View all posts by Nikola Dimitrijevic

Latest posts by Nikola Dimitrijevic (see all)

• Przewodnik po flagach trace SQL Server; od -1 do 840 – March 4, 2019
• Jak obsługiwać typ oczekiwania SQL Server WRITELOG – June 13, 2018
• Liczniki wydajności SQL Server (Batch Requests/sec lub Transactions/sec): co monitorować i dlaczego – 5 czerwca 2018

.