Simon Learning server

Simon Learning server

i et tidligere indlæg talte jeg om låsning, blokering og isolationsniveauer. I dette indlæg vil jeg gå nærmere ind på de nøglelåse, der bruges i det SERIALISERBARE isolationsniveau, og også demonstrere at bruge sys.dm_tran_locks DMV til at se eksisterende låse og også hvordan man finder ud af, hvilke ressourcer låsene er placeret på.

Nøglelåse bruges kun i det SERIALISERBARE isolationsniveau, og deres formål er at stoppe fantomlæsninger, som er mulige i de andre tre pessimistiske samtidighedsisolationsniveauer, nemlig læst uforpligtende, læst engageret og gentagelig læsning. En fantomlæsning er, hvor en transaktion udfører en læsning på en række data, f.eks. ved hjælp af en forespørgsel med en hvor-klausul, og en anden transaktion tilføjer data, der opfylder betingelserne i hvor-klausulen. Hvis den første transaktion udfører den samme læsning, returnerer den nu de nye data, hvilket resulterer i, at fantomdata vises i den anden læsning inden for den samme transaktion. Nøglelåsene bruges til at forhindre, at fantomdata tilføjes, og de placeres på nøglerne til indekset, der bruges til at hente dataene. Følgende eksempel ved hjælp af personen.Persontabel i Adventurearbejds2012-databasen viser, hvordan disse nøgleområder bruges.

Personen.Persontabellen har et ikke-grupperet indeks-Id_person_lastname_firstname_middlename med kolonnen efternavn som den første kolonne i indeksnøglen. Dette betyder, at enhver forespørgsel, hvor vi filtrerer efter efternavn, skal bruge dette indeks til at hente dataene.

hvis vi kører en forespørgsel for at returnere et par rækker fra Person.Person, der filtrerer på en række efternavne, så skal vi se, at dette indeks bruges. For eksempel

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

returnerer følgende resultatsæt.

nøgleområde 2

og når vi ser på udførelsesplanen, kan vi se, at dette indeks bruges.

nøgleområde 3

hvis vi nu indstiller isolationsniveauet til SERIALISERBART, skal du åbne en transaktion og køre den samme select-erklæring, som vi skal kunne se, hvilke låse der holdes.

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

nu fra en anden forbindelse kan vi forespørge DMV sys.dm_tran_locks for at se de aktuelle låse holdes. Vi er kun interesseret i låsene på de vigtigste ressourcer, så vi filtrerer i overensstemmelse hermed.

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

nøgleområde 4

vi får syv rækker retuned med to forskellige anmod_mode værdier. “S” anmodning_mode svarer til delte låse placeret på de tre rækker, der returneres. Fordi vi bruger det SERIALISERBARE isolationsniveau, holdes disse delte låse, indtil transaktionen forpligter eller ruller tilbage for at forhindre ikke-gentagelige læsninger. “RangeS-S” -anmodning_mode svarer til de nøgleområdelåse, der er tildelt. Men hvorfor er der fire rækker? For at besvare dette kan vi bruge de data, der returneres fra sys.dm_tran_locks og bruge dette til at finde ud af, hvilke ressourcer der faktisk er låst.

de kolonner, vi er interesseret i, er resource_associated_entity_id og resource_description. Dataene i disse kolonner kan variere baseret på resource_type og for flere detaljer om dette se TechNet. For nøgleressourcer svarer resource_associated_entity_id til partition_id fra sys.skillevægge

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

nøgleområde 5

den returnerede object_id svarer til personen.Person tabel

SELECT object_name(1765581328)

nøgleområde 6

vi kan se, at de to forskellige partition_id værdier svarer til forskellige indekser på Person.Person

SELECT * FROM sys.indexes WHERE object_id = 1765581328

nøgleområde 7

sys.partitionsvisning fortæller os, at de partitioner, vi har låse på, er for indekserne PK_Person_BusinessEntityID og Iperson_lastname_firstname_middlename.

vi kan kombinere alt dette i en forespørgsel for klarhed.

 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

nøgleområde 8

Dette viser os, at tre delte låse er placeret på rækkerne selv (bladniveauet for det klyngede indeks), og at fire nøglelåse er placeret på nøgler inden for det ikke-klyngede indeks. Men dette har ikke besvaret spørgsmålet om, hvorfor vi har fire låse?

før vi gør det, vil jeg demonstrere to måder at bevise, at de tre delte låse er placeret på de rækker, der blev returneret af den oprindelige forespørgsel, og derefter bruge det til at finde ud af, hvilke ressourcer de fire “intervaller-s” Låse er placeret på.

for det første skal vi bruge værdierne %%physloc%% og %%lockres%% sammen med sys.fn_physlocformatter () funktion til at fortælle os filen, side og slot af rækkerne returneres af den oprindelige forespørgsel. For det andet bruger vi disse oplysninger i kommandoen DBCC side.

vi skal bare opdatere vores oprindelige forespørgsel som følger

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

nøgleområde 9

vi kan nu se, at rækken for Dennis varer er fysisk placeret på datafil 1, på side 2806 og på slot 3. Vi kan også se, at det er lås ressource er (05c585e839d4).

hvis vi ser tilbage på de data, der returneres fra sys.dm_tran_locks, kan vi se, at en af de delte låse har en resource_description af (05c585e839d4), så dette fortæller os, at låsen er placeret på den række i det grupperede indeks.

