Simon Learning SQL Server

Simon Learning SQL Server

I et tidligere innlegg snakket jeg Om Låsing, Blokkering og Isolasjonsnivåer. I dette innlegget skal jeg gå nærmere inn på nøkkellåsene som brukes i DET SERIALISERBARE isolasjonsnivået, og også demonstrere å bruke sys. dm_tran_locks DMV for å se eksisterende låser og også hvordan man skal finne ut hvilke ressurser låsene er plassert på.

key-range låser brukes bare I SERIALISERBAR isolasjonsnivå og deres formål er å stoppe phantom leser som er mulig i de andre tre pessimistiske samtidighet isolasjonsnivåer, nemlig lese uforpliktet, lese forpliktet og repeterbare lese. En fantomlesing er hvor en transaksjon utfører en lesning på en rekke data, for eksempel ved hjelp AV en spørring MED EN WHERE-klausul, og en annen transaksjon legger til data som vil tilfredsstille betingelsene I WHERE-klausulen. Hvis den første transaksjonen utfører den samme lesingen, vil den nå returnere de nye dataene, noe som resulterer i fantomdata som vises i den andre lesingen i samme transaksjon. Nøkkellåsene brukes til å hindre at fantomdata blir lagt til, og de plasseres på tastene til indeksen som brukes til å hente dataene. Følgende eksempel bruker Personen.Persontabellen I AdventureWorks2012-databasen vil demonstrere hvordan disse nøkkellåsene brukes.

Personen.Persontabellen har en ikke-gruppert indeks ix_person_lastname_firstname_middlename med Kolonnen Etternavn som den første kolonnen i indeksnøkkelen. Dette betyr at alle spørringer der vi filtrerer På Etternavn, skal bruke denne indeksen til å hente dataene.

hvis vi kjører en spørring for å returnere noen rader Fra Person.Person filtrering på en rekke etternavn så vi bør se denne indeksen blir brukt. For eksempel

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

Returnerer følgende resultatsett.

Nøkkelområde 2

Og når vi ser på utførelsesplanen, kan vi se at denne indeksen brukes.

 Nøkkelområde 3

nå hvis vi setter isolasjonsnivået TIL SERIALISERBAR, åpner du en transaksjon og kjører samme select-setning, vi skal kunne se hvilke låser som holdes.

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

Nå fra en annen tilkobling kan vi spørre DMV sys.dm_tran_locks for å se de nåværende låsene som holdes. Vi er bare interessert i låsene på NØKKELRESSURSENE, så vi filtrerer tilsvarende.

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

Key Range 4

vi får syv rader retuned med to forskjellige request_mode verdier. «S» request_mode tilsvarer delte låser plassert på de tre radene som returneres. Fordi vi bruker DET SERIALISERBARE isolasjonsnivået, holdes disse delte låsene til transaksjonen forplikter eller ruller tilbake for å forhindre ikke-repeterbar lesing. «Områder-S» request_mode tilsvarer nøkkelområdet låser som er gitt. Men hvorfor er det fire rader? For å svare på dette kan vi bruke dataene som er returnert fra sys.dm_tran_locks og bruke dette til å finne ut hvilke ressurser som faktisk er låst.

kolonnene vi er interessert i er resource_associated_entity_id og resource_description. Dataene i disse kolonnene kan variere basert på resource_type og For mer informasjon om Dette Se TechNet. For NØKKELRESSURSER svarer resource_associated_entity_id til partition_id fra sys.skillevegger

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

Key Range 5

object_id returnert tilsvarer Personen.Person tabell

SELECT object_name(1765581328)

Key Range 6

Vi kan se at de to forskjellige partition_id-verdiene tilsvarer forskjellige indekser på Person.Person

SELECT * FROM sys.indexes WHERE object_id = 1765581328

Key Range 7

sys.partisjoner vis forteller oss at partisjonene vi har låser på er for indeksene PK_Person_BusinessEntityID og IX_Person_LastName_FirstName_Middlename.

Vi kan kombinere alt dette i ett spørsmål for klarhet.

 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

Key Range 8

Dette viser oss at tre delte låser er plassert på radene selv (bladnivået til den grupperte indeksen) og at fire nøkkellåser er plassert på nøkler i den ikke-grupperte indeksen. Men dette har ikke besvart spørsmålet om hvorfor vi har fire låser?

før vi gjør det, skal jeg demonstrere to måter å bevise at de tre delte låsene er plassert på radene som ble returnert av den opprinnelige spørringen, og deretter bruke det til å finne ut hvilke ressurser de fire» RangeS-S » låsene er plassert på.

For det Første skal vi bruke%% physloc %%og%% lockres % % verdier sammen med sys.fn_PhysLocFormatter () – funksjonen for å fortelle oss filen, siden og sporet av radene som returneres av den opprinnelige spørringen. For det andre bruker vi denne informasjonen i dbcc-sidekommandoen.

Vi trenger bare å oppdatere vår opprinnelige spørring som følger

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

Key Range 9

vi kan nå se at raden For Dennis Ware er fysisk plassert på datafil 1, på side 2806 og på slot 3. Vi kan også se at det er lås ressurs er (05c585e839d4).

hvis vi ser tilbake til dataene som er returnert fra sys. dm_tran_locks, kan vi se at en av de delte låsene har en resource_description av (05c585e839d4), så dette forteller oss at den låsen er plassert på den raden i gruppert indeksen.

nå kan vi kjøre følgende dbcc SIDE kommando for å faktisk se informasjon om hva som er på siden, men for at dette skal returnere noen resultater må vi slå på trace flag 3604.

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

