Simon Learning SQL Server

Simon Learning SQL Server

v předchozím příspěvku jsem mluvil o zamykání, blokování a izolaci úrovně. V tomto příspěvku se chystám podrobněji o zámcích řady klíčů používaných v SERIALIZOVATELNÉ úrovni izolace a také demonstrovat použití sys. dm_tran_locks DMV pro zobrazení stávajících zámků a také jak zjistit, na jaké zdroje jsou zámky umístěny.

klíčové zámky se používají pouze v SERIALIZOVATELNÉ izolační úrovni a jejich účelem je zastavit fantomové čtení, které je možné v dalších třech pesimistických úrovních izolace souběžnosti, jmenovitě číst nezávazné, číst odevzdané a opakovatelné čtení. Fantomové čtení je místo, kde jedna transakce provede čtení na rozsahu dat, např. pomocí dotazu s klauzulí WHERE, a další transakce přidá data, která splní podmínky klauzule WHERE. Pokud první transakce provede stejné čtení, nyní vrátí nová data, což má za následek, že se fantomová data objeví ve druhém čtení v rámci stejné transakce. Zámky řady klíčů se používají k zabránění přidávání fantomových dat a jsou umístěny na klíčích indexu, který se používá k načtení dat. Následující příklad použití osoby.Tabulka osob v databázi AdventureWorks2012 ukáže, jak jsou tyto zámky řady klíčů používány.

Osoba.Tabulka osob má neklustrovaný index ix_person_lastname_firstname_middlename se sloupcem LastName jako prvním sloupcem v klíči indexu. To znamená, že jakýkoli dotaz, kde filtrujeme na LastName, by měl tento index použít k načtení dat.

pokud spustíme dotaz, abychom vrátili několik řádků od osoby.Filtrování osob na řadu příjmení, pak bychom měli vidět, jak se tento index používá. Například

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

vrací následující sadu výsledků.

 rozsah klíčů 2

a při pohledu na plán provádění vidíme, že se tento index používá.

 rozsah klíčů 3

Nyní, pokud nastavíme úroveň izolace na SERIALIZABLE, otevřete transakci a spusťte stejný příkaz select, měli bychom být schopni vidět, jaké zámky jsou drženy.

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

nyní z jiného připojení můžeme dotazovat DMV sys. dm_tran_locks, abychom viděli aktuální zámky držené. Zajímáme se pouze o zámky na klíčových zdrojích, takže podle toho filtrujeme.

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

rozsah klíčů 4

získáme sedm řádků přeladěných dvěma různými hodnotami request_mode. Request_mode“ S “ odpovídá sdíleným zámkům umístěným na třech řádcích, které jsou vráceny. Protože používáme SERIALIZOVATELNOU úroveň izolace, jsou tyto sdílené zámky drženy, dokud se transakce nezaváže nebo nevrátí zpět, aby se zabránilo neopakovatelnému čtení. Request_mode „RangeS-S“ odpovídá zadaným zámkům řady klíčů. Ale proč existují čtyři řádky? Abychom na to odpověděli, můžeme použít data vrácená ze sys. dm_tran_locks a pomocí toho zjistit, jaké zdroje byly skutečně uzamčeny.

sloupce, které nás zajímají, jsou resource_associated_entity_id a resource_description. Data v těchto sloupcích se mohou lišit v závislosti na typu resource_type a další podrobnosti viz TechNet. Pro klíčové zdroje odpovídá resource_associated_entity_id partition_id ze sys.příčky

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

rozsah klíčů 5

vrácené object_id odpovídá osobě.Tabulka osob

SELECT object_name(1765581328)

rozsah klíčů 6

vidíme, že dvě různé hodnoty partition_id odpovídají různým indexům na osobě.Osoba

SELECT * FROM sys.indexes WHERE object_id = 1765581328

rozsah klíčů 7

sys.zobrazení oddílů nám říká, že oddíly, na kterých máme zámky, jsou pro indexy PK_Person_BusinessEntityID a IX_Person_LastName_FirstName_Middlename.

to vše můžeme pro přehlednost zkombinovat do jednoho dotazu.

 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

rozsah klíčů 8

to nám ukazuje, že tři sdílené zámky jsou umístěny na samotných řádcích (úroveň listů seskupeného indexu) a že čtyři zámky s rozsahem klíčů jsou umístěny na klíčích v neklustrovaném indexu. Ale to neodpovědělo na otázku, proč máme čtyři zámky?

než to uděláme, ukážu dva způsoby, jak dokázat, že tři sdílené zámky jsou umístěny na řádcích, které byly vráceny původním dotazem, a poté je použijí k určení, na jaké zdroje jsou umístěny čtyři zámky“ rozsahy-S“.

nejprve použijeme hodnoty % % physloc%% a %%lockres%% spolu s sys.funkce fn_PhysLocFormatter () nám sdělí soubor, stránku a slot řádků vrácených původním dotazem. Za druhé používáme tyto informace v příkazu stránky DBCC.

stačí aktualizovat náš původní dotaz následovně

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

rozsah klíčů 9

nyní vidíme, že řádek pro Dennis Ware je fyzicky umístěn v datovém souboru 1, na straně 2806 a na slotu 3. Můžeme také vidět, že je to zdroj zámku (05c585e839d4).

pokud se podíváme zpět na data vrácená ze sys. dm_tran_locks, můžeme vidět, že jeden ze sdílených zámků má resource_description (05c585e839d4), takže nám to říká, že tento zámek je umístěn na tomto řádku v seskupeném indexu.