nu kan vi køre følgende DBCC-sidekommando for faktisk at se oplysninger om, hvad der er på siden, men for at dette kan returnere resultater, skal vi tænde trace flag 3604.

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

der vises en masse tekstoplysninger her, og du kan angive et andet nummer i formatparameteren for at returnere forskellige datamængder. I dette tilfælde er den første bit sidehovedet, og derefter har vi Tildelingsstatus. Du vil se den første slot bruges er Slot 0. Der er en Offsetværdi, der fortæller os, hvor på siden denne slot starter, og en Længdeværdi, der fortæller os, hvor lang posten er.

nøgleområde 10

hvis vi ruller ned, indtil vi finder Slot 3, og rul derefter forbi datablokken, kan vi se de fysiske data for hver kolonne og kan bekræfte, at dette er rækken for Dennis-varer.

nøgleområde 11

hvis vi nu går tilbage til slutningen af Slot 3, kan vi se

KeyHashValue = (05c585e839d4)

dette matcher resource_description i sys.dm_tran_locks og værdien returneret af %%lockres%%.

men hvordan gør vi noget lignende for nøgleområdet låse? Hvordan finder vi ud af, hvilke ressourcer de låser? En måde ville være at bruge sys.dm_db_database_page_allocations til at finde ud af, hvilke sider der tilhører et ikke-grupperet indeks. Vi passerer i database_id, object_id og indeks_id i indekset for IP-Person_lastname_firstname_middlename, og det returnerer en række for hver side, der bruges i indekset. Vi kan derefter køre DBCC side med de medfølgende allocated_page_page_id værdier og finde den side, der indeholder Dennis varer.

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 derefter køre DBCC side med den medfølgende værdi og finde den side, der indeholder Dennis varer.

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

den praktiske ting er, at DBCC-siden i dette tilfælde returnerer dataene på siden i et resultatsæt, så vi ganske hurtigt kan finde den rigtige side, i dette tilfælde side 24745.

nøgleområde 12

nu, hvis vi bruger resource_description værdier for vores fire nøgle-range låse i sys.dm_tran_locks vi kan se, at “RangeS-S” – låsene faktisk er blevet tildelt rækkerne, der indeholder data for Dennis-varer, Victoria-varer, Clay varten og Aaron. Men Aaron er ikke inkluderet i resultatsættet, så hvorfor er denne fjerde nøglelås til stede? Det skyldes, at når et nøgleområde tages på en nøgle, forhindrer det, at data tilføjes umiddelbart før en nøgle med en nøgleområde lås. Med andre ord kan der ikke tilføjes data mellem f.eks.

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

nøgleområde 13

her kan vi se den ventende nøgle-rækkevidde lås.

det betyder også, at der ikke kan tilføjes data mellem Timothy Menighed og Aaron, og betyder, at nøgleområdelåsene faktisk låser flere data end dem, der kunne tilfredsstille forespørgslen i den åbne transaktion. For eksempel, vi kunne ikke indsætte en række for Simon Varts som ville blive sat mellem Clay Vartshen og Aaron USA, selv om det ikke ville blive returneret i den oprindelige forespørgsel i den åbne transaktion.

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

nøgleområde 14

vi kan finde ud af, hvilke ressourcer der har nøgleområdelåsene på dem uden behov for at søge gennem siderne i det ikke-grupperede indeks. Vi kan blot inkludere et tabeltip for at returnere en del af de data, vi har brug for.

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

nøgleområde 18

som du kan se, giver dette os den samme fil, side-og slotdata og den samme resource_description for de tre rækker, der returneres af forespørgslen. Vi ved nu, at den fjerde “RangeS-S” lås er placeret på nøglen i indekset umiddelbart efter nøglen til ler Vorteså, dvs.slot 140. Igen kan vi bruge DBCC side til at se dataene på side 25065.

nu i demonstrationen, jeg lige har kørt igennem, har vi tilfældigvis et ikke-grupperet indeks, hvor den første kolonne i klyngetasten er den, vi bruger i Vores hvor-klausul. Men hvad sker der, hvis vi fjerner det ikke-klyngede indeks? Eller med andre ord, Hvad sker der, hvis vi kørte en forespørgsel mod en anden tabelfiltrering på en kolonne, der ikke er inkluderet i en indeksnøgle? For nemt at demonstrere dette kan vi bare fjerne det ikke-klyngede indeks.

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

vi får de samme rækker tilbage som forventet, men denne gang har vi været nødt til at bruge det klyngede indeks for at få dataene.

nøgleområde 15

så hvilke låse har vi? Denne gang er der ingen nøgle-rækkevidde lås, der kan anvendes, så en delt lås skal placeres på hele bordet.

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

nøgleområde 16

da denne delte lås holdes, indtil transaktionen er lukket, betyder det, at ingen data i tabellen kan opdateres, før transaktionen er afsluttet, selvom vi kun har læst tre rækker.

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

nøgleområde 17

dette er noget, du skal være opmærksom på, hvis du bruger det SERIALISERBARE isolationsniveau.

oversigt

hvis en nøglekolonne bruges til at filtrere data i en forespørgsel, kan nøgleområdelåse tages mod tasterne i indekset. De vil blive taget imod alle de nøgler, der opfylder forespørgslen plus den sekventielt næste nøgle uden for forespørgslen. Dette forhindrer derefter, at der tilføjes data, der vil indsætte en ny nøgle umiddelbart før en nøgle med en nøglelås.

Skriv et svar

Din e-mailadresse vil ikke blive publiceret.