det er mye tekstinformasjon som vises her, og du kan angi et annet tall i formatparameteren for å returnere forskjellige datamengder. I dette tilfellet er den første biten sideoverskriften, og deretter har vi Tildelingsstatusen. Du vil se det første sporet som brukes Er Slot 0. Det er En Offset verdi som forteller oss hvor på siden dette sporet starter og En Lengde verdier som forteller oss hvor lenge posten er.

 Nøkkelområde 10

hvis vi ruller ned til Vi finner Slot 3, og deretter ruller forbi datablokken, kan vi se de fysiske dataene for hver kolonne og kan bekrefte at dette er raden For Dennis Ware.

 Nøkkelområde 11

Nå, hvis vi går tilbake til Slutten Av Slot 3, kan vi se

KeyHashValue = (05c585e839d4)

dette samsvarer med resource_description i sys. dm_tran_locks og verdien returnert av % % lockres%%.

men Hvordan gjør vi noe lignende for nøkkelområdet låser? Hvordan finner vi ut hvilke ressurser de låser? En måte ville være å bruke sys. dm_db_database_page_allocations å finne ut hvilke sider som tilhører ut ikke-gruppert indeks. Vi passerer i database_id, object_id og index_id av ix_person_lastname_firstname_middlename indeksen, og det vil returnere en rad for hver side som brukes i denne indeksen. Vi kan da kjore DBCC-SIDEN med de medfølgende allocated_page_page_id-verdiene og finne siden som inneholder 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 da kjøre DBCC-SIDEN med den medfølgende verdien og finne siden som inneholder Dennis Ware.

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

den praktiske tingen er at I dette tilfellet returnerer DBCC-SIDEN dataene på siden i et resultatsett, slik at vi ganske raskt kan finne riktig side, i dette tilfellet side 24745.

Nøkkelområde 12

Nå hvis vi bruker resource_description-verdiene for våre fire nøkkelområde låser i sys.dm_tran_locks vi kan se at» RangeS-S » låser har faktisk blitt gitt til radene som inneholder data For Dennis Ware, Victoria Ware, Clay Warten Og Aaron Washington. Men Aaron Washington er ikke inkludert i resultatsettet, så hvorfor er denne fjerde nøkkellåsen til stede? Det er fordi når et nøkkelområde tas på en nøkkel, vil det forhindre at data legges til umiddelbart før en nøkkel med en nøkkelområdelås. Med andre ord kan ingen data legges til Mellom For Eksempel Victoria Ware og Clay Warthen, For Eksempel 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

Key Range 13

Her kan vi se den ventende nøkkellåsen.

Det betyr også at Ingen data kan legges mellom Timothy Ward Og Aaron Washington, og betyr at nøkkellåsene faktisk låser flere data enn det som kunne tilfredsstille spørringen i den åpne transaksjonen. For eksempel kunne vi ikke sette inn en rad For Simon Wartz som ville bli satt mellom Clay Warthen og Aaron Washington, selv om den ikke ville bli returnert i den opprinnelige spørringen i den åpne transaksjonen.

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

Key Range 14

nå vet vi hvordan key-range låser brukes vi kan finne ut hvilke ressurser har key-range låser på dem uten behov for å søke gjennom sidene i ikke-gruppert indeksen. Vi kan ganske enkelt inkludere et bordtips for å returnere en del av dataene vi trenger.

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

Key Range 18

Som du kan se dette gir oss samme fil, side og slot data og samme resource_description for de tre radene returnert av spørringen. Vi vet nå at den fjerde» RangeS-S » – låsen er plassert på nøkkelen i indeksen umiddelbart etter nøkkelen Til Clay Warthen, dvs.slot 140. Igjen kan vi bruke DBCC-SIDEN til å vise dataene på side 25065.

Nå i demonstrasjonen har jeg nettopp kjørt gjennom at vi tilfeldigvis har en ikke-gruppert indeks med den første kolonnen i klyngnøkkelen som den vi bruker I VÅR WHERE-klausul. Men hva skjer hvis vi fjerner den ikke-klyngede indeksen? Eller med andre ord, hva skjer hvis vi kjørte en spørring mot en annen tabellfiltrering på en kolonne som ikke er inkludert i en indeksnøkkel? For å enkelt demonstrere dette kan vi bare fjerne den ikke-klyngede indeksen.

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

vi får de samme radene tilbake som forventet, men DENNE gangen HAR SQL Server måtte bruke gruppert indeksen for å få dataene.

 Nøkkelområde 15

hvilke låser har vi? Denne gangen er det ingen nøkkelområde lås som kan brukes, så en delt lås må plasseres på hele bordet.

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

Key Range 16

fordi denne delte låsen holdes til transaksjonen er lukket, betyr dette at ingen data i tabellen kan oppdateres til transaksjonen er fullført, selv om vi bare har lest 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

Key Range 17

Dette er noe du må være oppmerksom på hvis du bruker SERIALISERBAR isolasjonsnivå.

Sammendrag

hvis en nøkkelkolonne brukes til å filtrere data i en spørring, kan nøkkellåser tas mot tastene i den indeksen. De vil bli tatt mot alle nøklene som tilfredsstiller spørringen pluss sekvensielt neste nøkkel utenfor spørringen. Dette forhindrer da at data blir lagt til som vil sette inn en ny nøkkel umiddelbart før en nøkkel med en nøkkelområde lås.

Legg igjen en kommentar

Din e-postadresse vil ikke bli publisert.