Simon Learning SQL Server

Simon Learning SQL Server

In een vorige post sprak ik over het vergrendelen, blokkeren en isoleren van niveaus. In dit bericht ga ik in meer detail over de key-range sloten gebruikt in de SERIALIZABLE isolatie niveau en ook demonstreren om het gebruik van de sys.dm_tran_locks DMV om bestaande sloten te bekijken en ook hoe uit te werken welke middelen de sloten worden geplaatst op.

key-range locks worden alleen gebruikt in het SERIALISEERBARE isolatieniveau en hun doel is om fantoomlezen te stoppen die mogelijk zijn in de andere drie pessimistische concurrency isolatieniveaus, namelijk lezen zonder commitment, lezen zonder commitment en herhaalbaar lezen. Een phantom read is wanneer een transactie een read uitvoert op een reeks gegevens, bijvoorbeeld met behulp van een query met een WHERE-clausule, en een andere transactie gegevens toevoegt die voldoen aan de voorwaarden van de WHERE-clausule. Als de eerste transactie dezelfde read uitvoert, zal het nu de nieuwe gegevens retourneren, wat resulteert in fantoomgegevens die in de tweede read binnen dezelfde transactie verschijnen. De key-range sloten worden gebruikt om te voorkomen dat phantom gegevens worden toegevoegd en ze worden geplaatst op de toetsen van de index die wordt gebruikt om de gegevens op te halen. Het volgende voorbeeld met behulp van de persoon.Persoonentabel in de AdventureWorks2012 database zal laten zien hoe deze key-range sloten worden gebruikt.

De Persoon.Persoon tabel heeft een niet-geclusterde index ix_person_lastname_firstname_middlename met de laatste naam kolom als de eerste kolom in de index sleutel. Dit betekent dat elke query waar we filteren op LastName deze index moet gebruiken om de gegevens op te halen.

als we een query uitvoeren om een paar rijen van persoon te retourneren.Persoon filteren op een reeks van achternaam Dan moeten we zien dat deze index wordt gebruikt. Bijvoorbeeld

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

geeft de volgende resultatenreeks terug.

Sleutelbereik 2

en kijkend naar het uitvoeringsplan kunnen we zien dat deze index wordt gebruikt.

Sleutelbereik 3

als we nu het isolatieniveau instellen op serialiseerbaar, een transactie openen en hetzelfde SELECT statement uitvoeren, moeten we in staat zijn om te zien welke sloten worden vastgehouden.

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

vanaf een andere verbinding kunnen we het DMV sys.dm_tran_locks opvragen om te zien dat de huidige vergrendelingen worden vastgehouden. We zijn alleen geïnteresseerd in de sloten op de belangrijkste middelen, dus we filteren dienovereenkomstig.

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

Sleutelbereik 4

we krijgen zeven rijen retuned met twee verschillende request_mode-waarden. De” S ” request_mode komt overeen met gedeelde sloten geplaatst op de drie rijen die worden geretourneerd. Omdat we het serialiseerbare isolatieniveau gebruiken, worden deze gedeelde vergrendelingen vastgehouden totdat de transactie commit of terugrolt om niet-herhaalbare reads te voorkomen. De” RangeS-S ” request_mode komt overeen met de key-range locks die zijn verleend. Maar waarom zijn er vier rijen? Om dit te beantwoorden kunnen we de gegevens van sys.dm_tran_locks gebruiken en dit gebruiken om uit te zoeken welke bronnen daadwerkelijk zijn vergrendeld.

de kolommen waarin we geïnteresseerd zijn zijn resource_associated_entity_id en resource_description. De gegevens in deze kolommen kunnen variëren op basis van het resource_type en voor meer details hierover zie TechNet. Voor belangrijke bronnen komt de resource_associated_entity_id overeen met de partition_id van sys.partities

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

Sleutelbereik 5

de object_id geretourneerd komt overeen met de persoon.Personen-tabel

SELECT object_name(1765581328)

Sleutelbereik 6

we kunnen zien dat de twee verschillende partition_id waarden overeenkomen met verschillende indexen op persoon.Persoon

