Simon Learning SQL Server

Simon Learning SQL Server

w poprzednim poście mówiłem o blokowaniu, blokowaniu i poziomach izolacji. W tym poście zamierzam przejść do bardziej szczegółowych informacji na temat zamków z zakresu kluczy używanych w SERIALIZOWALNYM poziomie izolacji, a także zademonstrować użycie SYS. dm_tran_locks DMV do przeglądania istniejących zamków, a także jak dowiedzieć się, na jakich zasobach zamki są umieszczone.

zamki typu Key-range są używane tylko w SERIALIZOWALNYM poziomie izolacji i ich celem jest zatrzymanie odczytów fantomowych, które są możliwe w pozostałych trzech pesymistycznych poziomach izolacji współbieżności, a mianowicie odczytu niezaangażowanego, odczytu popełnionego i odczytu powtarzalnego. Odczyt fantomowy to sytuacja, w której jedna transakcja wykonuje odczyt z zakresu danych, np. za pomocą zapytania z klauzulą WHERE, a inna transakcja dodaje dane, które spełnią warunki klauzuli WHERE. Jeśli pierwsza transakcja wykona ten sam odczyt, zwróci teraz nowe dane, w wyniku czego dane widmowe pojawią się w drugim odczycie w ramach tej samej transakcji. Zamki typu key-range służą do zapobiegania dodawaniu danych phantom i są umieszczane na kluczach indeksu, który jest używany do pobierania danych. Poniższy przykład przy użyciu osoby.Tabela osób w bazie danych AdventureWorks2012 pokaże, w jaki sposób wykorzystywane są te zamki.

Osoba.Tabela osób ma nieklastrowany indeks IX_Person_LastName_FirstName_Middlename z kolumną LastName jako pierwszą kolumną w kluczu indeksu. Oznacza to, że każde zapytanie, w którym filtrujemy LastName, powinno użyć tego indeksu do pobrania danych.

jeśli uruchomimy zapytanie, aby zwrócić kilka wierszy od osoby.Osoba filtrująca w zakresie nazwisk, wtedy powinniśmy zobaczyć, że ten indeks jest używany. Na przykład

SELECT *FROM Person.PersonWHERE LastName BETWEEN 'Ware' AND 'Warthen'ORDER BY LastName

zwraca następujący zestaw wyników.

 zakres kluczy 2

patrząc na plan wykonania możemy zauważyć, że ten indeks jest używany.

 zakres kluczy 3

teraz, jeśli ustawimy poziom izolacji na SERIALIZOWALNY, otworzymy transakcję i uruchomimy tę samą instrukcję select, powinniśmy być w stanie zobaczyć, jakie blokady są trzymane.

SET TRANSACTION ISOLATION LEVEL SERIALIZABLEBEGIN TRANSELECT *FROM Person.PersonWHERE LastName BETWEEN 'Ware' AND 'Warthen'

teraz z innego połączenia możemy odpytywać DMV sys.dm_tran_locks, aby zobaczyć bieżące blokady. Interesują nas tylko Zamki na kluczowych zasobach, więc odpowiednio filtrujemy.

SELECT *FROM sys.dm_tran_locksWHERE resource_type = 'KEY'ORDER BY request_mode

zakres kluczy 4

dostajemy siedem wierszy retuned z dwoma różnymi wartościami request_mode. Request_mode ” s ” odpowiada blokadom współdzielonym umieszczonym w trzech zwracanych wierszach. Ponieważ używamy SERIALIZOWALNEGO poziomu izolacji, te blokady współdzielone są zatrzymywane, dopóki transakcja nie zatwierdzi lub nie cofnie się, aby zapobiec nie powtarzalnym odczytom. Request_mode „RangeS-s” odpowiada blokadom key-range, które zostały przyznane. Ale dlaczego są cztery rzędy? Aby odpowiedzieć na to pytanie, Możemy użyć danych zwróconych z sys. dm_tran_locks i użyć tego do ustalenia, jakie zasoby zostały faktycznie zablokowane.

