Simon Learning SQL Server

Simon Learning SQL Server

egy korábbi bejegyzésben a zárolási, blokkolási és elszigetelési szintekről beszéltem. Ebben a bejegyzésben részletesebben ismertetem a SOROSÍTHATÓ elszigetelési szinten használt kulcstartományú zárakat, valamint bemutatom a sys.dm_tran_locks DMV használatát a meglévő zárak megtekintéséhez, valamint azt is, hogyan lehet kitalálni, hogy milyen erőforrásokra kerülnek a zárak.

a Kulcstartományú zárakat csak a SOROSÍTHATÓ elkülönítési szinten használják, és céljuk a fantomolvasások megállítása, amelyek a másik három pesszimista párhuzamossági elkülönítési szinten lehetségesek, nevezetesen a read uncommitted, read committed és repeatable read. A fantomolvasás az, amikor az egyik tranzakció leolvasást végez egy adattartományon, például egy WHERE záradékkal rendelkező lekérdezést használ, egy másik tranzakció pedig olyan adatokat ad hozzá, amelyek megfelelnek a WHERE záradék feltételeinek. Ha az első tranzakció ugyanazt az olvasást hajtja végre, akkor visszaadja az új adatokat, amelyek eredményeként fantomadatok jelennek meg a második olvasatban ugyanazon tranzakción belül. A kulcstartomány-zárak a fantomadatok hozzáadásának megakadályozására szolgálnak, és az adatok lekérésére használt index kulcsaira kerülnek. A következő példa a személy használatával.Az AdventureWorks2012 adatbázisban található személy táblázat bemutatja, hogyan használják ezeket a kulcstartomány-zárakat.

A Személy.Személy tábla nem fürtözött index IX_Person_LastName_FirstName_Middlename a LastName oszlop az első oszlop az index kulcs. Ez azt jelenti, hogy minden olyan lekérdezésnek, ahol a vezetéknévre szűrünk, ezt az indexet kell használnia az adatok lekéréséhez.

ha futtatunk egy lekérdezést, hogy visszatérjen néhány sort személy.Személy szűrés egy sor vezetéknevek akkor meg kell látni ezt az indexet használják. Például

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

a következő eredményhalmazt adja vissza.

 Kulcstartomány 2

a végrehajtási tervet nézve láthatjuk, hogy ezt az indexet használják.

 Kulcstartomány 3

most, ha az elkülönítési szintet SERIALIZABLE értékre állítjuk, megnyitunk egy tranzakciót és futtatjuk ugyanazt a select utasítást, akkor látnunk kell, hogy milyen zárak vannak.

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

most egy másik kapcsolatból lekérdezhetjük a DMV sys.dm_tran_locks fájlt, hogy lássuk az aktuális zárakat. Csak a legfontosabb erőforrások zárai érdekelnek, ezért ennek megfelelően szűrjük.

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

Kulcstartomány 4

hét sort kapunk két különböző request_mode értékkel. Az” S ” request_mode a visszaadott három sorra helyezett megosztott záraknak felel meg. Mivel a SOROSÍTHATÓ elkülönítési szintet használjuk, ezeket a megosztott zárolásokat addig tartjuk, amíg a tranzakció el nem kötelezi vagy vissza nem gördül, hogy megakadályozza a nem megismételhető olvasásokat. A” RangeS-S ” request_mode megfelel a megadott kulcstartomány-záraknak. De miért van négy sor? Ennek megválaszolásához felhasználhatjuk a sys. dm_tran_locks fájlból visszaadott adatokat, és ezzel kideríthetjük, hogy milyen erőforrások vannak lezárva.

a minket érdeklő oszlopok: resource_associated_entity_id és resource_description. Ezekben az oszlopokban az adatok a resource_type-tól függően változhatnak, további részletekért lásd: TechNet. A legfontosabb erőforrások esetében a resource_associated_entity_id a sys partition_id-jének felel meg.partíciók

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

Kulcstartomány 5

a visszaadott object_id megfelel a személynek.Személy asztal

SELECT object_name(1765581328)

Kulcstartomány 6

láthatjuk, hogy a két különböző partition_id érték a személy különböző indexeinek felel meg.Személy

SELECT * FROM sys.indexes WHERE object_id = 1765581328