SELECT * FROM sys.indexes WHERE object_id = 1765581328

Sleutelbereik 7

de sys.partities view vertelt ons dat de partities die we hebben sloten op zijn voor de indexen PK_Person_BusinessEntityID en IX_Person_LastName_FirstName_Middlename.

we kunnen dit alles combineren in één zoekopdracht voor duidelijkheid.

 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

Sleutelbereik 8

dit laat zien dat er drie gedeelde sloten op de rijen zelf worden geplaatst (het bladniveau van de geclusterde index) en dat er vier key-range sloten worden geplaatst op toetsen binnen de niet-geclusterde index. Maar dit is geen antwoord op de vraag waarom we vier sloten hebben?

voordat we dat doen ga ik twee manieren demonstreren om te bewijzen dat de drie gedeelde sloten zijn geplaatst op de rijen die werden geretourneerd door de oorspronkelijke query en gebruik dat om uit te zoeken op welke bronnen de vier “RangeS-S” sloten zijn geplaatst.

eerst gaan we de %%physloc%% en %%lockres%% waarden gebruiken samen met de sys.fn_physlocformatter () functie om ons het dossier, de pagina en de sleuf van de rijen te vertellen die door de originele vraag worden geretourneerd. Ten tweede gebruiken we deze informatie in het DBCC PAGE Commando.

we hoeven alleen maar onze oorspronkelijke query als volgt bij te werken

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

Sleutelbereik 9

we kunnen nu zien dat de rij voor Dennis Ware zich fysiek bevindt op data file 1, op pagina 2806 en op slot 3. We kunnen ook zien dat het lock bron is (05c585e839d4).

als we terugkijken naar de gegevens van sys.dm_tran_locks kunnen we zien dat een van de gedeelde sloten een resource_description heeft van (05c585e839d4) dus dit vertelt ons dat dat slot op die rij in de geclusterde index is geplaatst.

nu kunnen we het volgende DBCC PAGE commando uitvoeren om daadwerkelijk informatie te zien over wat er op de pagina staat, maar om de resultaten te kunnen retourneren moeten We trace flag 3604 inschakelen.

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

er wordt hier veel tekstuele informatie weergegeven en u kunt een ander nummer opgeven in de opmaakparameter om verschillende hoeveelheden gegevens te retourneren. In dit geval is de eerste bit de paginakop en daarna hebben we de Allocatiestatus. U zult zien dat de eerste sleuf gebruikt is Slot 0. Er is een Offset waarde die ons vertelt waar op de pagina deze sleuf begint en een lengte waarden die ons vertelt hoe lang het record is.

Sleutelbereik 10

als we naar beneden scrollen tot we Slot 3 vinden, en dan langs het gegevensblok scrollen, kunnen we de fysieke gegevens voor elke kolom zien en kunnen bevestigen dat dit de rij is voor Dennis Ware.

Sleutelbereik 11

nu, als we terug gaan naar het einde van Slot 3 zien we

KeyHashValue = (05c585e839d4)

dit komt overeen met de resource_description in sys.dm_tran_locks en de waarde geretourneerd door %%lockres%%.

maar hoe doen we iets gelijkaardigs voor de key-range locks? Hoe komen we erachter welke middelen ze vergrendelen? Een manier zou zijn om sys.dm_db_database_page_allocations te gebruiken om uit te zoeken welke pagina ‘ s behoren tot niet-geclusterde index. We passeren in de database_id, de object_id en de index_id van de ix_person_lastname_firstname_middlename index en het zal een Rij retourneren voor elke pagina die binnen die index wordt gebruikt. We kunnen dan DBCC pagina uitvoeren met de meegeleverde allocated_page_page_id waarden en de pagina vinden die Dennis Ware bevat.

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

we kunnen dan DBCC PAGE uitvoeren met de opgegeven waarde en de pagina vinden die Dennis Ware bevat.

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

het handige is dat in dit geval DBCC PAGE de gegevens in de pagina in een resultatenreeks retourneert zodat we vrij snel de juiste pagina kunnen vinden, in dit geval pagina 24745.