kolumny, które nas interesują to resource_associated_entity_id i resource_description. Dane w tych kolumnach mogą się różnić w zależności od resource_type, a więcej szczegółów na ten temat można znaleźć w TechNet. Dla kluczowych zasobów resource_associated_entity_id odpowiada identyfikatorowi partition_id z sys.przegrody

SELECT *FROM sys.partitionsWHERE partition_id in (72057594057523200, 72057594045595648)

zakres kluczy 5

zwrócony object_id odpowiada osobie.Stół osobowy

SELECT object_name(1765581328)

zakres kluczy 6

widzimy, że dwie różne wartości partition_id odpowiadają różnym indeksom w Person.Osoba

SELECT * FROM sys.indexes WHERE object_id = 1765581328

zakres kluczy 7

sys.widok partycji mówi nam, że partycje, na których mamy blokady, są przeznaczone dla indeksów PK_Person_BusinessEntityID i IX_Person_LastName_FirstName_Middlename.

możemy połączyć to wszystko w jednym zapytaniu dla jasności.

 SELECT L.resource_type ,L.resource_description ,L.resource_associated_entity_id ,L.request_mode ,L.request_type ,L.request_status ,OBJECT_NAME(I.object_id) AS TableName ,I.object_id ,I.index_id ,I.name AS IndexName ,I.type_desc FROM sys.dm_tran_locks L INNER JOIN sys.partitions P ON P.partition_id = L.resource_associated_entity_id INNER JOIN sys.indexes I ON I.object_id = P.object_id AND I.index_id = P.index_id WHERE L.resource_type = 'KEY' ORDER BY L.request_mode

zakres kluczy 8

to pokazuje nam, że trzy wspólne zamki są umieszczone na samych rzędach (poziom liścia indeksu klastrowego) i że cztery zamki z zakresem kluczy są umieszczone na kluczach w indeksie nieklastrowym. Ale to nie odpowiada na pytanie, dlaczego mamy cztery zamki?

zanim to zrobimy, zademonstruję dwa sposoby udowodnienia, że trzy wspólne blokady są umieszczone w wierszach, które zostały zwrócone przez oryginalne zapytanie, a następnie użyj tego, aby dowiedzieć się, na jakich zasobach znajdują się cztery blokady „RangeS-S”.

najpierw użyjemy wartości %%physloc%% i %%lockres%% wraz z sys.funkcja fn_physlocformatter () informuje nas o pliku, stronie i szczelinie wierszy zwróconych przez oryginalne zapytanie. Po drugie używamy tej informacji w Komendzie strony DBCC.

musimy tylko zaktualizować nasze oryginalne zapytanie w następujący sposób

SELECT sys.fn_PhysLocFormatter(%%physloc%%) AS FilePageSlot, %%lockres%% AS LockResource, *FROM Person.PersonWHERE LastName BETWEEN 'Ware' AND 'Warthen'

zakres kluczy 9

możemy teraz zobaczyć, że wiersz dla Dennisa Ware ’ a znajduje się fizycznie w pliku danych 1, na stronie 2806 i na slocie 3. Możemy również zobaczyć, że jest to zasób blokady (05c585e839d4).

jeśli spojrzymy wstecz na dane zwrócone z sys.dm_tran_locks, zobaczymy, że jeden z współdzielonych blokad ma resource_description (05c585e839d4), więc to mówi nam, że blokada jest umieszczona w tym wierszu w indeksie klastrowym.

teraz możemy uruchomić następujące polecenie DBCC PAGE, aby zobaczyć informacje o tym, co jest na stronie, ale aby to zwróciło jakiekolwiek wyniki, musimy włączyć trace flag 3604.

DBCC TRACEON (3604);DBCC PAGE (10, 1, 2806, 3); -- DB_ID, FileId, PageId, Format