Kulcstartomány 7

a sys.partíciók nézet azt mondja, hogy a partíciókat megvan zárak vannak az indexek PK_Person_BusinessEntityID és IX_Person_LastName_FirstName_Middlename.

az egyértelműség érdekében mindezt egyetlen lekérdezésben kombinálhatjuk.

 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

Kulcstartomány 8

ez azt mutatja, hogy három megosztott zárat helyeznek magukra a sorokra (a fürtözött index levélszintje), és hogy négy kulcstartomány-zárat helyeznek a nem fürtözött index kulcsaira. De ez nem válaszolt arra a kérdésre, hogy miért van négy zárunk?

mielőtt ezt megtennénk, két módszert fogok bemutatni annak bizonyítására, hogy a három megosztott zárat az eredeti lekérdezés által visszaadott sorokra helyezzük, majd ezt használjuk annak meghatározására, hogy a négy “RangeS-S” zár milyen erőforrásokra kerül.

először a %%physloc%% és %%lockres%% értékeket fogjuk használni a sys-szel együtt.az fn_PhysLocFormatter () függvény az eredeti lekérdezés által visszaadott sorok fájljának, oldalának és nyílásának megadására szolgál. Másodszor ezeket az információkat a DBCC oldal parancsban használjuk.

csak frissíteni kell az eredeti lekérdezést az alábbiak szerint

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

Kulcstartomány 9

most már láthatjuk, hogy Dennis Ware sora fizikailag az 1. adatfájlban, a 2806. oldalon és a 3. nyílásban található. Azt is láthatjuk, hogy a zárolási erőforrás (05c585e839d4).

ha visszatekintünk a sys. dm_tran_locks-ból visszaadott adatokra, láthatjuk, hogy az egyik megosztott zár resource_description értéke (05c585e839d4), tehát ez azt mondja nekünk, hogy ez a zár a fürtözött index azon sorába kerül.

most már futtathatjuk a következő DBCC PAGE parancsot, hogy valóban láthassuk az oldalon található információkat, de ahhoz, hogy ez visszaadja az eredményeket, be kell kapcsolnunk a trace flag 3604-et.

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

itt sok szöveges információ jelenik meg, és a formátum paraméterben megadhat egy másik számot a különböző mennyiségű adat visszaadásához. Ebben az esetben az első bit az oldal fejléce, majd utána megvan az allokációs állapot. Látni fogja az első slot használt Slot 0. Van egy eltolási érték, amely megmondja, hogy az oldalon hol kezdődik ez a rés, valamint egy Hosszérték, amely megmondja, hogy mennyi ideig tart a rekord.

 Kulcstartomány 10

ha lefelé görgetünk, amíg meg nem találjuk a 3.helyet, majd az adatblokk mellett görgetünk, láthatjuk az egyes oszlopok fizikai adatait, és megerősíthetjük, hogy ez a Dennis Ware sora.

 Kulcstartomány 11

most, ha visszamegyünk a 3. rés végére, láthatjuk

KeyHashValue = (05c585e839d4)

ez megegyezik a sys.dm_tran_locks resource_description értékkel és a %%lockres%%által visszaadott értékkel.

de hogyan csináljunk valami hasonlót a kulcstartós záraknál? Honnan tudjuk, hogy milyen erőforrásokat zárolnak? Ennek egyik módja a sys. dm_db_database_page_allocations használata annak meghatározására, hogy mely oldalak tartoznak a nem fürtözött indexhez. Átadjuk az ix_person_lastname_firstname_middlename index database_id-jét, object_id-jét és index_id-jét, és az indexben használt minden oldalhoz egy sort ad vissza. Ezután futtathatjuk a DBCC oldalt a mellékelt allocated_page_page_id értékekkel, és megkereshetjük a Dennis Ware-t tartalmazó oldalt.

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

ezután futtathatjuk a DBCC oldalt a megadott értékkel, és megkereshetjük a Dennis Ware-t tartalmazó oldalt.

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

a praktikus dolog az, hogy ebben az esetben a DBCC oldal eredménykészletben adja vissza az oldal adatait, így elég gyorsan megtalálhatjuk a megfelelő oldalt, ebben az esetben a 24745 oldalt.

 Kulcstartomány 12