Nyní můžeme spustit následující příkaz stránky DBCC, abychom skutečně viděli informace o tom, co je na stránce, ale abychom vrátili všechny výsledky, musíme zapnout trace flag 3604.

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

zde je zobrazeno mnoho textových informací a v parametru formát můžete zadat jiné číslo pro vrácení různých množství dat. V tomto případě je prvním bitem záhlaví stránky a poté máme stav alokace. Uvidíte první slot použitý je štěrbina 0. Existuje offsetová hodnota, která nám říká, kde na stránce začíná tento slot, a hodnoty délky, které nám říkají, jak dlouho je záznam.

 rozsah klíčů 10

pokud se posuneme dolů, dokud nenajdeme Slot 3, a pak přejděte kolem bloku dat, můžeme vidět fyzická data pro každý sloupec a potvrdit, že se jedná o řádek pro Dennis Ware.

 rozsah klíčů 11

Nyní, pokud se vrátíme zpět na konec slotu 3, můžeme vidět

KeyHashValue = (05c585e839d4)

to odpovídá resource_description v sys. dm_tran_locks a hodnota vrácená %%lockres%%.

ale jak uděláme něco podobného pro zámky s rozsahem klíčů? Jak zjistíme, jaké zdroje zamykají? Jedním ze způsobů by bylo použít sys. dm_db_database_page_allocations k určení, které stránky patří do neklustrovaného indexu. Předáme index database_id, object_id a index_id indexu ix_person_lastname_firstname_middlename a vrátí řádek pro každou stránku použitou v tomto indexu. Poté můžeme spustit stránku DBCC s dodanými hodnotami allocated_page_page_id a najít stránku, která obsahuje 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

pak můžeme spustit stránku DBCC s dodanou hodnotou a najít stránku, která obsahuje Dennis Ware.

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

užitečné je, že v tomto případě stránka DBCC vrací data na stránce v sadě výsledků, takže můžeme docela rychle najít správnou stránku, v tomto případě stránku 24745.

 rozsah klíčů 12

Nyní, pokud použijeme hodnoty resource_description pro naše čtyři zámky s rozsahem klíčů v sys.dm_tran_locks vidíme, že zámky „rozsahy-S“ byly skutečně uděleny řádkům, které obsahují data pro Dennis Ware, Victoria Ware, Clay Warten a Aaron Washington. Aaron Washington však není zahrnut do sady výsledků, tak proč je tento čtvrtý klíčový zámek přítomen? Je to proto, že když je na klíči převzat rozsah kláves, zabrání tomu, aby byla všechna data přidána bezprostředně před klíč se zámkem rozsahu kláves. Jinými slovy, žádná data nemohou být přidány mezi, například, Victoria Ware a Clay Warthen, např 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

rozsah klíčů 13

zde vidíme čekající zámek dosahu klíčů.

to také znamená, že mezi Timothy Wardem a Aaronem Washingtonem nelze přidat Žádná data a znamená to, že zámky s rozsahem klíčů skutečně uzamknou více dat, než která by mohla uspokojit dotaz v otevřené transakci. Například jsme nemohli Vložit řádek pro Simona Wartze, který by byl vložen mezi Clay Warthen a Aaron Washington, i když by nebyl vrácen v původním dotazu v otevřené transakci.

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

rozsah klíčů 14

nyní víme, jak se používají zámky key-range, můžeme zjistit, jaké zdroje mají zámky key-range na nich, aniž by bylo nutné prohledávat stránky non-clusteru indexu. Můžeme jednoduše zahrnout nápovědu tabulky, abychom vrátili část dat, která požadujeme.

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

rozsah klíčů 18

jak vidíte, dává nám to stejný soubor, data stránek a slotů a stejný resource_description pro tři řádky vrácené dotazem. Nyní víme, že čtvrtý zámek „RangeS-S“ je umístěn na klíči v indexu bezprostředně za klíčem pro Clay Warthen, tj. slot 140. Opět můžeme použít stránku DBCC pro zobrazení dat na stránce 25065.

nyní v demonstraci, kterou jsem právě prošel, máme index bez seskupení, přičemž první sloupec v klíči clustering je ten, který používáme v klauzuli WHERE. Ale co se stane, když odstraníme index bez seskupení? Nebo jinými slovy, co se stane, když spustíme dotaz proti jinému filtrování tabulky ve sloupci, který není součástí indexového klíče? Abychom to mohli snadno prokázat, můžeme pouze odstranit index bez seskupení.

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

dostaneme stejné řádky zpět, jak se očekávalo, ale tentokrát SQL Server musel použít seskupený index k získání dat.

 rozsah klíčů 15

tak jaké zámky máme? Tentokrát není možné použít žádný zámek s rozsahem klíčů, takže sdílený zámek musí být umístěn na celém stole.

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

rozsah klíčů 16

protože je tento sdílený zámek držen až do uzavření transakce, znamená to, že do dokončení transakce nelze aktualizovat žádná data v tabulce, i když jsme přečetli pouze tři řádky.

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

rozsah klíčů 17

to je něco, co musíte vědět, pokud používáte SERIALIZOVATELNOU úroveň izolace.

shrnutí

pokud se k filtrování dat v dotazu použije sloupec klíče, lze zámky rozsahu klíčů vzít proti klíčům v tomto indexu. Budou přijata proti všem klíčům, které splňují dotaz plus postupně další klíč mimo dotaz. Tím se zabrání přidávání dat, která by vložila nový klíč bezprostředně před klíč se zámkem rozsahu kláves.

Napsat komentář

Vaše e-mailová adresa nebude zveřejněna.