tutaj jest wyświetlane wiele informacji tekstowych i możesz określić inną liczbę w parametrze format, aby zwrócić różne ilości danych. W tym przypadku pierwszym bitem jest nagłówek strony, a następnie mamy Status alokacji. Zobaczysz, że pierwszy użyty slot to Slot 0. Istnieje wartość offsetu, która mówi nam, gdzie na stronie zaczyna się ten slot i wartość długości, która mówi nam, jak długi jest rekord.

 zakres kluczy 10

jeśli przewijamy w dół, aż znajdziemy Slot 3, a następnie przewijamy obok bloku danych, widzimy fizyczne dane dla każdej kolumny i możemy potwierdzić, że jest to wiersz dla Dennisa Ware ’ a.

 zakres kluczy 11

teraz, jeśli wrócimy do końca slotu 3, zobaczymy

KeyHashValue = (05c585e839d4)

to pasuje do resource_description w sys.dm_tran_locks i wartości zwracanej przez %%lockres%%.

ale jak zrobić coś podobnego do zamków na klucze? Jak dowiemy się, jakie zasoby blokują? Jednym ze sposobów byłoby użycie sys. dm_db_database_page_allocations, aby ustalić, które strony należą do indeksu nieklastrowego. Przekazujemy w database_id, object_id i index_id indeksu IX_Person_LastName_FirstName_Middlename index i zwróci wiersz dla każdej strony używanej w tym indeksie. Następnie możemy uruchomić stronę DBCC z dostarczonymi wartościami allocated_page_page_id i znaleźć stronę zawierającą Dennis Ware.

SELECT allocated_page_page_id, *FROM sys.dm_db_database_page_allocations (DB_ID(N'AdventureWorks2012'), object_Id('AdventureWorks2012.Person.Person'), 4, null, 'DETAILED')WHERE is_allocated = 1

możemy wtedy uruchomić stronę DBCC z podaną wartością i znaleźć stronę zawierającą Dennis Ware.

DBCC PAGE (10, 1, 24745, 3); -- DB_ID, FileId, PageId, Format

przydatne jest to, że w tym przypadku strona DBCC zwraca dane na stronie w zestawie wyników, dzięki czemu możemy dość szybko znaleźć właściwą stronę, w tym przypadku stronę 24745.

 zakres kluczy 12

teraz, jeśli użyjemy wartości resource_description dla naszych czterech zamków w zakresie kluczy w sys.dm_tran_locks widzimy, że blokady” RangeS-S ” zostały faktycznie przyznane wierszom, które zawierają dane dla Dennis Ware, Victoria Ware, Clay Warten i Aaron Washington. Ale Aaron Washington nie jest uwzględniony w zestawie wyników, więc dlaczego ten czwarty zamek jest obecny? Dzieje się tak dlatego, że gdy zakres klucza jest brany na kluczu, zapobiega to dodawaniu danych bezpośrednio przed kluczem z blokadą zakresu klucza. Innymi słowy nie można dodawać danych między np. Victoria Ware i Clay Warthen, np. Jennifer Warner.

INSERT INTO Person.Person (BusinessEntityID, PersonType, FirstName, LastName)VALUES (888888, 'VC', 'Jennifer', 'Warner')SELECT *FROM sys.dm_tran_locksWHERE resource_type = 'KEY'ORDER BY request_mode

zakres kluczy 13

tutaj możemy zobaczyć czekający Zamek na klucz.

oznacza to również, że żadne dane nie mogą być dodawane między Timothy Wardem a Aaronem Washingtonem i oznacza, że zamki typu key-range faktycznie blokują więcej danych niż te, które mogłyby zaspokoić zapytanie w otwartej transakcji. Na przykład, nie mogliśmy wstawić wiersza dla Simona Wartza, który zostałby umieszczony pomiędzy Clayem Warthenem i Aaronem Washingtonem, nawet jeśli nie zostałby zwrócony w oryginalnym zapytaniu w otwartej transakcji.

