Simon Learning SQL Server

Simon Learning SQL Server

i ett tidigare inlägg pratade jag om låsning, blockering och Isoleringsnivåer. I det här inlägget kommer jag att gå in mer i detalj om de nyckellås som används i den SERIALISERBARA isoleringsnivån och visa också att använda sys.dm_tran_locks DMV för att se befintliga lås och även hur man räknar ut vilka resurser låsen placeras på.

nyckellås används endast i den SERIALISERBARA isoleringsnivån och deras syfte är att stoppa fantomläsningar som är möjliga i de andra tre pessimistiska samtidighetsisoleringsnivåerna, nämligen läs obefogad, läs engagerad och repeterbar läsning. En fantomläsning är där en transaktion utför en läsning av ett dataområde, t.ex. med en fråga med en where-klausul, och en annan transaktion lägger till data som uppfyller villkoren för Where-klausulen. Om den första transaktionen utför samma läsning kommer den nu att returnera de nya data som resulterar i fantomdata som visas i den andra läsningen inom samma transaktion. Nyckellåsen används för att förhindra att fantomdata läggs till och de placeras på tangenterna i indexet som används för att hämta data. Följande exempel med personen.Persontabellen i AdventureWorks2012-databasen visar hur dessa nyckellås används.

Personen.Persontabellen har ett icke-grupperat index ix_person_lastname_firstname_middlename med kolumnen efternamn som den första kolumnen i indexnyckeln. Det betyder att alla frågor där vi filtrerar på efternamn ska använda detta index för att hämta data.

om vi kör en fråga för att returnera några rader från Person.Personfiltrering på en rad efternamn så ska vi se att detta index används. Till exempel

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

returnerar följande resultatuppsättning.

nyckelområde 2

och när vi tittar på exekveringsplanen kan vi se att detta index används.

nyckelområde 3

nu om vi ställer in isoleringsnivån till serialiserbar, öppna en transaktion och kör samma select-uttalande som vi borde kunna se vilka lås som hålls.

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

nu från en annan anslutning kan vi fråga DMV sys.dm_tran_locks för att se de aktuella låsen hålls. Vi är bara intresserade av låsen på NYCKELRESURSERNA så vi filtrerar därefter.

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

nyckelområde 4

vi får sju rader retuned med två olika request_mode värden. ”S” request_mode motsvarar delade lås placerade på de tre raderna som returneras. Eftersom vi använder den SERIALISERBARA isoleringsnivån hålls dessa delade lås tills transaktionen åtar sig eller rullar tillbaka för att förhindra icke-repeterbara läsningar. ”RangeS-S” request_mode motsvarar de nyckellås som har beviljats. Men varför finns det fyra rader? För att svara på detta kan vi använda data som returneras från sys.dm_tran_locks och använda detta för att ta reda på vilka resurser som faktiskt har låsts.

kolumnerna vi är intresserade av är resource_associated_entity_id och resource_description. Data i dessa kolumner kan variera beroende på resource_type och för mer information om detta se TechNet. För nyckelresurser motsvarar resource_associated_entity_id partition_id från sys.partitioner

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

nyckelområde 5

det returnerade object_id motsvarar personen.Person tabell

SELECT object_name(1765581328)

nyckelområde 6

vi kan se att de två olika partition_id-värdena motsvarar olika index på Person.Person

SELECT * FROM sys.indexes WHERE object_id = 1765581328

nyckelområde 7

sys.partitionsvyn berättar att partitionerna vi har lås på är för indexen PK_Person_BusinessEntityID och ix_person_lastname_firstname_middlename.

vi kan kombinera allt detta i en fråga för tydlighet.

 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

nyckelområde 8

Detta visar oss att tre delade lås placeras på själva raderna (bladnivån för det grupperade indexet) och att fyra nyckellås placeras på nycklar inom det icke-grupperade indexet. Men detta har inte svarat på frågan om varför vi har fyra lås?

innan vi gör det ska jag visa två sätt att bevisa att de tre delade låsen placeras på raderna som returnerades av den ursprungliga frågan och sedan använda det för att ta reda på vilka resurser de fyra ”RangeS-S” – låsen placeras på.

för det första kommer vi att använda %%physloc%% och %%lockres%% värden tillsammans med sys.fn_physlocformatter () funktion för att berätta filen, sida och slits av raderna returneras av den ursprungliga frågan. För det andra använder vi denna information i DBCC-SIDKOMMANDOT.

vi behöver bara uppdatera vår ursprungliga fråga enligt följande

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

nyckelområde 9

vi kan nu se att raden för Dennis Ware är fysiskt belägen på datafil 1, på sidan 2806 och vid spår 3. Vi kan också se att det är lås resurs är (05c585e839d4).

om vi ser tillbaka på de data som returneras från sys.dm_tran_locks kan vi se att ett av de delade låsen har en resource_description av (05c585e839d4) så det här berättar att det låset placeras på den raden i det grupperade indexet.

nu kan vi köra följande DBCC-SIDKOMMANDO för att faktiskt se information om vad som finns på sidan men för att detta ska returnera några resultat måste vi aktivera trace flag 3604.

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

