Simon Learning SQL Server

Simon Learning SQL Server

edellisessä viestissä puhuin lukitsemisesta, estämisestä ja eristämisestä. Tässä viestissä aion mennä tarkemmin avain-alueen lukot käytetään SERIALISABLE eristäminen tasolla ja myös osoittaa käyttää sys.dm_tran_locks DMV tarkastella olemassa olevat lukot ja myös miten selvittää, mitä resursseja lukot on sijoitettu.

avainalueen lukkoja käytetään vain SARJAMUOTOISELLA eristystasolla ja niiden tarkoituksena on pysäyttää haamulukemat, jotka ovat mahdollisia kolmella muulla pessimistisellä samanaikaiseristystasolla, eli lukea sitomatta, lukea sitoutunut ja toistaa lukea. Phantom-luku tarkoittaa sitä, että yksi tapahtuma suorittaa lukemisen tietomäärälle, esimerkiksi käyttämällä kyselyä, jossa on WHERE-lauseke, ja toinen tapahtuma Lisää tietoja, jotka täyttävät WHERE-lausekkeen ehdot. Jos ensimmäinen tapahtuma suorittaa saman lukeman, se palauttaa nyt uudet tiedot, jolloin toisessa lukemassa esiintyy saman tapahtuman sisällä haamudataa. Avainalueen lukkoja käytetään estämään haamutiedon lisääminen ja ne sijoitetaan sen indeksin avaimiin, jota käytetään tiedon hakemiseen. Seuraavassa esimerkissä henkilön avulla.AdventureWorks2012-tietokannan henkilötaulukko osoittaa, miten näitä avainlukkoja käytetään.

Henkilö.Henkilötaulukossa on ryhmittelemätön indeksi IX_Person_LastName_FirstName_Middlename, jonka sukunimen sarake on indeksiavaimen ensimmäinen sarake. Tämä tarkoittaa, että jokainen kysely, jossa suodatamme sukunimi pitäisi käyttää tätä hakemistoa hakea tietoja.

jos suoritamme kyselyn palauttaaksemme muutaman rivin henkilöltä.Henkilö suodatus erilaisia sukunimiä sitten meidän pitäisi nähdä tämän indeksin käytetään. Esimerkiksi

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

palauttaa seuraavan tulosjoukon.

 avainalue 2

ja kun katsomme teloitussuunnitelmaa, voimme nähdä, että tätä indeksiä käytetään.

 avainalue 3

jos asetamme eristystason sarjamuotoiseksi, avaamme tapahtuman ja suoritamme saman select statement-pitäisi pystyä näkemään, mitä lukkoja pidetään.

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

nyt toisesta yhteydestä voimme tiedustella DMV sys. dm_tran_lukkoja nähdäksemme, että nykyiset lukot pidetään. Olemme kiinnostuneita vain avainresurssien lukituksista, joten suodatamme sen mukaisesti.

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

avainalue 4

saamme seitsemän riviä uudelleen kahdella eri request_mode-arvolla. ”S” request_mode vastaa jaettuja lukkoja, jotka on sijoitettu niille kolmelle riville, jotka palautetaan. Koska käytämme sarjamuotoista eristystasoa, näitä jaettuja lukkoja pidetään, kunnes tapahtuma sitoutuu tai rullaa takaisin estääkseen toistamattomat lukemat. ”RangeS-S” request_mode vastaa avainlukkoja, jotka on myönnetty. Mutta miksi rivejä on neljä? Vastataksemme tähän Voimme käyttää sys.dm_tran_locksista palautettuja tietoja ja käyttää tätä selvittääksemme, mitä resursseja on todella lukittu.

meitä kiinnostavat sarakkeet ovat resource_associated_entity_id ja resurce_description. Näiden sarakkeiden tiedot voivat vaihdella resurssi_typen perusteella ja lisätietoja tästä on Technetissä. Keskeisten resurssien osalta resursce_associated_entity_id vastaa sys: n osiota_id.väliseinät

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

avainalue 5

palautettu objekti_id vastaa henkilöä.Henkilötaulukko

SELECT object_name(1765581328)

avainalue 6

voimme nähdä, että kaksi eri partition_id arvot vastaavat eri indeksit henkilö.Henkilö