most, ha a resource_description értékeket használjuk a sys négy kulcstartományához.dm_tran_locks láthatjuk, hogy a” RangeS-S ” zárakat ténylegesen megadták azoknak a soroknak, amelyek Dennis Ware, Victoria Ware, Clay Warten és Aaron Washington adatait tartalmazzák. De Aaron Washington nem szerepel az eredménykészletben, akkor miért van jelen ez a negyedik kulcszár? Ez azért van, mert amikor egy kulcstartományt felvesz egy kulcsra, megakadályozza az adatok hozzáadását közvetlenül a kulcstartományú zárral rendelkező kulcs előtt. Más szóval nem adhatunk hozzá adatokat például Victoria Ware és Clay Warthen, például Jennifer Warner között.

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

Kulcstartomány 13

itt láthatjuk a várakozó kulcstartót.

ez azt is jelenti, hogy nem lehet adatokat hozzáadni Timothy Ward és Aaron Washington között, és azt jelenti, hogy a kulcstartós zárak valójában több adatot zárolnak, mint amennyit a nyitott tranzakcióban a lekérdezés kielégít. Például nem tudtunk beszúrni egy sort Simon Wartz számára, amelyet Clay Warthen és Aaron Washington közé helyeznénk, annak ellenére, hogy a nyitott tranzakcióban nem lenne visszaadva az eredeti lekérdezésben.

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

Kulcstartomány 14

most már tudjuk, hogyan használják a kulcstartomány-zárakat, ki tudjuk dolgozni, hogy milyen erőforrásokon vannak a kulcstartomány-zárak, anélkül, hogy a nem fürtözött index oldalain kellene keresni. Egyszerűen belefoglalhatunk egy táblázatot, amely visszaadja a szükséges adatok egy részét.

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

Kulcstartomány 18

mint látható, ez ugyanazt a fájlt, oldalt és slot adatokat, valamint ugyanazt a resource_description-t adja meg a lekérdezés által visszaadott három sorhoz. Most már tudjuk, hogy a negyedik “RangeS-S” zárat az index kulcsára helyezzük közvetlenül a Clay Warthen kulcs után, azaz 140.rés. Ismét használhatjuk a DBCC oldalt az adatok megtekintéséhez a 25065 oldalon.

most a bemutatóban, amelyet éppen végigfutottam, történetesen van egy nem fürtözött indexünk, amelynek a fürtözési kulcs első oszlopa az, amelyet a WHERE záradékunkban használunk. De mi történik, ha eltávolítjuk a nem fürtözött indexet? Vagy más szavakkal, mi történik, ha egy lekérdezést egy másik táblaszűréssel futtatunk egy olyan oszlopon, amely nem szerepel az indexkulcsban? Ennek egyszerű bemutatásához egyszerűen eltávolíthatjuk a nem fürtözött indexet.

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

ugyanazokat a sorokat kapjuk vissza, mint várták, de ezúttal az SQL Server-nek a fürtözött indexet kellett használnia az adatok megszerzéséhez.

 Kulcstartomány 15

milyen zárunk van? Ezúttal nincs olyan kulcstartós zár, amely alkalmazható lenne, ezért a megosztott zárat az egész asztalra kell helyezni.

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

Kulcstartomány 16

mivel ez a megosztott zárolás a tranzakció lezárásáig tart, ez azt jelenti, hogy a táblázat adatai nem frissíthetők a tranzakció befejezéséig, annak ellenére, hogy csak három sort olvastunk.

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

Kulcstartomány 17

ezzel tisztában kell lennie, ha a SOROSÍTHATÓ elkülönítési szintet használja.

Összegzés

ha egy kulcsoszlopot használnak a lekérdezés adatainak szűrésére, akkor a kulcstartomány-zárak az adott index kulcsaival összevethetők. A lekérdezést kielégítő összes kulcshoz, valamint a lekérdezésen kívüli szekvenciálisan következő kulcshoz kerülnek. Ez megakadályozza az olyan adatok hozzáadását, amelyek közvetlenül egy kulcstartós zárral rendelkező kulcs elé helyeznének egy új kulcsot.

Vélemény, hozzászólás?

Az e-mail-címet nem tesszük közzé.