det finns mycket textinformation som visas här och du kan ange ett annat nummer i formatparametern för att returnera olika datamängder. I det här fallet är den första biten sidhuvudet och sedan har vi Allokeringsstatus. Du ser den första slitsen som används är Slot 0. Det finns ett Förskjutningsvärde som berättar var på sidan Denna plats börjar och en längd värden som talar om för oss hur länge posten är.

nyckelområde 10

om vi rulla ner tills vi hittar Slot 3, och sedan bläddra förbi blocket av data kan vi se de fysiska data för varje kolumn och kan bekräfta detta är raden för Dennis Ware.

nyckelområde 11

om vi nu går tillbaka till slutet av Slot 3 kan vi se

KeyHashValue = (05c585e839d4)

detta matchar resource_description i sys.dm_tran_locks och värdet returneras av %%lockres%%.

men hur gör vi något liknande för nyckellåsen? Hur får vi reda på vilka resurser de låser? Ett sätt skulle vara att använda sys.dm_db_database_page_allocations för att ta reda på vilka sidor som tillhör det icke-klustrade indexet. Vi passerar i database_id, object_id och index_id för ix_person_lastname_firstname_middlename index och det kommer att returnera en rad för varje sida som används i det indexet. Vi kan sedan köra DBCC sida med de medföljande allocated_page_page_id värden och hitta den sida som innehåller 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

vi kan sedan köra DBCC sida med det medföljande värdet och hitta den sida som innehåller Dennis Ware.

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

det praktiska är att i detta fall returnerar DBCC-sidan data på sidan i en resultatuppsättning så att vi ganska snabbt kan hitta rätt sida, i detta fall sidan 24745.

nyckelområde 12

nu om vi använder resource_description-värdena för våra fyra nyckellås i sys.dm_tran_locks vi kan se att” RangeS-S ” – Lås faktiskt har beviljats raderna som innehåller data för Dennis Ware, Victoria Ware, Clay Warten och Aaron Washington. Men Aaron Washington ingår inte i resultatuppsättningen, så varför är detta fjärde nyckellås närvarande? Det beror på att när ett nyckelområde tas på en nyckel, kommer det att förhindra att data läggs till omedelbart före en nyckel med ett nyckellås. Med andra ord kan Inga data läggas till mellan till exempel Victoria Ware och Clay Warthen, t.ex. 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

nyckelområde 13

här kan vi se det väntande nyckellåset.

det betyder också att inga data kan läggas till mellan Timothy Ward och Aaron Washington och innebär att nyckellåsen faktiskt låser mer data än det som kunde uppfylla frågan i den öppna transaktionen. Till exempel kunde vi inte infoga en rad för Simon Wartz som skulle sättas mellan Clay Warthen och Aaron Washington, även om den inte skulle returneras i den ursprungliga frågan i den öppna transaktionen.

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

nyckelområde 14

nu vet vi hur nyckellåsen används vi kan ta reda på vilka resurser som har nyckellåsen på dem utan att behöva söka igenom sidorna i det icke-klustrade indexet. Vi kan helt enkelt inkludera ett tabelltips för att returnera en del av de data vi behöver.

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

nyckelområde 18

som ni kan se detta ger oss samma fil, sida och slot data och samma resource_description för de tre raderna returneras av frågan. Vi vet nu att det fjärde ”RangeS-S” – låset placeras på nyckeln i indexet omedelbart efter nyckeln för Clay Warthen, d.v. s. slits 140. Återigen kan vi använda DBCC sida för att visa data på sidan 25065.

nu i demonstrationen jag just har gått igenom råkar vi ha ett icke-grupperat index med den första kolumnen i klusternyckeln som den som vi använder i vår where-klausul. Men vad händer om vi tar bort det icke-klustrade indexet? Eller med andra ord, vad händer om vi körde en fråga mot en annan tabellfiltrering på en kolumn som inte ingår i en indexnyckel? För att enkelt visa detta kan vi bara ta bort det icke-klustrade indexet.

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

vi får samma rader tillbaka som förväntat men den här gången har SQL Server varit tvungen att använda det grupperade indexet för att få data.

nyckelområde 15

så vilka lås har vi? Den här gången finns det inget nyckellås som kan appliceras, så ett delat lås måste placeras på hela bordet.

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

nyckelområde 16

eftersom detta delade lås hålls tills transaktionen är stängd betyder det att inga data i tabellen kan uppdateras förrän transaktionen är klar, även om vi bara har läst tre rader.

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

nyckelområde 17

Detta är något du måste vara medveten om om du använder den SERIALISERBARA isoleringsnivån.

sammanfattning

om en nyckelkolumn används för att filtrera data i en fråga kan nyckellås tas mot nycklarna i det indexet. De kommer att tas mot alla nycklar som uppfyller frågan plus sekventiellt nästa nyckel utanför frågan. Detta förhindrar sedan att data läggs till som skulle infoga en ny nyckel omedelbart före en nyckel med ett nyckellås.

Lämna ett svar

Din e-postadress kommer inte publiceras.