SELECT * FROM sys.indexes WHERE object_id = 1765581328

avainalue 7

sys.osiot näkymä kertoo, että osiot meillä lukot ovat indeksit PK_Person_BusinessEntityID ja IX_Person_LastName_FirstName_Middlename.

voimme yhdistää tämän kaiken yhteen selvyyspyyntöön.

 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

avainalue 8

tämä osoittaa, että kolme jaettua lukkoa on sijoitettu itse riveille (ryhmitetyn indeksin lehtitasolle) ja että neljä avainalueen lukkoa on sijoitettu ei-ryhmitetyssä indeksissä oleviin avaimiin. Mutta tämä ei ole vastannut kysymykseen, miksi meillä on neljä lukkoa?

ennen kuin teemme sen, aion osoittaa kahdella tavalla, että kolme jaettua lukkoa on sijoitettu alkuperäisen kyselyn palauttamille riveille ja sitten käyttää sitä selvittääkseni, mihin resursseihin neljä ”RangeS-S” – lukkoa on sijoitettu.

ensinnäkin aiomme käyttää %%physloc%% ja %%lockres%% arvoja yhdessä sys: n kanssa.fn_physlocformatter () toiminto kertoo meille tiedoston, sivun ja korttipaikka rivien palasi alkuperäisen kyselyn. Toiseksi käytämme näitä tietoja DBCC-PAGE-komennossa.

meidän tarvitsee vain päivittää alkuperäinen kyselymme seuraavasti

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

avainalue 9

voimme nyt nähdä, että rivi Dennis Ware on fyysisesti sijaitsee tiedosto 1, sivulla 2806 ja slot 3. Voimme myös nähdä, että sen lukitus resurssi on (05c585e839d4).

jos katsomme taaksepäin sys.dm_tran_locks-järjestelmästä palautettuja tietoja, voimme nähdä, että yksi jaetuista lukoista on resurce_description of (05c585e839d4), joten tämä kertoo, että lukko on sijoitettu kyseiselle riville ryhmitetyssä indeksissä.

nyt voimme ajaa seuraavan DBCC-SIVUKOMENTON nähdäksemme oikeasti tietoa siitä, mitä sivulla on, mutta jotta tämä palauttaisi mahdolliset tulokset, meidän on otettava trace flag 3604 käyttöön.

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

tässä näkyy paljon tekstimuotoista tietoa ja voit määrittää eri numeron format-parametrissa, jotta voit palauttaa eri tietomääriä. Tässä tapauksessa ensimmäinen bitti on sivun otsikko ja sen jälkeen meillä on Jakotilanne. Näet ensimmäinen käytetty paikka on Slot 0. On Offset arvo, joka kertoo meille, missä sivulla tämä paikka alkaa ja pituus arvot, joka kertoo, kuinka pitkä ennätys on.

 avainalue 10

jos me selaa alas, kunnes löydämme Slot 3, ja sitten selaa ohi lohkon tiedot voimme nähdä fyysiset tiedot kunkin sarakkeen ja voi vahvistaa Tämä on rivi Dennis Ware.

 avainalue 11

nyt, jos menemme takaisin loppuun Slot 3 voimme nähdä

KeyHashValue = (05c585e839d4)

tämä vastaa resource_description SYS. dm_tran_locks ja arvo palautetaan % % lockres%%.

mutta miten tehdään jotain vastaavaa avainten kantaman lukoille? Miten saamme selville, mitä resursseja he lukitsevat? Yksi tapa olisi käyttää sys.dm_db_database_page_allocations selvittää, mitkä sivut kuuluvat ulos ei-ryhmitelty indeksi. Siirrämme database_id, object_id ja index_id on ix_person_lastname_firstname_middlename indeksi ja se palauttaa rivin jokaisen sivun käytetään kyseisessä hakemistossa. Voimme sitten ajaa DBCC sivu mukana allocated_page_page_id arvot ja löytää sivun, joka sisältää 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

voimme sitten ajaa DBCC-sivun mukana toimitetulla arvolla ja löytää sivun, joka sisältää Dennis waren.

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