Sleutelbereik 12

als we nu de resource_description waarden gebruiken voor onze vier key-range locks in sys.dm_tran_locks we kunnen zien dat de “RangeS-S” sloten daadwerkelijk zijn verleend aan de rijen die gegevens bevatten voor Dennis Ware, Victoria Ware, Clay Warten en Aaron Washington. Maar Aaron Washington is niet opgenomen in de result set, dus waarom is dit vierde sleutelslot aanwezig? Het is omdat wanneer een key-range wordt genomen op een sleutel, het zal voorkomen dat gegevens worden toegevoegd direct voor een sleutel met een key-range lock. Met andere woorden, er kunnen geen gegevens worden toegevoegd tussen bijvoorbeeld Victoria Ware en Clay Warthen, bijvoorbeeld 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

Sleutelbereik 13

hier zien we het wachtende sleutelbereik slot.

het betekent ook dat er geen gegevens kunnen worden toegevoegd tussen Timothy Ward en Aaron Washington en betekent dat het sleutelbereik meer gegevens vergrendelt dan dat waarmee de query in de open transactie kon worden voldaan. We konden bijvoorbeeld geen rij invoegen voor Simon Wartz die zou worden geplaatst tussen Clay Warthen en Aaron Washington, hoewel het niet zou worden geretourneerd in de oorspronkelijke query in de open transactie.

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

Sleutelbereik 14

nu we weten hoe de key-range locks worden gebruikt, kunnen we uitzoeken welke bronnen de key-range locks op hen hebben zonder de noodzaak om door de pagina ‘ s van de niet-geclusterde index te zoeken. We kunnen gewoon een tabelhint toevoegen om een deel van de gegevens die we nodig hebben te retourneren.

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

Sleutelbereik 18

zoals u kunt zien geeft dit ons hetzelfde bestand, pagina en slot gegevens en dezelfde resource_description voor de drie rijen geretourneerd door de query. We weten nu dat het vierde “RangeS-S” slot direct na de sleutel voor Clay Warthen op de sleutel in de index wordt geplaatst, dat wil zeggen slot 140. Opnieuw kunnen we DBCC pagina gebruiken om de gegevens op pagina 25065 te bekijken.

in de demonstratie die ik zojuist heb doorlopen hebben we toevallig een niet-geclusterde index met de eerste kolom in de clustering sleutel die we gebruiken in onze where clausule. Maar wat gebeurt er als we de niet-geclusterde index verwijderen? Of met andere woorden, wat gebeurt er als we een query uitvoeren tegen een andere tabel die filtert op een kolom die niet is opgenomen in een indexsleutel? Om dit eenvoudig aan te tonen kunnen we gewoon de niet-geclusterde index verwijderen.

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

we krijgen dezelfde rijen terug als verwacht maar deze keer heeft SQL Server de geclusterde index moeten gebruiken om de gegevens te krijgen.

Sleutelbereik 15

welke sloten hebben we? Deze keer is er geen sleutelbereik slot dat kan worden toegepast, dus een gedeeld slot moet worden geplaatst op de hele tafel.

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

Sleutelbereik 16

omdat dit gedeelde slot wordt vastgehouden totdat de transactie wordt gesloten, betekent dit dat er geen gegevens in de tabel kunnen worden bijgewerkt totdat de transactie is voltooid, ook al hebben we slechts drie rijen gelezen.

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

Sleutelbereik 17

Dit is iets waar je je bewust van moet zijn als je het SERIALISEERBARE isolatieniveau gebruikt.

samenvatting

als een sleutelkolom wordt gebruikt om gegevens in een query te filteren, kunnen sleutelbereikvergrendelingen worden gebruikt tegen de sleutels in die index. Ze zullen worden genomen tegen alle sleutels die voldoen aan de query plus de sequentieel volgende sleutel buiten de query. Dit voorkomt dan dat gegevens worden toegevoegd die een nieuwe sleutel direct vóór een sleutel met een sleutelbereik vergrendelen zou invoegen.

Geef een antwoord

Het e-mailadres wordt niet gepubliceerd.