INSERT INTO Person.Person (BusinessEntityID, PersonType, FirstName, LastName)VALUES (999999, 'VC', 'Simon', 'Wartz')SELECT *FROM sys.dm_tran_locksWHERE resource_type = 'KEY'ORDER BY request_mode

zakres kluczy 14

teraz wiemy, w jaki sposób wykorzystywane są zamki typu key-range, możemy dowiedzieć się, jakie zasoby mają na sobie zamki typu key-range bez potrzeby przeszukiwania stron indeksu non-clustered. Możemy po prostu dołączyć podpowiedź tabeli, aby zwrócić część danych, których potrzebujemy.

SELECT sys.fn_PhysLocFormatter(%%physloc%%), %%lockres%%, *FROM Person.Person WITH (INDEX = IX_Person_LastName_FirstName_MiddleName)WHERE LastName BETWEEN 'Ware' AND 'Warthen'

zakres kluczy 18

jak widzisz, daje nam to ten sam plik, DANE strony i slotu oraz ten sam resource_description dla trzech wierszy zwróconych przez zapytanie. Wiemy już, że czwarty zamek „RangeS-S” umieszczony jest na kluczu w indeksie zaraz po kluczu dla Clay Warthen, czyli slot 140. Ponownie możemy użyć strony DBCC, aby wyświetlić dane na stronie 25065.

teraz w demonstracji, którą właśnie przejrzałem, mamy nieklastrowany Indeks, z pierwszą kolumną w kluczu klastrowym, która jest tą, której używamy w naszej klauzuli WHERE. Ale co się stanie, jeśli usuniemy indeks nieklastrowy? Innymi słowy, co się stanie, jeśli przeprowadzimy zapytanie z inną tabelą filtrującą kolumnę nieuwzględnioną w kluczu indeksowym? Aby to łatwo zademonstrować, możemy po prostu usunąć indeks nieklastrowy.

DROP INDEX ON .BEGIN TRANSELECT *FROM Person.PersonWHERE LastName BETWEEN 'Ware' AND 'Warthen'

otrzymujemy te same wiersze z powrotem, jak oczekiwano, ale tym razem SQL Server musiał użyć indeksu klastrowego, aby uzyskać dane.

 zakres kluczy 15

jakie mamy zamki? Tym razem nie można zastosować zamka typu key-range, więc wspólny zamek musi zostać umieszczony na całym stole.

SELECT *FROM sys.dm_tran_locksWHERE resource_type = 'OBJECT'ORDER BY request_modeSELECT object_name(1765581328)

zakres kluczy 16

ponieważ blokada współdzielona jest przechowywana do momentu zamknięcia transakcji, oznacza to, że żadne dane w tabeli nie mogą być aktualizowane do momentu zakończenia transakcji, mimo że odczytaliśmy tylko trzy wiersze.

INSERT INTO Person.Person (BusinessEntityID, PersonType, FirstName, LastName)VALUES (777777, 'VC', 'Simon', 'ZZZZZ')SELECT *FROM sys.dm_tran_locksWHERE resource_type = 'OBJECT'ORDER BY request_mode

zakres kluczy 17

jest to coś, o czym musisz być świadomy, jeśli używasz SERIALIZOWALNEGO poziomu izolacji.

podsumowanie

jeśli kolumna klucza jest używana do filtrowania danych w zapytaniu, to blokady zakresu kluczy mogą być pobierane względem kluczy w tym indeksie. Zostaną one pobrane względem wszystkich kluczy spełniających zapytanie plus kolejno następny klucz poza zapytaniem. Zapobiega to dodawaniu danych, które wstawiałyby nowy klucz bezpośrednio przed kluczem z blokadą zakresu klucza.

Dodaj komentarz

Twój adres e-mail nie zostanie opublikowany.