kätevä asia on, että tässä tapauksessa DBCC-sivu palauttaa sivun tiedot tulosjoukossa, joten löydämme melko nopeasti oikean sivun, tässä tapauksessa sivun 24745.

 avainalue 12

nyt jos käytämme resursce_description arvot meidän neljä avainalueen lukot sys.dm_tran_locks voimme nähdä, että” RangeS-s ” lukot on todella myönnetty rivit, jotka sisältävät tietoja Dennis Ware, Victoria Ware, Clay Warten ja Aaron Washington. Mutta Aaron Washington ei ole mukana tulosjoukossa, joten miksi tämä neljäs avainlukko on mukana? Se johtuu siitä, että kun avainalue otetaan avaimeen, se estää tietojen lisäämisen juuri ennen avainta, jossa on avainalueen Lukko. Toisin sanoen tietoja ei voida lisätä esimerkiksi Victoria Waren ja Clay Warthenin, esimerkiksi Jennifer Warnerin, välille.

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

avainalue 13

Tässä näemme odottavan avaimen kantaman lukon.

se tarkoittaa myös sitä, että Timothy Wardin ja Aaron Washingtonin välille ei voi lisätä tietoja, ja tarkoittaa, että avainten kantaman lukot todella lukitsevat enemmän dataa kuin mikä voisi täyttää kyselyn avoimessa kaupassa. Esimerkiksi Simon Wartzille ei voitu laittaa riviä, joka laitettaisiin Clay Warthenin ja Aaron Washingtonin väliin, vaikka sitä ei palautettaisi alkuperäisessä kyselyssä avoimessa kaupassa.

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

avainalue 14

nyt tiedämme, miten avainalueen lukkoja käytetään, voimme selvittää, mitä resursseja on avainalueen lukkoja niihin ilman tarvetta etsiä ei-ryhmitetyn indeksin sivuilta. Voimme yksinkertaisesti sisällyttää taulukkovihjeen palauttaaksemme osan tarvitsemistamme tiedoista.

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

avainalue 18

kuten näette, tämä antaa meille saman tiedoston, sivun ja slot tiedot ja samat resource_description kolme riviä palasi kyselyn. Nyt tiedetään, että neljäs ”RangeS-S” – lukko on sijoitettu avaimeen indeksissä heti savisammalen avaimen jälkeen eli rakoon 140. Jälleen Voimme käyttää DBCC sivu tarkastella tietoja sivulla 25065.

now in the demonstration I ’ve just run through we satut have a non-clustered index with the first column in the clustering key is the one that we’ re using in our WHERE clause. Mutta mitä tapahtuu, jos poistamme ryhmittelemättömän indeksin? Tai toisin sanoen, mitä tapahtuu, jos ajoimme kyselyn vastaan toisen taulukon suodatus sarakkeeseen ei sisälly indeksinäppäin? Tämän osoittamiseksi voimme vain poistaa ei-ryhmitetyn indeksin.

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

saamme odotetusti samat rivit takaisin, mutta tällä kertaa SQL Server on joutunut käyttämään ryhmitettyä indeksiä tietojen saamiseksi.

 avainalue 15

mitä lukkoja meillä on? Tällä kertaa avainlukkoa ei voi käyttää, joten jaettu lukko on asetettava koko pöydälle.

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

avainalue 16

koska tämä jaettu lukitus pidetään, kunnes tapahtuma on suljettu, tämä tarkoittaa, että mitään taulukon tietoja ei voida päivittää ennen tapahtuman päättymistä, vaikka olemme lukeneet vain kolme riviä.

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

avainalue 17

tämä on jotain sinun täytyy olla tietoinen, jos käytetään SERIALISABLE eristäminen tasolla.

Yhteenveto

jos avainsaraketta käytetään datan suodattamiseen kyselyssä, voidaan avainlukkoja käyttää kyseisen indeksin avaimia vastaan. Ne otetaan kaikkia avaimia vastaan, jotka täyttävät kyselyn, sekä kyselyn ulkopuolella olevaa peräkkäistä avainta vastaan. Tämä estää tietojen lisäämisen, joka asettaisi uuden avaimen juuri ennen avainta, jossa on avainalueen Lukko.

Vastaa

Sähköpostiosoitettasi ei julkaista.